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!