Easy MySQL Performance Tweaks

This article is meant to be an easy and relatively safe way to enhance mysql performance. It is not meant to be a complete guide to tuning MySQL. Fully optimizing MySQL takes both time and effort since every application has different requirements. The Debian MySQL packages ship with very conservative memory usage settings and the same is probably true for other Linux distributions and the Windows binaries. If you loosen these settings up a little, MySQL will perform much faster under ordinary circumstances.

The items are ordered on impact, high to low. Feedback and hints will be greatly appreciated.

Key Buffer
The key buffer holds the indexes of tables in memory and a bigger key buffer results in faster row lookups. Adjust according to your own needs. Bigger is better, but prevent swapping at all costs. A good rule of thumb seems to be to use 1/4 of system memory.

key_buffer = 128M

Query Cache
This is where the magic happens. Well, not magic really, just plain old caching. Keeping the result of queries in memory until they are invalidated by additional writes enhances performance by magnitudes. The query_cache_size, as the name suggests, is the total size of memory available to query caching. The value query_cache_limit is the maximum number of kilobytes one query may be in order to be cached. Setting this value too high might prevent a lot of smaller queries to be cached. Setting it too low will result in bigger queries to never be cached, and the smaller queries not being able to completely fill the cache size, which would be a waste of resources. Adjust according to your own needs and memory available:

query_cache_size = 128MB
query_cache_limit = 4MB

Table Cache
An important variable if your application accesses many tables. It is the number of tables a thread can keep open at the same time. A value of 512 should do no harm.

table_cache = 512

Sort Buffers
sort_buffer_size (the variable previously known as sort_buffer), used for grouping and sorting and is a per-thread buffer. If the buffer can not hold the data to be sorted, a sort is performed on disk. Watch out for making this too large as the buffer is allocated for every thread that needs sorting and with many sorts it can easily consume all your memory.

sort_buffer_size = 32M
myisam_sort_buffer_size = 32M

The InnoDB Engine
Most people do not use the InnoDB engine in MySQL and use MyISAM instead. Since MySQL reserves memory for this engine, you are better off without it. If you need InnoDB, you can find more on its settings in the official MySQL docs.

Add `skip-innodb’ to my.cnf to disable the engine.

Binary Logging
MySQL has a few powerful features. Replicating data changes to a second server is one of them. MySQL keeps a log file of data changes which is used for this purpose. If you do not use replication or use the file as incremental backup, you can disable it. This will save you expensive disk write actions for every change to your data. For applications that have a lot of frequently updated data, this can be quite a performance boost. According to the official docs, this will generally result in just a 1% boost but it’s an easy gain if you do not need the log. Read more about the binary log here. Comment the following line:

log-bin = /var/log/mysql/mysql-bin.log

Temporary Tables
Temporary tables are used for sorting and grouping. The buffer is created on demand so watch out for setting this too high here as well. If the buffer cannot accomodate the data, a temp file is used on disk instead.

tmp_table_size = 64MB

Delayed Writing
This setting can greatly improve writing or updating data to a table. Instead of directly committing data to the disk, MySQL queues writes and returns write queries immediately. Be very very careful with this, because this also means that in case of a power failure or crash, you lose data. You can use this for logging if you don’t mind losing a couple of rows in case of a crash.

delay_key_write = 1

Connection Timeout
This is a little tweak that determines the closing of sleeping connections. The default is one hour and is often too long for practical purposes. I often set this at one minute instead (60).

wait_timeout = 60

The above settings are just to make mysql a little faster in general. You can get much better speed improvements by optimizing the database itself. Setting the correct indexes on tables can be a life-saver.