In the early morning hours yesterday we moved UMW Blogs from multi-db (which is a multi-database setup provided through WPMuDev Premium at a cost) to the ever so free SharDB multi-db setup, and I’m happy to say it worked. We’ve had some “unscheduled downtime” recently at UMW Blogs, and between the ever-growing traffic and a syndication/tags blog that’s all too full with 99,000 posts—it was high time to start cleaning database house. This post will take you through the switch from multi-db to SharDB, which is surprisingly simple. And the following post will be about optimizing a multiple database setup that depends heavily on syndication like UMW Blogs does.
Multi-DB –> SharDB
So, our first step was to move to Ron Rennick’s (of wpmututotials and @wpmuguru fame) SharDB setup because UMW is no longer a subscriber of WPMuDEV Premium, and given upgrades to multi-db are no longer available to us—-we were eager to be rid of it. What’s more, I’m hoping this move to SharDB (accompanied by the database optimization and tags blog clean up) will bring UMW Blogs back to a snappiness we haven’t known for a couple of months now. So here’s to hoping….
When I downloaded SharDB I expected a lot more work than there actually was. The move is rather simple, and save a hiccup or two on my end, it went smooth as silk. The first thing to do is configure the db-settings.php file. UMW Blogs is running on 16 databases (well actually 18, but that is besides the point) on one dedicated server, we don’t have several servers or multiple data centers so I think our setup was rather simple. And while we probably should consider getting another server or two some time soon, as Joss Winn suggested, for right now we’re easy.
[What follows is pretty ugly 
]
The UMW Blogs db-settings.php configs look like this:
// If you have multiple datacenters you can come up with your own datacenter
// detection logic (php_uname?). This helps ensure the web servers try to
// connect to the nearest database servers first, then distant ones.
define( 'DATACENTER', '' );
function add_slave($read, $host, $lhost = '', $user = DB_USER, $password = DB_PASSWORD) {
global $slaves;
$slaves[] = compact('read', 'host', 'lhost', 'name', 'user', 'password');
}
/* Add your configuration here */
//Use this configuration for a hexidecimal based hash
//Ex. you have 256 databases that follow the naming convention acct_wpmuXX
//where XX is the hexidecimal hash for the blog DB
// how many characters of hexidecimal hash
$shardb_hash_length = 1;
// what is the prefix of your blog database shards (everything before the hexidecimal hash)
$shardb_prefix = 'umwblogs_wpmu_';
// set a string to be used as an internal identifier for the dataset
$shardb_dataset = 'umwblogs';
// do you want to put your primary blog (blog_id 1) in its own 'home' database?
//$enable_home_db = true;
// how many, if any, VIP databases do you have?
$num_vipdbs = 1;
// add this to set the write master read priority (default 1)
//$shardb_master_read = 99;
// add this if all of your databases are on a local server
$shardb_local_db = true;
// use this function to add a read slave host
//add_slave($read_priority, $hostname, $local_hostname, $user, $password);
// instructions for adding vip blogs at the bottom of this confg filei
/* That's all, stop editing! Happy blogging. */
I wasn’t entirely certain about all the details in the config file, so before I did the upgrade I posted a few questions on the WPMu Forums here, and Ron himself bailed me (thanks Ron!). Once I had the db-settings file squared away I uploaded it to the main directory of UMW Blogs. After that, I added two lines to the wp-config.php file:
//SharDB Settings
define(‘WPMU’, ‘1′);
require_once(‘db-settings.php’);
And finally, I uploaded the dp.php file to the wp-content directory, and the shardb-admin.php file to the mu-plugins directory. (Keep in mind I had already deleted the db-config.php and db.php files that were running multi-db setup.)
Once I had everything up, I went to the homepage of UMW Blogs and got this error:
Connected to localhost:3306 but unable to select database ‘umwblogs_umwblogs’ while querying table ‘wp_domain_mapping’ (global_r)Connected to localhost:3306 but unable to select database ‘umwblogs_umwblogs’ while querying table ‘wp_site’ (global_r)Connected to localhost:3306 but unable to select database ‘umwblogs_umwblogs’ while querying table ‘wp_site’ (global_r)Connected to localhost:3306 but unable to select database ‘umwblogs_umwblogs’ while querying table ‘wp_site’ (global_r)Connected to localhost:3306 but unable to select database ‘umwblogs_umwblogs’ while querying table ‘wp_site’ (global_r)Connected to localhost:3306 but unable to select database ‘umwblogs_umwblogs’ while querying table ‘wp_site’ (global_r)No WPMU site defined on this host. If you are the owner of this site, please check Debugging WPMU for further assistance.
Turns out, multi-db does not require you to change the database name in the wp-config for the mutliple databases. so our database settings were still set to the original single database we started UMW Blogs on: umwblogs_umwblogs. Once I figure this out, I changed the db_name setting to point to our global database:
define('DB_NAME', 'umwblogs_wpmu_global');
Now this aneming convention is particualr to our setup, we have our tables named umwblogs_wpmu_global, umwblogs_wpmu_0, umwblogs_wpmu_1, umwblogs_wpmu_2, etc., up through 9, and then a through f. So our database names look like this:
umwblogs_wpmu_global
umwblogs_wpmu_0
umwblogs_wpmu_1
umwblogs_wpmu_2
umwblogs_wpmu_3
umwblogs_wpmu_4
umwblogs_wpmu_5
umwblogs_wpmu_6
umwblogs_wpmu_7
umwblogs_wpmu_8
umwblogs_wpmu_9
umwblogs_wpmu_a
umwblogs_wpmu_b
umwblogs_wpmu_c
umwblogs_wpmu_d
umwblogs_wpmu_e
umwblogs_wpmu_f
umwblogs_wpmu_vip1
Once I changed the db_name setting in wp-config, SharDB seemed to work fine on every other blog but the main blog: umwblogs.org. After a second of panic, I realized that SharDB was looking for the home blog (or Blog ID 1) in the database _wpmu_c, but I had it in the wpmu_vip1 database. Once I exported the tables from the wpmu_vip1 database into the wpmu_c database, everything worked perfectly. Put the question for me remains, why didn’t SharDB pick up the vip1 database? I had it noted in my db-settings file as follows:
// VIP DB's
if ($enable_home_db === true) {
add_vip_blog(1,'vip1'); // home blog
}
Additionally, when I was doing something in the backend, I came across this error:
Connected to localhost:3306 but unable to select database ‘umwblogs_wpmu_global’ while querying table ‘wp_sitemeta’ (global_w)
I can’t for the life of me remember where I was precisely now, but I’m sure I’ll find it again soon. I was wondering if this might have something to do with a number of global tables we added to the db-config.php file for getting BuddyPress to work with multi-db? Not sure here, kind of fishing for some help, but I know with multi-db we had issues adding global tables for BuddyPress to the global database (same goes for the domain mapping plugin table), and one way to ensure those tables were created in the global database was through the following list of code I stole from D’Arcy, and he breaks the whole thing down better than I ever could here. Now I imagine SharDB doesn;t have this issue, but just wanted to get a sense if anyone else playing with the move from MultiDb to SharDB had any ideas.
// Usage: add_global_table(TABLE_NAME)
// EX: add_global_table(’something’);
add_global_table(‘umwblogs_wpmu_global’);
// BuddyPress
add_global_table(‘bp_activity_sitewide’);
add_global_table(‘bp_activity_user_activity’);
add_global_table(‘bp_activity_user_activity_cached’);
add_global_table(‘bp_friends’);
add_global_table(‘bp_groups’);
add_global_table(‘bp_groups_groupmeta’);
add_global_table(‘bp_groups_members’);
add_global_table(‘bp_groups_wire’);
add_global_table(‘bp_messages_messages’);
add_global_table(‘bp_messages_notices’);
add_global_table(‘bp_messages_recipients’);
add_global_table(‘bp_messages_threads’);
add_global_table(‘bp_notifications’);
add_global_table(‘bp_user_blogs’);
add_global_table(‘bp_user_blogs_blogmeta’);
add_global_table(‘bp_user_blogs_comments’);
add_global_table(‘bp_user_blogs_posts’);
add_global_table(‘bp_xprofile_data’);
add_global_table(‘bp_xprofile_fields’);
add_global_table(‘bp_xprofile_groups’);
add_global_table(‘bp_xprofile_wire’);
add_global_table(‘wp_domain_mapping’);
Other than that SharDB is up and running on UMW Blogs, an given it may be one of the most unstable WPMu sites in the world give we have over 100 plugins and 130 themes, it has been solid as a rock thus far, so fine work Ron.. But to make sure we don’t push our luck too much, now it’s time for the database optimization drill, post forthcoming.
Reverend
Andre Malan
Matthew K. Gold