Scaling WordPress with MySQL Replicas and HyperDB Plugin

Advantages of MySQL Replication

Typical WordPress sites are limited to the capacity of a single database to serve read and write requests. As a result, high traffic sites can experience latency as requests are fulfilled. MySQL replication rapidly copies content from the "master" database to one or more "replica" databases. This allows you to spread requests across multiple databases to improve site performance and load times.

About HyperDB

The HyperDB plugin replaces the standard wpdb class so that WordPress is able to write and read from additional database servers. The drop-in plugin supports database replication, failover, load balancing, and partitioning — all tools for scaling WordPress.

Keep in mind, HyperDB is a powerful tool with several tuning options based on database architecture and network topology. Before you implement a complex configuration, it’s best to see if a simpler configuration suits your needs.

Install and Configure HyperDB

Download the archive of HyperDB from the WordPress plugin repository and move the db.php file into the /wp-content directory. This is a drop-in plugin and does not require activation at any time.

Next, configure the master/replica databases within db-config.php. This file should be stored within the same directory as the site's wp-config.php file.

When the db.php database drop-in is deployed to production, WordPress will begin allocating MySQL database reads and writes based on the configuration details you’ve provided in db-config.php.

The following sample configurations can be used in place of the dp-config.php file provided within the plugin archive. These examples require no additional edits for sites running on Pantheon. For more advanced options, refer to the db-config.php file provided in the HyperDB plugin archive.

Split Reads Between Master and Replica

Split reads between the master and the replica, to simply distribute the load between two servers.

<?php
/**
 * Register the master server to HyperDB
 */
$wpdb->add_database( array(
        'host'     => DB_HOST,
        'user'     => DB_USER,
        'password' => DB_PASSWORD,
        'name'     => DB_NAME,
        'write'    => 1, // master server takes write queries
        'read'     => 1, // ... and read queries
) );
/**
 * Register replica database server if it's available in this environment
 */
if ( ! empty( $_ENV['REPLICA_DB_HOST'] ) ) {
        $wpdb->add_database(array(
                'host'     => $_ENV['REPLICA_DB_HOST'] . ':' . $_ENV['REPLICA_DB_PORT'],
                'user'     => $_ENV['REPLICA_DB_USER'],
                'password' => $_ENV['REPLICA_DB_PASSWORD'],
                'name'     => $_ENV['REPLICA_DB_NAME'],
                'write'    => 0, // replica doesn't take write queries
                'read'     => 1, // ... but it does take read queries
        ));
}
// That's it!

Pass Frontend Read Queries to Replica, WordPress Dashboard Reads and Writes to Master

Pass all frontend database read queries to the replica, leaving the master dedicated to WordPress dashboard reads and writes. This can better ensure WordPress dashboard availability during high frontend load.

<?php
/**
 * Use HyperDB to just use the replica for frontend reads.
 * Register the master server to HyperDB
 */
$wpdb->add_database( array(
        'host'     => DB_HOST,
        'user'     => DB_USER,
        'password' => DB_PASSWORD,
        'name'     => DB_NAME,
        'write'    => 1, // master server takes write queries
        'read'     => is_admin() || empty( $_ENV['REPLICA_DB_HOST'] ) ? 1 : 0, // ... but only takes read queries in the admin if the replica is available
) );
/**
 * Register replica database server if it's available in this environment
 */
if ( ! empty( $_ENV['REPLICA_DB_HOST'] ) && ! is_admin() ) {
        $wpdb->add_database(array(
                'host'     => $_ENV['REPLICA_DB_HOST'] . ':' . $_ENV['REPLICA_DB_PORT'],
                'user'     => $_ENV['REPLICA_DB_USER'],
                'password' => $_ENV['REPLICA_DB_PASSWORD'],
                'name'     => $_ENV['REPLICA_DB_NAME'],
                'write'    => 0, // replica doesn't take write queries
                'read'     => 1, // ... but it does take read queries
        ));
}

That's it!