6.9.2 Query Cache Configuration

The query cache adds a few MySQL system variables for mysqld which may be set in a configuration file, on the command-line when starting mysqld.

• query_cache_limit Don't cache results that are bigger than this. (Default 1M).
• query_cache_min_res_unit This variable is present from version 4.1. The result of a query (the data that is also sent to the client) is stored in the query cache during result retrieval. Therefore the data is usually 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 (time wise), the query cache allocates blocks with a minimum size of query_cache_min_res_unit. When a query is executed, the last result block is trimmed to the actual data size, so that unused memory is freed.
  • The default value of query_cache_min_res_unit is 4 KB which 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 (indicated by a large number of free blocks (Qcache_free_blocks), which can cause the query cache to have to delete queries from the cache due to lack of memory (Qcache_lowmem_prunes)). In this case you should decrease query_cache_min_res_unit.
  • If you mostly have queres with big results (see Qcache_total_blocks and Qcache_queries_in_cache), you can increase performance by increasing query_cache_min_res_unit. However, be careful to not make it to large (see the previous point).
• query_cache_size The amount of memory (specified in bytes) allocated to store results from old queries. If this is 0, the query cache is disabled (default).
• query_cache_type This may be set (only numeric) to

  Option	Description
  0	(OFF, don't cache or retrieve results)
  1	(ON, cache all results except SELECT SQL_NO_CACHE ... queries)
  2	(DEMAND, cache only SELECT SQL_CACHE ... queries)

Inside a thread (connection), the behaviour of the query cache can be changed from the default. The syntax is as follows:

QUERY_CACHE_TYPE = OFF | ON | DEMAND QUERY_CACHE_TYPE = 0 | 1 | 2

User Comments

Add your own comment.