(mysql.info) multiple-key-caches

 
 7.4.6.2 Multiple Key Caches
 ...........................
 
 Shared access to the key cache improves performance but does not
 eliminate contention among threads entirely. They still compete for
 control structures that manage access to the key cache buffers. To
 reduce key cache access contention further, MySQL also provides
 multiple key caches. This feature enables you to assign different table
 indexes to different key caches.
 
 Where there are multiple key caches, the server must know which cache
 to use when processing queries for a given `MyISAM' table. By default,
 all `MyISAM' table indexes are cached in the default key cache. To
 assign table indexes to a specific key cache, use the `CACHE INDEX'
 statement (see  cache-index). For example, the following
 statement assigns indexes from the tables `t1', `t2', and `t3' to the
 key cache named `hot_cache':
 
      mysql> CACHE INDEX t1, t2, t3 IN hot_cache;
      +---------+--------------------+----------+----------+
      | Table   | Op                 | Msg_type | Msg_text |
      +---------+--------------------+----------+----------+
      | test.t1 | assign_to_keycache | status   | OK       |
      | test.t2 | assign_to_keycache | status   | OK       |
      | test.t3 | assign_to_keycache | status   | OK       |
      +---------+--------------------+----------+----------+
 
 The key cache referred to in a `CACHE INDEX' statement can be created
 by setting its size with a `SET GLOBAL' parameter setting statement or
 by using server startup options. For example:
 
      mysql> SET GLOBAL keycache1.key_buffer_size=128*1024;
 
 To destroy a key cache, set its size to zero:
 
      mysql> SET GLOBAL keycache1.key_buffer_size=0;
 
 Note that you cannot destroy the default key cache. Any attempt to do
 this will be ignored:
 
      mysql> SET GLOBAL key_buffer_size = 0;
 
      mysql> show variables like 'key_buffer_size';
      +-----------------+---------+
      | Variable_name   | Value   |
      +-----------------+---------+
      | key_buffer_size | 8384512 |
      +-----------------+---------+
 
 Key cache variables are structured system variables that have a name
 and components. For `keycache1.key_buffer_size', `keycache1' is the
 cache variable name and `key_buffer_size' is the cache component. See
  structured-system-variables, for a description of the syntax
 used for referring to structured key cache system variables.
 
 By default, table indexes are assigned to the main (default) key cache
 created at the server startup. When a key cache is destroyed, all
 indexes assigned to it are reassigned to the default key cache.
 
 For a busy server, we recommend a strategy that uses three key caches:
 
    * A `hot' key cache that takes up 20% of the space allocated for all
      key caches. Use this for tables that are heavily used for searches
      but that are not updated.
 
    * A `cold' key cache that takes up 20% of the space allocated for
      all key caches. Use this cache for medium-sized, intensively
      modified tables, such as temporary tables.
 
    * A `warm' key cache that takes up 60% of the key cache space.
      Employ this as the default key cache, to be used by default for
      all other tables.
 
 One reason the use of three key caches is beneficial is that access to
 one key cache structure does not block access to the others. Statements
 that access tables assigned to one cache do not compete with statements
 that access tables assigned to another cache. Performance gains occur
 for other reasons as well:
 
    * The hot cache is used only for retrieval queries, so its contents
      are never modified. Consequently, whenever an index block needs to
      be pulled in from disk, the contents of the cache block chosen for
      replacement need not be flushed first.
 
    * For an index assigned to the hot cache, if there are no queries
      requiring an index scan, there is a high probability that the
      index blocks corresponding to non-leaf nodes of the index B-tree
      remain in the cache.
 
    * An update operation most frequently executed for temporary tables
      is performed much faster when the updated node is in the cache and
      need not be read in from disk first. If the size of the indexes of
      the temporary tables are comparable with the size of cold key
      cache, the probability is very high that the updated node is in the
      cache.
 
 `CACHE INDEX' sets up an association between a table and a key cache,
 but the association is lost each time the server restarts. If you want
 the association to take effect each time the server starts, one way to
 accomplish this is to use an option file: Include variable settings that
 configure your key caches, and an `init-file' option that names a file
 containing `CACHE INDEX' statements to be executed. For example:
 
      key_buffer_size = 4G
      hot_cache.key_buffer_size = 2G
      cold_cache.key_buffer_size = 2G
      init_file=/path/to/data-directory/mysqld_init.sql
 
 The statements in `mysqld_init.sql' are executed each time the server
 starts. The file should contain one SQL statement per line. The
 following example assigns several tables each to `hot_cache' and
 `cold_cache':
 
      CACHE INDEX db1.t1, db1.t2, db2.t3 IN hot_cache
      CACHE INDEX db1.t4, db2.t5, db2.t6 IN cold_cache
Info Catalog
(mysql.info) shared-key-cache
(mysql.info) myisam-key-cache
(mysql.info) midpoint-insertion
automatically generated byinfo2html