… 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 4 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 30 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…
Post: MySQL MyISAM Active Active Clustering - looking for trouble ?
March 15, 2007 By 4 Comments
… Active Clustering and MyISAM tables if you follow certain rules like enabling external locking, disabling query cache and delayed key writes etc. This… locks besides extra overhead you get from external locking. Plus key cache have to be invalidated with remote writes which makes even… was not repairing corrupted tables in this configuration even if myisam_repair is set. Space Saving ? As I mentioned replication can…
Post: Should you move from MyISAM to Innodb ?
January 12, 2009 By 59 Comments
…. On the benefits side we usually see clustering by primary key, caching data, higher concurrency, background flushes while on the drawbacks side… for you when you’re running MyISAM but instantly break upon upgrading to Innodb. Features The MyISAM features which forbid moving to… defaults. I’ve seen hundreds of GB databases ran with MyISAM with default settings and it worked reasonably. Innodb needs resources…
Post: Beware: key_buffer_size larger than 4G does not work
February 14, 2007 By 17 Comments
… or RAM running MyISAM, so they set key_buffer_size to 16G… and every few days MySQL crashes. Why ? Because key_buffer_size… just fine until you have less than 4GB worth of key cache used and then it would crash. If you do not… not only fix was not made available but even maximum key_buffer_size was not restricted to safe limit. Former would…
Comment: 128GB or RAM finally got cheap
August 4, 2008 By 14 Comments
Is that memory usable with MySQL, or do you use if for a large OS buffer cache? 128GB in the MyISAM key cache protected by one mutex doesn’t sound like a winner. I am not sure if 128GB in the InnoDB buffer cache protected at times by one mutex would be much better.
Comment: MySQL Query Cache
March 12, 2007 By 70 Comments
The MyISAM key cache retains index blocks in RAM for fast access, with both default and custom-created caches being available. Its just like the code cache in oracle.
Comment: MyISAM Scalability and Innodb, Falcon Benchmarks
August 12, 2008 By 10 Comments
[...] thousands of context switches per second with far less work done than one would hope. As we already discussed MyISAM key cache has serious mutex contention issue as there is global mutex which is held for the [...]
