Testing HyperDB

I’m as excited as a kid in a candystore! I’ve heard a lot about HyperDB and what can be done with it, and I’ve been thinking how cool it would be to implement it on the sites I’m hosting. And today my friends I’m proud to say that IT WORKS A TREAT!

Hyper What?

Let me tell you a bit about HyperDB. It’s another super cool Automattic project fronted by Matt, Andy, Ryan and Blogluftwaffe. It’s a rather complex plugin for WordPress that will let you specify more than one Database Server. In a high production environment your one and only Database Server can get busy or even crash and die – so all websites would be down until you restart it.

HyperDB solves this by letting you specify several servers in the handy db-config.php file. You can set one as master server for write queries and several others for read queries. This means that the master server doesn’t get bothered with read queries while several other servers can deal with those. If one read server dies, the others can pick up the slack. It’s genius!

The Experiment

Before a general roll-out to live sites a little test might be an idea. Currently I have three servers at my disposal and a dev site for testing. All servers are running CentOS 5 in a mixture of 32-bit and 64-bit installations, with Plesk 9.3/9.5 on them. Here’s my setup:

• Server 1 (Hubert) hosts the content. This server can be defined as “localhost” in wp-config.php. We’ll make him a Database Slave later.
• Server 2 (Drexel) is the Database Master.
• Server 3 (Verslpzy) is another Database Slave.

Let’s replicate some data

Before we start, I want to make sure that I can test which server is chosen by HyperDB at any given time. The easiest way to do this is to replicate the current database to all servers using the following commands.

In a production environment you’d setup proper database replication for all databases on the master. In this case though, I’m only working with one replicated database and I don’t want it to be updated automatically – hence I’m going for a manual approach.

Let’s run the following command on Server 1:

mysqldump -u root -pyourpassword testdatabase > testdb.sql

Let’s move this file to Server 1

rsync root@server1.com:/testdb.sql /temp

Now we’ll pop it into an existing database of the same name with the same privileges – (I’ve already created this via phpMyAdmin becasue I’m lazy):

mysql -u root -pyourpassword testdatabase < testdb.sql

We’ll do the same on Server 3 so we have the exact database 3 times.

Identifying those databases

Now I need to write a post on my dev site stating which Database Server we’re looking at. All I need is a good headline really that says “Looking at Server 1”. So far so good.

Next we’ll edit the wp-config file and change “localhost” to our next Database Server (let’s say it’s server2.com – obviously it’ll be the IP address or domain of your server 2). Reload the front page of your dev site and magically that “Looking at Server 1” message has disappeared. Just what we wanted 😉

If at this point you get the “Error establishing Database Connection” it means some of your details in wp-config aren’t correct, or your replication didn’t work. Could be the replicated server hasn’t got the right privileges defined in MySQL. I won’t go into detail about that here though.

Let’s write a new post that says “Looking at Server 2”. Publish and refresh. Nice.

On to change the wp-config again to the third database server, and over to writing a new post entitled “Looking at Server 3”.

All we’ve done now is amend each single database slightly – and more importantly make this change visible on our front page.

Bring on HyperDB

Now comes the fun part: let’s bring in the star of the show.

Download HyperDB and unpack it. Upload db.php into your wp-content directory – this alone will activate it. It’s super simple steps that made WordPress great!

Let’s have a look at the db-config.php file before we upload it. It’s already pre-configured to work with whatever database host is configured in wp-config.php via this bit of code here:

$wpdb->add_database(array(
'host'     => DB_HOST,     // If port is other than 3306, use host:port.
'user'     => DB_USER,
'password' => DB_PASSWORD,
'name'     => DB_NAME,
));

Note that you can change these values to override the wp-config settings. This code defines that one server as database master/slave for both reading and writing. A bit further down, similar bit of code re-defines the server as a read-only slave:

$wpdb->add_database(array(
'host'     => DB_HOST,     // If port is other than 3306, use host:port.
'user'     => DB_USER,
'password' => DB_PASSWORD,
'name'     => DB_NAME,
'write'    => 0,
'read'     => 1,
'dataset'  => 'global',
'timeout'  => 0.2,
));

Because this is defined as an array, you can copy this bit of code for as many Database Slaves as you’d like to setup. All you have to do is amend the DB_HOST constant with your server’s domain name or IP address like so:

$wpdb->add_database(array(
'host'     => 'server2.com',     // If port is other than 3306, use host:port.
'user'     => DB_USER,
'password' => DB_PASSWORD,
'name'     => DB_NAME,
'write'    => 0,
'read'     => 1,
'dataset'  => 'global',
'timeout'  => 0.2,
));

or like so:

$wpdb->add_database(array(
'host'     => '192.168.0.101',     // If port is other than 3306, use host:port.
'user'     => DB_USER,
'password' => DB_PASSWORD,
'name'     => DB_NAME,
'write'    => 0,
'read'     => 1,
'dataset'  => 'global',
'timeout'  => 0.2,
));

Then just copy the same bit of code underneath and amend the host again – say like this:

$wpdb->add_database(array(
'host'     => '192.168.0.102',     // If port is other than 3306, use host:port.
'user'     => DB_USER,
'password' => DB_PASSWORD,
'name'     => DB_NAME,
'write'    => 0,
'read'     => 1,
'dataset'  => 'global',
'timeout'  => 0.2,
));

Save the file and upload it into the same folder that holds your wp-config.php file (as described above, db.php should be uploaded into the /wp-content/ folder).

Testing Testing… is this thing on?

If you now go back to your live site and refresh the home page, you should see that your latest post is different upon every refresh. Keep refreshing, and the “Looking at Server x” messages will just keep coming. We’ve just proven that HyperDB works a TREAT with minimal effort!

Once you’ve played that game for a while, you’ll want to see what would happen in an emergency. Say “Server 1” decides it’s time for a coffee break and crashes. We’ll simulate this by simply switching off the MySQL deamon with this command:

service mysqld stop

Go back and keep refreshing your site. “Looking at Server 1” shouldn’t come back anymore. You can do the same with the other servers for testing, obviously at least one must be active. The command

service mysqld start

should bring MySQL back online on any given server 😉

Conclusion

HyperDB works so well it’s scary!

Simple to implement and hyper functional. Of course you need to implement proper Database Replication for all servers to produce the same content, but that’s fairly easy to do (if you know how). Maybe we’ll find out in a forthcoming article 😉

What I’m pleased about is that WordPress works like magic with multiple database servers and minimal effort. I can’t wait to implement this on a bigger scale on all my sites.