Query Cache Optimization

Query Cache Optimization

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  |

mysql>  show global variables like 'query_cache_type';

         | Variable_name    | Value|

                                | query_cache_type | OFF |

mysql>  show global variables like 'query_cache_limit';

            | Variable_name | Value   |

           | query_cache_limit | 1048576 |

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

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     |

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      |

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.