The key is understanding the buffers. There are buffers and caches for all
sorts of things, and finding out how full they are is crucial to maximizing
performance. MySQL performs best when it is making full use of its buffers,
which in turn places a heavy demand on system RAM. Unless you have 4GB
RAM or more in your machine, you do not have enough capacity to set very
high values for all your buffers; you need to pick and choose.
Measuring Key Buffer Usage
Adding indexes to your data enables MySQL to find data faster. However,
ideally you want to have these indexes stored in RAM for maximum speed,
and the variable key_buffer_size defines how much RAM MySQL can
allocate for index key caching. If MySQL cannot store its indexes in RAM,
you will experience serious performance problems. Fortunately, most
databases have relatively small key buffer requirements, but you should
measure your usage to see what work needs to be done.
To do this, log in to MySQL and type SHOW STATUS LIKE
‘%key_read%’; this returns all the status fields that describe the hit rate of
your key buffer. You should get two rows back, Key_reads and
Key_read_requests, which are the number of keys being read from disk
and the number of keys being read from the key buffer. From these two
numbers you can calculate the percentage of requests being filled from RAM
and from disk, using this simple equation:
Click here to view code image
100 – ((Key_reads / Key_read_requests) ∞ 100)
That is, you divide Key_reads by Key_read_requests, multiply the
result by 100, and then subtract the result from 100. For example, if you have
Key_reads of 1,000 and Key_read_requests of 100,000, you divide
1,000 by 100,000 to get 0.01; then you multiply that by 100 to get 1.0, and
subtract that from 100 to get 99. That number is the percentage of key reads
being served from RAM, which means in this case, 99% of your keys are
served from RAM.
Most people should be looking to get more than 95% served from RAM,
although the primary exception is if you update or delete rows very often;
MySQL can’t cache what keeps changing. If your site is largely read-only,
this should be around 98%. Lower figures mean you might need to bump up
the size of your key buffer.
If you are seeing problems, the next step is to check how much of your