Scaling WordPress MU with multiple databases
Posted by Ron in 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.
![[Post to Twitter]](http://wpmututorials.com/wp-content/plugins/tweet-this/icons/tt-twitter-micro3.png){kind=icon}
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.
I’ve been testing and running SharDB. Thank you for the good work and Happy new year!
You’re welcome. Thanks for letting me know it was working for you
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.
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.
I’ll try WP MU + BuddyPress + some other plugins + SharDB
Great work. It is very interesting.
How is this one different from the one WPMUdev.org Premium is offering? Pros and cons?
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
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.
[...] will have a full write up soon for migrating a single mu database to Shardb. « Hosting WordPress Mu [...]
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.
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?
That’s in the plans.
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.
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.
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!
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
I neglected to ask if this plugin can even be dropped into a live operation with multiple blogs already on it?
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