8.10.3.3 Query Cache Configuration
The query cache is deprecated as of MySQL 5.7.20, and is removed in MySQL 8.0.
The have_query_cache
server system variable indicates whether the query cache is available:
mysql> SHOW VARIABLES LIKE 'have_query_cache';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| have_query_cache | YES |
+------------------+-------+
When using a standard MySQL binary, this value is always YES
, even if query caching is disabled.
Several other system variables control query cache operation. These can be set in an option file or on the command line when starting mysqld. The query cache system variables all have names that begin with query_cache_
. They are described briefly in Section 5.1.7, “Server System Variables”, with additional configuration information given here.
To set the size of the query cache, set the query_cache_size
system variable. Setting it to 0 disables the query cache, as does setting query_cache_type=0
. By default, the query cache is disabled. This is achieved using a default size of 1M, with a default for query_cache_type
of 0.
To reduce overhead significantly, start the server with query_cache_type=0
if you will not be using the query cache.
When using the Windows Configuration Wizard to install or configure MySQL, the default value for query_cache_size
will be configured automatically for you based on the different configuration types available. When using the Windows Configuration Wizard, the query cache may be enabled (that is, set to a nonzero value) due to the selected configuration. The query cache is also controlled by the setting of the query_cache_type
variable. Check the values of these variables as set in your my.ini
file after configuration has taken place.
When you set query_cache_size
to a nonzero value, keep in mind that the query cache needs a minimum size of about 40KB to allocate its structures. (The exact size depends on system architecture.) If you set the value too small, you'll get a warning, as in this example:
mysql> SET GLOBAL query_cache_size = 40000;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> SHOW WARNINGS\G
*************************** 1. row ***************************
Level: Warning
Code: 1282
Message: Query cache failed to set size 39936;
new query cache size is 0
mysql> SET GLOBAL query_cache_size = 41984;
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW VARIABLES LIKE 'query_cache_size';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| query_cache_size | 41984 |
+------------------+-------+
For the query cache to actually be able to hold any query results, its size must be set larger:
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)
The query_cache_size
value is aligned to the nearest 1024 byte block. The value reported may therefore be different from the value that you assign.
If the query cache size is greater than 0, the query_cache_type
variable influences how it works. This variable can be set to the following values:
A value of
0
orOFF
prevents caching or retrieval of cached results.A value of
1
orON
enables caching except of those statements that begin withSELECT SQL_NO_CACHE
.A value of
2
orDEMAND
causes caching of only those statements that begin withSELECT SQL_CACHE
.
If query_cache_size
is 0, you should also set query_cache_type
variable to 0. In this case, the server does not acquire the query cache mutex at all, which means that the query cache cannot be enabled at runtime and there is reduced overhead in query execution.
Setting the GLOBAL
query_cache_type
value determines query cache behavior for all clients that connect after the change is made. Individual clients can control cache behavior for their own connection by setting the SESSION
query_cache_type
value. For example, a client can disable use of the query cache for its own queries like this:
mysql> SET SESSION query_cache_type = OFF;
If you set query_cache_type
at server startup (rather than at runtime with a SET
statement), only the numeric values are permitted.
To control the maximum size of individual query results that can be cached, set the query_cache_limit
system variable. The default value is 1MB.
Be careful not to set the size of the cache too large. Due to the need for threads to lock the cache during updates, you may see lock contention issues with a very large cache.
You can set the maximum size that can be specified for the query cache at runtime with the SET
statement by using the --maximum-query_cache_size=
option on the command line or in the configuration file. 32M
When a query is to be cached, its result (the data sent to the client) is stored in the query cache during result retrieval. Therefore the data usually is not handled in one big chunk. The query cache allocates blocks for storing this data on demand, so when one block is filled, a new block is allocated. Because memory allocation operation is costly (timewise), the query cache allocates blocks with a minimum size given by the query_cache_min_res_unit
system variable. When a query is executed, the last result block is trimmed to the actual data size so that unused memory is freed. Depending on the types of queries your server executes, you might find it helpful to tune the value of query_cache_min_res_unit
:
The default value of
query_cache_min_res_unit
is 4KB. This should be adequate for most cases.If you have a lot of queries with small results, the default block size may lead to memory fragmentation, as indicated by a large number of free blocks. Fragmentation can force the query cache to prune (delete) queries from the cache due to lack of memory. In this case, decrease the value of
query_cache_min_res_unit
. The number of free blocks and queries removed due to pruning are given by the values of theQcache_free_blocks
andQcache_lowmem_prunes
status variables.If most of your queries have large results (check the
Qcache_total_blocks
andQcache_queries_in_cache
status variables), you can increase performance by increasingquery_cache_min_res_unit
. However, be careful to not make it too large (see the previous item).