Movatterモバイル変換

Thehave_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 alwaysYES, 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 startingmysqld. The query cache system variables all have names that begin withquery_cache_. They are described briefly inSection 5.1.7, “Server System Variables”, with additional configuration information given here.

To set the size of the query cache, set thequery_cache_size system variable. Setting it to 0 disables the query cache, as does settingquery_cache_type=0. By default, the query cache is disabled. This is achieved using a default size of 1M, with a default forquery_cache_type of 0.

To reduce overhead significantly, start the server withquery_cache_type=0 if you do not intend to use the query cache.

When you setquery_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: 1282Message: Query cache failed to set size 39936;         new query cache size is 0mysql> 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)

Thequery_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, thequery_cache_type variable influences how it works. This variable can be set to the following values:

Ifquery_cache_size is 0, you should also setquery_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 theGLOBALquery_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 theSESSIONquery_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 setquery_cache_type at server startup (rather than at runtime with aSET statement), only the numeric values are permitted.

To control the maximum size of individual query results that can be cached, set thequery_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.

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 thequery_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 ofquery_cache_min_res_unit: