Databases Reference
In-Depth Information
Now when MySQL reads blocks from the indexes on these tables, it will cache the
blocks in the specified buffer. You can also preload the tables' indexes into the cache
with the
init_file
option and the
LOAD INDEX
command:
mysql>
LOAD INDEX INTO CACHE t1, t2;
Any indexes you don't explicitly map to a key buffer will be assigned to the default
buffer the first time MySQL needs to access the
.MYI
file.
You can monitor key buffer usage with information from
SHOW STATUS
and
SHOW VARI
ABLES
. You can calculate the percentage of the buffer in use with this equation:
100 - ( (Key_blocks_unused * key_cache_block_size) * 100 / key_buffer_size )
If the server doesn't use all of its key buffer after it's been running for a long time, you
can consider making the buffer smaller.
What about the key buffer hit ratio? As we explained previously, this number is useless.
For example, the difference between 99% and 99.9% looks small, but it really repre-
sents a tenfold increase. The cache hit ratio is also application-dependent: some ap-
plications might work fine at 95%, whereas others might be I/O-bound at 99.9%. You
might even be able to get a 99.99% hit ratio with properly sized caches.
The number of cache
misses
per second is much more empirically useful. Suppose you
have a single hard drive that can do 100 random reads per second. Five misses per
second will not cause your workload to be I/O-bound, but 80 per second will likely
cause problems. You can use the following equation to calculate this value:
Key_reads / Uptime
Calculate the number of misses incrementally over intervals of 10 to 100 seconds, so
you can get an idea of the current performance. The following command will show the
incremental values every 10 seconds:
$
mysqladmin extended-status -r -i 10 | grep Key_reads
Remember that MyISAM uses the operating system cache for the data files, which are
often larger than the indexes. Therefore, it often makes sense to leave more memory
for the operating system cache than for the key caches. Even if you have enough memory
to cache all the indexes, and the key cache miss rate is very low, cache misses when
MyISAM tries to read from the data files (not the index files!) happen at the operating
system level, which is completely invisible to MySQL. Thus, you can have a lot of data
file cache misses independently of your index cache miss rate.
Finally, even if you don't have any MyISAM tables, bear in mind that you still need to
set
key_buffer_size
to a small amount of memory, such as
32M
. The MySQL server
sometimes uses MyISAM tables for internal purposes, such as temporary tables for
GROUP BY
queries.
