(mysql.info) query-cache-configuration

Info Catalog

(mysql.info) query-cache-in-select

(mysql.info) query-cache

(mysql.info) query-cache-status-and-maintenance

 
 5.14.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 
 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).
 

Info Catalog

(mysql.info) query-cache-in-select

(mysql.info) query-cache

(mysql.info) query-cache-status-and-maintenance