Mysql Query Caching

gistfile1.txt

Check if query caching is available:
mysql> show variables like 'have_query_cache';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| have_query_cache | YES   |
+------------------+-------+

Check if query caching is enabled:
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             | 8388608 |
| query_cache_type             | ON      |
| query_cache_wlock_invalidate | OFF     |
| query_prealloc_size          | 8192    |
+------------------------------+---------+

query_cache_size – This is the size of the cache in bytes. Setting this value to 0 will effectively disable caching.
query_cache_type – This value must be ON or 1 for query caching to be enabled by default.
query_cache_limit – This is the maximum size query (in bytes) that will be cached.

Changing the variables at runtime (temporary):
SET GLOBAL query_cache_size = 8388608;
SET GLOBAL query_cache_limit = 1048576;
SET GLOBAL query_cache_type = 1;

Check if it’s actually working:
mysql> SHOW STATUS LIKE 'Qc%';
+-------------------------+--------+
| Variable_name           | Value  |
+-------------------------+--------+
| Qcache_free_blocks      | 65     | 
| Qcache_free_memory      | 201440 | 
| Qcache_hits             | 18868  | 
| Qcache_inserts          | 2940   | 
| Qcache_lowmem_prunes    | 665    | 
| Qcache_not_cached       | 246    | 
| Qcache_queries_in_cache | 492    | 
| Qcache_total_blocks     | 1430   | 
+-------------------------+--------+
8 rows in set (0.00 sec)

Enable in Config File (permanent): 1048576 = 1M, 268435456 = 256M
query_cache_size = 268435456 
query_cache_type = 1
query_cache_limit= 1048576