WordPress requires that you use a MySQL compatible database for its database backend. It used to be that you could confidently choose MySQL and go on with life, but in 2019 the choice isn’t quite that simple. With the MySQL, MariaDB, and Percona as the attractive options, how do you know which to choose?
Choosing a database isn’t always about performance, but for the sake of this article it will be 🙂
For this series of tests we tested database performance out-of-the-box with no special tweaks. It is quite possible that a professional database administrator could make each database run more performant, but most people hosting WordPress aren’t DBAs. With regards to caching, none was enabled. We wanted to test database performance, not cache performance.
What was tested?
The WordPress host machine was a DigitalOcean CPU Optimized droplet with 16 vCPUs (dedicated hyper-threads) and 32 GB of RAM. This monster of a machine was chosen so that we could be certain that Nginx + PHP-FPM weren’t the cause of any bottlenecks. A minor tweak to the PHP-FPM config allowed for full use of all 16 vCPUs.
The database host machines were DigitalOcean CPU Optimized droplets with 4 vCPUs (dedicated hyper-threads) and 8GB of RAM. CPU Optimized droplets were chosen because we didn’t want our tests to be at the mercy of shared CPU resources.
Each deployment was in DigitalOcean’s SFO2 region with the WordPress server communicating with the database over the internal private network. The traffic producing nodes were deployed in DigitalOcean’s NYC3 data center and communicated via the public internet.
For each database that was tested, we ran a load test with the following parameters:
500 concurrent users
2 req/s ramp up
30 minute duration
The goal here wasn’t to bring the database to it’s knees but instead see how it performed under sustained heavy load, but not so heavy that it falls over.
MariaDB WordPress Performance
During the MySQL acquisition of Oracle in 2009 there was a lot of concern amongst the core developers that Oracle would eventually close off MySQL to the world (similar to Oracle’s business model). Before that could take place, a GPL fork of MySQL was created named MariaDB.
MariaDB is open source and in active development. But how does it stand up to our WordPress load tests? Let’s find out.
First we’ll take a look at the requests and failures per second.
As you can see from the results above the performance scales up very well over time eventually peaking at ~379 req/s. We see periodic database-related errors (the vCPUs on the database were almost completely saturated) but nothing too crazy. Next, let’s see how the response times look.
The median response time of WordPress when backed by MariaDB under heavy load stays remarkably consistent. You can see that the average is slowly creeping up by the end of the test, but nothing that would be noticeable by customers yet.
The response time distribution is a little more interesting than the median response time. 90% of all requests finish in under 300ms and 99% of all requests finish in less than 500ms. Overall, the performance of MariaDB out of the box with no configuration is quite good.
Percona WordPress Performance
Another open-source fork of MySQL, Percona was started in 2006 and has been steadily delivering value-added features and enterprise support on top of MySQL for more than 12 years. With all that accumulated experience, how does Percona measure up?
Overall the Percona WordPress performance was pretty good. Not quite a good as MariaDB but nothing to scoff at. The one interesting thing was that the error rate was consistent across most of the test once the request volume passed ~200 requests/s. Lets see if the response time graph adds to the story.
The data here is actually pretty interesting. The response time for Percona was comparable to MariaDB right up until the time it started getting consistent errors. After that, it more than doubled and stayed that way for the rest of the test.
The response time distribution tells similar story to the response time chart. The difference between the 50th percentile and the 99th percentile shows that performance was very consistent across the entire test, it just wasn’t as good as MariaDB.
MySQL WordPress Performance
Last but not least (well….) in our test is MySQL. It’s been around since 1994 and is now owned by Oracle. In it’s latest releases there has been lot of great features such as window functions and more JSON features to compete with PostgreSQL. So let’s see how it did.
MySQL had a similar trajectory to Percona: It did pretty good across the board with a small but consistent series of errors after it started to go past 200 req/s. Also note that it never achieved higher than 300 req/s, which both MariaDB and Percona did.
The shape of the MySQL response time graph looks nearly identical to Percona, just about 50ms slower once the vCPUs started to get saturated. The detailed look in the response time distribution tells a similar story.
The response time distribution is where you can see Percona and MySQL diverge a bit. At the 99th percentile MySQL is returning at 875ms, while Percona was more like 550ms. In general, the distribution matches what one would expect given how the response time graph looks
The out of the box numbers for MariaDB make it look like the clear winner here. And compared to both Percona and MySQL it is in the raw performance department. This isn’t to say that you couldn’t tune Percona or MySQL to out-perform MariaDB, but only that you get more performance with zero configuration changes.
The database is the most important part of any WordPress site. If you aren’t adept at managing MySQL, it can be a serious risk for you and your clients. One way to de-risk this portion of your WordPress site is by going with a managed MySQL offering. There are ton of different options out there, but for this post we’re going to look at Digital Ocean’s Managed MySQL.
Why Use DigitalOcean Managed MySQL?
There are a lot of reasons to use Digital Ocean’s Managed MySQL (or some other offering) including:
Simple Setup – With a few simple clicks you can have a high quality MySQL cluster set up.
Horizontal Scalability – Site growing fast? You can spin up read-only nodes to help scale out read operations.
Automated Daily Backups – No need to set up your own backups. DigitalOcean takes care of it for you.
Automated Failover – If for some reason your primary database node fails, you will automatically fail over to your warm spare.
Security – MySQL best practices for security are automatically followed by DigitalOcean. In addition to that your database is isolated to your private network so that outside requests can’t access it by default.
Baseline Performance Test
To get things started let’s do a baseline performance test where the MySQL database is on the same box as the Nginx server. The server configuration is as follows:
For completeness, let’s also take a look at the response time distribution.
Given how many failing requests we had and zero caching, the ones that did manage to get through didn’t perform too poorly. 99% in 2 seconds or less.
DigitalOcean Managed MySQL
When setting up a managed MySQL database on DigitalOcean you get the option to select the underlying hardware that powers it. For this blog post we went with the minimum possible configuration (1GB RAM, 1vCPU).
Knowing that fewer resources were allocated, it was expected that performance would actually be a bit worse on the dedicated MySQL instance. These expectations were confirmed with the load test.
All things considered, 50 req/s against a WordPress site with no caching isn’t all that bad. Once we hit that level, the database charts were showing 100% CPU saturation and thats when we started to see the error rate increase. Now let’s look at the response time distribution.
As expected performance is slightly worse here due to fewer resources on the dedicated MySQL machine, but not in a huge way.
Cost & Why Managed MySQL is Important
For our test we used the most basic configuration that DigitalOcean offers:
1 GB RAM
10 GB Hard Disk
All of this and not having to manage MySQL for $15/month. Not too bad, but to really be getting your money’s worth (automatic failover) you need to spend more money.
2 GB RAM
25 GB Hard Disk
1 Standby node
With automatic failover you start to reduce the risk to yourself and your customers a lot. However, this level of availability isn’t free 🙂 DigitalOcean doesn’t support failover on their cheapest node, so you have to upgrade to the next level ($30/month). After that, the failover node costs you an additional $20/month. Now we’re looking at $50/month for a managed MySQL cluster with automated failover.
If you run a WordPress based business on DigitalOcean, $50/month buys you a lot of peace of mind. It’s also easy to scale up as traffic increases and the cost is competitive in the landscape of managed MySQL. If you happen to be an excellent DBA though, you can definitely manage your own cluster at a reduced monetary cost, but increased time cost.
Managing your own database cluster is probably fine if cost is a problem and you are good at it. In general though, if you can afford it we recommend using a managed MySQL host so you can push the complexity of operating a highly available MySQL cluster on to someone else.