Mysql Query Caching
Enabling Caching
*verify caching is on. if need to update, then restart.
jesterj@jesterj-laptop:/etc/mysql$ grep "query" /etc/mysql/my.cnf
query_cache-type = 1 #cache everything
query_cache_limit = 1M
query_cache_size = 16M
*Note that variables in the my.cnf file are reflecteed in the below variables.
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 | 16777216 |
| query_cache_type | ON |
| query_cache_wlock_invalidate | OFF |
+------------------------------+----------+
6 rows in set (0.00 sec)
View the query cache
mysql> SHOW STATUS LIKE '%qcache%';
+-------------------------+----------+
| Variable_name | Value |
+-------------------------+----------+
| Qcache_free_blocks | 1 |
| Qcache_free_memory | 16612448 |
| Qcache_hits | 1 |
| Qcache_inserts | 2 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 110 |
| Qcache_queries_in_cache | 2 |
| Qcache_total_blocks | 6 |
+-------------------------+----------+
8 rows in set (0.00 sec)
Now run test queries for a database so we can test query cache..
mysql> select * from test.data order by name ASC
mysql> select * from test.data order by name DESC
Notice as you run more and more queries the available free memory descreases and the queries in cache number increases
mysql> SHOW STATUS LIKE '%qcache%';
+-------------------------+----------+
| Variable_name | Value |
+-------------------------+----------+
| Qcache_free_blocks | 1 |
| Qcache_free_memory | 16534728 |
| Qcache_hits | 5 |
| Qcache_inserts | 3 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 110 |
| Qcache_queries_in_cache | 3 |
| Qcache_total_blocks | 8 |
+-------------------------+----------+
8 rows in set (0.00 sec)
Demand Query Caching
You can also specify query cache on demand so only queries that you specify
are cached.
Change query cache type to 2.
[mysql]
….
query-cache-type = 2
….
Restart mysql.
sudo /etc/init.d/mysql restart
Now log back into mysql and verify that DEMAND caching is set
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 | 16777216 |
| query_cache_type | DEMAND |
| query_cache_wlock_invalidate | OFF |
+------------------------------+----------+
6 rows in set (0.00 sec)
Now specify SQL_CACHE when you run a statemnt.
mysql> select SQL_CACHE * from test.data order by name ASC
Note that you can also use SQL_NO_CACHE when query-cache-type=1 to turn off caching for a particular sql stmt.