Optimize MySQL query_cache_size

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.

1. 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)
   

2. 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)
   

3.  Check all the variable are set perfectly by issuing  the show command again

4. 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.

1. 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)
   
   

2. 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)
   
   

3. 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.

4. 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.

5. 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

Top Reviewed Books of MySQL at amazon.com