system variable indicates whether the query cache is
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.4, “Server System Variables”, with additional
configuration information given here.
To set the size of the query cache, set the
variable. Setting it to 0 disables the query cache. The
default size is 0, so the query cache is disabled by default.
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.
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
You should check the values of these variables as set in
my.ini file after configuration
has taken place.
When you set
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:
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:
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)
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
influences how it works. This variable can be set to the
A value of
prevents caching or retrieval of cached results.
A value of
enables caching except of those statements that begin with
A value of
DEMAND causes caching of only those
statements that begin with
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
query_cache_type value. For
example, a client can disable use of the query cache for its
own queries like this:
SET SESSION query_cache_type = OFF;
To control the maximum size of individual query results that
can be cached, set the
variable. The default value is 1MB.
You can set the maximum size that can be specified for the
query cache at runtime with the
statement by using the
option on the command line or in the configuration file.
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
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
The default value of
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, you should
decrease the value of
The number of free blocks and queries removed due to
pruning are given by the values of the
If most of your queries have large results (check the
status variables), you can increase performance by
However, be careful to not make it too large (see the