A deep analisys of a performance bottleneck could be long and is based on different kind of data (OS metrics, IO performance tests, DB wait stats and so on…). Before starting with this (or simply after a new delivery) some simple checks could help you to correct rapidly some of the common (and bigger) bottlenecks you can find in a low-performance installation.
One of this is cache sizing.
A cache disabled or not correctly configured is a common reason of low-performance problems.
MySQL engine use a result-caching to improve query speed. A query result is cached in memory to avoid slow IO reading operations. Mysql use the query_cache_size parameter to size the engine cache. A result is saved in the cache only if it’s larger then the query_cache_min_res_unit parameter but smaller then the query_cache_limit.
A query result is kept in memory until:
– the result is valid (data are not modified)
– there are no more-used results using spaces in cache dedicated memory
A good sizing of cache instance parameters is based on instance activity. My suggestion are:
– Start with a simple configuration using system memory available (avoiding paging). note: by default mysql cache is DISABLED
– Start keeping query_cache_min_res_unit little.
– Check istance metrics after some days of normal database activity to understand how you can tune better cache parameters
This is an example of an installed configuration:
mysql> show variables like '%query_cache%'; +------------------------------+----------+ | Variable_name | Value | +------------------------------+----------+ | have_query_cache | YES | | query_cache_limit | 2097152 | | query_cache_min_res_unit | 4096 | | query_cache_size | 33554432 | | query_cache_type | ON | +------------------------------+----------+ 5 rows in set (0.00 sec)
After some days of normal instance activity start watching some metrics to understand whats heppening.
Those values are self explained
mysql> show status like 'QCache%'; +-------------------------+----------+ | Variable_name | Value | +-------------------------+----------+ | Qcache_free_blocks | 957 | | Qcache_free_memory | 15523704 | | Qcache_hits | 1978478 | | Qcache_inserts | 132298 | | Qcache_lowmem_prunes | 6037 | | Qcache_not_cached | 5969 | | Qcache_queries_in_cache | 3389 | | Qcache_total_blocks | 8396 | +-------------------------+----------+ 8 rows in set (0.00 sec)
Talking about performances there are no “top configuration” usable. You have to work with users and system integrators to understand:
– Any application suggested cache configuration
– In low performance situations: what is the performance target ?
– Are there any test query usable to test configuration tuning?
For a deeper explanation of all mysql cache parameters start from here: http://dev.mysql.com/doc/refman/5.6/en/query-cache.html
zabolab 