[wp-hackers] Max number of WP sites per MySQL DB

[Otto]

On Tue, May 11, 2010 at 12:46 PM, John Bloch <jbloch at olympianetworks.com> wrote:
> I don't think there's any theoretical limit. As long as all the installs have different table prefixes and the DB has enough space, there is no limit placed by WordPress (I could be wrong on this, so please correct me if I'm wrong).

WordPress has no limit, so the limit would depend on the number of
tables that can be stored in the MySQL database. WordPress creates 11
tables by default, so find however many tables MySQL can store, divide
by 11, and you have your answer.

Unfortunately, this depends on a number of factors. MySQL has
different ways of storing tables.

MyISAM databases store tables as sets of 2 files each, so the
filesystem becomes your limitation there. And that differs depending
on the filesystem being used as well as the platform you're using
(Linux, Windows, etc). The theoretical limits on filesystems and such
varies greatly, but on all of them you'd probably run into major
performance issues at, say, 30,000 tables or so. Which means things
start bogging down at around 2700 installations.

InnoDB on the other hand uses a single file to store tables. Its
maximum tablespace size is 4 billion database pages (2^32 -1). Since a
table takes at least 1 page, the max in theory is 4 billion. Divide by
11 to get about 390 million installations possible. In theory. In
practice, you'd probably hit the same limits.

A rule of thumb I'd use would be to limit the number of installations
per database to 1000 or so. However, you can create multiple databases
too, so there's always that.

You might ask the WordPress.com folks. They're running one big
installation, they can probably tell you how they've subdivided to
help performance.

-Otto