MySQL query cache

By:    Updated: May 4,2017

The overhead of MySQL query cache

When a query cache hit occurs, the server can simply return the stored results immediately, skipping the parsing, optimization, and execution steps. In this case, MySQL query cache improves performance, but it also adds some overhead for both reads and writes.

  • Read queries must check the cache (wheather hir or not) before beginning
  • If the query is cacheable and isn't in the cache yet (The reasons may be cache invalidation, cache fragmentation, cache no warmed up, or low-memory prune), there's some overhead due to storing the result after generating it
  • There's overhead for write queries, which must invalidate the cache entries for queries that use tables they change (In other words, When tables are changed, any relevant entries in the query cache are flushed. A table can be changed by many types of statements, such as insert, update, delete, truncate table, alter table, drop table, or drop database.). Invalidation can be very costly if the cache is fragmented and/or large (has many cached queries, or is configured to use a large amount of memory, which increases the overhead required to maintain the cache, possibly beyond the benefit of enabling it)
  • The processes of cache invalidation, cache defragmentation (merge the leftover space into the adjacent free block) and low-memory prune also increase the system overhead

 

To verify that enabling the query cache is beneficial,we need to test the operation of your MySQL server with the cache enabled and disabled by MySQL benchmarking tools (such as mysqlslap, the MySQL benchmark suite, sysbench, ...). Then retest periodically because query cache efficiency may change as server workload changes.

11 conditions will cause MySQL query cache not store results

MySQL query cache is a lookup table. The lookup key is a hash of the query text itself, the current database, the client protocol version, the default charset, and a handful of other things that might affect the actual bytes in the query's result. MySQL does not parse, "normalize", or parameterize a query when it checks for a cache hit; it uses the query and other bits of data exactly as the client sends them (In other words, the queries must be exactly the same (byte for byte) to be seen as identical). Any difference in the databases, the protocol versions, the default charsets, character case, spacing, will prevent a query from matching a previously cached version (They are considered different queries and are cached separately). Another caching consideration is that the query cache will not store a result under the following conditions (As of MySQL 5.7.18):

  • A query cannot be cached if it contains any of the nondeterministic functions, such as NOW(), CURRENT_DATE(), CONNECTION_ID(), USER(), ... For more functions list, you can check the MySQL manual.
  • It refers to user-defined functions (UDFs) or stored functions
  • It refers to user variables or local stored program variables
  • It refers to tables in the mysql, information_schema, or performance_schema database
  • It refers to any partitioned tables
  • It uses TEMPORARY tables
  • It does not use any tables
  • It generates warnings
  • The user has a column-level privilege for any of the involved tables
  • For InnoDB users, When a statement inside a transaction modifies a table, the server invalidates any cached queries that refer to the table. The table is also globally uncacheable until the transaction commits, so no further queries against that table (whether inside or outside the transaction) can be cached until the transaction commits.
  • It is of any of the following forms:
    SELECT ... LOCK IN SHARE MODE
    SELECT ... FOR UPDATE
    SELECT ... INTO OUTFILE ...
    SELECT ... INTO DUMPFILE ...
    SELECT * FROM ... WHERE autoincrement_col IS NULL

 

How to determine whether MySQL query cache is helpful

MySQL query cache is helpful only if the savings (time and resources) are greater than the overhead. This will depend on the server's workload. In theory, you can tell whether the cache is helpful by comparing the amount of work the server has to do with the cache enabled and disabled. With the cache disabled, each read query has to execute and return its results, and each write query has to execute. With the cache enabled, each read query has to first check the cache and then either return the stored result or, if there isn't one, execute, generate the result, store it, and return it. Each write query has to execute and then check whether there are any cached queries that must be invalidated.

 

The type of query that benefits most from caching is one whose result is expensive to generate but doesn't take up much space in the cache, so it's cheap to store, return to the client, and invalidate. Aggregate queries, such as small COUNT() results from large tables, fit into this category. As a rule of thumb, you can consider the query cache if your workload is dominated by complex SELECT queries, such as multitable joins with ORDER BY and LIMIT clauses, which produce small result sets. You should have very few UPDATE, DELETE, and INSERT queries in comparison to these complex SELECT queries. One of the ways to tell if you are benefiting from the query cache is to examine the query cache hit rate: Qcache_hits / (Qcache_hits + Com_select).

mysql> show global status like 'Qcache_%';
+-------------------------+--------+
| Variable_name           | Value  |
+-------------------------+--------+
| Qcache_free_blocks      | 38     |
| Qcache_free_memory      | 146552 |
| Qcache_hits             | 321    |
| Qcache_inserts          | 3486   |
| Qcache_lowmem_prunes    | 3258   |
| Qcache_not_cached       | 17     |
| Qcache_queries_in_cache | 357    |
| Qcache_total_blocks     | 808    |
+-------------------------+--------+
8 rows in set (0.00 sec)

mysql> show global status like 'Com_%';
+-----------------------------+--------+
| Variable_name               | Value  |
+-----------------------------+--------+
| Com_delete                  | 4      |
| Com_delete_multi            | 0      |
| Com_insert                  | 900    |
| Com_insert_select           | 0      |
| Com_select                  | 130210 |
| Com_update                  | 26     |
| Com_update_multi            | 0      |

mysql> flush query cache;
Query OK, 0 rows affected (0.00 sec)

mysql> show global status like 'Qcache_%';
+-------------------------+--------+
| Variable_name           | Value  |
+-------------------------+--------+
| Qcache_free_blocks      | 1      |
| Qcache_free_memory      | 143248 |
| Qcache_hits             | 321    |
| Qcache_inserts          | 3494   |
| Qcache_lowmem_prunes    | 3259   |
| Qcache_not_cached       | 17     |
| Qcache_queries_in_cache | 364    |
| Qcache_total_blocks     | 780    |
+-------------------------+--------+
8 rows in set (0.00 sec)

You should monitor how much of the query cache your server actually uses. If it doesn't use as much memory as you've given it, make it smaller. If memory restrictions are causing excessive invalidations you can try making it bigger, but it can be dangerous to configured a large amount. The best way to know how beneficial the query cache really is is to measure how long queries take to execute with and without the cache, if possible. If the query cache isn't saving you a significant amount of time, it's probably best to try disabling it.

The reasons of MySQL query cache miss

Any SELECT query that MySQL doesn't serve from the cache is a cache miss. A cache miss can occur for any of the following reasons:

  • The query is not cacheable, either because it contains conditions which cause MySQL query cache not store results or because its result set is too large to store. Both types of uncacheable queries increment the Qcache_not_cached status variable.
  • The server has never seen the query before, so it never had a chance to cache its result.
  • The query's result was previously cached, but the server removed it. This can happen because there wasn't enough memory to keep it (such as cache prune based on LRU), because someone instructed the server to remove it (such as using RESET QUERY CACHE), or because it was invalidated (such as INSERT, UPDATE, DELETE, TRUNCATE TABLE, ALTER TABLE, DROP TABLE, or DROP DATABASE).

 

If the server has a lot of cache misses but very few uncacheable queries, one of the following must be true:

  • The query cache is not warmed up yet. That is, the server hasn't had a chance to fill the cache with result sets.
  • The server is seeing queries it hasn't seen before. If we don't have a lot of repeated queries, this can happen even after the cache is warmed up.
  • There are a lot of cache invalidations.

 

If we have allocated enough memory to the cache and configured the query_cache_min_res_unit value properly (Qcache_free_blocks as small as possible), most cache invalidations should be due to data modifications. We can see how many queries have modified data by examining the Com_* status variables (Com_update, Com_delete, and Com_insert), and check the Qcache_lowmem_prunes variable to see how many queries have been invalidated due to low memory.

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             | 1048576 |
| query_cache_type             | ON      |
| query_cache_wlock_invalidate | OFF     |
+------------------------------+---------+
6 rows in set (0.00 sec)

 

How to optimize MySQL query cache

In principle, configuring MySQL query cache is pretty simple, but understanding the effects of the changes is more complicated. There are some points to help to optimize it. The following variables influence the query cache's configuration:

  • query_cache_type: Whether the query cache is enabled, default value is OFF. Possible values are 0 (or OFF. Do not cache results in or retrieve results from the query cache), 1 (or ON. Cache all cacheable query results except for those that begin with SELECT SQL_NO_CACHE), or 2 (or DEMAND. Cache results only for cacheable queries that begin with SELECT SQL_CACHE).
  • query_cache_size: The total memory to allocate for caching query results, default value is 1048576 (1M), in bytes. The permissible values are multiples of 1024; other values are rounded down to the nearest multiple.
  • query_cache_min_res_unit: The minimum size when allocated a block by the query cache. The default value is 4096 (4KB), in bytes.
  • query_cache_limit: The largest result set that MySQL will cache, default value is 1048576 (1M), in bytes. Queries whose results are larger than this setting will not be cached, but increase the Qcache_not_cached status variable.
  • query_cache_wlock_invalidate: Whether to serve cached results that refer to tables other connections have locked. The default value is OFF: when one client acquires a WRITE lock on a MyISAM table, other clients can read from the table if the query results are present in the query cache. Changing it to ON will keep you from reading this data (invalidate any queries in the query cache that refer to the table), but it might increase lock waits. This really doesn't matter for most applications, so the default is generally fine.

Reducing fragmentation and improving MySQL query cache usage

Choosing query_cache_min_res_unit value carefully can help you avoid wasting a lot of memory in the query cache. The trick is to balance the size of each new block against the number of allocations the server has to do while storing results. If you make this value too small, the server will waste less memory, but it will have to allocate blocks more frequently, which is more work for the server. If you make it too large, you'll get too much fragmentation. The trade-off is wasting memory versus using more CPU cycles during allocation.

The best setting varies with the size of your typical query result. You can adjust the cache unit size based on the average size: (query_cache_size — Qcache_free_memory)/Qcache_queries_in_cache. It's not beneficial to cache the larger results, so you can keep large results from being cached by decrease the query_cache_limit variable.

You can detect query cache fragmentation by examining the Qcache_free_blocks status variable, which shows you how many blocks in the query cache are of type FREE. After you find many fragmentations, you can defragment the query cache with FLUSH QUERY CACHE or RESET QUERY CACHE.

If your query cache isn't fragmented but you're still not getting a good hit rate, you might have given it too little memory. If the server can't find any free blocks that are large enough to use for a new block, it must "prune" some queries from the cache. When the server prunes cache entries, it increments the Qcache_lowmem_prunes status variable. If this value increases rapidly, there are two possible causes:

  • If there are many free blocks, fragmentation is the likely culprit.
  • If there are few free blocks, it might mean that your workload can use a larger cache size than you're giving it. You can see the amount of unused memory in the cache by examining Qcache_free_memory.

The following points may help you optimize MySQL query cache:

  • Having multiple smaller tables instead of one huge one can help the query cache.
  • It's more efficient to batch writes than to do them singly, because this method invalidates cached cache entries only once.
  • The server can stall for a long time while invalidating entries in or pruning a very large query cache.
  • For a write-heavy application, disabling the query cache completely might improve performance.
1.  If you want to avoid the query cache for most queries but cache for some:
  
     query_cache_type = 2 #DEMAND

     select SQL_CACHE ...

2.  If you want to cache most queries but exclude just a few:

     query_cache_type = 1 #ON

     select SQL_NO_CACHE ...

 

More in Development Center
New on Valinv
Sources
  • https://dev.mysql.com/doc/refman/5.7/en/query-cache.html
Related Articles
Sponsored Links