… significant size in the table it often makes sense to keep it compressed. Unfortunately MySQL does not provide compressed BLOB/TEXT columns (I… you with true data size and performance gains, especially for Innodb tables. With Innodb tables BLOB gets its own page (allocated outside of clustered…
Post: My Innodb Feature wishes
May 4, 2006 By 30 Comments
… MyISAM and Innodb is huge for read only workload it is caused by the fact Innodb does not have prefix compression for its… like page compression but it would only give up to 2 times space compression. Insert buffer for delete. For Insert operation Innodb uses… and promoting real one to unique. Tablespaces. To call “innodb_file_per_table” option tablespaces is a joke. It is better than…
Comment: Adventures in archiving
February 14, 2013 By 16 Comments
… been playing around with innodb compression and KEY_BLOCK_SIZE. One should really try *all* permitted values. Most tables I’ve tested look…; insert into $table_dynamic select * from $table limit 100000; #drop table if exists $table_01; create table $table_01 like $table; alter table $table_01 row_format=compressed key_block…
Post: INFORMATION_SCHEMA tables in the InnoDB pluggable storage engine
May 24, 2008 By Leave a Comment
…_SCHEMA (INNODB%) | +—————————————-+ | INNODB_CMP | | INNODB_CMP_RESET | | INNODB_CMPMEM | | INNODB_CMPMEM_RESET | | INNODB_LOCK_WAITS | | INNODB_LOCKS | | INNODB_TRX | +—————————————-+ The _CMP tables show statistics about compression; they…
Post: Innodb Caching (part 2)
May 10, 2011 By 7 Comments
… picking the random set of primary key values from sysbench table and reading them over and over again. This time I… trying standard Innodb page size, 4KB page size as 16K page size compressed to 4. The data in this case compresses perfectly (all…. Compression results come as a huge disappointment. The compressed .idb file was only 620MB so if Innodb would chose to keep only compressed pages…
Post: To pack or not to pack - MyISAM Key compression
May 13, 2006 By 4 Comments
… performance implications it causes. First lets see how key compression works. Key compression applies on the block boundaries – first index value is… for Innodb for example difference is not so large. Let us try couple of benchmarks now. I’ve created simple table with… packing them it is almost no brainer Summary: Key prefix compression is cool feature and may give great space savings. Defaults…
Post: Compression for InnoDB backup
March 16, 2009 By 19 Comments
… results also by request from comments. For compression test I took ~12GB of InnoDB data files generated by tpcc benchmark with 100….google.com/ccc?key=pOIo5aX59b6biPZ0QTVMXHg&hl=en, and I copy table in place in case if Google stops to work. threadslevelcompressed… in all cases Level 1 of compression shows better tradeoff between size of archive and compression/decompression time There is no obvious…
Comment: InnoDB compression woes
May 20, 2011 By 14 Comments
… did InnoDB cache the table in the buffer pool, it was also able to cache uncompressed copies of all data for the compressed tables and performance for compressed tables was the same as for the uncompressed table.” http://www.facebook.com/notes/mysql-at-facebook/innodb-compression-for…
Post: Join performance of MyISAM and Innodb
May 29, 2006 By 6 Comments
… id, i and C columns appropriately. CREATE TABLE `t1` ( `id` int(10) unsigned NOT NULL default ‘… together with index and generally highly optimized Innodb builds hash indexes which helps to speed …and using hash, which is faster MyISAM does compression for character keys which makes it perform …
Post: Announcing Percona Server for MySQL version 5.5.29-30.0
February 26, 2013 By Leave a Comment
… an INSERT or UPDATE statement containing BLOB values for a compressed table. This regression was introduced in Percona Server 5.5.28… fixed #1083058 (Laurynas Biveinis). Upstream changes made to improve InnoDB DROP TABLE performance were not adjusted for XtraDB. This could cause server…
