Managing & Using MySQL, 2nd Edition

Database Server Tuning

There are a number of settings you can tweak at the MySQL server level to influence application performance. One thing to keep in mind when tuning a server is that server behavior will affect all the applications using that server. An improvement for one application may have a detrimental effect for other applications on the same server.

There are a number of variables that can be modified in the MySQL server that may improve your performance. A full reference to these parameters can be found by typing mysqld -help.

In general, when tuning MySQL, the two most important variables are:

table_cache

table_cache controls the size of the MySQL table cache. Increasing this parameter allows MySQL to have more tables open simultaneously without opening and closing files.

key_buffer_size

key_buffer_size controls the size of the buffer used to hold indexes. Increasing this will improve index creation and modification and will allow MySQL to hold more index values in memory.