Adjusting the Caches

Let’s now analyze the caching information given by

The two lines below give you the size and efficiency of the cache that stores the database keys, called “key buffer”:

Key buffer size / total MyISAM indexes: 64.0M/2.7G

Key buffer hit rate: 98.8% (243M cached / 3M reads)

In our case, the key buffer is configured with 64 MiB and this size seems to be excellent, because the buffer was used 98.8% of the time. If this percentage is below 90%, you should increase the size of the key buffer, which is done through the option key_buffer_size = 64M, where you should change “64M” with the amount of RAM you want to reserve for the key buffer.

The second most important cache available is the query cache, which stores queries that were already requested before. You can verify its efficiency through the following lines:

Query cache efficiency: 59.7% (2M cached / 3M selects)

Query cache prunes per day: 566279

The higher the percentage shown is, the better. Also, you want to have a low number of cache prunes. The “query cache prunes per day” indicates how many times the cache had to be deleted because more space was needed.

To increase this percentage and decrease the number of prunes per day, you must increase the size of the query cache.

If the line “query cache efficiency” has a “0%” on it, this means that you must turn on the query caching mechanism, because it is disabled (you are missing a query_cache_type=1 in your my.cnf file)!

The three configuration parameters for the query cache that you must have in your my.cnf file are:

query_cache_type = 1

query_cache_size = 4M

query_cache_limit = 1M

The first parameter enables the query cache. The second one configures the size of the query cache. And the third one configures the maximum size of the query to be cached. This is to prevent very big queries from being cached, which would delete several small queries that were previously inside the cache, if the query cache is not big enough to store all queries at the same time.

The next piece of information indicates the number of temporary tables that were created to deal with sorts. You want this number to be as low as possible.

Sorts requiring temporary tables: 0% (58 temp sorts / 641K sorts)

If the number of temporary tables required for sorts is high, you should increase the parameter sort_buffer_size = 4M (where you must replace “4M” with the size you want this cache to be) in the my.cnf file.

The efficiency of the join buffer can be checked through a line such as:

Joins performed without indexes: 86064

If you think the number shown is too high, you can try increasing the join buffer through the parameter join_buffer_size = 4M (where you must replace “4M” with the size you want this cache to be). However, this means JOIN statements on the source code of your website are not using indexes, and you should try to correct this on the source code.

Next we have the number of temporary tables created on disk. This should be as low as possible, since accessing data on a hard drive is much slower than accessing data that is in RAM.

Temporary tables created on disk: 6% (726 on disk / 11K total)

If this percentage is high, you should first optimize the structure of the database tables. Mainly, you must change all rows that are from the type TEXT and storing 255 characters or less to the type VARCHAR.

If after making this adjustment the number of temporary tables created on disk is still high, try increasing the amount of RAM configured through the parameters tmp_table_size = 4M and max_heap_table_size = 4M (where you must replace “4M” with the maximum size you want for tables created in memory to be). Both must be configured with the same value.

Then we have the thread cache, and its efficiency is given by a line such as:

Thread cache hit rate: 97% (938 created / 41K connections)

Here you want this percentage to be as high as possible. If the percentage shown is below 90%, you must increase the thread cache through the parameter thread_cache_size = 4M (where you must replace “4M” with the size you want this cache to be).

And, finally, we have the table cache, and its efficiency can be verified through the line:

Table cache hit rate: 96% (1K open / 1K opened)

Again, we want this percentage to be as high as possible. If this percentage is below 90%, you must adjust the parameter table_open_cache= 4096 (where you must replace “4096” with the size you want this cache to be) in your my.cnf file.

And the line below indicates how bad the table-locking issue is on your server:

Table locks acquired immediately: 99% (17M immediate / 17M locks)

As you can see, we are not having a problem with table locks on this particular server. However, if this percentage is low, you should consider migrating from MyISAM to InnoDB.

Gabriel Torres is a Brazilian best-selling ICT expert, with 24 books published. He started his online career in 1996, when he launched Clube do Hardware, which is one of the oldest and largest websites about technology in Brazil. He created Hardware Secrets in 1999 to expand his knowledge outside his home country.