MySQL : One database for all users or multiple databases for each user

Question

I have an existing system ( on PHP, MYSQL on AWS EC2 & RDS accordingly). Current statistics for the MySQL (InnoDB) as database is 600 GB in total size (from the phymyadmin) and containing almost 150 tables which my program requires. Out of that two tables ( Order, Order_items ) containing more than 200GBs each.

While the database is growing, we are finding problems in few things. 1. Updates in any database schema to get for new feature updates takes too much amount of time. 2. Is it good to remain in the same kind of data structure for longer run, as expecting much larger data (getting in TB) as we are growing?

To solve problem no 1 and deal with problem no. 2, we are trying to find out solution hence posting a question here.

One of the possible ways we are thinking is as below.

We are thinking to separate Order, Order_items ( and such bulkier tables ) to store per User base Database rather than current structure of everything in a single database. So for every user, Order, Order_items will store in separate database and rest other tables in common databases. (one master database, and x Customer database for such entries.) hence data will be segregated.

Pitfalls in this mentioned as - Directory structure getting larger for storing per user wise DB.

Getting references which says it's not a good option when being smaller database. [ http://datacharmer.blogspot.com/2009/03/normalization-and-smoking.html ] [ max limit of databases that can be created in mysql? ] [https://forums.mysql.com/read.php?20,74692,74705#msg-74705]

Not able to get clear view as what to do when having a larger data in a one table.

Would it be a better option to separate out tables per user or any other options available to cater long term data growth ( Point no.1 & 2 mentioned above.)?

Answer 1

(In MySQL...) A DATABASE is more of a logical and administrative construct that anything to do with performance.

On disk, a database is a directory; a table in that database is 1-3 files in that directory. Operating systems start to choke when you have many thousands of files or subdirectories in a single directory. But it does not sound like you are getting to that scale.

Within MySQL, there is essentially no performance difference whether two tables (that are being JOINed, etc) are in the same database (directory) or separate.

As for 'security'... With a separate database for each user, it is easier to give different access permissions to different users. This, alone, could be a reason to have db-per-user.

A table's size can have an impact on performance, but we are usually talking about a billion rows or a terabyte. Even so, indexes, etc, may be able to keep the issues in check. A 200GB table should be looked at to see if you have the minimal sized datatypes, sufficient normalization, but not over-normalization. Appropriate indexes, especially composite indexes, not extra indexes, etc. (Start a new Question if you want to pursue that.)

ALTER TABLE affects the one table in question; it does not matter what the database structure is. BTW, MySQL 8.0 now has some 'instant' alters.

Will you outgrow your one server? What will you do then? Start another server, and migrate some users to it? In this case, it might be more convenient to have db-per-user. Then you just move the one db as a unit; all else is untouched. (That is, you are already "sharded" by user.)

Are there any table (aside from Grant tables) that are shared among users? With db-per-user, make that its own DB.

As for when to upgrade the schema, there are many techniques, none is perfect. I would look at each case individually. One technique for adding columns is to build a parallel table (vertical partitioning) to house the new columns. This is zero-downtime, but sloppy. The next time you can't use this trick, you could un-partition those columns.

150 tables seems like a lot for a single app.

200GB for a single table sounds like you are saving more stuff than you need to. Think about Summary Tables.

Or did I misread the question... Do you have

• One set of 150 tables with all the users in them?
• One database with multiple sets of 150 tables, one set per user?
• Many databases, one-per-user, with 150 table each?