WordPress MU

Hi
Maybe I'm getting ahead of myself here, but I thought I might as well start planning for these things now rather than later.

If I have a single database server, would I see much (any) gains (speed, latency) by using multiple databases (on the same server) from the beginning? Or would it not make any difference at all (other than the obvious getting around the 32k limit).

Also, from reading around the Wordpress.com setup information posted in numerous places, they seem to have a Read database (for simplicity lets singularise this) from which they run the select queries, and a write database into which they write posts, etc. I assume these are then kept in sync via replication.

If I wanted to consider this approach (again single server to start with) would I be seeing huge performance drops because of the complexity initially?

I'm just thinking out loud here really, as I want to get my initial setup to be easy to scale quickly so would rather spend the time and effort at the beginning rather than later on when I also have to worry about downtime and users data.

Thanks
Barry

Initially, there isn't any real benefit. However, as the site grows there are obvious gains from this. Some people will tell you otherwise, but I've seen firsthand, and many others have as well.

The biggest part is figuring out how many databases you need to break it into.
Speaking of strictly user tables, it's essentially a choice between 16, 256, and 4096 user db's.

This is where you will have to be realistic about how many users your site will draw, and push the hopes and dreams aside for a bit.

If you think you're only going to draw maybe 2500, go with 16. More? Go with 256. 4096 is an option, but unless you're going to draw hundreds of thousands, don't worry about it.

Early on, you won't see a benefit in performance. However, it's much easier to implement from the start than to have to move stuff later on.

As a note, since it's been referenced many times before, Andrew has a premium plugin on his site that handles all of this for you over on the http://premium.wpmudev.org site.

It works really well, and a lot of people are using it with great success.

+1. Do note that there's nothing stopping you from setting up the multiple databases now. It saves you from headaches down the road for when you have to use them.

Andrew talked about having separate Read databases but I believe the decision was that it wouldn't be worth the effort.

Pretty much. For "most" sites, the benefits aren't worth the expense. Unless you're huge, of course. And by huge I mean EDUblogs, WP.com, etc.

Something where there are always posts being created, a lot of write queries, etc. there would be obvious benefits, but you're talking tens of thousands of posts a day.

I think I will be using that premium service soon...I have over 2500 blogs and I think its time to get several databases for the future.

On a small install, multiple db are easier to manage. Seriously, if you can, do it.

One of the sites I take care of is built off of MU (we use it to handle multiple blogs and the shared user base, but the public can't sign up for a blog). We have been using HyperDB for about a year and it runs great. We get about 100,000 page views a day and with the contributor base, we average 2-3 posts being worked on, published an hour. We also get in the neighborhood of 1,500-2000 comments per day across the 3 (current) blogs.

For this we are running a Master-slave-slave set up. We have another site coming off the database, which is running standard WP and gets about 250,000 page views per day, with the same amount of commenting. That site will soon be moving over to MU for the same basic setup.

Basically we have had very little problems. Every once in awhile replication might stop working, but that is more a fault in MySQL and/or the hardware than it is HyperDB.

Something else we did on the current MU site. We started with wp-cache and object cache. Well since it was a very established site, and we went to registered comments, wp-cache couldn't keep up (6,000 registered users, of which a majority go primarily to the main blog). We dropped wp-cache, then as things kept growing, the file based object cache started having trouble keeping up. We disabled that. With all the extra stuff we have going on now, we average about 35 queries per page view, and HyperDB still kicks butt on it. We do have a static cache for non-logged in users running on the front page and post pages, to help out. We actually had one day a couple of weeks ago where we got over 20,000 hits in 1 hour, and everything hummed along perfectly.

Here's HyperDB. It's pretty straightforward to set up from the WP point. Replication isn't too bad to set up. There are tons of references on the web about it.

http://codex.wordpress.org/HyperDB

I haven't yet had a need to look at HyperDB yet, but my little site is starting to see some growth now so I should start looking into it a bit more.

For those that have implemented HyperDB - how did you go about migrating from a single database to multiple databases? Is it just a case of dropping the HyperDB config in place, or do you need to prep your existing and new databases?

Any help would be appreciated.

HyperDB. Nice.

One thing you might like to consider before playing with the database set up is try adding another box with MemcacheD.

http://www.danga.com/memcached/

Livejournal, Slashdot, Facebook, Wikipedia and other big sites all use this and I understand that Wordpress.com use this.

I talked to a guy from Automattic at a Wordcamp and he recommended it.

Amazon's Ec2 is a really great and relatively inexpensive platform. It now includes persistant storage and permanent IP addresses - the two things that were holding it back. It's easy to add extra servers and take them offline as needed.

lunabyte: thanks, I did have a quick look at the WPMUDEV site as I had seen the multi-databases "plugin" mentioned in a few other threads.

One of my main requirements (assuming I do take the multi-database approach from the outset - which is looking increasingly likely) is that I need to be able to specify (or ensure) that a certain group of sites/blogs/domains are held in a particular database as the information held across these particular blogs needs to be consolidated for a parent site/portal.

Is this something that the WPMUDEV multi-database system can handle? as other than splitting across 16 databases, the description is a bit sparse.

intoxination: HyperDB sounds interesting, I'll have to dig into the code and have a good look around. Did you have many problems setting it up? From looking at the archives, the mailing list seems to be a bit quiet recently.

Other than that I guess it will be a roll-your-own solution :) I do love a challenge :)

"One of my main requirements (assuming I do take the multi-database approach from the outset - which is looking increasingly likely) is that I need to be able to specify (or ensure) that a certain group of sites/blogs/domains are held in a particular database as the information held across these particular blogs needs to be consolidated for a parent site/portal."

Absolutely. There's a feature with that plugin that allows for "VIP" databases, where you can put certain blogs in a private db, vice being thrown in with the general population. I'd also recommend having your main blog in its own db as well.

I'm a bit concerned about all these issues.
- Which cache solution should I use for my wpmu install ? What's the best for wpmu ? Hyperdb / memcached / the core wp-cache / super-cache from donncha ?
- From which moment do we need to think about this issue of multiple database ?

Regards

It really is a matter of opinion. The sites that are large enough already have the multiple database solution in place and the ones that are close (2000+ blogs) are also figuring these things out. I am nowhere near the size of some of these sites and just put the solution in because it doesn't hurt to have it already if you anticipate growth. I have a testbed trying out Hyperdb and use a database solution from premium.wpmudev.org for my main install.

In terms of caching, most have the object (internal) cache turned on and use super-cache on the busy sites only as most users don't like having their sidebars not updating ;)

I guess when you talk about timing to do something, you will know as you start to outgrow hardware. There are many sites that still use 1 database for their sites and that is fine. Whether you do it with 100 blogs or 10,000 is the debate :)

Trent

1. can someone point me to a resource on how to set up my WP MU on separate databases? I just set it up last week, but I think I'll take it down and start all over again.

Before I read this post, I just took a looked at the database content from the few blogs already created and I started wondering what would happen when I get to like 500 users.

2. How easy would it be to migrate to a multiple database installation from a single one in the future.

Thanks in advance.

freshestnoob:
Have a look here for my solution:
http://mu.wordpress.org/forums/topic.php?id=8029

Or here for the one recommended by lunabyte:
http://premium.wpmudev.org/project/Multiple-Databases

It's easier to start form scratch with multiple databases than it is to migrate later on, as depending on the method used to pick the database for each blog, you would have to calculate which db you need to transfer data to and then manually (or write a script) to transfer it across, test it and remove the original tables from what was the single db.

I'll note that for the preimium multi-db plugin, the transfer script it included.
I moved over a few hundred blogs, and it helped.

will the premium multi-db plugin transfer data without errors during conversion from single database to multiple databases?

Mine did.