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).
|
