MySQL Query cache Optimization

1.   Enabling the Query Cache

         Query cache can be enabled by setting cache size. Initially the cache size will be ‘0’ and then the values should be changed to multiples of 1024 upto 10.

         Eg:

                 Query_cache_size=5 GB

         Here query cache block size can be allocated using query_cache_min_res_unit, its default value is 4K. Total block size can be allocated using the query_alloc_block_size.

2.   Setting the Query Cache type

         By default, the query cache type is ‘1’ i.e. (ON). Query cache consists of three types such as

                 Query_cache_type=0 (OFF)

         In this type, it does not cache any queries.

                 Query_cache_type=1 (ON)

         In this type, all queries are cacheable except the queries with SELECT SQL_NO_CACHE.

                 Query_cache_type=2 (DEMAND)

         In this type, cache the queries that begin with SQL_CACHE.

3.   Analyze the status of Query Cache

         Query cache status can be analyzed by the following server status variables. By analyzing these status variables, the query cache can be optimized.

1.    Qcache_free_blocks – no of free blocks in total allocated blocks

2.    Qcache_free_memory – total free spaces from the allocated size

3.    Qcache_hits – count of repeatable queries

4.    Qcache_inserts – no of new queries can be thrown

5.    Qcache_lowmem_prunes – no of queries that are deleted from cache.

6.    Qcache_not_cached - no of queries that are not cached.

7.    Qcache_queries_in_cache – total no of queries currently in cache.

8.    Qcache_total_blocks – no of blocks created for the caching.

4.   Query Cache Limitations

         All the incoming queries can be stored in query cache. Here the incoming queries can compared and then the query result can be fetched.

i)             Queries can be identical but different from character set, database and the changes made in any of column names.

ii)           Queries are not cached under the following conditions.

·       Cache is not used when sub queries are used.

·       Cache is not used when queries executed within the stored functions, triggers and events.

·       Before fetching results, MySQL checks for select privilege for the selected user. If the users don’t have the privilege then cache is not used.

·       When changes made in table using insert, delete, update, merge, alter, drop and truncate then the corresponding table query cache can be removed from query cache using least recently used algorithm (LRU).

                

·       A query cannot be cached if it uses the following function.

BENCHMARK (), CONNECTION_ID()CONVERT_TZ(), CURDATE() CURRENT_DATE(), CURRENT_TIME()  CURRENT_TIMESTAMP(), CURTIME() DATABASE(), ENCRYPT(), FOUND_ROWS() GET_LOCK(), LAST_INSERT_ID()LOAD_FILE(), MASTER_POS_WAIT() NOW(), RAND() RELEASE_LOCK(), SLEEP() SYSDATE(), UNIX_TIMESTAMP()USER(), UUID(), UUID_SHORT()

·       Queries are not cached for user defined functions and user defined variables.

·       Queries in any of these forms are not cacheable.

mysql> select name from test lock in share mode;

mysql> select name from test for update;

mysql> select name into outfile 'e:/name.txt' from service_new;

mysql> select * from test where serviceid is null;

·       Queries are not cacheable when fetching data from information_schema and mysql tables.

·       Queries are not cacheable when using temporary tables and when user having only column level privileges.

Calculations(Server1):

         By observing the query cache the efficiency of the query cache can be calculated. If the efficiency is low then we have to optimize the query cache variables. Following are ways to calculate the efficiency.

1. Hit Rate : Qcache_hits/( Qcache_hits + com_select )*100

             Here the hit rate should not be less than 20%. If the hit rate is less than 20% then we have to optimize the query cache limit.

                 Eg:

                          Qcache_hits=7367496

                          Com_select=73640519

                 Hit Rate(Tugela) = 7367496/ (7367496+73640519)*100

                                       = 9.09 % (Not Opiimal)

         Here we did got  the optimal percentage so we need to optimize any variables.

2. Insert Rate: Qcache_inserts/(Qcache_inserts + com_select) * 100

Here the insert rate should not be below 10%. If the insert rate is below

10% then we have to optimize the query cache by disabling it.

   

             Eg:

                     Qcache_inserts = 72774650

                     Com_select = 73640519

             Insert Rate = Qcache_inserts/ (Qcache_inserts + com_select) * 100

                            = 72774650/ (72774650 + 73640519) * 100

                            = 49.70 %

3. Prune Rate: Qcache_lowmem_prunes / Qcache_inserts

Here the prune rate value should not be greater than ‘0’, if it greater than ‘0’ then we have to increase the size of query cache or else we have to disable query cache.

                 Eg:

                          Qcache_lowmem_prunes = 37932888

                          Qcache_inserts = 72774650

                 Prune rate = (Qcache_lowmem_prunes / Qcache_inserts)*100

                                 = (37932888 / 72774650)*100

                                 = 52.18 %

4. Worst case for blocks:

In this case query cache free blocks should not be half of the query cache total blocks. If it is like that then we have to increase the size of the allocated block.

             Qcache_free_blocks = Qcache_total_blocks/2

    Eg:

             Qcache_free_blocks = 3

             Qcache_total_blocks = 6428991/2

                                          = 3214495.5 ≠ 3

5. Qcache_invalidation:

In this case, queries that are not matched with queries in query cache then the count will be increased. Qcache_invalidation should not be high.

Qcache_invalidation = Qcache_inserts – Qcache_queries_in_cache.

                          = 72774650 – 3195126

                          = 69579524 (this is not optimal, it should not be high)

6. Block Size:

In this case block size can be optimized by finding the value from following calculation.

Block size = (Query_cache_size – Qcache_free_memory) / Qcache_queries_in_cache

         Eg:

                 Block size = (5368709120 – 150397408) / 3195126

                                         = 1633.21

                 Qcache_alloc_block_size = 8192

         If the calculated block size is greater than the allocated block size then we have increase the Qcache_alloc_block_size and Qcache_prealloc_size.

Calculations:(Server2)

Query_cache_size= 1 Gb

         By observing the query cache the efficiency of the query cache can be calculated. If the efficiency is low then we have to optimize the query cache variables. Following are ways to calculate the efficiency.

1. Hit Rate : Qcache_hits/( Qcache_hits + com_select )*100

             Here the hit rate should not be less than 20%. If the hit rate is less than 20% then we have to optimize the query cache limit.

                 Eg:

                          Qcache_hits=1703189

                          Com_select=11975690

                 Hit Rate(Tugela) = 1703189/ (1703189+11975690)*100

                                       = 12.45 % (Not Opiimal)

         Here we did got  the optimal percentage so we need to optimize any variables.

2. Insert Rate: Qcache_inserts/(Qcache_inserts + com_select) * 100

Here the insert rate should not be below 10%. If the insert rate is below

10% then we have to optimize the query cache by disabling it.

   

             Eg:

                     Qcache_inserts = 5232234

                     Com_select = 11975690

             Insert Rate = Qcache_inserts/ (Qcache_inserts + com_select) * 100

                            = 5232234/ (5232234 + 11975690) * 100

                            = 30.40 %

3. Prune Rate: Qcache_lowmem_prunes / Qcache_inserts

Here the prune rate value should not be greater than ‘0’, if it greater than ‘0’ then we have to increase the size of query cache or else we have to disable query cache.

                 Eg:

                          Qcache_lowmem_prunes = 0

                          Qcache_inserts = 5232234

                 Prune rate = (Qcache_lowmem_prunes / Qcache_inserts)*100

                                 = (0 / 5232234)*100

                                 = 0 %

4. Worst case for blocks:

In this case query cache free blocks should not be half of the query cache total blocks. If it is like that then we have to increase the size of the allocated block.

             Qcache_free_blocks = Qcache_total_blocks/2

    Eg:

             Qcache_free_blocks = 5

             Qcache_total_blocks = 26/2

                                          = 13 ≠ 5

5. Qcache_invalidation:

In this case, queries that are not matched with queries in query cache then the count will be increased. Qcache_invalidation should not be high.

Qcache_invalidation = Qcache_inserts – Qcache_queries_in_cache.

                          = 5232234 - 8

                          = 5232226 (this is not optimal, it should not be high)

6. Block Size:

In this case block size can be optimized by finding the value from following calculation.

Block size = (Query_cache_size – Qcache_free_memory) / Qcache_queries_in_cache

         Eg:

                 Block size = (1073741824 – 1073683832) / 8

                                         = 7249

                 Qcache_alloc_block_size = 8192

         If the calculated block size is greater than the allocated block size then we have increase the Qcache_alloc_block_size and Qcache_prealloc_size.

Query Cache Flow Diagram