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!
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:
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.
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.
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.
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.
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.
