… MySQL 5.6 and MariaDB 5.5. Note that query cache is disabled during these benchmark runs and that the disks… of MRR when the optimizer is choosing the query execution plan, is not sufficiently tuned and it is recommended to turn… at what are the good and bad query execution plans. a. Bad Plan id select_type table type possible_keys key key…
Post: The MySQL optimizer, the OS cache, and sequential versus random I/O
April 28, 2008 By 17 Comments
… every column in the WHERE clause. Here’s the query plan initially. *************************** 1. row *************************** table: dim1 type: range key_len: 195… random I/O operations. In contrast, the optimizer chose a plan that it thought would cause only 11.3 million random…, our STRAIGHT_JOIN plan would take more than a month to complete! Good thing we know the difference between cache and disk!
Post: MySQL Query Cache
July 27, 2006 By 69 Comments
… Query Cache is the same as Oracle Query Cache – meaning cache where execution plans are cached. MySQL Query Cache is not. It does not cache the plan but…. Lets talk a bit about features and limitations of Query Cache: Transparent Caching – Caching is fully transparent to the application, and what is…
Post: More on dangers of the caches
September 23, 2010 By 5 Comments
… as change in query plan or increase in search engine bot activity. It may be RAID writeback cache disabled due to battery… gradually recover by populating cache – if because of cache misses performance is so bad the speed of populating cache with new items is… this, besides cache design related issues I mentioned in the previous post. Include Rollback in Maintainance Window Ensure you plan the maintainance…
Post: Caching could be the last thing you want to do
July 24, 2010 By 14 Comments
…, but it is somewhat hidden. Cache management is really hard – have you planned for cache stampeding, or many cache items being invalidated at the same time? What alternative approach should be taken? Caching should be…
Post: Cache Miss Storm
September 10, 2010 By 29 Comments
… this problem ? You should carefully watch frequently accessed cache items as well as cache items which take long to generate in case… getting slow over time as data size growths or execution plan changes, you can also have some items becoming hot unexpectedly… better wait rather than starting populating it. For MySQL Query Cache the solution should have been to make queries wait on…
Post: Crashes while using MyISAM with multiple key caches
July 18, 2009 By Leave a Comment
… ran into random MySQL crashes in production when multiple key caches were used. Unfortunately this never was frequent or critical enough… bug is not really related to multiple key caches but to online key cache resize. It is just this code most actively… 4.1 still did not have it fixed. It was planned for it to be backported in MySQL 5.0 after…
Post: What MySQL buffer cache hit rate should you target
June 29, 2006 By 5 Comments
… looking for. First thing which needs to be understand is – cache hit ratio can be computed differently for different engines. For… were these reads/writes, how good was OS or SAN cache hit rate – something you would not see otherwise. The bad… – MySQL currently does not provide this information (it exists in plans). The next best thing would be to look at the…
Post: Reality of Innodb Caching
April 21, 2011 By 8 Comments
I have mentioned few times Innodb caches data in pages and even if you have working set … once before timing, so when there is enough memory to cache every single row there should not be any disk reads… can show better results in such benchmark. Falcon with plans for row cache would fair better, so I would expect better results…
Post: How Percona does a MySQL Performance Audit
November 24, 2008 By 17 Comments
…+———————————–+———————-+ | Aborted_clients | 205174 | | Aborted_connects | 29 | | Binlog_cache_disk_use | 0 | | Binlog_cache_use | 9630066 | | Bytes_received | 38563413074 | | …about it (execution times, etc), the EXPLAIN plan, and the desired modifications to the …
