CODEWITHSUNDEEP
databasemultiple-databases
ryy
ryy

Reputation: 317

What is more taxing on the server? The number of databases or the amount of data?

I have inherited a web application at work. It creates new a MySQL database every time an user/account is added. Each database usually goes up to 300mb but no more. At 271 databases running on AWS RDS the web application is running like molasses.

There is one table in each database that holds 90% of the data and it holds the same data in all databases. Therefore, I can take the data from this table and put in a separate database and have all the accounts connect to this database as well. This will shrink the size of each database considerably.

However, I worry that it will not speed up the server because there are simply too many databases on it. I am not DB admin and I apologize ahead of time if my train of thought does not make sense. But I am very willing to learn.

Under the circumstances, should I try to shrink the number of databases or should I try to shrink the size of each database? Would shrinking the size of each database really help?

Upvotes: 1

Views: 135

Answers (2)

paxdiablo
paxdiablo

Reputation: 881913

You should (seriously) look into reducing the number of database to one.

The concept of a single database per user is one of the more ... interesting ... ones I've heard - that's Pax showing an unusual amount of tact there :-)

Short of certain legislative requirements to separate different users from each other, there is really no valid reason to do this. Especially if you ever expect your database to scale.

300M times 300 accounts is about 90G and that's still a relatively small database in the scheme of things.

As you've stated, a lot of that information is duplicated so it won't get anywhere near that when you combine. If 90% of that 300M is shared, you'll end up with something like 30M multiplied by 300 accounts plus 270M shared, or under 10G.

I'm pretty certain some of our configuration tables are bigger than that :-)

Upvotes: 0

David Schwartz
David Schwartz

Reputation: 182809

I doubt the number of databases is the problem directly. But that's such an obvious sign of bad design that I bet there are issues elsewhere. For example, many optimizations are possible within a table but not across tables. So if you're constantly manipulating information of the same type but held in different tables, it's going to be absurdly slow.

Upvotes: 2

Related Questions