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 Section 5.1.9.5, “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, you can use a strategy that involves 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 nonleaf 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 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. 

The CACHE INDEX  statement 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  system variable 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 : 

