Using Read Replica Databases With Craft CMS
Posted: 12th Jan 2020
You've deployed your website. It's massively popular. You're getting thousands of shares. Then the site goes down and everyone starts sending messages in uppercase... 😢
It's a common enough story and the causes can be varied, but one regular culprit with Craft CMS applications is database load.
There are a few different ways to approach this problem and each solution is beneficial in different scenarios, but one method which kills a few birds with a single stone is to set up a database read replica.
This read replica is a copy of your existing database which is only able to receive read queries - it can't change any data at all. So no INSERTS or UPDATES at all, they'll just be ignored - these can only be sent to your original (master) database.
The read replica keeps in sync with the master by looking at the master's logs and applying any data changes that it sees to itself. This means that the read replica's data is always a little behind the master, but usually only a few milliseconds.
Not only can the addition of a read replica effectively double your potential throughput, it also partially splits the responsibilities of your two database servers: one is primarily for writes, the other is only for reads.
Database writes tend to be much slower than reads and benefit from a different type of internal caching to perform optimally. Splitting responsibilities therefore allows each server to use its available resources more intelligently.
As well as increased and improved resource use, a read replica can offer piece of mind. The majority of popular databases offer a failover system which allows a replica to become a master if the original master fails. This can be a huge benefit for applications which can't afford downtime.
So how do we set this up in Craft? We need some way to ensure that SELECT queries go to one server, and INSERT/UPDATE queries go to another.
Luckily Yii2, the framework underlying Craft CMS already supports this functionality, we just need to configure it. We can do that by overriding the default db component in config/app.php:
<?php
return [
'components' => [
'db' => function() {
$config = craft\helpers\App::dbConfig();
$config['slaveConfig'] = [
'username' => getenv('DB_REPLICA_USER'),
'password' => getenv('DB_REPLICA_PASSWORD'),
'tablePrefix' => getenv('DB_TABLE_PREFIX'),
'charset' => 'utf8',
];
$config['slaves'] = [
['dsn' => getenv('DB_REPLICA_HOST')],
];
return Craft::createObject($config);
},
],
];As you can see you're able to provide a different username and password for the read replica as well as define its host address. You can also supply multiple read replica hosts if you have a database cluster that contains multiple slaves:
$config['slaves'] = [
['dsn' => getenv('DB_REPLICA_HOST_1')],
['dsn' => getenv('DB_REPLICA_HOST_2')],
['dsn' => getenv('DB_REPLICA_HOST_3')],
];Once these config options have been added, Craft will begin sending all of its SELECT queries to a slave but all of its INSERT and UPDATE queries to the master defined in Craft's config/db.php.
Be aware though! This configuration will easily let you split traffic between functioning servers, but if your cluster undergoes a failover and one of the slaves becomes the master, Craft won't be aware of this and will fail to perform any data writes until its configuration is updated.
If you're in a position where database replication might help, Servd's enterprise plans can offer a zero-config solution which work in tandem with our other Craft specific optimisations.
Get started with a free trial or get in touch to let us know exactly what you're after.
