Improve MySQL performance to speed up websites

In every business the rule is that if your service is slow, you’ll loose customers, and it certainly applies online too. If your website takes two seconds to load instead of a half second, you’ll loose visitors and business. Even if it is just about improving by a few seconds, on the web it does matter.

We’ve written before about the web server speed and application level performance, now it’s time to tackle the most common bottle neck in larger web apps: the database. This time we did some benchmarks of our WordPress hosting optimized server, running Linux,  Nginx, MySQL and PHP. Since the LAMP-stack is the most common application stack on the web and WordPress might be the most common web server app, we hope the performance tips below will be useful for many. The research presented here will for sure be closer to real life scenarios than generic benchmarks done by The Transaction Processing Performance Council.

For the benchmarks we disabled our FastCGI cache and started out by taking the baseline figures for the seravo.fi/blog page, which is somewhat database heavy and most likely to reflect possible speed gains. Testing was done with Apache Bench, measuring 500 page loads in 10 concurrent connections:

$ ab -n 500 -c 10 -g blogpage-baseline.dat http://linuxnatives.net/blog

This was run twice and the first run was considered a warm-up, so the results where recorded only for the latter run. Baseline speed was 8.23 requests per second and 1211 milliseconds per request (mean values).

As the first step in MySQL optimization we installed MariaDB, a drop-in-replacement of MySQL. It is basically a fork of MySQL, but maintained and developed by the original MySQL developers, many of who fled from Oracle. When Oracle acquired Sun and thus also their most serious  competitor, the MySQL database and trademark, a concern rose whether or not Oracle is serious about developing MySQL, or will it simply keep MySQL just enough alive to shake off the competition authorities. You can’t yet directly say that MySQL has gone sour, but there are some concerning signs, like Oracle adding close source extensions to MySQL and Oracle handling security updates poorly. Luckily all the security vulnerabilities are fixed in MariaDB and it is easy to migrate to it by installing MariaDB via the MariaDB.org website and repositories.

After simply switching to MariaDB the speed was 8.64 requests per second and 1151 milliseconds per request (mean values). Not a big change, but still in the right direction. The next thing was to change the storage engine from WordPress default MyISAM to XtraDB (as InnoDB in called in MariaDB). That was done using this little spell:

$ sudo -s
$ DATABASENAME="wp"
$ echo 'SHOW TABLES;' \
 | mysql --defaults-file=/etc/mysql/debian.cnf ${DATABASENAME} \
 | awk '!/^Tables_in_/ {print "ALTER TABLE `"$0"` ENGINE = InnoDB;"}' \
 | column -t \
 | mysql --defaults-file=/etc/mysql/debian.cnf ${DATABASENAME}
$ exit

This change alone however didn’t have any notable gains, as the speed was 8.55 req/s and 1165 ms/req. Next we increased the InnoDB caching capabilities by editing file /etc/mysql/my.cnf and increasing the sizes of the following options:

innodb_log_file_size = 32M
innodb_buffer_pool_size = 1024M
innodb_log_buffer_size = 4M

Since innodb_log_file size cannot be applied via simple restart, we ran sudo /etc/init.d/mysql stop, edited options, deleted old log files out of the way with sudo rm /var/lib/mysql/ib_logfile* and then restarted with sudo /etc/init.d/mysql start. A good log file size depends on the amount of traffic your site gets. In addition to InnoDB changes we enabled the option that allows to log long running queries:

slow_query_log=1

The file /var/log/mysql/mariadb-slow.log was empty however.

With these changes we stood at 8.73 req/s and 1141 ms/req. As the last thing we increased the query cache:

query_cache_limit = 512K
query_cache_size = 128M

After the last round the final score was  8.87 req/s and 1124 ms/req for our 500 request benchmark. All in all the gains were very small (8.87 vs. 8.23 req/s is below 8%) and no single significant option was found. Graph of all benchmarks below:

From the graph we can conclude that our optimization was successful for high loads. At the left we can see that the addition of query cache will slow things down slightly on low loads, so you might want to consider leaving out our last optimization tip in your setup.

Note that this article was about choosing your MySQL flavor and settings. There is still many other things you can do to optimize your database speed. You could for example time all SQL queries and see which are slow and which could be rewritten or perhaps have an index added for a common WHERE statement. For example for WordPress there are many handy plugins available for analyzing SQL queries, which we will present in a later article. Stay tuned!