How To Optimize WordPress Performance With MySQL Replication On Ubuntu 14.04
Introduction
In this tutorial, we will teach you how to scale up your WordPress MySQL database server setup using master-slave database replication and the HyperDB plugin for WordPress. Adding more database servers to your environment in this manner allows your WordPress application to read from multiple database servers, increasing read performance.
MySQL replication reaps the most performance benefits for a system that processes frequent reads and infrequent writes, like most WordPress installations. By using a single-master with multiple-slave setup, you can add more slaves to scale your system, until you run out of network bandwidth or your master cannot handle the update load. If you wish, you can add more than one slaves by repeating the “slave” portions of the replication sections of this tutorial.
We are assuming that your setup includes two load balanced WordPress application servers that connect to a separate MySQL database server (see the prerequisites for a tutorial on how to set that up). It is not strictly necessary to have load balanced application servers to follow this tutorial, but your MySQL database server should be separate from your application servers.
Prerequisites
Before continuing with this tutorial, you should have completed two tutorials or have a similar environment:
- How To Set Up a Remote Database to Optimize Site Performance with MySQL
- Optionally, How To Use HAProxy As A Layer 4 Load Balancer for WordPress Application Servers on Ubuntu 14.04
After following those tutorials, to set up WordPress with two load balanced web application servers and a separate database server, you should have four VPSs. Because we will be dealing with several VPSs, for reference purposes, we will call your four existing VPSs the following:
- haproxy-www: Your HAProxy server for layer 4 load balancing your WordPress web application servers. This is the entry point into your website
- wordpress-1: Your first WordPress web application server
- wordpress-2: Your second WordPress web application server
- mysql-1: Your MySQL server for WordPress
That is, your environment should look something like this:
In addition to your current environment, we will require one additional VPS during this tutorial. We will call it:
- mysql-2: Your slave MySQL database server