Optimize MySQL query_cache_size
The MySQL query_cache_size is an in memory caches that stores the complete result sets of frequent SELECT queries. Instead of issuing the query again to the database engine, MySQL retrieves the result set from the cache and returns that to the client. The bigger query_cache_size can cache more result set. Query has to be exactly the same and deterministic, so MySQL hit rate would generally be less than Oracle hit rate.
Increasing your query_cache_size will definitely have an impact on performance of a few high frequency queries. The defaults value of query_cache_size is 0, which disable the query cache. For small to medium size database it should set to 16MB to 32MB. But it depends on other two MySQL system variables: query_cache_type and query_cache_limit. Query_cache_type must be set to 1 and query_cache_limit must be a reasonable value. It is recommended to set 1MB.. Here at first we will show how to set up query cache with 16MB size.
- Check the current set up of query_cache_size, query_cache_type and query_cache_limit
mysql> show global variables like 'query_cache_size'; +------------------+-------+ | Variable_name | Value | +------------------+-------+ ;| query_cache_size | 0 | +------------------+-------+ 1 row in set (0.00 sec)
mysql> show global variables like 'query_cache_type'; +------------------+-------+ | Variable_name | Value | +------------------+-------+ | query_cache_type | OFF | +------------------+-------+ 1 row in set (0.00 sec)
mysql> show global variables like 'query_cache_limit'; +-------------------+---------+ | Variable_name | Value | +-------------------+---------+ | query_cache_limit | 1048576 | ;+-------------------+---------+ 1 row in set (0.00 sec)
- Set the query_cache_size to 16MB, query_cache_type to 1 and query_cache_limit to 1MB
mysql> set global query_cache_size=16*1024*1024; Query OK, 0 rows affected (0.00 sec) mysql> set global query_cache_type=1; Query OK, 0 rows affected (0.00 sec) mysql> set global query_cache_limit=1*1024*1024; Query OK, 0 rows affected (0.00 sec)
- Check all the variable are set perfectly by issuing the show command again
- To keep the settings prevailed after MySQL restart edit /etc/my.cnf file and add the following three lines at bottom of the file
# vim /etc/my.cnf query_cache_size= 16M query_cache_type=1 query_cache_limit=1M
Now we will go through how to Optimize MySQL query_cache_size . Here I have taken data from an existing MySQL database where query cache is already enabled. In a cache configured database you need to monitor some status variables over the time and you will understand the improvement after any adjustment.
- First check the system variables associated with query cache
mysql> SHOW VARIABLES LIKE '%query_cache%'; +------------------------------+---------+ | Variable_name | Value | +------------------------------+---------+ | have_query_cache | YES | | query_cache_limit | 1048576 | | query_cache_min_res_unit | 4096 | | query_cache_size | 1048576 | | query_cache_type | ON | | query_cache_wlock_invalidate | OFF | +------------------------------+---------+ 6 rows in set (0.00 sec)
- Check performance/status variables related to query cache
mysql> SHOW STATUS LIKE 'qcache%'; +-------------------------+----------+ | Variable_name | Value | +-------------------------+----------+ | Qcache_free_blocks | 44 | | Qcache_free_memory | 940424 | | Qcache_hits | 70839825 | | Qcache_inserts | 25695972 | | Qcache_lowmem_prunes | 5903364 | | Qcache_not_cached | 4570044 | | Qcache_queries_in_cache | 56 | | Qcache_total_blocks | 183 | +-------------------------+----------+ 8 rows in set (0.00 sec)
- Calculate the percentage used value for the query cache
Percentage Used= ((query_cache_size-Qcache_free_memory)/query_cache_size)*100 = ((1048576 – 940424)/ 1048576)*100 = 10.31%
That means only 10.31% of query_cache_size is occupied , so it is not necessary to increase the size. But there are may be other reasons you need to increase the size.
- Find out the query cache hit rate
Cache hit rate = ((Qcache_hits/(Qcache_hits+Qcache_inserts+Qcache_not_cached))*100) = (70839825/(70839825 + 25695972 + 4570044))* 100 = 70%
It means total 101105841(Qcache_hits+Qcache_inserts+Qcache_not_cached) times MySQL looked up query cache and 70839825 times result were served from cache and Cache hit rate is 70% which is very good. If this is below 50% you need to increase query_cache_size and over the time you need to monitor the Cache hit rate.
- Find out Insert to Prune Ratio (HTPR)
HTPR=Qcache_inserts/ Qcache_lowmem_prunes = 25695972/5903364 =4.35
25695972 times new result sets were inserted in cache and 5903364 times existing result sets were removed. So, over the time if this ratio decreases you need to increase query_cache_size.
The important characteristics of query_cache_size is as follows
Command-Line Format | –query_cache_size=# | |
Option-File Format | query_cache_size | |
System Variable Name | query_cache_size | |
Variable Scope | Global | |
Dynamic Variable | Yes | |
Permitted Values | ||
Platform Bit Size | 32 | |
Type | Numeric | |
Default | 0 | |
Min Value | 0 | |
Max Value | 4294967295 | |
Permitted Values | ||
Platform Bit Size | 64 | |
Type | Numeric | |
Default | 0 | |
Min Value | 0 | |
Max Value | 18446744073709547520 |
Top Reviewed Books of MySQL at amazon.com
My VPS has 17,1% percentage used query cache and a 70% hit rate. I have the cache set at 64MB. If there is more than enough memory available, would it be useful to increase to 128MB of even 256MB?
if you use formula Cache hit rate = ((Qcache_hits/(Qcache_hits+Qcache_inserts+Qcache_not_cached))*100) and get 70% I think it is fair enough. However, if you have enough free memory you can increase query_cache_size=128 and obersve some days if hit ratio increases .