(mysql.info.gz) Query Cache Configuration

Info Catalog

(mysql.info.gz) Query Cache in SELECT

(mysql.info.gz) Query Cache

(mysql.info.gz) Query Cache Status and Maintenance

 
 5.11.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   |
      +------------------+-------+
 
 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-related 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 cache
 size is 0; that is, the query cache is disabled.
 
 If the query cache is enabled, 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' value of `query_cache_type' 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' value of `query_cache_type'. 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' variable. The default value is 1MB.
 
 The result of a query (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).
 
 
 `query_cache_min_res_unit' is present from MySQL 4.1.
 

Info Catalog

(mysql.info.gz) Query Cache in SELECT

(mysql.info.gz) Query Cache

(mysql.info.gz) Query Cache Status and Maintenance