Time for an update
In 2001, I wrote an
article entitled Optimizing
MySQL: Hardware and the Mysqld Variables. That was almost three years ago,
when MySQL 4 was but a twinkling in Monty Widenius' eye. With MySQL 4 now
stable, and MySQL 4.1 and 5.0 already out in alpha, it is time for an update to
that article. This month we look at the mysqld variables, focusing on the key
ones to tweak to get your system working optimally. I assume you know all about
the my.cnf file, and how to set variables. If not, read the earlier
article.
key_buffer_size
The key_buffer_size
is probably the most useful single variable to tweak. The larger you set it,
the more of your MyISAM table indexes you store in memory. With most queries
making use of an index, and memory being an order of magnitude faster than
disk, the importance of this variable cannot be overestimated.
On dedicated MySQL
servers, the rule-of-thumb is to aim to set the key_buffer_size to at
least a quarter, but no more than half, of the total amount of memory on the
server. Ideally, it will be large enough to contain all the indexes (the total
size of all .MYI files on the server). If you are unable to make it large
enough for this, the best way to fine-tune the setting is to compare the key_reads
and the key_read_requests status variables. The latter is the total
number of requests making use of an index, while the former is the total number
of those requests that had to read from disk. You want at least 100 requests to
every request from disk, preferably a lot more. Have a look at scenario 1, with
the same query run a few seconds apart.
Scenario
1
mysql> SHOW STATUS LIKE '%key_read%';
+-------------------+------------+
| Variable_name | Value |
+-------------------+------------+
| Key_read_requests | 3606100254 |
| Key_reads | 2594030 |
+-------------------+------------+
mysql> SHOW STATUS LIKE '%key_read%';
+-------------------+------------+
| Variable_name | Value |
+-------------------+------------+
| Key_read_requests | 3606102741 |
| Key_reads | 2594030 |
+-------------------+------------+
This is a healthy ratio,
around 1400 to 1. Of the 2500 index requests between the two samples, none
required the disk. On this server, the key_buffer is set to 768M, while
the total memory available is 3GB.
Scenario
2
mysql> SHOW STATUS LIKE '%key_read%';
+-------------------+-----------+
| Variable_name | Value |
+-------------------+-----------+
| Key_read_requests | 609601541 |
| Key_reads | 46729832 |
+-------------------+-----------+
In scenario 2, it is
shocking, about 13 to 1. On this server, the key_buffer_size was set to
16MB out of 64MB. If you are in a similar situation, it is clear what your next
hardware upgrade should be. RAM is always the primary hardware upgrade you can
do to improve your system.
The Query Cache variables
MySQL 4 added an
extremely useful tool for getting some extra from a database server - the query
cache. I have recently written a dedicated article on the topic, which you can
read here.
The table cache
The table_cache
remains a useful variable to tweak. 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. One thing to note is that you may have more open tables
than there are database tables on the server. MySQL, being multi-threaded, may
be running many queries on the table at one time, and each of these will open a
table. A good way to see whether your system needs to increase this is to
examine the value of open_tables at peak times. If you find it stays at
the same value as your table_cache value, and then the number of opened_tables starts
rapidly increasing, you should increase the table_cache if you have enough
memory. Look at these three scenarios, during peak times.
Scenario
1
mysql> SHOW STATUS LIKE 'open%tables%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Open_tables | 98 |
| Opened_tables | 1513 |
+---------------+-------+
The table_cache is
set to 512, and the server has been running for a long time. If the server is
taking strain elsewhere, the table_cache setting could probably be reduced
safely.
Scenario
2
mysql> SHOW STATUS LIKE 'open%tables%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Open_tables | 64 |
| Opened_tables | 517 |
+---------------+-------+
The table_cache is
set to 64, and the server has been running for a long time. Even though open_tables
is at its maximum, the number of open_tables is very low considering that the
server has been up for ages. There is probably not much benefit in upping the table_cache.
This example came from a development server.
Scenario 3
mysql> SHOW STATUS LIKE 'open%tables%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Open_tables | 64 |
| Opened_tables | 13918 |
+---------------+-------+
The table_cache is
set to 64, and the server has been running for a short time. This time the table_cache
is clearly set too low. The open_tables is running at maximum, and the
number of opened_tables is already high. If you have the memory, up the table_cache.
sort_buffer
The sort_buffer is
very useful for speeding up myisamchk operations (which is why it is set
much higher for that purpose in the default configuration files), but it can
also be useful everyday when performing large numbers of sorts. It defaults to
2M in the my-huge.cnf sample file, but I have successfully upped it to
9MB on a 3GB server running quite a few sorts.
read_rnd_buffer_size
The read_rnd_buffer_size
is used after a sort, when reading rows in sorted order. If you use many
queries with ORDER BY, upping this can improve performance. Remember
that, unlike key_buffer_size and table_cache, this buffer is allocated
for each thread. This variable was renamed from record_rnd_buffer in
MySQL 4.0.3. It defaults to the same size as the read_buffer_size, which
defaults to 128KB. A rule-of-thumb is to allocate 1KB for each 1MB of memory on
the server, for example 3MB on a machine with 3GB memory.
tmp_table_size
This variable determines
the maximum size for a temporary table in memory. If the table becomes too
large, a MYISAM table is created on disk. Try to avoid temporary tables by
optimizing the queries where possible, but where this is not possible, try to
ensure temporary tables are always stored in memory. Watching the processlist
for queries with temporary tables that take too long to resolve can give you an
early warning that tmp_table_size needs to be upped. Be aware that
memory is also allocated per-thread. An example where upping this worked for
more was a server where I upped this from 32MB (the default) to 64MB with
immediate effect. The quicker resolution of queries resulted in less threads
being active at any one time, with all-round benefits for the server, and
available memory.
innodb_buffer_pool_size
While the key_buffer_size
is the variable to target for MyISAM tables, for InnoDB tables, it is innodb_buffer_pool_size.
Again, you want this as high as possible to minimize slow disk usage. On a
dedicated MySQL server running InnoDB tables, you can set this up to 80% of the
total available memory.
innodb_additional_mem_pool_size
This variable stores the
internal data structure. Make sure it is big enough to store data about all
your InnoDB tables (you will see warnings in the error log if the server is
using OS memory instead).
Conclusion
There are many MySQL
variables, but the ones I've discussed here are usually key to target if your
database is underperforming. Always look at optimizing
your queries first though - the most dramatic benefits usually come from
proper indexing and carefully written queries. Once you are confident in your
queries, then it is time to increase the memory allocated to the variables, and
purchase more RAM if necessary. The effect on many underperforming servers I
have encountered has been startling!
»
See All Articles by Columnist Ian Gilfillan
» 
Comment and Contribute