Книга: Fedora™ Unleashed, 2008 edition

Measuring Key Buffer Usage

Measuring Key Buffer Usage

When you add indexes to your data, it 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 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%';. That 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:

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 1000 and Key_read_requests of 100000, you divide 1000 by 100000 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 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, you should get 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 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 eight million bytes, or 8MB. Now, use the SHOW STATUS command and look up the value of Key_blocks_used.

You can now determine how much of your key buffer is being used by multiplying Key_blocks_used by 1024, dividing by key_buffer_size, and multiplying by 100. For example, if Key_blocks_used is 8000, you multiply that by 1024 to get 8192000; then you divide that by your key_buffer_size (8388600) to get 0.97656, and finally multiply that by 100 to get 97.656. Thus, almost 98% of your key buffer is being used.

Now, on to the important part: You have ascertained that you are reading lots of keys from disk, and you also now 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 of thumb 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 one, 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:

[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 16773120 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 (if you get frequent accesses, this might be only a few minutes), recalculate how much of the key buffer is being used. If you get another high score, double the size again, restart, and retest. You should keep repeating this until you see your key buffer usage is below 50% or you find you don't have enough RAM to increase the buffer further—remember that you should never allocate more than 25% of system RAM to the key buffer.

Оглавление книги