current key buffer is being used. Use the SHOW VARIABLES command and
look up the value of the key_buffer_size variable. It is probably
something like 8388600, which is 8 million bytes, or 8MB. Now, use the
SHOW STATUS command and look up the value of Key_blocks_used.
You can determine how much of your key buffer is being used by multiplying
Key_blocks_used by 1,024, dividing by key_buffer_size, and
multiplying by 100. For example, if Key_blocks_used is 8,000, multiply
that by 1,024 to get 8,192,000; then divide that by your key_buffer_size
(8,388,600) to get 0.97656. Finally, multiply that by 100 to get 97.656. Thus,
you find that almost 98% of your key buffer is being used.
Now, the important part: You have ascertained that you are reading lots of
keys from disk, and you also know that the reason for reading from disk is
almost certainly because you do not have enough RAM allocated to the key
buffer. A general rule is to allocate as much RAM to the key buffer as you
can, up to a maximum of 25% of system RAM—128MB on a 512MB system
is about the ideal for systems that read heavily from keys. Beyond that, you
will actually see drastic performance decreases because the system has to use
virtual memory for the key buffer.
Open /etc/my.cnf in your text editor and look for the line that contains
key_buffer_size. If you do not have this line, you need to create a new
one. It should be under the line [mysqld]. When you set the new value, do
not just pick some arbitrarily high number. Try doubling what is there right
now (or try 16MB if there’s no line already); then see how it goes. To set
16MB as the key buffer size, you need a line like this:
Click here to view code image
[mysqld]
set-variable = key_buffer_size=16M
datadir=/var/lib/mysql
Restart your MySQL server with service mysqld restart and then
go back into MySQL and run SHOW VARIABLES again to see the
key_buffer_size. It should be 16,773,120 if you have set it to 16M.
Now, because MySQL just got reset, all its values for key hits and the like
will also have been reset. You need to let it run for a while so that you can
assess how much has changed. If you have a test system you can run, this is
the time to run it.
After your database has been accessed with normal usage for a short while
(which might be only a few minutes if you get frequent accesses), recalculate
how much of the key buffer is being used. If you get another high score,