Scaling WordPress MU with multiple databases

Our larger WordPress MU install, Homeschool Journal has been running on multiple databases for about 3 years. Technically, it’s possible to have thousands of blogs in a single database install. Usually where people running MU start to have issues with the number of blogs is with getting complete backups of the database.

Upgrades with multiple databases have always been more work and many people running multiple databases typically did not upgrade until the .1 release of whatever version of MU. When I upgraded Homeschool Journal to MU 2.7.1, it was two weeks before I had found and repaired all of the database issues (via several plugins). I decided I wasn’t going to go through that again.

With the number of blogs at WordPress.com, Automattic must be using a database plugin that supports WordPress MU better. So, over a couple of weekends, I tweaked HyperDB to use the same MD5 hash based sharding structure that we were using.

I released SharDB (Download) last week as a beta release intended for larger WordPress MU installs using an existing 1-3 character MD5 hash (by blog id) based multi-database sharding structure. (Other structures will be added in early 2010.) The alpha testing of SharDB was carried out this fall with the help of a few people who I knew were using the same multi-database that we were. SharDB has been powering live sites since early October.

SharDB has been tested with over 50 plugins including BuddyPress 1.1 through 1.1.3, domain mapping (both Donncha’s and my version) and my multi-site plugin. I have not found any issues with any of the tested plugins. It should support any plugin that accesses data via the $wpdb object. It has been used to upgrade live sites from 2.7.1 through to 2.8.6 and I’ve upgraded a test site to the WordPress MU 2.9 release candidate tagged 2009/12/21. (Note: I used the plugin in this post to create the commentmeta tables before updating MU from 2.8.6 to 2.9RC.)

One of the features that I really like about SharDB is that I don’t need to edit the database config when adding plugins (a feature inherited from HyperDB). Also, I haven’t had issues with plugins failing to create tables. As an added bonus, I added a handy column to the site admin blog screen so that you can see which database a blog’s tables are in.

Comments

  1. If we are not offering blogs and using BP features then I wont be worrying about setting up multi databases or we still need shardb to handle the large group of users to make it smoother anyway, right?

    • Right, the multiple databases are for blog tables. However, a large BP install might take advantage of MySQL replication and implement either HyperDB or SharDB to distribute the load across multiple database servers.

  2. I’ve been testing and running SharDB. Thank you for the good work and Happy new year!

  3. Jed Barish says:

    How will SharDB be using in the latest trunk of WPMU/MU then evolve into v3.0 with BP which I would set it up as a single wp with BP. Are you doing this because you knew what it will be alike for v3.0 to run SharDB in a single wp setup when we disable MU option?

    • Both HyperDB and SharDB will run a standard WP (or bbPress) install.

      I did SharDB to allow WordPress MU sites to scale in terms of the number of blogs that they have.

  4. Could you expand on the differences between this and HyperDB please?
    Many thanks

    • SharDB uses “an existing 1-3 character MD5 hash (by blog id) based multi-database sharding structure”.

      HyperDB uses a different sharding structure.

      Otherwise, the two DB plugins are the same.

  5. I’ll try WP MU + BuddyPress + some other plugins + SharDB :D

  6. Great work. It is very interesting.

    How is this one different from the one WPMUdev.org Premium is offering? Pros and cons?

  7. very impressive! I have 256 running on the multi-db premium plugin. Would this one be able to take over from it? how exactly does it name the databases?

    currently mine starts at _00 and ends at _FF

    unrelated: every time I get a big head from my new coding exploits, I see what you’re up to and it makes me realize I have a lot more to learn! hehe, you’re my geek hero, when i grow up to be Canadian, I wanna be just like you

    :)

  8. It’s designed to work with the 0 to f, 00 to ff or 000 to fff database names. If you used uppercase letters you may need to tweak the db-settings.php to change the MD5 hash to uppercase.

  9. Can’t install it properly.. Can someone please post a step-by-step process to run this? I really need this.. Please help..

    • Is this for a fresh install or an already working mu installation?

      • The beta is for an existing multi-DB install. In the next few weeks I’ll be adding a migration script.

        • I already migrated and its working really well.

          I ended up having to migrate from one server to another using bzip dumps for each blogs tables. Then I wrote a script to associate each blogs tables to its new db (I did the 3 hash) and set up a bash script to import using mysql. Bit of a mission.

          • cool. Thanks for letting me know.

            That’s a decent idea :) I already have a bash script for backing any sharding size up. It wouldn’t take much work to extend it to do the backup from the original DB and restore to the new ones.

          • Hello!

            Do you have sharing your script to other people?

          • Sorry, I haven’t had a chance to look at it. The bulk of the merge development time should be complete in a week and a half. After that I’ll get back to some other projects.

  10. I really hope you’ll also release a script so it automatically takes a single DB wpmu installation and turns it into a multi DB one. i.e. one could pass the right credentials to the plugin to create the desired number of DBs?

    Can we look forward to anythign liek that in the future?

  11. Hi Ron,

    First of all thank you for the plugin :), i kinda confuse how to use it on fresh install ? i mean what i suppose to set on db-settings for a new wpmu without any upgrade from multi-db plugin and where is the script to split to multi DB ? . Any help and respose is appreciated. Thank you.

    • Assuming you want to go to 16 DBs, if you only have the main blog, back the blog up and restore it to the ‘c’ database. Then you can set up the database config and use the plugin.

      If you have a handful of blogs you can find what database each blog goes in by running a query against the DB:

      SELECT blog_id, MD5(blog_id) FROM wp_blogs

      The first letter of the MD5 is the database the tables for that blog go in.

  12. Ron,

    Nice work! I’m a big fan of using as much of the native code as possible to get what I want/need.

    I need some help in detective work. I’ve got the plugin working for the most part.

    in the admin -> blogs screen I can see the blogs are assigned to the proper dbase….

    Dataset / Partition

    ie: blog_id 4 -> dbase_a….blog_id 1 -> dbase_home.

    I can see my gui on the front end of each blog, but no posts found on any blog.

    Where should I begin to look to solve this?
    Any ideas?

    Thanks in advance for your time.

  13. LOL…. Wow you’re quick. ;-)!

    I answered my own question. I’m not running any replication and don’t require master and slave settings so I commented out that variable and function call in the config file.

    Works great!

  14. Thanks firstly for this plugin. However, I tried implementing it on an active free blog host which has over 14,000 blogs on it and when visiting wp-admin it claimed no wpmu sites were defined. Is there a step-by-step tutorial on how to implement your plugin? Any help would be so greatly appreciated as you can imagine the load on the server with this many blogs.

    Best regards,

    James Ellison

  15. I neglected to ask if this plugin can even be dropped into a live operation with multiple blogs already on it?

  16. Hi! Any status with the migration-script? And did you test this with a wp 3.0 build? Hope you get time for this really nice plugin!

    Thanks for your time & work! M

  17. Wow, this is awesome. Have a singel wpmu db wich we want to split to several DB servers. Looking forward to any migration scripts.

    Thanks

  18. Ron – Just wanted to pass along my appreciation for your great plugin. I was using an alternative multi-db tool, and was constantly running into strange compatibility issues. I’ve had yours running for almost 2 weeks now and haven’t had a single compatibility issue – you are a live saver! I just have a couple of comments for you:

    1) I typically use skip-networking in the mysql my.cnf file to shave a little time on sql queries. All of my databases are running localhost along with my wpmu install. However I was getting DB errors until I cut it off. This probably won’t effect many people, but thought I would mention it in case anyone else experiences the same thing.

    2) I wrote a php script for migrating single to multiple databases. I used it when I first converted to multiple DB using the wpmu premium plugin. I would be happy to send it to you for inclusion with your tool if you are interested.

    All the best!!
    Another Ron :-)

  19. any news on the migrating from 1 to 16/256 Db scripts guys?

  20. i have a script that uses this line:

    wpmuBaseTablePrefix.blog['blog_id']. “_options

    this has stopped working with shardb. because it got all info from the same DB. how do i rewrite the code to get the right DB?

  21. I cant get deannas new blog default to work after i started using shardb

    i save the options but when i register a new blog i get “vanilla” settings….

  22. lechuck says:

    Will this plugin work with release 3.0 multisite ?

    • I’ve been testing a MU install upgraded to WP 3.0 and haven’t had any issues yet.

      It will need to be updated before it will support a WP single site that has had the network enabled in WP 3.0.

      • Alok Sharma says:

        Hi,

        Currently I am using WP 3.0 Multisite but I plan to use WP Hive to create a network of sites with domain, sub-domain & sub-directory installs.

        Will this plugin work or some mods will be required.?

  23. Hi Ron, I have a problem with high loads on the server (I am hosted with company that hosts websites and databases on clusters), so I was wondering if this would help me? Running WordPress 3.0 and intending to switch to multisite at some point.

    • This plugin isn’t for single WP installs. See my comment on Jan 3 regarding the sharding algorithm for MU installs (including those upgraded to WP 3.0).

      SharDB uses “an existing 1-3 character MD5 hash (by blog id) based multi-database sharding structure”.

      • I guess I am either very confused or not good with words and I apologise.

        I do plan to break the contents of my complex website into a couple of MU-style websites. I was wondering if this would help me once I’ve done that, as I’m afraid of my host and their site lockdowns due to high load (S*e*r*v*a*g*e).

        I hope it made more sense now and I do hope I’m not wasting your time.

  24. beachbum says:

    Any news on the 3.0 compatible version for multi-site with the migrating single to multiple databases database tool?

    • I didn’t have time to look at it this month. If you have an upgraded MU install, you can use the version that is there. If you have a new WP 3.0 network you can run up to several thousand sites before you really *need* to switch to a sharded DB structure.

  25. I just tried the upgrade from WPMu 2.9.2 to Wp 3.0 with SharDB, and I got the following error:
    Fatal error: Call to undefined method wpdb::tables() in /home/…/public_html/wp-admin/includes/upgrade.php on line 1403

  26. Okay… I got it now. Guess I answered my own question. The configuration didn’t work because it was commented out. I uncomment and WOOP !! There it is…

    Please update the Shardb installation instructions to include the missing line define(‘WPMU’, ’1′);

    And also inform that the db-settings.php configurations are commented, to include uncommenting ? Thickheads like me would appreciate it !! Ha ha…

    I got a few db tables to export and import now :)

    Thanks for the plugin !!

    • The latest version does not require the WPMU constant. That’s why it was removed from the instructions.

      • Thanks for the response :)
        Okay, perhaps remove the instruction for 2 lines, rather than the required 1 ?

        I am stuck with one more question…
        Where does vip db entry go ? Anywhere I put add_vip_blog(3,’vip1′); below the add_vip function to the php closing tag, it shows as being in ‘home’ db in admin. Is this the norm that all vip entries will say acc_examplehome in admin rather than acc_examplevip1 ?

        • I’m also interested to know how to get vip databases to work.

          I configured shardb for separate home database, and add {prefix}_home to the vip database array but don’t know how to get shardb to read from there.

          It works if I import all the tables into global.

        • I also noticed this and it didnt make any sense.

          I assumed that the main database defined in wp-config would be the master database, then the main site(blog1) would be a separate database called home, and any vip databases would also be separate with the vip prefix.

          Seeing that it made each site a separate database I commented out the blog1-home entry and added all sites that should share a database.(kinda backwords)

          So now I have what I thought this would do, giving each vip site(a site not defined in the vip section) and home its own database(prefix_xx), and all the rest are prefix_home.

          I havent had a chance to delete some of the tables in the master database as defined in wp-config so see what effect this has but Im guessing that this database is no longer used.

          Also, I have define(‘WPMU’, ’1′); and define(‘WPMU’, ’true′); in my wp-config. These are obsolete now?

  27. Hi Ron
    Great script. Got it working but just got one question. I need to setup a wp_users table for one specific vip blog Can I just create a table wp_users and wp_usermeta for that VIP db Or will it always use the users from the default home db

    Hope this makes sense
    Thanks a Mill

  28. Is there anyway to run WP 3 (not networked) with multiple DB’s? I have a site thats gotten into the 100,000 post range and its pretty much dead in the water when it comes to page load times.

    We were going to use MU, but a bunch of plugins we needed couldn’t handle the database tables being named differently in MU.

    Thanks!

  29. Hi,
    I was playing with SharDB last night and had a few minor issues. So, here I am today with a few minor questions.
    First, there’s still no migration script correct? Should I export/import my db tables manually? or could I grab the script from the wpmu dev multi-db plugin, and give that a go?
    Also, it was mentioned that SharDb is incompatible with ‘new blog defaults’ plugin, and I believe you confirmed this. I see SharDB is listed as having been tested with over 50 plugins, but you don’t mention the outcome of these trials. I am very curious if you have a running list of tests and their results..
    Thank you for your time,
    -Erik

    P.s I love your custom captcha images! I think we all agree, Andrea Rocks!

  30. Dr. Kenneth Noisewater says:

    BTW, is WP or WPMU compatible with MySQL multimaster replication (MMR)? A theoretical use for that would be to have one or more masters at more than 1 site MMR with each other, then hang slaves off of them, and have HyperDB or SharDB split read/write traffic among them.

    This requires a bit of discipline when it comes to insert/replace as Mysql’s auto_increment indexes are handled by MMR, and I’ve seen code (such as Gallery2) have issues with MMR as it didn’t use auto_increment and would otherwise have had to been hacked with MMR coherence in mind.

Trackbacks

  1. [...] will have a full write up soon for migrating a single mu database to Shardb. « Hosting WordPress Mu [...]

  2. [...] Version 2.7.3 | By Ron Rennick | Visit plugin site [...]