… bit more details about packed keys and performance implications it causes. First lets see how key compression works. Key compression applies on the block boundaries… PACK_KEYS=0|1|DEFAULT. Value 0 disables compression for all keys. Value 1 forces it for all keys. Value DEFAULT will use key compression for…
Post: Using CHAR keys for joins, how much is the overhead ?
June 18, 2007 By 18 Comments
… as MyISAM uses key compression for varchar columns so random key lookups become significantly slower. I tried to set pack_keys=0 which typically… may suffer significantly if key compression is not disabled Joining on Shorter CHAR keys is significantly faster than Long keys Latin1 (I guess any…
Post: Testing InnoDB "Barracuda" format with compression
April 23, 2008 By 3 Comments
…’ as compressed field, doing compress()/uncompress() to store/restore. To test InnoDB compression I use only uncompressed text, though I tested compression on compress()-ed… index compression. For test I use dump created with mysqldump, final size 30286M. To create InnoDB compression table I use ENGINE=InnoDB KEY_BLOCK_SIZE=8 and to test fast index creation I create table only with primary key…
Post: Innodb Caching (part 2)
May 10, 2011 By 7 Comments
… which is about 40% more keys. Where Compression results were good though is with very low number of keys, when everything could be… to see from the graph but with large number of keys compressed results were best of all, probably because the data size… acceptable performance with 9216 keys touched, 4K with 27518 and 16K compressed with 11516. This gives us 3x more keys we can fit…
Post: Compression for InnoDB backup
March 16, 2009 By 19 Comments
… last version of xtrabackup and compress it I noticed that gzip is unacceptable slow for both compression and decompression operations. Actually Peter… raw results are available here http://spreadsheets.google.com/ccc?key=pOIo5aX59b6biPZ0QTVMXHg&hl=en, and I copy table in place in… in all cases Level 1 of compression shows better tradeoff between size of archive and compression/decompression time There is no obvious…
Post: InnoDB compression woes
May 20, 2011 By 14 Comments
… regular tables: 19693 sec, for compressed tables: 38278 sec. Compressed tables are create as: ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8, with final size… sec. I expected some overhead for compressed tables, but not such: the throughput with compressed tables are 30 transactions per sec. This…_mutex’ Apparently using compressed tables we have very strong contention in LRU_list_mutex. I should check how compressed tables perform in…
Post: Redundant index is not always bad
August 28, 2007 By 7 Comments
…` varchar(3) NOT NULL DEFAULT ”, PRIMARY KEY (`id`), UNIQUE KEY `email` (`email`), KEY `country_id` (`country_id`), KEY `state_id` (`state_id`), ) ENGINE=MyISAM… key-compression for VARCHAR columns, so makes things worse). So to have good performance for both case we should leave both indexes: KEY…
Comment: Countless storage engines
May 4, 2007 By 6 Comments
… features. I however agree the lack of clustering by primary key support and not being able to use covering indexes means… be adequate. Solid also has clustering by primary key and unlike Innodb has key compression (one of major Innodb problems in my opinion…
Comment: Working with large data sets in MySQL
July 18, 2007 By 12 Comments
Right. You can’t use large limits because skipping rows becomes expensive. Also DESC can be slower for MyISAM Tables with key compression you can set PACK_KEYS=0 for this table and check if it helps. Anyway this becomes rather offtopic for this post 
Post: Heikki Tuuri Innodb answers - Part I
October 26, 2007 By 17 Comments
… go with gzip compression instead of implementing “classical” approach of index prefix compression ? HT: The reason is that index prefix compression prevents one from using a binary search on a B-tree node. We could not compare a search key K… whole record R. Also, a gzip compression potentially compresses the database more than just prefix compression. The downside in gzip is increased usage…
