Wordpress Multisite with thousands of blog

Question

I'm setting up a wordpress site where each user will need to have their own blog. My thought was it's the perfect use case of Wordpress Multisite. I've setup it with multisite using Sub-directory and everything works great.

But the problem I'm facing is with the numbers of tables in the database. Currently for each blog additional 12 tables are created which might increase a few more. So, if there is 1000 users on my site, there will be more than 12000 tables on my Database. It will grow beyond that as the site needs to be scaled for thousands of users.

My question is -

1. What is the performance penalty for having thousands of tables?
2. Is it going to scale with the growth of users/tables?
3. Is multisite the right tool for this job?

Answer 1

The number of tables is not an issue by itself. Actually separating the tables for each blog make each operation related to a specific blog more performant. Your scaling problem will come more likely from the big monolithic users tables.

Scaling issues will come from the amount of requests your DB will have to handle, not the actual organization of the data, and this is where the flexibility of having of having separate tables per blog shines as a simple technique you can implement is to move some of them to another DB (use blog number modulo 2 to decided which blog goes where between two DB servers)

When will you need to split the DB? IMO with proper caching and modern hardware you should probably handle more then thousands on one server, but this really depends on specific usage patterns.