This article is half-done without your Comment! *** Please share your thoughts via Comment ***
In the previous post, I shared a basic note on MySQL Query Cache.
In this post, I am sharing the details on Query Cache configuration and monitoring.
The first check, Query Cache is available or not.
Please execute below SHOW command:
|
1 2 3 4 5 6 |
mysql> SHOW VARIABLES LIKE 'have_query_cache'; +------------------+-------+ | Variable_name | Value | +------------------+-------+ | have_query_cache | YES | +------------------+-------+ |
To disable the query cache at server startup, set the query_cache_size system variable to 0. By default, the query cache is disabled.
Using query_cache_size you can set the size of Query Cache which must be larger when you are planning to hold more data into Cache but again this is a task of analysis of your memory usage.
|
1 2 3 4 5 6 7 8 9 10 |
mysql> SET GLOBAL query_cache_size = 1000000; Query OK, 0 rows affected (0.04 sec) mysql> SHOW VARIABLES LIKE 'query_cache_size'; +------------------+--------+ | Variable_name | Value | +------------------+--------+ | query_cache_size | 999424 | +------------------+--------+ 1 row in set (0.00 sec) |
Query Cache with SELECT :
|
1 2 |
SELECT SQL_CACHE * FROM tbl_users; SELECT SQL_NO_CACHE * FROM tbl_users; |
In the above two select statements, you can find two identifiers.
SQL_CACHE
The query result is cached if it is cacheable and the value of the query_cache_type system variable is ON or DEMAND.
SQL_NO_CACHE
The server does not use the query cache. It neither checks the query cache to see whether the result is already cached nor does it cache the query result.
|
1 |
SHOW VARIABLES LIKE 'query_cache_type'; |
- A value of 0 or OFF prevents caching or retrieval of cached results.
- A value of 1 or ON enables caching except of those statements that begin with SELECT SQL_NO_CACHE.
- A value of 2 or DEMAND causes caching of only those statements that begin with SELECT SQL_CACHE.
To monitor query cache performance, use SHOW STATUS to view the cache status variables:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
mysql> SHOW STATUS LIKE 'Qcache%'; +-------------------------+--------+ | Variable_name | Value | +-------------------------+--------+ | Qcache_free_blocks | 36 | | Qcache_free_memory | 138488 | | Qcache_hits | 79570 | | Qcache_inserts | 27087 | | Qcache_lowmem_prunes | 3114 | | Qcache_not_cached | 22989 | | Qcache_queries_in_cache | 415 | | Qcache_total_blocks | 912 | +-------------------------+--------+ |
