I am running a finance related web portal which involve millions of debit credit transactions in MySQL , getting the balance of a specific user at a certain limit i.e. with 3 million rows becomes slow.

Now I am thinking to create separate MySQL container for each user and record only the relevant user transactions in every container and I am sure It will be fast to calculate balance of any user. I have around 20 thousands of user and I wants to know is it practical to create separate MySQL container for each user ? or shall I go for any other approach. Thanks

CodePudding user response：

I would not recommend a separate MySQL instance per user.

I operated MySQL in docker containers at a past job. Even on very powerful servers, we could run only about 30 MySQL instances per server before running out of resources. Perhaps a few more if each instance is idle most of the time. Regardless, you'll need hundreds or thousands of servers to do what you're describing, and you'll need to keep adding servers as you get more users.

Have you considered how you will make reports if each user's data is in a different MySQL instance? It will be fine to make a report about any individual user, but you probably also need reports about aggregate financial activity across all the users. You cannot run a single query that spans MySQL instances, so you will have to do one query per instance and write custom code to combine the results.

You'll also have more work when you need to do backups, upgrades, schema changes, error monitoring, etc. Every one of these operations tasks will be multiplied by the number of instances.

You didn't describe how your data is organized or any of the specific queries you run, but there are techniques to optimize queries that don't require splitting the data into multiple MySQL instances. Techniques like indexing, caching, partitioning, or upgrading to a more powerful server. You should look into learning about those optimization techniques before you split up your data, because you'll just end up with thousands of little instances that are all poorly optimized.

CodePudding user response：

I have around 20 thousands of user and I wants to know is it practical to create separate MySQL container for each user

No, definitely not. While docker containers are relatively lightweight 20k of them is a lot which will require a lot of extra resources (memory, disk, CPU).

getting the balance of a specific user at a certain limit i.e. with 3 million rows becomes slow.

There are several things you can try to do.

1. First of all try to optimize the database/queries (can be combined with vertical scaling - by using more powerful server for the database)
2. Enable replication (if not already) and use secondary instances for read queries
3. Use partitioning and/or sharding