MySQL Query Caching

MySQL query caching is simple to implement and very beneficial to high volume websites. Query cache actually caches the query results for a given SQL query. To see if MySQL has query caching enabled issue the following command:

 mysql> show variables like 'query%';

+------------------------------+---------+

| Variable_name                | Value   |

+------------------------------+---------+

| query_alloc_block_size       | 8192    |

| query_cache_limit            | 1048576 |

| query_cache_min_res_unit     | 4096    |

| query_cache_size             | 0       |

| query_cache_type             | ON      |

| query_cache_wlock_invalidate | OFF     |

| query_prealloc_size          | 8192    |

+------------------------------+---------+

If query_cache_size is zero, then it is not being utilized. Set the cache size based on your hardware and available memory. To set the cache to 1G, issue the following command:

 mysql> set global query_cache_size=100000000;Query OK, 0 rows affected (0.00 sec)

You can check on cache query hits issuing the following commands:

MySQL query caching is simple to implement and very beneficial to high volume websites. To see if MySQL has query caching enabled issue the following command:

mysql> show variables like 'query%';

+------------------------------+---------+

| Variable_name                | Value   |

+------------------------------+---------+

| query_alloc_block_size       | 8192    |

| query_cache_limit            | 1048576 |

| query_cache_min_res_unit     | 4096    |

| query_cache_size             | 0       |

| query_cache_type             | ON      |

| query_cache_wlock_invalidate | OFF     |

| query_prealloc_size          | 8192    |

+------------------------------+---------+

If query_cache_size is zero, then it is not being utilized. Set the cache size based on your hardware and available memory. To set the cache to 1G, issue the following command:

mysql> set global query_cache_size=100000000;

Query OK, 0 rows affected (0.00 sec)

You can check on cache query hits issuing the following commands:

mysql> show status like 'qc%';

+-------------------------+----------+

| Variable_name           | Value    |

+-------------------------+----------+

| Qcache_free_blocks      | 1        |

| Qcache_free_memory      | 49988976 |

| Qcache_hits             | 1        |

| Qcache_inserts          | 1        |

| Qcache_lowmem_prunes    | 0        |

| Qcache_not_cached       | 1        |

| Qcache_queries_in_cache | 1        |

| Qcache_total_blocks     | 5        |

+-------------------------+----------+