… 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… compressed block this is not going to work and MySQL will need to scan keyblock from the start uncompressing keys to find matching key… 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: Innodb Caching (part 2)
May 10, 2011 By 7 Comments
… to see from the graph but with large number of keys compressed results were best of all, probably because the data size…, though I expect results for 16K and 16K Compressed to be very similar on MySQL 5.5 As a summary: If you have very random access pattern with small rows consider using 4K pages, compression…
Post: Ultimate MySQL variable and status reference list
January 8, 2011 By 4 Comments
…MySQL manual…key_cache_age_thresholdblogpercona.commanual key_cache_block_sizeblogpercona.commanual key_cache_division_limitblogpercona.commanual Key_read_requestsblogpercona.commanual Key_readsblogpercona.commanual Key_write_requestsblogpercona.commanual Key… slave_compressed_protocolblogpercona….
Post: Should we give a MySQL Query Cache a second chance ?
April 10, 2011 By 63 Comments
… applications. Many applications in MySQL have very large number of queries doing updates through primary key and use primary key for selects. I… is highly compressible and light compression can compress results at faster than wire speed for 1Gb networks. Plus if we can get MySQL protocol…
Post: Using CHAR keys for joins, how much is the overhead ?
June 18, 2007 By 18 Comments
…` int(10) unsigned NOT NULL, KEY `i` (`i`), KEY `j` (`j`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 mysql> select sum(t1.i+t2.j… 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: 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…=16 –max-requests=0 –rand-type=uniform –max-time=1800 –mysql-user=root –report-interval=10 run this will report us… sec. I expected some overhead for compressed tables, but not such: the throughput with compressed tables are 30 transactions per sec. This…
Post: Shard-Query EC2 images available
May 11, 2011 By 2 Comments
…KEY `DepDelay` (`DepDelay`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT=’Contains all avaialble data from 1988 to 2010′; mysql> use ontime1; Database changed mysql…the excellent compression that RLE techniques provide. I have not loaded InnoDB compressed tables yet but since InnoDB compression is …
Post: MySQL Users Conference - Innodb
May 9, 2007 By 6 Comments
… may well have very different compression ratio. As Innodb indexes are not prefix compressed they have very good compression potential. I also would like to see some stats about compressed tables, such as… similar problems in INSERT IGNORE and ON DUPLICATE KEY UPDATE cases. Now In MySQL 5.1+ it is also possible to fix…
Post: Shard-Query turbo charges Infobright community edition (ICE)
May 6, 2011 By 22 Comments
…( airport_id int auto_increment primary key, unique key(airport_code) ) as select Origin as… has tens of millions of flights: mysql> select count(*) from ontime_one.ontime_fact; +———–+ | count(*) | +———–+ |… 23GB of data. After loading, ICE compressed this data to about 2GB. The…
Post: High-Performance Click Analysis with MySQL
December 22, 2008 By 13 Comments
…files don’t store as compactly on disk as [Compressed] MyISAM or Archive. These are just some ideas …. There are already inconvenient limitations on some key parts of partitioning, such as maintenance and repair … work by either using MySQL 5.1′s row-based replication, or in MySQL 5.0 and earlier, …
