(mysql.info.gz) Multiple key caches

Info Catalog

(mysql.info.gz) Shared key cache

(mysql.info.gz) MyISAM key cache

(mysql.info.gz) Midpoint insertion

 
 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 4.1.1 offers the
 feature of multiple key caches.  This allows you to assign different
 table indexes to different key caches.
 
 When there can be 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.
 
 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       |
      +---------+--------------------+----------+----------+
 
 * If the server has been built with the `ISAM' storage engine
 enabled, `ISAM' tables use the key cache mechanism. However, `ISAM'
 indexes use only the default key cache and cannot be reassigned to a
 different cache.
 
 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;
 
 Key cache variables are structured system variables that have a name and
 components. For `keycache1.key_buffer_size', `keycache1' is the cache
 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. This is used 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.  This is used for medium-sized intensively modified
      tables, such as temporary tables.
 
    * A warm key cache that takes up 60% of the key cache space.  This
      is 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.  Queries
 that access tables assigned to one cache do not compete with queries
 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 will
      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 will be
      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' will be executed each time the
 server starts. It should contain one SQL statement per line.  The
 following example assigns several tables each to `hot_cache' and
 `cold_cache':
 
      CACHE INDEX a.t1, a.t2, b.t3 IN hot_cache
      CACHE INDEX a.t4, b.t5, b.t6 IN cold_cache
 

Info Catalog

(mysql.info.gz) Shared key cache

(mysql.info.gz) MyISAM key cache

(mysql.info.gz) Midpoint insertion