Tuning MySQL Server Settings

The default configuration file for MySQL is intended not to use many resources, because its a general purpose sort of a configuration file. The default configuration does enough to have MySQL running happily with limited resources and catering to simple queries and small data-sets. The configuration file would most definitely need to be customized and tuned if you intend on using complex queries and when you have good amount of data.
Most of the tunings mentioned in this post are applicable to the MyISAM storage engine, I will soon be posting tunings applicable to the Innodb storage engine.

Getting started

Before you start changing configuration settings, you should keep in mind the fact that “more is not always better”. You might have 16-20 GB of RAM, but that doesn’t mean that you should start setting values too high. Setting too high values, can degrade performance and might cause your server to run out of memory. So be very calculative about how much memory to assign.
Although there are many different configuration options, but I will only be touching the important ones, that will help you out the most in tuning the MySQL server.
Now let’s get our hands dirty.

Key buffer size

The the most important option if you are using lots of MyISAM tables is key_buffer. You should set it up to be 25% to 50% of available memory. You have to leave memory for OS cache as well because MyISAM uses OS cache to cache table data. Even if you are not using MyISAM tables, still you would be better of allocating 32M to 64M of memory to the key buffer, because internally MySQL uses MyISAM tables for chores such as creating temporary tables.

Thread cache

The thread cache holds threads that are not being used by any connection, but are available to be used. A thread is created for each connection to the MySQL server, and of course thread creation takes time and resources. The way MySQL works is, when a new connection is requested MySQL first looks into the thread cache to see if there is any ideal thread, if it finds one it uses that one for the connection, if it doesn’t find any it has to create the thread. Obviously MySQL response would be much faster if its just using threads from the cache and not creating them.
The number of threads in the cache is dictated by the thread_cache_size variable. This variable should have an adequate value, according to the connections requests per second that your server is receiving.
A good approach is to look at the Threads Created and make sure that the number of threads created per second does not exceed 1.
Each thread in a thread cache typically uses 128KB of memory, so its not really memory intensive and high numbers here won’t hurt you. But don’t keep it at more than 1000 because some OS don’t handle large number of threads well.

Table cache

Just like creating threads can be expensive, similarly opening tables is expensive too. Of course if the the table definition and data is already in the cache, then it can be queried much faster, as compared to opening the table file for each query. Keep an eye on the Opened Tables status variable and if you see the number large, increase the value of your table cache. Also keep in mind that every connection to the MySQL server needs to have its own copy of the table in the table cache, which means that if you have say 10 tables that are opened simultaneously by 5 connections, then each of the connection will have a separate entry for the same table in the table cache, hence in this case there will be 10×5 entries in the table cache. So generally a good value is calculated by multiplying the number of most frequently used tables by the average number of concurrent connections you have at peak times. For example, if you have 10 frequently accessed tables and 200 concurrent connections, table cache size should be 10×200. The configuration variable for table cache size is table_cache

Query cache size

Query cache is a very important functionality for read-intensive applications. Query cache stores complete result-sets of SELECT queries, enabling very fast retrievals on query cache-hits. The query cache is stored completely in memory, which of course is the reason for very fast retrievals from query cache. The configuration variable that governs the size of the query cache is query_cache_size. A value between 64M to 1024M is sufficient for most of the use cases.

Sort buffer size

Sort buffer size controls who big the buffer should be for filesorts. Sort buffer size is one of those configuration setting that will make the performance of MySQL server suffer, if set very high. And there are reasons for this. A sort buffer is all at once by the MySQL server although it many not be needed to be entirely filled (say if you have a sort buffer of 1MB and you only need 100KB for sorting, still MySQL will use the whole 1M and hence waste memory). Besides sort buffer is one of those settings that is used per connection, meaning if you have 100 connections open and each performing a sort query and the size of your sort buffer is 1MB, then the amount of memory being utilized for sorting would be 100 x 1MB = 100MB.
Hence, its best to keep this value small. The best method is to keep it small between 128KB to 1M and increase it only during the length of the connection if needed.

Read buffer size

Read buffer size is another of those variables whose value you should keep as small as possible. MySQL doesn’t allocate any memory from this buffer until a query needs it, but when it does it allocates the entire chunk of memory even if the entire chunk is needed or not. Hence large values here can really be a waste here.
Similar to the sort buffer size, the optimal way is to keep it small initially 128KB to 1MB, and then increase it only for the connection that needs more buffer memory.
Having gone through this article, I hope you will understand that allocating large amounts of memory is not always the right solution. You have to work on a case by case basis.
One more thing to keep in mind is that the best way to start changing the configuration is to change one or two values, and then run a benchmark test to see the effect of the change, this way you have a total grasp of how the configuration changes are effecting the performance of your server.