37.4. MyISAM Index Caching MySQL uses a key cache to buffer index information for MyISAM tables in memory. The key cache improves performance by reducing the need to read index blocks from disk each time they are accessed. By default, there is a single key cache, so all MyISAM tables share it. MySQL uses the key cache automatically for MyISAM tables, but you can control cache behavior in several ways: One way to improve server performance is to make the key cache larger so that the server can hold more index information in memory. Section 39.3.1.3, "The MyISAM Key Cache," discusses key cache configuration in more detail. You can create additional key caches and assign specific tables to them. If a table is heavily used and you want to make sure that its index information never is displaced from the cache by indexes from other tables, create a separate cache and dedicate it for use by the table. You can preload a table's indexes into the cache to which it is assigned. This causes the server to load the index all at once, which is more efficient than having it read blocks individually as they are needed.
The following example shows how to create a key cache for use by a particular table. The example uses the City table from the world database and assigns it to a key cache named city_cache. 1. | Create the key cache. Each cache is associated with a set of system variables that are grouped as components of a structured system variable. Each structured variable has a name, so you refer to a component variable using cache_name.var_name syntax. For our purposes here, the only relevant component is the key_buffer_size variable, which determines the size of a key cache. To create a key cache, assign a value to any of its component values. Thus, a cache named city_cache with a size of 4MB is created as follows:
mysql> SET GLOBAL city_cache.key_buffer_size = 4194304;
Key cache system variables are global, so the GLOBAL keyword is necessary in the SET statement. You must have the SUPER privilege to set global variables.
| 2. | Assign the City table to the city_cache key cache by using a CACHE INDEX statement:
mysql> CACHE INDEX world.City IN city_cache; +------------+--------------------+----------+----------+ | Table | Op | Msg_type | Msg_text | +------------+--------------------+----------+----------+ | world.City | assign_to_keycache | status | OK | +------------+--------------------+----------+----------+
| 3. | Once the table has been assigned to city_cache, MySQL discards any index information for the table that currently is in the default key cache and begins using the new cache for queries that refer to the table. If you want to preload the table's indexes into the cache immediately, use a LOAD INDEX INTO CACHE statement:
mysql> LOAD INDEX INTO CACHE world.City; +------------+--------------+----------+----------+ | Table | Op | Msg_type | Msg_text | +------------+--------------+----------+----------+ | world.City | preload_keys | status | OK | +------------+--------------+----------+----------+
|
The example demonstrates how to set up a key cache at runtime by issuing the appropriate SQL statements manually. To configure the cache every time the server starts, put the statements in an initialization file and use an --init-file option that names the file. Suppose that the data directory is /usr/local/mysql/data. Create a file named server.init in that directory and place the following statements in the file, one statement per line: SET GLOBAL city_cache.key_buffer_size = 4194304; CACHE INDEX world.City IN city_cache; LOAD INDEX INTO CACHE world.City;
Then put the following lines in an option file: [mysqld] init-file=/usr/local/mysql/data/server.init
When the server starts, it will read and execute the statements in the file, causing the city_cache key cache to be set up. The preceding discussion demonstrates how to associate a single table with a key cache, but you need not create a cache for each table. You might create a cache and associate a group of tables with it, such as all the tables used by a particular application. |
