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
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
$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.
Very clear, thank you
Sometimes he seems not to select the database correctly: I get the following error in the admin area under “plugins”:
But only once in a while. Sometimes it does not work to save user data. He simply ignores this. Is the problem known?
SET `option_value` = ‘1588860057’
WHERE `option_name` = ‘_transient_timeout_plugin_slugs’
The MySQL server is running with the –read-only option so it cannot execute this statement
Hey Martin – hard to debug without more information. For example, MySQL error logs.
I would first make sure you’re using the latest version of LudicrousDB and that your database drop-in is up to date.
Then I would raise an issue on the GitHub repo if you’re still having issues – https://github.com/stuttter/ludicrousdb
Best of luck!
Hi, this was really interesting, so thanks very much!
I suppose this is mandatory for running very high traffic WordPress instances… so I need to edit i.e wp-config.php and db-config.php, right?
In my case, I inherited a AWS WordPress website with very much visitors, and they configured Aurora MySQL in two instances (r/w). But something goes wrong: MySQL goes does almost every day.
wp-config.php is not configured as described here, and I am pretty sure all visitors overloading write one (505 select / sec vs 2 select/sec), causing trouble. Can this configuration balancing against down problems in your experience / opinion?
Howdy, glad you found it informative!
Auto-scaling isn’t mandatory for high traffic sites. If MySQL usage is consistent, you should be able to get by with a sufficiently large, single MySQL instance. But if traffic is liable to spike and fall, then scaling can help with that.
All that said, yes. If you have one instance handling 500 selects/s and another handling 2 selects/s, something is wrong with your configuration and you’re going to see spikes in CPU usage on the one instance, taking everything down. Sounds like you definitely want to update your configuration to look more like what we describe in this guide.
Any idea how to install LudicrousDB to replace the existing DB? Apparently it has to be installed very early.
Hey Piero – to my knowledge, it doesn’t need to be installed or configured from the start on a site. It should be fine to drop it in at any time. What kind of issues are you having?
Thank you for the reply. The website has a white screen and I’m unable to log in at all after installing it. I resorted to using AWS Aurora serverless which seems to do autoscaling without having to set up reader endpoints.
Glad you sorted it out. A whitescreen is almost always a PHP error, which enabling WP_DEBUG should allow you to solve. But you’re right, Aurora serverless can scale up/down your MySQL resources without multiple endpoints. I don’t remember why, but this wasn’t an option for us when I was implementing a scaling solution on this project.