Optimizing your MySQL server

When building a scalable web application, the first performance problem most people will encounter is with their database queries. As your database grows, it is very likely that things will slow down as the database server has to look through more and more data to find what you need.

The Solution

The good news is that database performance issues can usually be resolved without spending any extra money on hardware or software.

In the first of 2 articles, I am going to discuss simple ways to improve the performance of a MySQL database by reconfiguring the database server. This is often the easiest place to start, particularly if you are running other people's applications and cannot change the code.

Server Configuration

The aim of these changes is to allow MySQL to make the best use of the hardware available to it. The MySQL configuration file is quite daunting, with a huge number of options, but we are going to focus on a few that improve the performance of the InnoDB storage engine.

If for any reason your application is not using InnoDB, I would strongly recommend migrating to it in order to benefit from the improvements described in this guide. The good news is that this is the default for recent installations of MySQL and modern web frameworks.

All the settings discussed below can be changed by editing lines in the [mysqld] section of /etc/mysql/my.cnf and restarting MySQL. If a line cannot be found in the file, it can be added anywhere in the [mysqld] section.

Memory Allocation

MySQL holds as much of your database as possible in RAM, and will only read from the disk when necessary. In particular, it will try to keep all indexes in RAM to ensure that lookups are as fast as possible. This area of memory is called the InnoDB Buffer Pool and is often significantly too small.

This value should be set as large as possible, while obviously leaving suffucuent memory for other processes on the server. As an example, if you have an application server with 1GB of RAM running a Rails application, it might be sensible to allocate 400MB to the MySQL buffer pool, leaving 600MB free to comfortably run 2 Rails application servers.

Unfortunately, this value cannot be changed without restarting MySQL, so it is a good idea to carefully consider how much memory to allocate.

innodb_buffer_pool_size = 400M

Miscellaneous Settings

There are some InnoDB settings that improve performance in most cases.

Firstly, we will configure MySQL to store each table in its own file. This reduces disk space waste by properly freeing up space when tables are dropped. It also allows more effective disaster recovery.

innodb_file_per_table = 1

We will also configure MySQL to write directly to disk rather than through additional layers of the operating system. This is more efficient and improves IO performance.

innodb_flush_method = O_DIRECT

Flushing Data to Disk

All MySQL queries that insert or update data happen inside a transaction. Your application or web framework may start a transaction for each web request, wrapping several queries inside it, or if it does not, MySQL will automatically create a transaction for each query you run.

By default, when a transaction finishes, MySQL will write all data from that transaction to the disk, and not allow the application to continue until it has received confirmation that the data has been safely stored away. This is a very sensible way for things to work, particularly if it is essential that a user does not receive a confirmation until their data has definitely 100% been saved to disk.

However, committing each transaction to disk carries a significant overhead, so if you are running a large number of transactions, you may find that INSERT and UPDATE queries seem slow. An example of this might be a web application that logs each page a user visits, or a background task that runs a large number of INSERT queries without wrapping them in a transaction.

MySQL allows you to change this behaviour so that data is only committed to disk once per second, and applications are allowed to insert more data before the previous data has finished been written to disk. This greatly improves the performance of INSERT and UPDATE queries, however it carries the risk that if there is a power failure, very recent changes will be lost.

If this is an acceptable balance, then you can enable this behaviour with this setting:

innodb_flush_log_at_trx_commit = 2

Next time...

In the next article, I will discuss indexes. When used correctly, these are the most effective way to boost database performance.