WordPress MU

So - I think I've found out the maximum number of blogs that a basic version of WPMU can handle.

The config at edublogs.org has been pretty simple so far - the only really major hack being one to get over the 32,000 limit on uploads.

Sitting on its own dual P4 4 Gig RAM server.

However, in the last week or so a lot of new sign-ups have been falling over when they try to activate new blogs - it just doesn't happen or 'half' creates a new blog.

So... I suppose I could always put another server behind it and go for brute force - or (and this is probably best) get someone to split the database up and look into another dedicated SQL server at the same time.

What do people think? Where's the inbuilt multi-database aspect of WPMU at? Am I at risk of creating big problems for myself down the line with a custom solution?

Cheers, James

I don't remember your config off the top of my head James, but would splitting the db and web content be an option?

Database on box 1, everything else on box 2. This would only require a modification to your config file to specify a diff db host.

Not a long term solution, but at least an interim step.

After listening to Donncha's speech from the Ireland conference recently, it "sounded" like multiple db support isn't too far off in some form.

The only problem is that i've been bugging donncha for multiple db support for a while now. I honestly think he's just too busy with wp.com at the moment.

Thanks guys. I might try both. It'll cost but you only live once huh!

The only thing that confuses me is that performance is fine (well, pretty good) - it's just the activation of new blogs... the whole process is quick enough but the activation email just gets stuck - could this be a sql config issue or would it all be down to memory / too big a database?

True, true. If it comes down to it, you end up coding your own and sticking with it.

WP.com uses the "4096" method, as I lovingly refer to it, for their stuff.

That's 4096 total different databases, not servers. I think he said they currently have about 15 database servers total, some master, some slave, some for just reads.

I "think" he said that one of those were strictly for "their stuff", like here, wp.org, automattic, etc.

All of which uses a hash of the blog id, and then using the first 3 characters from that (which is 4096 possibilities).

Initially, you could just pop your db onto it's own box, and then go from there as your needs dictate.

Eventually, you'll have to do something beyond that probably, but at least initially splitting it into a db server and a web server should buy you some time.

As for splitting it beyond that, it's anyones guess.

For me, I'll need to pick a method and stick to it. This will depend on the number of blogs I'm expecting. Most likely it will be something based on a hash, but I'll probably go with the first 2 characters of the blog_id hash instead of 3.

To do a hash thing, you'd have to sit down and figure out all the possible combinations, then translate those into database names (and locations if needbe). Might be "easier" to just use the blog id, figure out then max number of sites you want in that db, and then do something with that.

Like: if blog_id < 1000 : use db alpha1.

Or something like that. Where "1000" is the number of blogs you want in each db. Then based on your # of accounts, create X number of db's, plus a few extra for growth.

Of course you'd have to keep track of your id usage, and then if a damn spammer gets through and registers 300 blogs it would hurt. Which would be the only real downside to this approach. Due to cancellations, spammers, etc, you wouldn't be effectively using all your db's.

plus you'd probably want to keep all the stuff for site_1 in it's own db, which means you're going to need 2 mysql connections every time. Not that it's a big deal.

Either way, as I mentioned, splitting the web and db stuff onto separate boxes should buy you some time. At least some time to plan and figure out what you want to do to scale. By then, if there isn't multiple db support "officially" in MU, you'll have a plan ready for action. Once you implement it though, you're probably "stuck" with it, and with making the edits necessary to make it work. Could be that your best option is including your own "config" file, to put in the correct values to the db class. Might at least make upgrading the core stuff a little easier.

Hey lunabyte, thanks heaps and heaps for this!

I'm currently on a dualP4 with 4Gig RAM and the following SQL config.. could you suggest any tweaks to assist in activating blogs while I go and buy another box and get the dbase split:

[mysqld]
set-variable = max_connections=5000
set-variable = key_buffer=50M
set-variable = myisam_sort_buffer_size=64M
set-variable = join_buffer=16M
set-variable = record_buffer=16M
set-variable = sort_buffer=32M
set-variable = table_cache=10240
set-variable = thread_cache_size=256
set-variable = wait_timeout=2
set-variable = connect_timeout=10
set-variable = max_allowed_packet=16M
set-variable = max_connect_errors=100
set-variable = max_user_connections=150
set-variable = query_cache_size=128M
datadir=/var/lib/mysql
#log = /var/log/mysql.log
socket=/var/lib/mysql/mysql.sock

[mysqld_safe]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
#
open_files_limit=819200

[mysqldump]
quick
set-variable = max_allowed_packet=16M

[myisamchk]
set-variable = key_buffer=64M
set-variable = sort_buffer=64M
set-variable = read_buffer=16M
set-variable = write_buffer=16M

I read somewhere that wordpress.com was on a very large number of databases. Here it is from the FAQ:

"Does it scale? (Also: The way you do your databases and tables doesn't scale!)

WordPress MU creates tables for each blog, which is the system we found worked best for plugin compatibility and scaling after lots of testing and trial and error. This takes advantage of existing OS-level and MySQL query caches and also makes it infinitely easier to segment user data, which is what all services that grow beyond a single box eventually have to do. We're practical folks, so we'll use whatever works best, and for the 400k and counting on WordPress.com, MU has been a champ."

This makes me think that the code is out there.

The code is definetely out there. The problem is that it's wp.com code which rarely ever makes it into the nightlies.

I'm thinking that it could be the:

set-variable = wait_timeout=2

setting that's stopping the activate script half way through?

Whaddya reckon?

OK, so I changed that to 10 and it did nothing :(

Any other ideas?

The error message I'm getting every time is that "Explanation: The request timed out before the page could be retrieved."

Essentially this is in calling /wp-activate.php which in turn includes wp-includes/registration.php

Surely there is something I can change in the config which will stop these requests from timing out so quickly?

Whatabout setting wait_timeout to something like 100?

Any thoughts?

I dropped by and created a blog (i'll delete it later) to see what exactly is going on. This is the most notable error i recieved:

WordPress database error: [Table 'edublogs_wpmu.wp_36973_posts' doesn't exist]

Basically the tables weren't created. I think you've just literally hit the MySQL limit.

Edit: nice redesign btw.

Yep - changing timeout or anything else doesn't help either.

Time to suspend new edublogs!

That could very well be possible Andrew. If that's the case, James, you're going to run into problems even with the database being on a separate box.

A good temporary fix might be to split based on 5k or 10k blog id's, and then use those db's on the new db server. That would at least keep the file limit in check, I'd think, if that is what's going on.

One recommendation I have though is to get as much beef in your db server as possible. 8G of RAM would be nice, or more if possible just to be safe. There is a kind of sweet spot with it, but running multiple large db's on it will probably need it at some point.

I'd also look into having a db strictly for site_1, and then splitting up the rest.

At least if you split it up at like the 5k or 10k level (by blog_id), it's not something that would take much to convert to another format. I can't remember off the top of my head how many different configs there would be just using the first 2 digits of a hashed value of the blog_id, but it might be enough. Bad thing about that is figuring what the blog_id hash is, then moving a blog to the right db.

Breaking it up into groups of say, 5k (by the blog_id), would at least allow for additional scaling at some point and breaking up your db's into other groups. Then you could at least do something like 'if blog_id <20k : use db_server1', and then from there it would look to see what db to call in the db_server1 group based on the blog_id. If that makes sense. Again, the downside is the gaps in the blog_id but there's downsides to everything you do at this point. Question is, which one works best for you? If you're starting from "scratch", then planning for scaling is easier in the long run. However since not many of us, or any of us at all, can simply implement some big scheme of scaling from the start since it would really not be feasible to start out creating 4096 databases (as an example), we have to look at other alternatives.

I think Quenting mentioned that he based his scaling off of the first letter of the username, which would give you 36 possible combinations (26 letters, plus 0-9), which might be an option as well. Problem with that would be if you got to a point where a certain letter was "so popular" that it hit the file limit for mysql like you seem to possibly be encountering now. Also, some letters/numbers may not be used much or at all.

This is definitely something I've put a lot of consideration into myself, and so far I think the best answer I have for myself is either do something based on the blog_id, or if any of my sites get to the point it's really needed I'll be able to afford the additional support from Automattic and I'd work with their kind experts on the issue.

If I was in a real pinch, and needed a quick and easy to undo or change fix, I'd use the blog_id and break it up into lots like I mentioned at the start of this long winded reply, with a separate db for the site_1 stuff. lol

I indeed split up not only the DB but the whole WPMU installs, and put them behind a reverse proxy, I've detailed this in another post.
I route the users via mod_rewrite in the proxy based on first letter, although nothing will prevent me from using 2 letters if one letter gets too big.
Each DB then has only a small part of the total users. Allows for easier updates, backups, and scaling across multiple servers. If you're at a critical point, you should probably just drop some of the not used blogs by looping through them and deleting the tables.

I have the same kind of problems happenning sometimes with emails being lost, mind you. I don't think that's a mysql issue, but I haven't figured yet where those emails got lost...