Movatterモバイル変換

Shared access to the key cache improves performance but does not eliminate contention among sessions 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 givenMyISAM table. By default, allMyISAM table indexes are cached in the default key cache. To assign table indexes to a specific key cache, use theCACHE INDEX statement (seeSection 13.7.6.2, “CACHE INDEX Statement”). For example, the following statement assigns indexes from the tablest1,t2, andt3 to the key cache namedhot_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 aCACHE INDEX statement can be created by setting its size with aSET 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;

You cannot destroy the default key cache. Any attempt to do this is 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. Forkeycache1.key_buffer_size,keycache1 is the cache variable name andkey_buffer_size is the cache component. SeeSection 5.1.8.3, “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:

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:

TheCACHE 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 aninit_file system variable that names a file containingCACHE INDEX statements to be executed. For example:

key_buffer_size = 4Ghot_cache.key_buffer_size = 2Gcold_cache.key_buffer_size = 2Ginit_file=/path/to/data-directory/mysqld_init.sql

The statements inmysqld_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 tohot_cache andcold_cache:

CACHE INDEX db1.t1, db1.t2, db2.t3 IN hot_cacheCACHE INDEX db1.t4, db2.t5, db2.t6 IN cold_cache