The size of the key cache is controlled by the parameter key_buffer_size in the my.cnf file). This is the most important parameter in the my.cnf file as related to the tuning of MyISAM performance.

Some quick facts about the key cache:

• It is crucial to the performance of MyISAM tables.
  
• It necessary even when using mainly other table types because the mysql database always uses MyISAM tables.
  
• Its maximum size is 4GB. Of course the amount of RAM in the machine is a limiter, but 4GB is the limit for machines with larger amounts of memory.
  
• The key cache can be split to increase speed by decreasing the potential for contention. Each cache can be up to 4GB which allows the limit above to be circumvented.
  
• Use of the key cache can be determined at the SQL statement level.

Effectively setting the size of the key cache is tricky because it should be as large as possible without causing the server to page. This must be balanced against not only all the other memory needs of MySQL, but also against any other processes on the machine. Most importantly, this includes the operating system, but may also include Apache, PHP, the Java virtual machine, etc. It is very useful to know how much of the cache is in use.

Key Cache Usage

Note: The parameters shown here can be obtained by issuing the statement:

SHOW STATUS LIKE 'key%';

Before MySQL 4.1.1 the maximum percentage of the buffer never used can be determined by:

In this formula, 1024 represents the size of a key cache block. It is worth repeating that this is not a current value, but rather the maximum amount of the cache ever used. Over time, if this number more than 20% it is worth investigating reducing the size of the cache so that memory resources can be allocated to other areas of the MySQL server, or other processes on the machine.

As of MySQL 4.1.1 the size of the key cache block can be set by the DBA. Also, the status variable key_blocks_unused is available. This means a real time percentage buffer use is approximated by the formula:

Note that the key cache contains internal housekeeping items, so this value is a good approximation.

Key Cache Efficiency

MySQL does not cache reads (select statements) unless you use the query cache, thus it is necessary to consider the needs of the file system's cache in your operating system.

A formula for checking the efficiency of the key cache for reads is:

This ratio should be lower that 1%.

For writes the formula is:

This ratio should be less than 100%. This rule is much less absolute because issuing UPDATE statements that affect many rows can skew it.

If your application is doing a large number of bulk writes, consider manually locking and flushing the tables during this process rather than increasing the key cache (and using memory unnecessarily).

Multiple Key Cache Usage

As of MySQL 4.1.1 it is possible to set up multiple key caches. Doing so improves performance by decreasing the likelihood of contention for cache blocks

The use of the multiple key caches is done at the table level by specifying the cache for each index. This is done with the CACHE INDEX statement. Indexes not assigned to a cache are assigned to the default cache. Consequently, different caches can be dedicated to different tables behaving in different ways. This further increases performance.

Currently MySQL recommends three key caches hot, cold and warm. Tables are assigned to each cache depending on their main function in the application. In this way, access to different tables makes it much less likely that there is contention for the same block in the key cache.

The size of each cache depends on the number of tables and the number of requests to the tables that fit the profile of the cache:

Hot

Use for tables that support a vast majority of reads. The main gain here is that any block that must be replaced does not cause a flush and thus is replaced much quicker. Of course, once done tuning at the table level, consideration of each index on the table should occur if more performance is necessary. An excellent candidate for this cache is a compressed, read-only table.

Warm

Used by all tables (and indexes) not placed in the hot or cold cache. This is the default cache.

Cold

Tables accepting a large number of updates belong in the cold cache. This is particularly true of temporary tables. When the row to be updated can be referenced in the cache the row does not have to be read from disk first, thus increasing the speed of the write.

MySQL recommends that the size of these caches be 20% of total cache size for hot, 20% for cold and 60% for warm. This is a good starting point, but full leverage of this feature can only be realized by studying the use of each table over time to determine which cache it belongs to.

Creation of multiple key caches consists of several lines of the my.cnf file. Here is a read intensive application:

key_buffer_size = 128M
hot.key_buffer_size = 128M
cold.key_buffer_size = 32M
init_file = /path_to_datadir/<filename>

Consider the database sis which contains the report_card table (very read intensive) that has an indexes ix_report_card1 and ix_report_card2. The first index is used to look up report_card rows for reads (by quarter) while the second index is used to look up report_card records for writes. The student table has index ak_student which is frequently used to look up students for updates or REPLACE INTO statements. Here are the cache assignments that would appear in the file referenced by the init_file parameter above:

cache index report_card.ix_report_card1 in hot;
cache index report_card.ix_report_card2, student.ak_student in cold;