Richard's Weblog

Indeed it's very easy to get WPMU installed and up, but you'll find the number of tables in WPMU database increases every time a new blog is created. If you're providing public blogging service, the number of tables is going to 100, 1000, … very soon. Yes, WPMU is great, but the tables keep increasing, that may drive you mad. I was mad, at least.

In WPMU version 2.7, there are 9 global tables: blogs, blog_versions, registration_log, signups, site, sitecategories, sitemeta, usermeta, users. These 9 tables are fixed and serving for WPMU system globally. Every blog has its own 8 tables: comments, links, options, postmeta, posts, terms, term_relationships, term_taxonomy. These 8 tables are created over and over every new blog is created.

This designing is cool and useful from scalability view, especially for those big blogging service providers, wordpress.com is a good example. But WPMU uses only one database by default, it's hard to manage the excessive tables in one database, and the performance should also not be good. OK, here comes HyperDB, which can solve this problem. Actually HyperDB is derived from the code using by wordpress.com.

Before starting installing and configuring, we need to define a rule for databases. In this post, the rule is:

• One database (wpmu_db0) for the global tables and the tables of first blog. It can be called global database.
• Additional 3 databases (wpmu_db1, wpmu_db2, wpmu_db3) for the tables of other blogs, each database serves 2 blogs.

Of course, you can define your own rules based-on your needs. It's flexible.

We can start now. The first thing is to set up a standard WPMU using the global database in the installation wizard. After that, go to WordPress website and download HyperDB. The version I'm using is 2008-11-27. There are three files in HyperDB package:

• db.php, which needs to be uploaded to wp-content directory
• db-settings.php, which needs to be uploaded to the directory that holds wp-config.php
• readme.txt, oh, you know what it means

Then add the following lines near the top of wp-config.php

define('WPMU', true);
require('db-settings.php');

Then add the blow code at the bottom of db-settings.php

// a handy function for mapping blog tables to dataset
function add_blog_tables($ds, $blog_id){
    add_db_table($ds, 'wp_' . $blog_id . '_comments');
    add_db_table($ds, 'wp_' . $blog_id . '_links');
    add_db_table($ds, 'wp_' . $blog_id . '_options');
    add_db_table($ds, 'wp_' . $blog_id . '_postmeta');
    add_db_table($ds, 'wp_' . $blog_id . '_posts');
    add_db_table($ds, 'wp_' . $blog_id . '_terms');
    add_db_table($ds, 'wp_' . $blog_id . '_term_relationships');
    add_db_table($ds, 'wp_' . $blog_id . '_term_taxonomy');
}

// add databases
add_db_server('global', 0, 'mysql3326_1', 1, 1, 'localhost:3326', '', 'wpmu_db0', 'wpuser', 'thepwd');
add_db_server('s1', 0, 'mysql3326_2', 1, 1, 'localhost:3326', '', 'wpmu_db1', 'wpuser', 'thepwd');
add_db_server('s2', 0, 'mysql3306_1', 1, 1, 'localhost:3306', '', 'wpmu_db2', 'wpuser', 'thepwd');
add_db_server('s3', 0, 'mysql3306_2', 1, 1, 'localhost:3306', '', 'wpmu_db3', 'wpuser', 'thepwd');

// add global tables which are in global database
add_db_table('global',  'wp_blogs');
add_db_table('global', 'wp_blog_versions');
add_db_table('global', 'wp_registration_log');
add_db_table('global', 'wp_signups');
add_db_table('global', 'wp_site');
add_db_table('global', 'wp_sitecategories');
add_db_table('global', 'wp_sitemeta');
add_db_table('global', 'wp_usermeta');

// add the tables for the first blog (created during wpmu installation)
// the first blog's tables are in global database
// of course, you can move it to any database you want
add_blog_tables('global', 1);

$dbsnum=3; // 3 additional databases
$blogs_per_db=2; // each database serves 2 blogs

for($db_id=1; $db_id<=$dbsnum; $db_id++){
    $dataset = 's' . $db_id;
    $max = $db_id * $blogs_per_db + 1; // include
    $min = $max - $blogs_per_db + 1; // include
    for($blog_id=$min; $blog_id<=$max; $blog_id++){
        add_blog_tables($dataset, $blog_id);
    }
}

That's it!