table_cache and key_buffer_size - Page 3

December 17, 2001

So, choose the right config file for your system, and you'll be well on your way. But you can get even more benefits from tweaking the variables some more, and for that, we'll need to take a closer look at some of the variables.

table_cache is a useful one. Each time MySQL accesses a table, it places it in the cache. If your system accesses many tables, it is faster to have these in the cache. A good way to see whether your system needs to increase this is to examine the value of open_tables at peak times (one of the extended status values, above). If you find it stays at the same value as your table_cache value, and then the number of opened_tables starts increasing, you should increase the table_cache if you have enough memory. Look at these three scenarios.

Scenario 1
table_cache - 512
open_tables - 98
opened_tables - 1513
uptime - 3046085

Here it looks like the table cache has been set too high. The server has been up for ages, the number of opened_tables is relatively low, and the open_tables (remember we're checking at a peak time) is nowhere near what it could be.

Scenario 2
table_cache - 64
open_tables - 64
opened_tables - 517
uptime - 1662790 (measure in seconds)

Here, although the open_tables is maxed out, the number of open_tables is very low even though the server has been up for ages. There is probably not much benefit in upping the table_cache (this example comes from a development server).

Scenario 3
table_cache - 64
open_tables - 64
opened_tables - 13918
uptime - 33754

This table_cache is set too low. The open_tables is running at maximum, and the number of open_tables is high, even though the uptime is less than a day. If you have the memory, up the table_cache. One thing to note is that even if you only have 64 tables in your database, you may still have more open tables. MySQL, being multi-threaded, may be running many queries on the table at one time, and each of these will open a table.

The key_buffer_size is another useful one to tweak. It affects the size of the index buffers, and making it as large as possible increases the speed of index handling, particularly reading. A good rule of thumb is to set it from between quarter to half of the available memory on your server (for systems dedicated to MySQL). A good way to check this is to look at the extended status variables, and compare key_read_requests to key_reads.

Scenario 1
key_buffer_size - 402649088 (384M)
Key_read_requests - 609601541
Key_reads - 67299
Scenario 2
key_buffer_size - 16777216 (16M)
Key_read_requests - 609601541
Key_reads - 46729832

The values in scenario 1 are looking healthy. The ratio of key_reads to key_read_requests should be as low as possible, no more than 1:100. In scenario 1 it is close to 1:10000. In scenario 2, it is shocking, about 1:15, and the key_buffer size should be increased to as much as the memory allows (you can see that RAM is the primary hardware upgrade you can do to improve your system).

The Network for Technology Professionals



Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers