CODEWITHSUNDEEP
mysqldatabasedatabase-designdatabase-schema
karto
karto

Reputation: 3668

Database design: how to effectively manage about 4000 databases with mysql

It sounds crazy, but i started a data intensive project[collecting online store inventories] which later grew to be very big. I currently have about 2000 users and each user has about 100 tables. So in essence, i created the system so that each user had his own mysql database and hosted it on a dedicated server. The problem is, the server becomes very slow and breaks due to the pressure and connections. Is there a tool i can use to optimize the db? or i should redesign to only 1 database, which will mean redesigning the whole system? I need an advice and help

Upvotes: 0

Views: 369

Answers (2)

Larry Lustig
Larry Lustig

Reputation: 51008

Nine times out of ten, when someone structures an application database this way (segmenting identical data into different databases, or even into different tables) it's a mistake based on an unnecessary attempt to pre-optimize the system.

But without more information we cannot tell whether:

  1. This is one one of the nine times it's a mistake, or the tenth time, when it's an appropriate design.

  2. Whether the number of connections is what's causing the performance problems you see (which would be solved by switching to a single database) or something else.

Upvotes: 1

Widor
Widor

Reputation: 13285

4000 databases for one system?! Wowzer, did you invent Google?

I'd definitely say that you need to redesign that setup - unless your 'system' is actually database hosting and each user has paid for a private db, of course.

Nothing wrong with having multiple discrete databases, but 2-per-user is the wrong approach.

The 'right' approach will depend entirely on what your system is meant to do.

You mention everyone has a dedicated server too - this should prevent contention issues for other users. Are you sure it's not shared hosting?

Upvotes: 1

Related Questions