"HyperDB" Plugin for DB redundancy and load balancing in WordPress

My name is Ito and I am an infrastructure engineer.

Using cloud services such as AWS, it is now possible to easily duplicate servers and provide redundant and load-balanced access to sites via a load balancer.

In this state, the Web server is redundant and load balanced, but the database is not redundant and load balanced.

If there is a lot of access to the database,
the load on the web server will be fine, but there is a possibility that the processing on the DB server side will be clogged and the site will not be displayed.

This is a long introduction, but Wordpress is content that runs in the so-called LAMP environment.
This means that a DB is used, so even if the web server is made redundant, if the DB becomes clogged...

503 Service Temporarily Unavailable

I'm getting an error that I don't want to see. .

In such a case, let's make the DB redundant! !
So, I would like to introduce HyperDB

In the case of AWS, RDS redundancy is easily possible.
All you need to do is create a "read replica" of the master RDS.
Therefore, I will omit the explanation.

Install HyperDB

Installing the HyperDB plugin is a bit complicated.
This is because although it is possible to install from the Wordpress management screen,
the actual settings must be written directly to the php file.

After installation, you will find the following files in wp-content/plugins/hyperdb of Wordpress.
"db-config.php"

, you will need to enter the settings in advance and upload it to the target directory

First, write the following content in wp-config.php. "The DB configuration file is db-config.php."

# vim /pass/to/docroot/wp-config.php define('DB_CONFIG_FILE', ABSPATH . 'db-config.php');

Let's continue with the settings.

HyperDB configuration

Configure the following settings for the installed db-config.php.

First, configure the master server. (Around line 217, confirmed in v1.1)

# vim /pass/to/docroot/db-config.php $wpdb->add_database(array( 'host' => 'Enter the RDS (master server) endpoint', 'user' => DB_USER, 'password' => DB_PASSWORD, 'name' => DB_NAME, 'write' => 1, 'read' => 2, ));

For host, enter the RDS endpoint, but if you use a port other than the default 3306, you
must enter <endpoint>:<port number> followed by a colon and the port number.

The key is writing and reading.

• write: Write priority to DB
• read: Priority for reading into the DB

If set to "0", writing and reading will not be performed.
In the above case, the write priority to the master server is high, and the read priority is low.

Next is the slave server settings.
Please add it below the master server settings mentioned earlier.

# vim /pass/to/docroot/db-config.php $wpdb->add_database(array( 'host' => 'Enter the RDS (slave server) endpoint', 'user' => DB_USER, 'password' => DB_PASSWORD, 'name' => DB_NAME, 'write' => 0, 'read' => 1, ));

Write and read are effective here as well.

• write: 0=Do not write to that server
• read: 1 = read has high priority, write is 0 so read only

With this setting, only reads are performed on the slave server.
Similarly, if you register multiple slave servers, reading will be performed only on the slave servers.
Now, reads to the DB (that is, normal accesses) are load-balanced on the slave servers.

In WordPress, writing to the DB is done only from the admin screen, such as changing settings or adding articles.
This is not a problem because the write priority to the master server is set high.

So, Wordpress DB is now redundant and load balanced!

Now you can feel safe even in sudden access! That's right.
HyperDB — WordPress Plugins