notes on converting ucalgaryblogs.ca to use multi-db

I followed Jim's instructions to get UCalgaryBlogs.ca converted from using a single database (as is the default) to using multiple databases (17 separate databases now) via the premium.wpmudev.org Multi-DB code to prevent growing pains. The single database config is good for getting up and running, but with 300 blogs in the system, table explosion was causing grief on the shared MySQL database server - there were almost 3000 tables, which was making the automated backup script complain a bit.

While reading the documentation, I was rather confused by the term "global" - which appeared to be used in slightly different ways. Eventually, I plugged through, and got it working. The key is to test it all on a local copy of the database before running the migration script on the production server. Thankfully, the script doesn't delete anything, so I was confident that if anything borked I could just back out the multi-db files and revert to single database config without losing anything.

"Global Tables" are tables that will be stored in a shared, common database rather than in each blog's database in one of the 16 databases used by the multi-db code. These are things that are accessed by all blogs on the WPMU install, and include administrative stuff.

In the db-config-sample-16.php file that ships with multi-db, it also mentions "global-db", "globaluser", and "globalpassword" - those are just the database server address, username, and password to use when connecting to the "Global" database containing the "global tables". They used "global-" in these parameters because it's possible to configure each of the 17 databases to use different database servers, different usernames, and different passwords. For simplicity, I used the same database server and account for all 17 databases.

My db-config.php file was edited as follows:

//	Plugin Name: Multi-DB
//	Plugin URI: http://premium.wpmudev.org/project/Multiple-Databases
//	Author: Andrew Billits (Incsub)
//  Version: 2.7.0
//------------------------------------------------------------------------//
//---DB Scaling-----------------------------------------------------------//
//------------------------------------------------------------------------//
//	16,256,4096
define ('DB_SCALING', '16'); // use 16 databases for the blogs
//------------------------------------------------------------------------//
//---DC IPs---------------------------------------------------------------//
//------------------------------------------------------------------------//
//	Usage: add_dc_ip(IP, DC)
//	EX: add_dc_ip('123.123.123.', 'dc1');
add_dc_ip('127.0.0.1', 'dc1'); // DN: change this to the IP address of your WEB SERVER
//------------------------------------------------------------------------//
//---Global Tables--------------------------------------------------------//
//------------------------------------------------------------------------//
//	Do not include default global tables
//	Leave off base prefix (eg: wp_)
//
//	Usage: add_global_table(TABLE_NAME)
//	EX: add_global_table('something');
// DN: These are tables that will be stored in the global database configured below (wpmu_global)
//     rather than in the 16 blog databases.
add_global_table('mass_mailer');
add_global_table('registration_log');
add_global_table('reports_comment_activity');
add_global_table('reports_post_activity');
add_global_table('reports_user_activity');
add_global_table('signups');
add_global_table('support_faq');
add_global_table('support_faq_cats');
add_global_table('support_tickets');
add_global_table('support_tickets_cats');
add_global_table('support_tickets_messages');
add_global_table('domain_mapping');
add_global_table('comment_activity');
add_global_table('blog_activity');
add_global_table('user_activity');
add_global_table('post_activity');

//------------------------------------------------------------------------//
//---DB Servers-----------------------------------------------------------//
//------------------------------------------------------------------------//
//	Database servers grouped by dataset.
//	R can be 0 (no reads) or a positive integer indicating the order
//	in which to attempt communication (all locals, then all remotes)
//
//	Usage: add_db_server(DS, DC, READ, WRITE, HOST, LAN_HOST, NAME, USER, PASS)
//	EX: add_db_server('global', 'dc1', 1, 1,'global.mysql.example.com:3509','global.mysql.example.lan:3509', 'global-db', 'globaluser',  'globalpassword');
// DN: NOTE: change 'dbserver.com' to the address of the mysql server,
//   'username' to your mysql username,
//   'password' to the appropriate password.

add_db_server('global', 'dc1', 1, 1, 'dbserver.com', 'dbserver.com', 'wpmu_global', 'username', 'password');
add_db_server('0', 'dc1', 1, 1, 'dbserver.com', 'dbserver.com', 'wpmu_0', 'username', 'password');
add_db_server('1', 'dc1', 1, 1, 'dbserver.com', 'dbserver.com', 'wpmu_1', 'username', 'password');
add_db_server('2', 'dc1', 1, 1, 'dbserver.com', 'dbserver.com', 'wpmu_2', 'username', 'password');
add_db_server('3', 'dc1', 1, 1, 'dbserver.com', 'dbserver.com', 'wpmu_3', 'username', 'password');
add_db_server('4', 'dc1', 1, 1, 'dbserver.com', 'dbserver.com', 'wpmu_4', 'username', 'password');
add_db_server('5', 'dc1', 1, 1, 'dbserver.com', 'dbserver.com', 'wpmu_5', 'username', 'password');
add_db_server('6', 'dc1', 1, 1, 'dbserver.com', 'dbserver.com', 'wpmu_6', 'username', 'password');
add_db_server('7', 'dc1', 1, 1, 'dbserver.com', 'dbserver.com', 'wpmu_7', 'username', 'password');
add_db_server('8', 'dc1', 1, 1, 'dbserver.com', 'dbserver.com', 'wpmu_8', 'username', 'password');
add_db_server('9', 'dc1', 1, 1, 'dbserver.com', 'dbserver.com', 'wpmu_9', 'username', 'password');
add_db_server('a', 'dc1', 1, 1, 'dbserver.com', 'dbserver.com', 'wpmu_a', 'username', 'password');
add_db_server('b', 'dc1', 1, 1, 'dbserver.com', 'dbserver.com', 'wpmu_b', 'username', 'password');
add_db_server('c', 'dc1', 1, 1, 'dbserver.com', 'dbserver.com', 'wpmu_c', 'username', 'password');
add_db_server('d', 'dc1', 1, 1, 'dbserver.com', 'dbserver.com', 'wpmu_d', 'username', 'password');
add_db_server('e', 'dc1', 1, 1, 'dbserver.com', 'dbserver.com', 'wpmu_e', 'username', 'password');
add_db_server('f', 'dc1', 1, 1, 'dbserver.com', 'dbserver.com', 'wpmu_f', 'username', 'password');

//
//	Note: you can also place this section in a file called db-list.php in wp-content
//  EX: add_db_server('global', 'dc1', 1, 1,'global.mysql.example.com:3509','global.mysql.example.lan:3509', 'global-db', 'globaluser',  'globalpassword');
//------------------------------------------------------------------------//
//---VIP Blogs------------------------------------------------------------//
//------------------------------------------------------------------------//
//	Usage: add_vip_blog(BLOG_ID, DS)
//	EX: add_vip_blog(1, 'vip1');
// DN: I didn't add any VIP blogs.

To create the databases, I used the script at http://db-tools.wpmudev.org/db.php and it generated the code below, which I ran on the MySQL server to create the databases:

CREATE DATABASE `wpmu_global` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
CREATE DATABASE `wpmu_0` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
CREATE DATABASE `wpmu_1` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
CREATE DATABASE `wpmu_2` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
CREATE DATABASE `wpmu_3` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
CREATE DATABASE `wpmu_4` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
CREATE DATABASE `wpmu_5` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
CREATE DATABASE `wpmu_6` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
CREATE DATABASE `wpmu_7` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
CREATE DATABASE `wpmu_8` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
CREATE DATABASE `wpmu_9` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
CREATE DATABASE `wpmu_a` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
CREATE DATABASE `wpmu_b` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
CREATE DATABASE `wpmu_c` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
CREATE DATABASE `wpmu_d` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
CREATE DATABASE `wpmu_e` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
CREATE DATABASE `wpmu_f` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

After copying the db.php and db-config.php files into place as per Jim's instructions, it all Just Worked™. New content was being stored in the 16 blog databases, and sites were behaving as expected, but with slightly less table explosion bloat as before.

One thing that makes me a little nervous is that the multi-db code isn't core to WordPress, and is part of the premium.wpmudev.org subscription. This means that it can break in the future - there is no obligation for WordPress to continue to work with it, and if for some reason premium.wpmudev.org decides to abandon the plugin or stop updating it, I'm locked into WordPress 2.7. Neither of these made me lose too much sleep. Worst case scenario, I can always recombine the tables from all 17 databases back into a single überdatabase, assuming we haven't outgrown the physical limits of a single MySQL database by then.

comments that didn't get included when I converted this blog from WordPress to Hugo:

Jim

http://bavatuesdays.com

2009-03-05 20:51:31 +0000

As the community stands now, if premium dev stopped supporting it in the future, someone else would take it up. They based their work on the work of another, and given the script is crucial for a big WPMu setup---and there are many of them now---I think we're in good shape. I hope, I pray!

dnorman

https://darcynorman.net

2009-03-05 21:09:44 +0000

maybe, but the code isn't GPL, or any other license that would allow others to pick up the gauntlet. The license.txt file that ships with the code reserves full copyright to Incsub - which is fine, but means a fork is of questionable legality without Incsub first relicensing the code under a compatible license.\r\n\r\nProbably not a big deal, but definitely something to keep in mind.

Scott

http://blogs.ubc.ca/scottmcmillan

2009-03-10 09:59:32 +0000

Thanks for posting this, very helpful. Were the backup problems the primary reason why you made the switch or was it performance? This is something we will have to consider over the summer. We are close to 300 blogs now but have not noticed a real change in cpu and memory usage and we have been averaging just over 100k/page views day the last few days. Might be the PHP APC and MySQL Qcache are saving us.

dnorman

https://darcynorman.net

2009-03-10 10:02:21 +0000

I only migrated because IT was complaining about the failed backup script - so far, performance hasn't really been an issue - but we're nowhere near 100K page views per day. I'm not even running WP-Super-Cache at the moment, but will turn it on if needed.

Andrea_R

http://wpmututorials.com

2009-03-10 16:44:47 +0000

If it's a huge concern (and it definitely gives pause) Hyper DB, despite its name, is also a multiple db configuration AND is Open Source, AND is exactly what wp.com uses.

So it'll be around.

dnorman

https://darcynorman.net

2009-03-10 20:47:09 +0000

Andrea, thanks for the clarification. HyperDB definitely looks like a more robust solution, and I'd be much more comfortable using the official wordpress.com database scaling method. I wound up using multi-db for 2 reasons:

1. IT was getting cranky and I needed to make them happy ASAP.
2. I couldn't find any documentation about how to actually configure HyperDB, beyond the stub documentation on the project website.

I'll explore a bit more, and will plan on migrating from multi-db to HyperDB as soon as I can - now that IT is happy, I have some more time to fully research and plan scaling the database. Fun with organically growing community resources :-)