…PRIMARY KEY (`transactionid`), KEY `marketsegment` (`price`,`customerid`), KEY `registersegment` (`cashregisterid`,`price`,`customerid`), KEY `pdc`…contention problems caused by buffer pool mutexes, using “estimate” checkpoint method to reduce chances of log flush storms and disabling the query cache…
Post: Beware of MyISAM Key Cache mutex contention
August 12, 2008 By 10 Comments
… 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: Using Multiple Key Caches for MyISAM Scalability
November 26, 2008 By 8 Comments
… 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… also 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: MySQL caching methods and tips
April 4, 2011 By 4 Comments
…mutex contention. Percona Server even has a state in the processlist ‘waiting on query cache mutex‘ so that this is easier to spot. External cache… the cache key expires, the contents are available immediately and you avoid performing work in the database. Eventually the cache contents expire. When…
Comment: The Doom of Multiple Storage Engines
May 17, 2010 By 22 Comments
… “all selects are faster” – MyISAM has serve problems with contention on the key cache mutex limiting scalability dramatically as well as slow access to… access is done by index because it is only cached in OS cache. What you seems to be missing is if application…
Post: MyISAM Scalability and Innodb, Falcon Benchmarks
October 12, 2007 By 10 Comments
… on 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… where these mutex lock requests come from. A second confirmation that key_cache is a problem is benchmark run with disabled key_cache (=0). Results…
Post: Ultimate MySQL variable and status reference list
January 8, 2011 By 4 Comments
…content…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…
Comment: Another scalability fix in XtraDB
January 24, 2009 By 13 Comments
… or something like it. When given thing was not a contention I assume he just kept it simple. As we get a lot of cores a lot of these contentions become exposed. Some in particular contention on buffer pool pages fixed by Innodb… – how could it get through the years with single mutex on table cache or key cache ? Look at Linux kernel with arguably most popular…
Comment: Should we give a MySQL Query Cache a second chance ?
April 10, 2011 By 63 Comments
… the query cache implementation – and for sure is a large contributor to lock contention. 2 things… schemas on the same instance contending query cache mutex. Second… needless invalidation of results sets unrelated … of the time, this will be primary keys in a properly normalized database, however I…
Comment: Should we give a MySQL Query Cache a second chance ?
April 10, 2011 By 63 Comments
… the query cache implementation – and for sure is a large contributor to lock contention. 2 things … schemas on the same instance contending query cache mutex. Second… needless invalidation of results sets unrelated … of the time, this will be primary keys in a properly normalized database, however I …
