Is 5000 tables too many?

This is now a central question in our work to configure Multi Site functionality in Drupal. If each website has its own set of tables, and we are looking at 40 websites, at least, then that's anywhere from 2500 to 5000 tables in a single database.  Is that going to be a problem?

A related question needs to be asked whether or not Organic Groups can do what we need without needing to use og_sites, but that is for a separate forum topic. 

To help with this problem, take a look at what is said here about the max number of tables in MySQL: 

http://www.devshed.com/c/a/MySQL/MySQL-Optimization-part-2/4/

If we end up with more than 2000 tables then the there is a small
overhead for each request as it must find the table in the larger
table_cache.

A minimal installation of Drupal for one website is 40 tables (we
should see how small that number can be) -- this is set to grow as each
website enables additional modules...  If we have 60 table for each of
our existing 37 websites, then it means we are starting with around
2220 tables in one database.  In 5 years I would like to see that
number increase to 100 websites, meaning 6000 tables.  It would be
crucial that we have a table_cache size that is right for the number of
tables at peak loads, but that number can not be too large or the whole
system will be slowed. We would have to 'flush_tables' from the cache
regularly to keep it from loading too many after connections have been
closed.  

What is the verdict?
 

Re: Is 5000 tables too many?

HI John, Personally I think, that is madness. That is completely unmanageable from the practical point of view. I have doubts about the performance of such a overblown database. Imagine the backup process for that database. Even if we stick with idea, to run all our sites using only one database, at the beginning that might work well enough. but I'm sure as time go on, and a number of our sites grow up, we come up to a performance impact. We will try to look for a way to separate that database somehow then. Besides, our database will contain a bunch of identical tables ( with different content and name). Mostly the Drupal's system tables. I think multiple database is better. Now, another question rises up. How to organize these database in order to meet our requirements? First we should clearly specify all requirements, i think. Afterwards, we will come back to this discussion. Do you agree ?

Pavel, thanks this is

Pavel, thanks this is helpful. The BIG question I have is: how do we proceed now? You told me yesterday that you didn't find my Requirements document useful, but here you are saying that we "should clearly specify all requirements". Where and how do we start doing this? Please can you guide John and me now so that we can start serious work on documenting our requirements in the format you consider best?

Too Many.

Yes, I entirely agree, although I do not see any advantage with having 50+ databases to manage in a few years from now. I question it because we are talking here of migrating less than 20MB of data (excluding image db)and less than 6,000 rows in any table. In 5 years time (2012) we would be optimistic to see that rise to 100MB with a max of 100,000 records in any table. And so computing power will very likely keep well ahead of our needs. Using a single database should therefore be adequate for the foreseeable future. In this way, our operation is relatively small. It is only seemingly complex because the functionality we need comes through a mashup of modules. I am tending to support the idea that everything we need can be achieved through the Organic Groups (OG) family of modules (single site with multiple themes and domains). But as you suggest, let's focus on the requirements first and not prejudice our thinking too much.
Ok?

Drupal's multi-site capabilities

Unfortunately, I can't find specs about the total amount of users, this multi-site system will have to deal with.

Anyway, the calculation of 40 x 60 tables looks wrong to me. In turn, this would mean, that you could setup 40 separate Drupal sites, no need for sharing the same database. I guess that's not what you guys have in mind.

If your requirements force you to split all of these sites, then you really should consider to use separate databases, too.

However, from above OG suggestion, I guess that your requirements would allow to at least share users, role, access, authmap, sequences, and sessions tables. That would centralize the user system. By further centralizing the contents and distributing them through realms, you would be left with separated cache tables, approx. 6 to 8 for each site, which means 8 x 40 = 320 in total. Since those tables are rather meaningless and do not need to be backup, they could be moved into a separate database [small core hack required].

regards,
Daniel Kudwien

PS: You really should disable the TinyMCE input format for anonymous users.