June 18, 2013

Post: Understanding the maximum number of columns in a MySQL table

… developed an in-house database named UNIREG. UNIREG was a text-based program for entering records into an ISAM-style database… can create. If you try setting the storage engine to InnoDB however, you will get an error message! The exact error… from ha_innodb.cc that enforces that limit: if (form->s->fields > 1000) { /* The limit probably should be REC_MAX_N_FIELDS – 3…

Post: Data compression in InnoDB for text and blob fields

… the TEXT field was compressed which we felt indicated there would be a benefit derived from table compression. With the original InnoDB Antelope… than 16KB) TEXT fields  off-page so we utilized the KEY_BLOCK_SIZE=16 directive.  This means that each TEXT / BLOB field that exceeds… the TEXT fields were not part of the query request due to Barracuda  not storing 768 bytes of the blob on field, and…

Post: Real-Life Use Case for "Barracuda" InnoDB File Format

… huge innodb table with a set of TEXT fields. We’ve been trying to optimize this server before by playing with various innodb parameters… pages down to 8kb) new innodb plugin performs TEXT/BLOB/VARCHAR fields compression off-page so every large TEXT field could become as small as… not so different because most of our data was in TEXT fields and there weren’t many other indexes aside from PRIMARY…

Post: Heikki Tuuri Innodb answers - Part I

… may not see a “free space” in Innodb tablespace to grow significantly. Q6: Does Innodb policy replacement algorithm takes into account page… MySQL 5.1 Q15: How frequently does Innodb fuzzy checkpointing is activated HT: InnoDB flushes about 128 dirty pages per flush. That… InnoBD handles blobs/text fields (needs more info) According to the documentation, InnoDB put first 768 chars of each text/blob in the page…

Post: Quickly preloading Innodb tables in the buffer pool

… more information. But lest first start with feature request for Innodb Team: All ways I mention here are hacks and they… native support. It would be great if Innodb would implement command to preload table to Innodb buffer pool, which would simply go… in memory neither it does externally stored objects – BLOB and TEXT fields. If you would like some non PRIMARY Indexes preloaded you…

Post: Innodb row size limitation

TEXT, ftitle TEXT NOT NULL, fsubtitle TEXT NOT NULL, fcontent TEXT NOT NULL, fheader TEXT, ffooter TEXT, fdisclaimer TEXT, fcopyright TEXT, fstylesheet TEXT, fterms TEXT, PRIMARY KEY (id) ) Engine=InnoDB…. For example, using the single BLOB field with COMPRESS/UNCOMPRESS can yield great results….

Post: Recover BLOB fields

… BLOB, TEXT were not supported by Percona InnoDB Recovery Tool. The reason consists in a special way InnoDB stores BLOBs. An InnoDB table is…) unsigned NOT NULL, `NAME` varchar(120), `N_FIELDS` int(10), PRIMARY KEY (`ID`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 Here COMPACT format is…_EXTERN_PAGE_NO is FIL_NULL. Percona InnoDB Recovery Tool supports now recovery of long fields. It is still in development branch…

Comment: InnoDB Full-text Search in MySQL 5.6: Part 2, The Queries!

… I have above, I can leave out any of the text fields entirely and still get the same results: mysql 5.5… +peterson arizona’ IN BOOLEAN MODE) AS score FROM dir_test_innodb ORDER BY 2 DESC LIMIT 5; +——–+——————–+ | id | score | +——–+——————–+ | 741223 | 59…

Post: MySQL-Memcached or NOSQL Tokyo Tyrant - part 1

… lookup up a row in another table based on a text field ( called email address ). Seems simple enough right?  My two… lookup up a row in another table based on a text field ( called email address ). Seems simple enough right? My two tables… of 1GB, 2GB, and 4GB. For these tests I left Innodb with a 256M buffer pool, or roughly with 9% of…

Post: Data Corruption, DRBD and story of bug

… to InnoDB-plugin/XtraDB, but not to regular InnoDB ( i.e in MySQL 5.0) In short, if you use big BLOBS ( TEXT, MEDIUMBLOB, etc) (that allocated in external segment in InnoDB), you can get your database in… let me reiterate my points: – if you have BLOB/TEXT fields in your InnoDB-plugin schema, it is recommended to upgrade to 5…