Optimizing Server Settings - High Performance MySQL - page 351

If you can't use Percona Server's fast warmup feature, some people issue full-table scans
or index scans immediately after a restart to load indexes into the buffer pool. This is
crude, but can sometimes be better than nothing. You can use the init_file setting to
accomplish this. You can place SQL into a file that's executed when MySQL starts up.
The filename must be specified in the init_file option, and the file can include
multiple SQL commands, each on a single line (no comments are allowed).
The MyISAM Key Caches
The MyISAM key caches are also referred to as key buffers ; there is one by default, but
you can create more. Unlike InnoDB and some other storage engines, MyISAM itself
caches only indexes, not data (it lets the operating system cache the data). If you use
mostly MyISAM, you should allocate a lot of memory to the key caches.
The most important option is the key_buffer_size . Any memory not allocated to it will
be available for the operating system caches, which the operating system will usually
fill with data from MyISAM's .MYD files. MySQL 5.0 has a hard upper limit of 4 GB
for this variable, no matter what architecture you're running. MySQL 5.1 allows larger
sizes. Check the current documentation for your version of the server.
When you're deciding how much memory to allocate to the key caches, it might help
to know how much space your MyISAM indexes are actually using on disk. You don't
need to make the key buffers larger than the data they will cache. You can query the
INFORMATION_SCHEMA tables and sum up the INDEX_LENGTH column to find out the size of
the files storing the indexes:
SELECT SUM(INDEX_LENGTH) FROM INFORMATION_SCHEMA.TABLES WHERE ENGINE='MYISAM';
If you have a Unix-like system, you can also use a command like the following:
$ du -sch `find /path/to/mysql/data/directory/ -name "*.MYI"`
How big should you set the key caches? No bigger than the total index size or 25% to
50% of the amount of memory you reserved for operating system caches, whichever is
smaller.
By default, MyISAM caches all indexes in the default key buffer, but you can create
multiple named key buffers. This lets you keep more than 4 GB of indexes in memory
at once. To create key buffers named key_buffer_1 and key_buffer_2 , each sized at
1 GB, place the following in the configuration file:
key_buffer_1.key_buffer_size = 1G
key_buffer_2.key_buffer_size = 1G
Now there are three key buffers: the two explicitly created by those lines and the default
buffer. You can use the CACHE INDEX command to map tables to caches. You can tell
MySQL to use key_buffer_1 for the indexes from tables t1 and t2 with the following
SQL statement:
mysql> CACHE INDEX t1, t2 IN key_buffer_1;