Powering WordPress with Auto-Scaling AWS RDS

Sterner Stuff recently inherited a pretty massive site. With over 100,000 posts, 200,000 attachments, and site loads reaching 4000+ visitors and anywhere from 5-9 logged-in authors at any one time, it’s been one of the largest projects we’ve tackled.

While a proper reverse-proxy solution can mitigate the majority of the front-end load on a project like this, handling even just a few logged-in users becomes a bit of a process. You can’t cache those sessions and requests, and some of the uglier and slower queries baked into WordPress core and plugins like Yoast start to rear their ugly heads.

Until the proper query optimizations can be made, we needed to configure the site’s database solution to roll out replicas in response to increasing load. AWS’s auto-scaling RDS service turned out to be a great solution for this. Here’s how it works.

Read and Write Databases

In many database scaling setups, the strategy is to let the application read from any number of database replicas and defer to a single write database for write queries like UPDATE and INSERT. Managed database services like RDS clusters handle the copying of new information from the write database to the read replicas. In our case, the lag generally runs under 20ms, so we’ve yet to notice it.

In auto-scaling setups, like auto-scaling RDS clusters, spinning up new read replicas when the load gets high happens without lifting a finger. So even if an article takes off in the middle of the night, everything scales nicely.

Configuring WordPress to Handle Multiple Database Connections

Traditionally, the WordPress config handles a single database connection. In this case, however, we need to set up multiple, and we also need WordPress to know that write operations go one place, while read operations go to another.

This was solved long before today. The original solution was HyperDB from Automattic. This plugin works a little differently than most plugins because it has some extra drop-in config files you have to manually setup.

The plugin hasn’t been updated in over a year, and it’s also tossing several PHP notices. While there probably aren’t that many updates to be made, it’d be nice to see WordPress curators like Automattic being more proactive.

Therefore, I suggest using LudicrousDB instead. It was forked from HyperDB with some small changes, and configures mostly the same. Not to mention it leveraged a Space Balls joke for its name.

These plugins allow a ton of configuration regarding database failovers, regional awareness, and more. But for now, we just needed to get all write operations to one database connection and all read operations to another.

RDS Cluster Endpoints

Now this had actually already been setup when we inherited this project, but someone goofed a bit.

When you configure an RDS cluster on AWS, each database in the cluster has its own hostname. However, a read database may be promoted to be the write database if the current one fails. And if you set up auto-scaling, these hostnames are changing every time an instance is created or destroyed. It would be impossible to keep your WordPress (or other application) configuration updated with the hostnames for the databases being spun up or down all the time.

Instead, RDS abstracts the cluster connection with two endpoints – one for writing and one for reading. The write connection connects to whatever database is currently considered the master, even when it changes. Meanwhile, the read endpoint will load balance requests across however many read replicas are active (up to 15). You’ll use these two endpoints for your database hosts.

Whoever came before Sterner Stuff had properly configured the write endpoint, but for the read endpoint, they’d connected directly to the one existing read replica. Not only did this kill the site when the two databases swapped roles, but it also couldn’t scale. So we updated things.

LudicrousDB Configuration for RDS Auto-Scaling

Okay, okay, here’s the final configuration for your db-config.php file that you set up with LudicrousDB. Be sure to define constants as appropriate in your wp-config.php file.

$wpdb->add_database(array(
    'host'     => DB_WRITE_HOSTNAME, // Use the RDS cluster write endpoint here
    'user'     => DB_USER,
    'password' => DB_PASSWORD,
    'name'     => DB_NAME,
    'write'    => 1, // master can write
    'read'     => !defined('DB_READ_HOSTNAME') ? 1 : 0, // master only reads if no replica is configured

));

if(defined('DB_READ_HOSTNAME')):
    $wpdb->add_database(array(
        'host'     => DB_READ_HOSTNAME, // Read endpoint here
        'user'     => DB_USER,
        'password' => DB_PASSWORD,
        'name'     => DB_NAME,
        'write'    => 0,
        'read'     => 1,
    ));
endif;Code language: PHP (php)

Allow read requests to the write endpoint

In the above setup, you might notice we direct all read operations to the read endpoint and all writes to the write endpoint. You can technically allow read operations to the write endpoint as well.

We originally allowed for this by sending all admin read operations to the write database. However, the admin is currently inefficient enough that even limiting the read operations this way would overload our write server, so we’ve limited the write server to just write operations.

Hopefully this helped you get going. If you’ve got thoughts or questions, drop them below. And may the Schwartz be with you.