CODEWITHSUNDEEP
mysqldatabase
santiagoIT
santiagoIT

Reputation: 9431

MySQL - using multiple DB's with identical schema instead of one large DB

I am helping a customer migrate a PHP/MySQL application to AWS. One issue we have encountered is that they have architected this app to use a huge number of databases. They create a new DB (with identical schema) for each user. They expect to have tens of thousands of users.

I don't know MySQL very well, but this setup does not seem at all good to me. My only guess is that the developers did this so they could avoid having tables with huge amounts of data. However I can only think of drawbacks (maintaining this system will be a nightmare, very difficult to extend, difficult to scale, etc..).

Anyhow, is this type of pattern commonly used within the MySQL community? What are the benefits, if any?

I am trying to convince them that they should re-architect the DB schema.

* [EDIT] *

In the meantime we know another drawback of this approach. We had originally intended to use Amazon RDS for data storage. However, RDS currently supports up to 30 databases per instance. So unfortunately RDS is now ruled out. The fact that RDS has this limit in place is already very telling, my interpretation is that having such a huge number of databases is not a common practice with MySQL.

Thanks!

Upvotes: 2

Views: 984

Answers (2)

Lee Dyche
Lee Dyche

Reputation: 129

This idea seems quite strange to me also! Databases are designed to handle large data sets after all! If there is genuine concern about the volume of data it is usually better practice to separate tables onto different databases - hosted on different physical servers as this allows you to spread the database level processes across hardware to boost performance

Also I don't know how they plan to host this application but many hosting providers are going to charge you per database instance!

Another problem this will give you is that it will make reporting more difficult - I wouldn't like to try including tables from 10,000 databases in a query!!

Upvotes: 0

havarc
havarc

Reputation: 934

This is one of most horrible ideas I've ever read (and I've read many). For once the amounts of databases do not scale as well as tables in databases and on the other it would be impossible to connect users to each other or at least share common attributes and options. It essentially defeats the purpose of the database itself.

My advise here is rather outside of original scope: Your intuition knows more than you think, listen to it more!

Upvotes: 3

Related Questions