Tagged: HyperDB Toggle Comment Threads | Keyboard Shortcuts

  • Jay Versluis 12:42 pm on January 28, 2012 Permalink | Reply
    Tags: HyperDB   

    Categories: MySQL ( 19 ), WordPress ( 145 )   

    How to deploy HyperDB 

    Ever since I’ve started experimenting with HyperDB I thought that once I’ve got enough servers at my disposal, and if I ever figure out how to setup MySQL replication I’d bring the two together. I believe that day has finally come: HyperDB is now deployed across over 60 sites I’m taking care of.

    I want to share with you my configuration and some of the pitfalls I’ve come across – if it helps, great. But mainly these notes are for me to remember how I did it when the next cluster needs to be built.

    (More …)

    • Jay Versluis 6:19 pm on February 6, 2012 Permalink | Reply


      It’s been three weeks since I’ve had HyperDB running in the above sample configuration on about 6 test sites. The concept works, albeit with minor performance decreases over a standard dual-server setup. However I’ve run into a couple of problems twice over the three weeks: MySQL database inconsistencies.

      MySQL just crashes every once in a while. Sad but true – and I don’t know why, I’m just the observer here. And it’s no biggie either, you just go and reboot the service. This means that one of the slaves takes about a minute to catch up with the master. No problem here either.

      But as soon as one server is out of sync with the others the real nightmare begins: say server 1 recorded a change before server 2 crashes. Server 2 reboots and applies the changes one cycle too late so he misses creating a database table for example. Now you delete said table on server 1… Server 2 tries to apply this change, finds that this table doesn’t exist and throws out an error – and by the looks of it stops replicating altogether. That’s extremely ungood!

      It means you have to play catch-up with every server and set the master log coordinates manually again. This can get ugly, and I had to do this twice. I’m sure there’s another way to avoid this, but sadly I don’t know how at this moment.

      There is a project which may help called MMM (or MySQL Multi Master Replication Manager): http://mysql-mmm.org/ – looks extremely complicated to setup so I’m going to leave this for another long winter night.

    • Tung 6:46 am on July 10, 2013 Permalink | Reply

      Hi, why do you 2 commands to add Master 1 for write queries and read queries? Why don’t use combine them into 1 command with (read=>1, write=>1) instead of (read=>1, write=>0) and (read=>0, write=>1)

      • Jay Versluis 8:18 am on July 11, 2013 Permalink | Reply

        Both work fine for the example, but in a production environment it is plausible to have a master purely for writes and never for reads (and let the slaves do that) hence I split it up. It’s just for clarity really.

  • Jay Versluis 2:36 pm on July 7, 2010 Permalink | Reply
    Tags: HyperDB   

    Categories: Plugins ( 19 ), WordPress ( 145 )   

    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:

    (More …)

    • Alexandru Vornicescu 8:48 pm on May 8, 2012 Permalink | Reply

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

      • Jay Versluis 2:19 pm on May 10, 2012 Permalink | Reply

        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.

    • Dharmendra 6:34 am on February 22, 2013 Permalink | Reply

      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?

      • Jay Versluis 7:52 am on February 24, 2013 Permalink | Reply

        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

    • Arif 1:49 am on November 8, 2013 Permalink | Reply

      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

    • Jay Versluis 12:25 pm on November 10, 2013 Permalink | Reply

      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.

Compose new post
Next post/Next comment
Previous post/Previous comment
Show/Hide comments
Go to top
Go to login
Show/Hide help
shift + esc