Today I was working with the client loading data to MyISAM tables at very high rate. Hundreds of millions rows are… already 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: Why you should ignore MySQL's key cache hit ratio
February 28, 2010 By 27 Comments
… 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… “your key cache hit ratio should be very high, ideally above 99.9%” or “if your key cache hit ratio is low, your key_buffer… to do a Key_read as a Key_read_request, what use would the key buffer be anyway? Let’s trust MyISAM‘s creators…
Post: MyISAM Scalability and Innodb, Falcon Benchmarks
October 12, 2007 By 10 Comments
… Key Read Request. This lock is per key cache so if you have contention while multiple indexes are used you can create multiple key caches…. A second confirmation that key_cache is a problem is benchmark run with disabled key_cache (=0). Results for MyISAM with key_buffer_size=0 Threads… is comparison of MyISAM Innodb and Falcon results in the graphical form: I’ve created bug, for MyISAM key cache contention issue and lets…
Post: Getting annoyed with MyISAM multiple key caches.
May 1, 2009 By 11 Comments
… few times using multiple key caches is a great way to get CPU scalability if you’re using MyISAM. It is however very annoying – this feature really looks half baked to me. The problem with multiple key caches and… relevant is MyISAM with MariaDB on a way but this is surely sad to see the feature of multiple key caches first introduced…
Post: Using Multiple Key Caches for MyISAM Scalability
November 26, 2008 By 8 Comments
I have written before – MyISAM Does Not Scale, or it does quite well – two main … 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… use multiple Key Caches to reduce or virtually eliminate key cache contention. Too bad you can only map single table to single key cache – it would…
Post: Crashes while using MyISAM with multiple key caches
July 18, 2009 By Leave a Comment
… 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… less than 1% of people would resize key cache while server is up or use multiple key caches. The fix of it is not…
Post: Using MyISAM in production
June 17, 2006 By 35 Comments
… you have decent myisam_sort_buffer_size and large myisam_max_sort_file_size otherwise recovery may be done by key_cache rather than sort which can take even longer. Be careful with myisam_recover. This is great… watch out. Lack of row cache. MyISAM tables only have indexes cached in key_buffer while data is cached in OS cache. It is performance issue…
Post: To pack or not to pack - MyISAM Key compression
May 13, 2006 By 3 Comments
MyISAM storage engine has key compression which makes its indexes much smaller, allowing better fit in caches and so improving performance dramatically. Actually…) NOT NULL default ”, KEY `c` (`c`), KEY `id` (`id`) ) ENGINE=MyISAM Index size: PACK_KEYS=DEFAULT – 1550K PACK_KEYS=1 – 1453K PACK_KEYS=0 – 8176K As…
Post: MySQL Crash Recovery
July 30, 2006 By 26 Comments
… caches. This may reduce performance dramatically. So if you’re bringing server back after crash you might want to populate caches. For MyISAM key_cache this can be done by using LOAD INDEX INTO CACHE statement, for other storage engines…
Post: What MySQL buffer cache hit rate should you target
June 29, 2006 By 5 Comments
…What cache hit rate is good for optimal MySQL Performance” is typical question I’m asked. It could by MyISAM key_buffer or… looking for. First thing which needs to be understand is – cache hit ratio can be computed differently for different engines. For… done. In partuclar you should be looking at Key_reads and Key_writes for MyISAM tables (note this does not include row data…
