[wp-hackers] multiple databases vs different database prefixes

[Jeremy Clarke]

On Mon, Aug 16, 2010 at 12:04 AM, Ken Zaraque <kenzaraque at gmail.com> wrote:

>
> What I am hearing is "a single database instance with different prefixes
> per site" is a good way to go... A good example of how well it scales is
> wordpress.com . Am I right?
>

The fundamental question is whether you're better off with "one db, lots of
tables" or "lots of dbs with a few tables each".

AFAIK the answer there is that there isn't much of a difference. Assuming
all the sites will run on the same machine the effect will be largely the
same.

In my experience with MySQL the big problems arise within a given table. If
you have one table receiving too much traffic (esp. if its mixed
reads/writes) then you get lockups and issues. If all your sites share one
table, watch out for that one. But as long as the traffic is coming to
different tables MySQL doesn't seem to care if they are in the same database
or not, it sees one table-connection as one connection regardless of the db.

The real difference is in the backup/export etc. situation that you end up
with. For example stuff like PHPMyAdmin is likely to choke on your giant DB,
but if you ever need to backup all sites at once it will be a LOT easier if
they are all in one database.

P.S. With regard to your original question, wherein you are absolutely
required to have 300 sites and not use MS: I have to call into question the
decision-making that resulted in those requirements. Whatever you are doing,
you are likely doing it wrong if that is your solution.

-- 
Jeremy Clarke | http://jeremyclarke.org
Code and Design | http://globalvoicesonline.org