4

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.)?

Improve this question
6
  • How many users do you have ? Assuming yours is a B2C ecommerce platform, I am guessing you will have atleast 100K+ unique customers (from database size), and growing. How many such tables/databases you will create !! Commented Nov 24, 2018 at 9:36
  • We are creating a B2B platform. So can consider it as each users will have their own orders table. We are having 10K customers and growing. So in case of db, need to create it will be 10K db (with four tables each and each table consisting of thousands of rows.) Commented Nov 24, 2018 at 9:45
  • Creating even 10k tables is not a scaleable solution. You can rather look at partitioning: dev.mysql.com/doc/refman/8.0/en/partitioning.html Commented Nov 24, 2018 at 9:50
  • Yes. Did the same for making query result faster. Would Schema change at later stage would be efficient? Commented Nov 24, 2018 at 10:11
  • Your question already has the answers (links shared in your problem statement). It does not make any sense to create more tables/databases per user. There are other ways to achieve scaling in MySQL. Commented Nov 24, 2018 at 10:12

1 Answer 1

Reset to default
4

(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?
Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

Thanks @Rick James for the information. Below are the information as you asked.
There are no tables shared among users. It's like independent copy of each of users. We are thinking to have Many databases, one-per-user with 150 table each. As you mentioned, we plan to have some other servers and move users over there when getting outgrown on one server.
I would probably opt for "Many databases".
Sure. Thanks @Rick James for the advice.
How are you managing the database connections when all of your users have different databases to which your multiple app instances need to connect to make a query? @RickJames

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.