Partitioning mysql database into client

Question

I am working on a relatively small database. It has a total of 67 tables with just over a million records. This is about 254 MB. The application that works with it has been working for about 5 years, and the amount of use is doubled every year. This year we plan to triple, which will almost double the database in one season. My questions are: is it inconvenient to split a database into several databases. Say we have 300 clients, then 300 separate databases will be created containing 67 tables, but only the data related to this client. There are not many reasons for the data to be together, in addition, for internal statistics that can be executed on another server. We must not become more than 10,000 customers throughout our lives.

The problems that I see in this setting, when we need to make changes to the "main database" schema, it will be necessary to copy the change in all the "subordinate databases"

Also, when adding a new client, replication will be a problem.

The code-level application is heavily tuned for this type of settings.

Is there anything I don't see? Is this a terrible idea?

The database was created in a hurry (not by me), not thinking about the future, and now it is my responsibility.

There are many possibilities for normalization, field type auditing, SQL optimization, indexing and server configuration. Any feedback would be greatly appreciated.

Answer 1

You have your own hands with "normalization, field type audit, sql optimization, indexing and server configuration"

There is no good reason to break it down into 300 databases. And there are many good reasons not to do what you have formulated. As long as CustomerId clearly shares customer data through the database, you're fine.

So work on what you have and don't give yourself any more unnecessary work.

When the database size and low speed require it, move on to the real SQL platform.

Answer 2

You currently have a quarter concert. This year you plan to double (half the concert). Is this the year 1997? No, this is 2010, and people have gigabytes of data on their phones.

So the question is what are you trying to solve? It cannot be memory, because it is a trivial amount of data. If this is performance, I think splitting into multiple databases is likely to make things worse when you plan to use a server for each database. There is an argument for individual databases in terms of security, but there are different ways to solve these problems.

Are you having problems with your current environment? Or at least trends that suggest that you may have problems in twelve months? If not, then just sit tight. If so, articulate them clearly, and then find out how 300 databases will solve these problems and whether they will cost the inevitable tribulation. Then recalibrate this grief to account for 10,000 users, and ask the question again.

There may be some questions that are best answered by “ten thousand databases,” but not very many.

---

"Our largest customer adds about 12,000 records per year."

In other words, one entry every ten business minutes (during an eight-hour business day). It doesn’t seem like a lot of write work.

"The idea, rather, is that the client goes through all the data, he simply accesses their data."

But this is not much data, and, of course, nothing that a decent indexing strategy can fix.

I still don't understand if you have a real real problem now, or are you just thinking about something that might be a problem at some point in the future.

Answer 3

The question I have is how to access the database? Is there one load on the client? If this is the case, then when you save individual databases, you can buy some time while updating the application (since you need to update the database only when updating the application). If they are accessible by one installation of the application, keep them together.

But there are other considerations. You mentioned that the size today is 1 million lines of 256 MB each. This should be very easily accessible for the commodity server. So if you expect the worst case to grow 5 times every year, you say 5 million lines this year, 25, 125, third, 625 fourth and 3125 million fifth. Even 3 billion rows (depending on the exact use and types of queries) are not so difficult to process for MySQL (still in the upper range of the commodity server) ...

In addition, if you encounter problems, you can always partition each (or only the main tables) on the client key ... It is automatically managed by MySQL for you, so you do not have a nightmare for maintenance that allows you to manage them yourself ...

Answer 4

Change the current schema to allow multiple clients, and if as your performance worsens when you reach the nth client (and SELECT optimization does not help), you can add new servers. In our case, we divide the data into "sites", so one user cannot access data that is not on their site.

Answer 5

Let's take a look at SAP ERP. It could potentially contain thousands of customers and billions of recodrs. This applies to the power system. And all the tables in it (except for the system tables) have a "MANDT" field in which the client is indicated. Offset SAP usually works with ORACLE, but in this case it is not so sufficient due to some of the smal data. Thus, according to the successful history of SAP and the good opinion of MySQL as a good DBMS, I can conclude that you should not clone DB among clients. It will not bring big results.