WordPress MU

Being close to reach the dreaded 32000 blogs limit in wpmu, I've been studying a scaling architecture that would allow to scale virtually indefinitely an MU site.
In this thread : http://mu.wordpress.org/forums/topic.php?id=1343 , Donncha explains some parts of wp.com setup. Matt on his blog has also given some clues as per their layout. Basically, from what I understand, a cluster of web servers, multiple rsync'ed nfs servers and database servers.
I wasn't too looking forward to implementing such a thing, because NFS means UDP, means a lot of bandwidth, means a VPN, means expenses. And because the whole setup meant quite a few modifications to the code, and because to me it looks like there would be more and more problems when adding new servers.

So, I've been looking into implementing another solution. You're probably interested in reading the following (or at least i'm interested in you reading it ;-) ) if:
- you're a WP developper.
- you have a sysadmin experience
- you plan on having thousands, tens of thousands or more users in your service.

I'm looking for feedback on the architecture I plan, so that if someone spots a weakness I would have overlooked I can avoid wasting a lot of time on its implementation.

Here you have a graph of the solution I plan to implement:
http://www.creerunblog.fr/scaling.gif

The idea is to receive all incoming requests on a single HTTP server.
This server, using mod_proxy and mod_rewrite, will route requests to X backend servers, acting as a reverse proxy.
This can be done very simply once mod_proxy is installed, by adding lines such as:

RewriteEngine on
RewriteRule ^t(.*)$ http://somewhere.com/ [P,L]

This would route all requests starting with a t to the site somewhere.com and present its contents to the user as if delivered by the front server.

In the following I'll give example with my own site urls ( http://unblog.fr/ )
So, with a set of directives like:

RewriteEngine on
# redirects abc.unblog.fr to abc.unbloga.fr
RewriteRule ^a(.*)\.unblog\.fr(.*)$ http://a$1.unbloga.fr$2 [P,L]
# redirects bcd.unblog.fr to bcd.unblogb.fr
RewriteRule ^b(.*)\.unblog\.fr(.*)$ http://b$1.unblogb.fr$2 [P,L]
RewriteRule ^b(.*)\.unblog\.fr(.*)$ http://c$1.unblogc.fr$2 [P,L]
RewriteRule ^b(.*)\.unblog\.fr(.*)$ http://d$1.unblogd.fr$2 [P,L]
etc.

And combined with the appropriate entries for unblogX.fr in bind, I can route requests to different servers based on the blog name.
Then, on the backend servers, I have one instance of MU installed for each domain like "unblogX.fr", with the scripts, blog files and database located on the same server.
Only the main set of tables is located on the front server (for instance), and needs to be modified so that queries on the main tables go to this database instead of the localhost.

Advantages of this solution over the wp.com one:
- Much less code needs modification regarding database access and uploaded file access. Only the requests to the main tables need to be redirected to the main database, otherwise all requests are treated locally.
- No need for NFS drives.
- No need for dynamic sync of any member in the system. Every instance of MU acts as a normal one with its own set of bloggers, its own set of uploaded files, its own DB. This means optimal performance on the local machines, and very little overhead overall when compared to a simple MU installation, with just the data transfer between the proxy and the backend server added.
- No problem with the 32000 bloggers limit anymore. If any letter reaches 32000 bloggers, you can split it with rewrite rules like:
RewriteRule ^aa(.*)\.unblog\.fr(.*)$ http://aa$1.unblogaa.fr$2 [P,L]
RewriteRule ^ab(.*)\.unblog\.fr(.*)$ http://ab$1.unblogab.fr$2 [P,L]
RewriteRule ^ac(.*)\.unblog\.fr(.*)$ http://ac$1.unblogac.fr$2 [P,L]
etc.
- One server gets overloaded ? A disk gets full ? Add a new server and split some of your users on it. Easy !
- You can split in as many environments as needed. Smaller databases -> easier backups, easier script updates, less downtime for users (particularly when upgrading DB.
- It's easy to beta-test new features, just create a test environment like:
RewriteRule ^test\.unblog\.fr(.*)$ http://test.unblogtest.fr$2 [P,L]
and do your changes on the unblogtest environment. When yo're happy, push files to the other environments.

Drawbacks of the solution:
- By default the environment unblogX.fr is meant to run on this domain and will generate html pages with this domain. Most requests in MU should be relative, but if there are some absolute paths the domain needs fixing. This can probably be done by cheating the HOST variable in mu.
- The IP of incoming requests is always the proxy one. IP detection in MU needs to be replaced to check the HTTP_X_FORWARDED_FOR or HTTP_VIA variables.
- wp-newblog.php needs to be modified to create the blog in the right environment.
- Many entries need to be created in bind, and although you don't need to actually register the domains, I'm not too sure what happens if unblogX.fr exists.
- A script to move users tables and files around is needed (would be in any multi-database setup).

That's about all I see. Please tell me if you think I overlooked something or about the big hidden drawback I didn't think about.

If anyone's interested in implementing something similar, here's the correct set of rewrite rules to implement the proxy archtecture described above:

RewriteCond %{HTTP_HOST} ^a(.*)\.domain\.com$
RewriteRule ^(.*) http://a%1.domaina.fr/$1 [P,L]
RewriteCond %{HTTP_HOST} ^b(.*)\.domain\.com$
RewriteRule ^(.*) http://a%1.domainb.fr/$1 [P,L]
RewriteCond %{HTTP_HOST} ^c(.*)\.domain\.com$
RewriteRule ^(.*) http://a%1.domainc.fr/$1 [P,L]
...

domaina.com, domainb.com, domainc.com etc. need to be defined in bind and point to either the same server or another one.

Also, on secondary MU installations, you need to add something like this:
$tmphost = explode('.',$_SERVER['HTTP_HOST']);
$_SERVER['HTTP_HOST']=$tmphost[0].'.domain.com';

so that even though served from domainX.com, absolute URLs are generated with the correct domain name.

// just a log entry for those interested

follow up on the splitting of global and local DB.

In addition to the SK2 join query explained here:
http://mu.wordpress.org/forums/topic.php?id=1769&page=2&replies=47#post-13919

there is another query that is a problem around line 57 of links-manager.php :
$results = $wpdb->get_results("SELECT link_id, link_owner FROM $wpdb->links LEFT JOIN $wpdb->users ON link_owner = ID WHERE link_id in ($all_links)");

Note: seems not to be there in 1.0 anymore.

Just giving you some input now that my personal programmer/sql guru/hubbie extraordinaire/brains behind this outfit has had a chance to look at this thread. :D

First, he likes your diagram. :)
Second, the short answer from him is "Yeah, that'll work".

In fact, we are working on a similar goal. We are not up to multiple servers or even thinking of rewrite rules yet, but we do need to have multiple dbs and your solution is slightly simplier than the one he was going to implement.

A way you could get around the join issue is to replicate the master (global) tables in all the child dbs.

thanks for your feedback :).
I know for sure it works now, since it's working on my site ;-).
The setup is indeed not necessarily linked to multiple servers. You can just have the proxy/rewrite rules hit the local machine, and use this to have multiple environments locally. Many DBs mean easier updates, backups etc. at the small cost of an rsync script to run when you tweak te code / add plugins.

Regarding the joins, apart those 2 I mentionned, one of which is linked to SK2 and the other isn't there in latest MU builds, I'm now fairly positive there isn't any others. So keeping the global tables in only one place is feasible (no replication needed...).

if you're interested, I can post my modified wp-db file which splits requests to global/local DB. It's fairly straightforward.

quenting,
I would be interested in the modified wp-db file.

Thanks!

Yes, I would totally be interested. Hubby's overworked at the moment.

I think this would make for a wpmudev.org howto, as we've talked about earlier.

Not to push it, but could andrew give an update on when the wiki at wpmudev will be up?

When andrew has time ;)

i'm actually contemplating on a new site that would serve as a wiki for wpmu.

At the moment, the only real spare time I have is on weekends. Cleaning the house, car, cat and general errands take up saturdays. So unless is it's a paid gig (In which case the cat can wait), sunday is my only day for wpmu stuff.

I'm sure we could work out some money andrew. How much would you need?

Nah, wouldn't charge for it. It's just a matter of finding the time. It looks like this afternoon may be a bit slow at the office.

Alright, I just registered a domain. I handle all of my domains through 1and1 and they typically have a domain ready within six hours. So, I should be able to start playing with wiki scripts/software when I get home.

WARNING: this is modified from the exact version i tested, USE AT YOUR OWN RISK. Do not use without much more testing on a prod environment. Note this has been modified from a wp-db file from a march nightly, so if you use a more recent build, you need to be specially careful with not missing out any changes.

here:
http://www.creerunblog.fr/wp-db.txt
you can see a sample implementation.
It's slightly different from what I have actually implemented regarding the settings mostly which are in an outside file for me but i put them inthere to make it clearer.
Basically those lines:
define('MY_USE_MULTIPLE_DBS', true);
define('MY_LOCAL_DB_NAME', 'xxx');
define('MY_LOCAL_DB_USER', 'xxx');
define('MY_LOCAL_DB_PASSWORD', 'xxx');
define('MY_LOCAL_DB_HOST', 'xxx');
define('MY_GLOBAL_DB_NAME', 'xxx');
define('MY_GLOBAL_DB_USER', 'xxx');
define('MY_GLOBAL_DB_PASSWORD', 'xxx');
define('MY_GLOBAL_DB_HOST', 'xxx');

allow you to define database access. Global settings should always be the same, while the local settings will connect to each local database.

Then there is this line:
var $my_global_tables = "wp_blogs|wp_users|wp_site|wp_sitemeta|wp_sitecategories|wp_usermeta|sk2_blacklist|my_counter|my_cache|my_categories|my_categories_links|wp_1_(\S)*";

where you need to define the regexp pattern that when matched will tell the class to connect to the global DB.
you should add here all the global table names. Note mine has the admin tables inthere too because I use sometimes the admin settings as global settings for some plugins (like sk2).
Then I created the my_db_connect function, that creates a link to each of the 2 DBs.

Finally the most tweaked method is the query one, which basically parses the query and matches the regexp to figure if it should use the local or global DB.
Only the first part of the query is matched because in some cases table names might be used as parameter values, or when a query is embedded in a log message for instance.

Happy scaling :-).

moderator , please delete the previous post.
An issue was raised with its contents that should be sorted before I repost it. I will repost the code when it's working properly.

quenting take a look at Perlbal which is a reverse-proxy to on your web frontend server(s). That can reverse proxy to multiple webservers which serve up the content for the blogs. You can also run a couple instances of memcached instances instead of caching data to disk to reduce the load on your MySQL servers. Look at using mogilefs for storing user content.

Splitting users up into over database clusters with a global wpmu database is suggested. Look at the various presentations from LiveJournal on how they scaled their infrastructure and learn from their mistakes which they made in the past. Start using their tools like perlbal, memcached, etc.

NFS works over TCP or UDP. Personally I would prefer to use mogilefs over nfs as it automatically mirrors files to multiple machines.

QUENTIN: the adsense in your blogs are drive by you or your users?

ok i've reposted the file since it is in fact working fairly well, only a couple of things must be taken care of:

in my mu nightly, there is in wpmu-functions a function get_blog_details() that runs a couple of queries. The problem of that function is that if you're logged in as a user, and visit another user's blog, it tries to read info from your local tables in the blog's local database, which, if they're not the same, will produce a DB error.
The thing is, I've checked carefully, and the "local calls" (on the options table) are never used anywhere in the rest of the script. So i just commented them out, and now things are working fine.

The only problem left is when you search for a blog in the MU admin, and then click edit/admin or any of the possible actions, it will try to run these actions off your main domain, which won't work if the localDB hosting the blog is different from your main blog's local DB. This is just solved by modifying those links so that the action is run off the target blogs'domain, instead of your main blog's.

Not sure if that's too clear hehehe.

I've copied this into http://codex.wordpress.org/How_to_Scale_WPMU