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:

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

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.

Jay is the CEO and founder of WP Hosting, a boutique style managed WordPress hosting and support service. He has been working with Plesk since version 9 and is a qualified Parallels Automation Professional. In his spare time he likes to develop iOS apps and WordPress plugins, or draw on tablet devices. He blogs about his coding journey at http://wpguru.co.uk and http://pinkstone.co.uk.

30 thoughts on “Testing HyperDB

  1. how about when I have a very long database and it takes 20 seconds to load… what should I do?

    1. Tough one. I’d say if your database is extremely large (we’re talking several GB) then HyperDB can indeed help – but you need to have two or more database servers at your disposal. You can then split the database into several tables per server.

      I would personally try upgrading to better hosting as this will likely solve the problem.

  2. I have a lots of problem doing this. I have couple of questions
    1 > Do I need to install the application on the slave server?

    2 > In the wp-config file there are two sections, one is for master and one is for slave. So do I need to give separate info for the database array section. One for master db details and one for slave details

    3 > In which folder the wp-config should go in the slave machine if there is no need of installing the application on the slave? Will it go to the www directory. Where should I put the db.php file?

    4 > Do I comment the master section info when I configure it for slave?

    1. Hi Dharmendra,

      don’t get this the wrong way, but your questions indicate that you may need a bit more knowledge of MySQL before you start playing with HyprDB. In a nutshell, for a basic test setup you’d have three servers:

      1.) a web server
      2.) MySQL Master
      3.) MySQL Slave

      1+2 can be installed on the same server – but therefore 3 is not a web server and does not need a copy of WordPress, and hence you only have one configuration file. In this you need to tell WordPress about the two database servers.

      However, the real magic is not HyperDB itself, but MySQL Master/Slave replication. Neither WordPress nor HyperDB will take care of this, you need to set this up first before you can start using HyperDB. This article will guide you through it – but be aware that this is a tough challenge to implement: http://dev.mysql.com/doc/refman/5.0/en/replication.html

  3. Hi
    I just integrated hyper db and it works. But i had a problem that it shows connection error when clicking a blog. if we two or three times the blog loads

  4. Update November 2013:

    I suppose today we wouldn’t really use HyperDB anymore. HyperDB was developed before affordable cloud hosting became a reality.

    Infrastructure such as Amazon RDS makes our lives much easier: with RDS you can setup a single MySQL instance which scales and replicates automagically, across several continents. WordPress only speaks to one database server, and Amazon handles the rest.

Add your voice!