OSS Robot

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.

mysql