5.13.3. Query Cache Configuration
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.2.2, “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. The default size is 0, so the
query cache is disabled by default.
When you set query_cache_size to a non-zero
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 |
+------------------+-------+
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 or OFF
prevents caching or retrieval of cached results.
A value of 1 or ON
allows caching except of those statements that begin with
SELECT SQL_NO_CACHE.
A value of 2 or DEMAND
causes caching of only those statements that begin with
SELECT SQL_CACHE.
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;
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.
When a query that 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, you should 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 the Qcache_free_blocks
and Qcache_lowmem_prunes status
variables.
If most of your queries have large results (check the
Qcache_total_blocks and
Qcache_queries_in_cache status
variables), you can increase performance by increasing
query_cache_min_res_unit. However, be
careful to not make it too large (see the previous item).
