Tuning Server Parameters

You can tune your MySQL server parameters to try to optimize your configuration.

As a reminder, you can check the current values of server parameters with this:

 show variables; 

You can see the effects of your server configuration by looking at the output of this:

 show status; 

Another useful tool for monitoring what is happening with your server at any given time is Jeremy Zawodny's mytop Perl script.

This tool acts as the equivalent to the Unix top command and shows what processes are active, the process state, the time spent, and so on. Additionally, it allows you to drill into a MySQL process and see the actual query being executed.

You can download this from

http://jeremy.zawodny.com/mysql/mytop

All of these tools will allow you to keep track of the changes you have made and the effects they have.

There are so many server parameters in my.cnf options files that you may well wonder where to begin. Most versions of MySQL come with sample my.cnf files, typically in the support-files directory of your installation. There are four suggested my.cnf files in this directory: my-huge.cnf , my-large.cnf , my-medium.cnf , and my-small.cnf . You can choose one as a starting point for your system.

The key parameters you will tune have to do with how MySQL uses memory. For any database server, more memory is a good thing, but it's important that this memory is available to the database server and that it is allocated appropriately between tasks .

MySQL has a set of internal buffers and caches. You can control how much memory is allocated to each of these. The two most important parameters to control are the key_buffer_size and the table_cache . These two are shared across all the threads running on the server, and they have a huge influence on performance.

The key buffer is where MyISAM indexes are stored in memory. As index blocks are used, they will be loaded into the buffer. Each time a query is issued, if the relevant index block is in the buffer, it will be read from there. Otherwise, the index block will need to be loaded from the disk into the key buffer, which is obviously slower. Generally speaking, with the key buffer, bigger is better.

When considering what value to set for the key_buffer_size , you should look at how much memory you have overall, whether or not the server is a dedicated MySQL server, and how big your index data is (that is, how big your .MYI files are in total). Jeremy Zawodny, renowned MySQL tuning expert at Yahoo!, recommends setting this value to somewhere between 20% and 50% of the total memory on a dedicated server. If you are using a shared machine, it should obviously be a smaller amount. It should also be a smaller amount if your index data is small. If you have only 20MB of index data, there is little point in allocating 128MB to the key buffer.

Note also that the key buffer is only for MyISAM tables. Other table types have their own separate parameters for tuning. There is no point in greatly increasing the size of the key buffer if you are using only InnoDB tables, for example. In this case, the parameter you need is called innodb_buffer_pool_size . The InnoDB buffer pool stores both index and table data. (You can find more information on InnoDB configuration in Chapter 12 and in the MySQL manual.)

The second really important parameter is the table cache , controlled via the table_cache option. This limits the maximum number of tables that can be open at once. With MyISAM tables, each table and each index is a separate file on your operating system. Opening and closing files is slow, so these files are left open until they are explicitly closed, the server shuts down, or the total number of open tables exceeds the table_cache parameter. Increasing the table_cache value will be helpful if you have a large number of tables on your server. Your operating system will impose an upper limit on the number of open files or the number of files opened by a single process or user , so check this for your system before resetting the table_cache value.

Besides these two global memory pools, there are various chunks of memory allocated on a per-thread basis ”for example, the sort buffer and the read buffer . The value is the same for each thread, but each thread can have this amount of memory allocated to the specified purpose.

The read buffer size, controlled by the read_buffer_size parameter, is used when a full table scan is performed to store the table data. The more table data that can be stored, the fewer disk reads that will need to be performed; however, if this value is set too high, the set of read buffers for each thread can become a memory hog. (You may want to note that this parameter was previously called the record buffer and was controlled by the record_buffer parameter.)

The sort buffer, controlled by the sort_buffer parameter, is used when you run queries containing ORDER BY clauses. It is used to sort the data. If you are sorting large datasets, make it bigger, but the same riders apply as to the record buffer.