Hosting WordPress Yourself at Scale Part 1 – Dedicated Database Server

In this series we’re going to look at how we can scale our WordPress app to handle more traffic. Throughout the course of this series, we’ll build upon what we learned in Hosting WordPress Yourself and introduce more advanced concepts such as load balancing, MySQL replication and distributed filesystems. So if you haven’t yet gone through the original series, you should start there or subscribe here to get the series via email.

Unlike the original Hosting WordPress Yourself, this series will form more of a high-level overview and the step-by-step guidance will be more terse in comparison, therefore before attempting these optimizations you should have a firm grasp of the CLI and server configuration in general. Our sole aim is to increase the number of requests we can handle per second. We’re not concerned with high availability or auto scaling, although we will introduce some form of redundancy by splitting our application over multiple servers.

Horizontal Scaling?

When it comes to scaling there are generally two types, vertical and horizontal. We’ll discuss each in turn, but before doing so it’s worth considering if scaling is indeed the correct way forward. Too often we see developers prematurely scale when there are much simpler and cost-effective ways of improving the throughput of a WordPress site. If you haven’t already you should look into page caching and microcaching, which can significantly improve the number of requests your server is able to handle. If you’ve exhausted these avenues and your server is on the brink of meltdown only then is it time to think about scaling. Running top or htop from the terminal should give you a good indication of your server’s current capacity. In the example below, the CPU is running at 100% under normal load. If a traffic spike were to occur the server would come to a grinding halt, which is not what we want!

Server resource usage

Vertical scaling is the process of adding more power to your existing server. This usually means increasing your server size by upgrading to a larger plan on your VPS provider. Generally, more CPU and memory equates to the server being able to handle more requests per second. This approach should always be your first port of call when scaling, as it’s simple to implement and usually more cost-effective. There is however one major downside to vertical scaling and that’s the single point of failure. If your server dies your entire WordPress app will go offline.

Horizontal scaling involves adding more servers and distributing traffic amongst them, thus increasing the number of requests per second that can be handled. This approach is also more fault tolerant, because if a single server dies your WordPress site could remain operational, because there are other servers to take the strain. Horizontal scaling also introduces a level of flexibility by allowing you to scale individual areas of your app as they grow.

As a general rule of thumb you should vertically scale until a single server is no longer able to handle the load, or you need to introduce some level of redundancy. In this series we’re going to tackle horizontal scaling. Let’s take a look at the server architecture that we’re going to implement:

Planned server architecture

It’s a pretty standard server cluster with a single load balancer distributing traffic to our app servers. The app servers will house PHP and process any requests as they’re received, which will allow you to fire up additional app servers as traffic increases. A separate database and filesystem will sit behind the app servers. These will both be clustered to improve redundancy and resilience.

In this article we’re going to take the first steps into horizontally scaling our WordPress app by extracting the database to a dedicated server. Let’s get started!

The steps in this article are written with Digital Ocean in mind, but the principles should be similar regardless of the chosen VPS provider and conventions used. You will also need to enable ‘Private networking’ for your original server if it wasn’t enabled on deployment.

Provision Database Server

Head over to the Digital Ocean dashboard and create a new droplet. Select ‘Ubuntu 16.04’ as the distro and an appropriate size, which you should be able to gauge based on the existing MySQL CPU and memory usage found when inspecting top. It’s important to remember that the droplet must be provisioned in the same region as your existing web server. You must also ensure ‘Private networking’ is enabled so that the two servers can communicate.

Provision Digital Ocean droplet

Once the server has finished provisioning you should take the usual security precautions outlined in Hosting WordPress Yourself Part 1 – Setting Up a Secure Virtual Server. The only difference in this case is that the firewall rules should be stricter. We don’t want to allow any inbound traffic except for SSH on the public network. The private network should be restricted to SSH and MySQL inbound traffic only.

sudo ufw allow ssh
sudo ufw allow in on eth1 to any port mysql

Install MariaDB

Now that your database server is secured it’s time to install MariaDB:

sudo apt-key adv --recv-keys --keyserver hkp://keyserver.ubuntu.com:80 0xF1656F24C74CD1D8
sudo add-apt-repository 'deb [arch=amd64,i386,ppc64el] http://mirror.sax.uk.as61049.net/mariadb/repo/10.1/ubuntu xenial main' -y
sudo apt-get update
sudo apt-get install mariadb-server -y

Once complete, perform the usual post installation tasks:

sudo mysql_install_db
sudo mysql_secure_installation

We now need to tweak the default configuration to allow connections from remote hosts. By default MariaDB will only listen for connections on localhost, but we want to listen for connections on our private IP address.

sudo nano /etc/mysql/my.cnf

Update the bind-address directive to the private IP address of your newly created database server. This will allow MariaDB to accept connections from the private network.

bind-address = 10.131.48.158

Save and restart MariaDB.

sudo service mysql restart

Create New Database

Now that your MariaDB installation is listening for remote connections we need to create a new database and user that our remote servers can connect via.

mysql -u root -p

Create a blank database:

CREATE DATABASE ashleyrich_com CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci;

Add a new user which is allowed to connect from within the private network. In my case the host is 10.131.%.%, which is dictated by the subnet mask of 255.255.0.0. The first two octets make up the network address and the second two are leftover for hosts. Anytime you fire up a new droplet in the current region its private IP address will look like 10.131.*.* with the last two octets being unique for the droplet. Using a % wildcard in the MySQL host ensures all droplets on the private network can connect.

CREATE USER 'wordpress'@'10.131.%.%’ IDENTIFIED BY 'password';

For now we’ll grant all privileges, but you’ll likely want to lock this down further in the future.

GRANT ALL PRIVILEGES ON ashleyrich_com.* TO 'wordpress'@'10.131.%.%';

Flush the privileges and exit MySQL.

FLUSH PRIVILEGES;
exit;

To test that everything is configured correctly, SSH into your original server and attempt to connect to the remote MySQL server using the newly created user.

mysql -u wordpress -h 10.131.48.158 -p

Hopefully you should login successfully, if not double-check your configuration.

Migrate the Database

It’s time to migrate the database from the original server to the dedicated database server. SSH into the original server and perform a MySQL dump.

mysqldump -u root -p ashleyrich_com > ashleyrich_com.sql

Open a new local terminal window and copy the SQL file to your local machine. Remember to adjust the username accordingly and connect via the public IP address of the web server.

scp ashley@138.68.188.186:/tmp/ashleyrich_com.sql ~/

Now copy the SQL file from your local machine to the database server.

scp ashleyrich_com.sql ashley@138.68.182.72:/tmp

Finally, switch back to the database server and import the SQL file.

mysql -u root -p ashleyrich_com < /tmp/ashleyrich_com.sql

Switch Database Server

It’s time to switch to using the dedicated database server. Log into your web server and edit the wp-config.php file.

nano ~/ashleyrich.com/public/wp-config.php

Update the database credentials to point towards the remote database server (remembering to also update the user if different). The DB_HOST should point toward the database server’s private IP address.

/** MySQL database username */
define('DB_USER', 'wordpress');

/** MySQL database password */
define('DB_PASSWORD', 'password');

/** MySQL hostname */
define('DB_HOST', '10.131.48.158');

Save the changes and stop the MariaDB service on the web server.

sudo service mysql stop

If you visit the site and reload the page everything should continue to work as normal. If not, double-check your settings.

The final step is to remove the MariaDB server to help save on system resources.

sudo apt-get remove --purge mariadb-server mariadb-client
sudo apt-get autoremove
sudo apt-get autoclean
sudo rm -rf /var/lib/mysql
sudo rm -rf /etc/mysql

Job done! You’ve now successfully offloaded your database to a dedicated server. If you would still like to access MySQL from the command line you can install the MariaDB client.

sudo apt-get install mariadb-client

I also set up Sequel Pro to connect to the database server. You have to use a SSH tunnel and connect via the app server, because MariaDB will only accept connections from the private network.

Wrapping Up

Here’s what we’ve implemented so far. You’ll notice that we only have a single database server at this point, which means there’s a single point of failure. However, in a later post we’ll look at MySQL replication to alleviate the issue.

Current server architecture

Let’s see how extracting the database server has impacted our original app server’s resource usage. Once again run top or htop from the app server and you’ll see that the CPU is still running at 100%, however, we have managed to reduce the total memory usage.

App server resource usage

Conversely, the database server is running comfortably under full load with the CPU ticking along at 6.5% and memory not exceeding 30%. That should give us plenty of breathing room, even when traffic to our WordPress site increases.

Database server resource usage

That’s all for part 1! Over the next couple of posts we’ll work toward resolving the high CPU usage on our app server by spinning up a few additional servers. However, before we can do that we need to take care of a few prerequisites. In the next post, we’ll create a distributed filesystem, which will allow data to remain consistent amongst our app servers. Stay tuned!

Have you scaled WordPress or extracted MySQL to a dedicated server? Let us know how it went in the comments below.

Don’t want to miss the next post in this series? Be sure to subscribe below.

About the Author

Ashley Rich

Ashley is a PHP and JavaScript developer with a fondness for solving complex problems with simple, elegant solutions. He also has a love affair with WordPress and learning new technologies.

  • Tyson Brady (Erbilacx)

    I think in most cases vertical scaling is probably going to take care of most high traffic WordPress websites. I’ve been working on finding the most optimal settings for a high traffic WooCommerce site and recently completed a load test of 10,000 users over 1 minute visiting a number of static and dynamic pages. Previously I was able to test up to 3,000 users with the CPU hitting around 450% with a 2500ms load time and after reworking the Apache configuration and OpCache settings I was easily able to push 10,000 users with a 1500ms response and an average of less than 50% CPU usage. Unless there are a great number of database requests I don’t think I’ll move the database off to it’s own server unless I’m chasing the redundancy because I’m always looking to scale before any issues arise.

    Great article anyhow for anyone wanting to scale horizontally.

    • Weedbay Guy

      I don”t think your test loads are worth a hill of beans.

      • Tyson Brady (Erbilacx)

        Considering the highest concurrent users we’ve had on that particular site was 2,500 at one time with similar results to the load testing, your comment isn’t worth a hill of beans πŸ™‚

        • Weedbay Guy

          Hire a freelancer from Russia they’ll tell you one php7-fpm NginX server with redis object cache and one server is all you need. Not by American standards. I want a page in 2 seconds for all my users and scaling horizontal is how you do it. Breaking out the database is key when you’re loading the load balancer.

          • Tyson Brady (Erbilacx)

            Good thing I’m not Russian, don’t use NginX and serve up pages in 1.5 seconds to all my users.

  • Any idea what kind of latency you can expect? I know with AWS it was not very good. Digital Ocean I think should be better overall.

    • Both AWS and DO have worked perfectly fine for us, where exactly were you noticing latency?

      • I saw about a 70-150 ms increase when running an AWS MySQL small cluster vs having the DB on the same server.

        I haven’t tried a separate server with MySQL or done the same with Digital Ocean.

        I was just curious if there are any significant performance losses.

        • Oh Yes, Compared to what we run in our own DC ( Private Gigabit Network, Same Rack). DO & AWS both have almost similar additional network latency, but for overall scale we have to learn to live with it.

    • I’m hoping to cover this in a later article with latency benchmarks with before and after scaling. Stay tuned!