… but Key Cache Mutex will still hunt you. If you aware of MySQL history you may think Key Cache scalability was fixed with new Key Cache in MySQL… to mysql directly as explained here to create key caches. Now you can use much more simple command to assign tables to the key caches: mysql> select concat(“CACHE INDEX “,table…
Post: Why you should ignore MySQL's key cache hit ratio
February 28, 2010 By 28 Comments
… tuning MySQL‘s key cache hit ratio is wrong, even when you hear it from experts. There are two major problems with the key… key buffer, key_buffer, and key cache interchangeably. However, I will be careful about the difference between “rate” and “ratio”. In this article, the key cache… scientific and authoritative — the “key cache hit ratio.” This is a shame. When you are new to MySQL, trying to configure my.cnf…
Post: Ultimate MySQL variable and status reference list
January 8, 2011 By 4 Comments
…MySQL manual, …key_buffer_sizeblogpercona.commanual 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…
Post: Crashes while using MyISAM with multiple key caches
July 18, 2009 By Leave a Comment
… couple of years I have ran into random MySQL crashes in production when multiple key caches were used. Unfortunately this never was frequent… multiple key caches but to online key cache resize. It is just this code most actively used in case you’re using multiple key caches. It is very rare one would resize single key cache in production and it only triggers…
Post: Getting annoyed with MyISAM multiple key caches.
May 1, 2009 By 11 Comments
… key cache parameters – via structured variables In particular I would like to: See the list of created caches Right now I can create key caches with random names causing invisible resource consumption. It is possible to make an error in key cache… is surely sad to see the feature of multiple key caches first introduced in MySQL 4.1 was newer quite polished in terms…
Post: Beware of MyISAM Key Cache mutex contention
August 12, 2008 By 10 Comments
… rate. Hundreds of millions rows are loaded daily into single MySQL instance with bursts up to 100K of records/sec which… discussed MyISAM key cache has serious mutex contention issue as there is global mutex which is held for the time of key_block being copied from key_cache to the thread local space. Happily MyISAM allows you to create multiple key caches . We use…
Post: MySQL Server Variables - SQL layer or Storage Engine specific.
June 8, 2006 By 14 Comments
…MySQL versions did not bother naming variables with storage engines prefix. This is why we have key_buffer, not “myisam_key…key_cache_age_threshold, key_cache_block_size, key_cache_division_limit Key Cache/Key Buffer replacement algorithm configuration variables. Obviously only apply to MyISAM as key…
Post: Innodb Caching (part 2)
May 10, 2011 By 7 Comments
… wrote about Innodb Caching with main idea you might need more cache when you think you are because Innodb caches data in pages… in cache any more. For 16K pages we see over 10x drop from 7630 qps to 580 qps as number of keys… for 16K and 16K Compressed to be very similar on MySQL 5.5 As a summary: If you have very random…
Post: MySQL 6.0 vs 5.1 in TPC-H queries
March 25, 2008 By 17 Comments
…cached in key_cache) What is difference in 6.0? Here is execution plain for 6.0: +—-+————-+———-+——-+—————+—————+———+——+———+———————————————–+ | id | select_type | table | type | possible_keys | key | key…
Post: Implications of Metadata Locking Changes in MySQL 5.5
February 1, 2013 By 9 Comments
… in the Table Cache, any query to a table that has a DDL operation waiting, will block. Why? Because MySQL sees that the old entries in the Table Cache have to be invalidated, and any…(i int(11) not null auto_increment primary key) ENGINE=InnoDB; When using MySQL 5.1 the second CREATE would just fail…
