How to Optimize MySQL: Indexes, Slow Queries, Configuration

MySQL is still the world's most popular relational database, and yet, it's still the most unoptimized - many people leave it at default values, not bothering to investigate further. In this article, we'll look at some MySQL optimization tips we've covered previously, and combine them with novelties that came out since.

Configuration Optimization

The first - and most skipped! - performance upgrade every user of MySQL should do is tweak the configuration. 5.7 (the current version) has much better defaults than its predecessors, but it's still easy to make improvements on top of those.

We'll assume you're using a Linux-based host or a good Vagrant box like our Homestead Improved so your configuration file will be in /etc/mysql/my.cnf. It's possible that your installation will actually load a secondary configuration file into that configuration file, so look into that - if the my.cnf file doesn't have much content, the file /etc/mysql/mysql.conf.d/mysqld.cnf might.

Editing Configuration

You'll need to be comfortable with using the command line. Even if you haven't been exposed to it yet, now is as good a time as any.

If you're editing locally on a Vagrant box, you can copy the file out into the main filesystem by copying it into the shared folder with cp /etc/mysql/my.cnf /home/vagrant/Code and editing it with a regular text editor, then copying it back into place when done. Otherwise, use a simple text editor like vim by executing sudo vim /etc/mysql/my.cnf.

Note: modify the above path to match the config file's real location - it's possible that it's actually in /etc/mysql/mysql.conf.d/mysqld.cnf

Manual Tweaks

The following manual tweaks should be made out of the box. As per these tips, add this to the config file under the [mysqld] section:

innodb_buffer_pool_size = 1G # (adjust value here, 50%-70% of total RAM)
innodb_log_file_size = 256M
innodb_flush_log_at_trx_commit = 1 # may change to 2 or 0
innodb_flush_method = O_DIRECT

• innodb_buffer_pool_size - the buffer pool is a storage area for caching data and indexes in memory. It's used to keep frequently accessed data in memory, and when you're running a dedicated or virtual server where the DB will often be the bottleneck, it makes sense to give this part of your app(s) the most RAM. Hence, we give it 50-70% of all RAM. There's a buffer pool sizing guide available in the MySQL docs.
• the log file size is well explained here but in a nutshell it's how much data to store in a log before wiping it. Note that a log in this case is not an error log or something you might be used to, but instead it indicates checkpoint time because with MySQL, writes happen in the background but still affect foreground performance. Big log files mean better performance because of fewer new and smaller checkpoints being created, but longer recovery time in case of a crash (more stuff needs to be re-written to the DB).
• innodb_flush_log_at_trx_commit is explained here and indicates what happens with the log file. With 1 we have the safest setting, because the log is flushed to disk after every transaction. With 0 or 2 it's less ACID, but more performant. The difference in this case isn't big enough to outweigh the stability benefits of the setting of 1.
• innodb_flush_method - to top things off in regards to flushing, this gets set to O_DIRECT to avoid double-buffering. This should always be done, unless the I/O system is very low performance. On most hosted servers like DigitalOcean droplets you'll have SSDs, so the I/O system will be high performance.

There's another tool from Percona which can help us find the remaining problems automatically. Note that if we had run it without the above manual tweaks, only 1 out of 4 fixes would have been manually identified because the other 3 depend on user preference and the app's environment.

Continue reading %How to Optimize MySQL: Indexes, Slow Queries, Configuration%