MySQL's Query Cache - Page 2

Clearing the Query cache

The cache cannot stay in memory indefinitely. Luckily, MySQL is clever enough to clear it when you make any changes to the tables used in a cache query. If we insert a new record to the cur table, MySQL will clear the affected queries (and only the affected queries) from the cache:

mysql> INSERT INTO cur(cur_user_text) 
    VALUES ('xxx');
Query OK, 1 row affected (0.06 sec)

mysql> SHOW STATUS LIKE '%qcache%';
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_queries_in_cache | 0        |
| Qcache_inserts          | 4        |
| Qcache_hits             | 1        |
| Qcache_lowmem_prunes    | 0        |
| Qcache_not_cached       | 2        |
| Qcache_free_memory      | 20962720 |
| Qcache_free_blocks      | 1        |
| Qcache_total_blocks     | 1        |
+-------------------------+----------+

Any of INSERT, UPDATE, DELETE, TRUNCATE, ALTER, DROP TABLE or DROP DATABASE potentially remove queries from the cache. You can manually clear the query cache with RESET QUERY CACHE.

Query Cache on demand

Earlier we saw there were three values for the query_cache_type. On, off and on demand. The latter option means that queries will only be cached if SQL_CACHE is specified in the query. Let's restart the server, with

query-cache-type = 2

in the configuration. Restarting the server flushes all the status variables. We run our previous query again:

SELECT cur_is_new FROM cur WHERE cur_user_text > 'Y'
...
2336 rows in set (0.27 sec)

It is back to a longer time again, as the cache has been flushed.

mysql> SHOW STATUS LIKE '%qcache%';
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_queries_in_cache | 0        |
| Qcache_inserts          | 0        |
| Qcache_hits             | 0        |
| Qcache_lowmem_prunes    | 0        |
| Qcache_not_cached       | 1        |
| Qcache_free_memory      | 20962720 |
| Qcache_free_blocks      | 1        |
| Qcache_total_blocks     | 1        |
+-------------------------+----------+

Nothing has been recorded. To store a query in the cache, we need to run the query with SQL_CACHE, as follows:

SELECT SQL_CACHE cur_is_new FROM cur WHERE cur_user_text > 'Y'
...
2336 rows in set (0.33 sec)

This time it has been stored in the cache.

mysql> SHOW STATUS LIKE '%qcache%';
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_queries_in_cache | 1        |
| Qcache_inserts          | 1        |
| Qcache_hits             | 0        |
| Qcache_lowmem_prunes    | 0        |
| Qcache_not_cached       | 1        |
| Qcache_free_memory      | 20947592 |
| Qcache_free_blocks      | 1        |
| Qcache_total_blocks     | 4        |
+-------------------------+----------+

If the cache type is set to 1, there may be times when you know the query you are running will not be repeated, or more infrequently. In these situations, you can ask MySQL not to store the results in the cache, even if they adhere to the size limitations, with the SQL_NO_CACHE clause in the SELECT statement.

Block allocation and the Query cache

MySQL allocates results into the cache in blocks, during retrieval. This allocation comes at an overhead (see the quicker time to run the above query when it was not being cached). You should not enable the query cache unless you can make good use of it. The number of free blocks (Qcache_free_blocks) can be an indication of fragmentation - a high number in relation to the total number of the blocks means that space is being wasted. In MySQL 4.1, there is another cache-related variable: query_cache_min_res_unit. This allows you to set a minimum block size. The default is 4KB. If most of your query results are small, and you see fragmentation, you should decrease this. The converse applies if most of your result sets are large. To defragment a query cache, you can use FLUSH QUERY CACHE (FLUSH TABLES has the same effect on the cache).

There are situations when a query cannot be cached, all of which make perfect sense, such as when returning the current time, a random number, user variables, or when dumping to a file. Any queries making use of the following functions, or of the following types, will not be cached:

• User-Defined Functions
• BENCHMARK
• CONNECTION_ID
• CURDATE
• CURRENT_DATE
• CURRENT_TIME
• CURRENT_TIMESTAMP
• CURTIME
• DATABASE
• ENCRYPT (with one parameter)
• FOUND_ROWS
• GET_LOCK
• LAST_INSERT_ID
• LOAD_FILE
• MASTER_POS_WAIT
• NOW
• RAND
• RELEASE_LOCK
• SYSDATE
• UNIX_TIMESTAMP (without parameters)
• USER
• query contains user variables
• query references the mysql system database
• query of the form SELECT ... IN SHARE MODE
• query of the form SELECT ... INTO OUTFILE ...
• query of the form SELECT ... INTO DUMPFILE ...
• query of the form SELECT * FROM AUTOINCREMENT_FIELD IS NULL
• queries inside transactions (in MySQL 4.0.x)

Wisely used, the query cache can make a substantial difference to struggling applications. Good luck!

» See All Articles by Columnist Ian Gilfillan