WordPress DB Tuning

Last weekend a regular client of mine asked me to take a look at their website and try to improve its load times. Like a lot of places on the web, it was powered by WordPress and had a number of caching plugins installed in an effort to reduce the amount of time visitors have to wait before they can read and move about the page. Unfortunately, despite their best efforts, they could not get everything to finish loaded in less than 3 seconds during the two busy periods of the day. After a couple of minutes of research, the problem was pretty apparent.

First a little background. The site is a mobile game review site based out of Vancouver, with readers aged anywhere from 13 to 30 … give or take. Amazon's EC2 platform is used, with the database being hosted on a dedicated c4.xlarge instance with two t2.large web servers reading from this database. There are typically 200,000 visitors a day to the site and most traffic happens around lunchtime during the week and between 4:00pm and 10:00pm every day. This is not anything too crazy, though the EC2 instances are much larger than the ones I use for 10Centuries1. Because of all the caching plugins in use, the main site itself usually loads in about 1.5 seconds. The problem came down to a pair of custom-made plugins that were not particularly efficient … and a default database configuration.

Tuning a MySQL 5.7 Database for WordPress

Note: This not for the faint of heart. If you're not comfortable getting into the nitty-gritty of configuration files and occasionally seeing that MySQL refuses to restart because of something that's not quite right in my.cnf, then you do not want to do this. You can either continue to run stock, or you can hire someone to do this for you.

Note 2: The settings that I'll outline here worked for my customer's use case. Do not simply copy/paste and expect the same results. Tuning requires planning.

The two custom plugins that were causing problems had inefficient queries that needed some optimization and a bit of a rewrite to avoid using a FILESORT, but this was just a small part of the performance issues. The big one was a buffer pool that was simply too small. A c4.xlarge instance has 7.5GB of memory, but most of it was sitting unused. The WordPress database was about 2GB in total, so it wouldn't be an issue to have it all in memory ready to be read. I set the buffer pool to 3GB.

innodb_buffer_pool_size = 3G

I then set up a tmpfs partition in memory for MySQL to use for temporary tables, and ensured the InnoDB database engine was properly configured to use it:

innodb_temp_data_file_path = ../../mysqltmp/ibtmp1:128M:autoextend:max:3G

I then went through and ensured some of the other InnoDB settings were set for maximum efficiency. Because ACID compliance is not 100% critical with this website, I could get away with using different log flushing times.

innodb_flush_method = O_DIRECT
innodb_flush_log_at_trx_commit = 0
sync_binlog = 0
innodb_flush_log_at_trx_commit = 0
innodb_buffer_pool_instances = 8
innodb_thread_concurrency = 8
query_cache_size = 0
query_cache_type = OFF

With this done, I restarted MySQL. Page load times dropped from a "good" 1.5 seconds mid-day to 0.2 seconds, and 0.5 seconds during peaks. Not too bad, if you ask me.

There are a lot of InnoDB settings that can be configured, tweaked, and explored. While the default configuration is "good enough" to get people going with a WordPress site, it's really important that some research and time be spent getting the database performance up to something respectable. This isn't something that plugins alone can fix.

  1. which, I will admit, does not run WordPress.