![]() MySQL Query Cache monitor by LogicmonitorĮven with a nicely tuned query cache, there’s still around 10% to 15% overhead required to maintain it. However, eventually, MySQL Query Cache was completely disabled on that 32GB server for improved performance (read on): query_cache_type = 0 I could have disabled Query Cache completely, but with the new settings, there’s still a +70% hit rate. Reducing MySQL Query Cache to 100 megabytes and lowering “query_cache_min_res_unit” and “query_cache_limit” solved the severe locking issues. This causes PHP-FPM spikes as they wait on MySQL. On the 32GB server mentioned above, with MySQL Query Cache Size set to 4GB, there were 100’s, sometimes 1000’s of queries with status “Waiting for query cache lock”. Instead, it begins to eat away at throughput. As a result, the larger the query cache, the more system time is used for locks, flushes, and overhead until cache management eventually negates any benefit of MySQL’s query cache. This happens even when there’s free query cache space available. In addition, any insert, update, delete, or other modifications to a table causes any relevant entries in the query cache to be flushed. Cacheable queries take out an exclusive lock on MySQL’s query cache. This is because of cache overhead and locking. Why? Because a query_cache_size of 4 gigabytes is a good example of how query caching cripples performance when trying to scale.Ī large query cache size leads to significant performance degradation. All of this without setting MySQL’s query cache size too large. Set MySQL Query Cache Size no larger than 100 to 200MB!Ī MySQL query cache size of 200 megabytes may even be too large! The key is to start very small (maybe 10mb), then increase in small increments while trying to keep a high ratio of query cache hits and also a low amount of query cache low memory prunes. This is an extremely common misconception and understandably so, because query cache sizing depends a lot on your database size, database query types, the ratio of database reads vs writes, database traffic, hardware, etc. Since the server had free RAM available, setting MySQL Query Cache Size very large would reduce cache prunes, thus increasing performance. The thought behind it seemed to be that more is better. This post references a web server with 32 gigabytes of RAM where the existing config had MySQL’s query cache size set incorrectly to 4 gigabytes. One of the most misconfigured MySQL performance features is MySQL query_cache_size. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |