Question 81535 in Docker Question posted in
The official documentation can be found here.

Is it good practice to create separate Docker database container for each user

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

Tags:

3

Answers


  1. 0

    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.

    Login or Signup to reply.
  2. 0

    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
    Login or Signup to reply.
  3. 0

    I know this is sacrilegious, but for a table like that I like to use two tables. (The naughty part is the redundancy.)

    • History — details of all the transactions.
    • Current — the current balance

    You seem to have just History, but frequently need to compute the Current for a single user. If you maintain this as you go, it will run much faster.

    Further, I would do the following:

    • Provide Stored Procedure(s) for all actions. The typical action would be to add one row to History and update one row in Current.

    • Never UPDATE or DELETE rows in History. If a correction is needed, add another row with, say, a negative amount. (This, I think, is "proper" accounting practice anyway.)

    • Once you have made this design change, your question becomes moot. History won’t need to have frequent big scans.

    • Use InnoDB (not MyISAM).

    • Another thing that may be useful — change the main indexes on History from

      PRIMARY KEY(id),
INDEX(user_id)

    to

      PRIMARY KEY(user_id, id),  -- clusters a user's rows together
  INDEX(id)   -- this keeps AUTO_INCREMENT happy
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search