0

I need some help in understanding the concept of thread pool and max connection in mariadb.

Says max_connection=500, thread_handling=pool-of-threads, thread_pool_size=50.

Assuming my database has no connection initially, now there are 1000 clients connecting to my database to run queries somewhat at the same time. (assuming each query takes some times)

So in my understanding, the first 500 clients can successfully connect to the database, and the first 50 clients out of these 500 can run queries immediately, while the rest of 450 will be queued in the thread pool.

And the later 500 clients out of 1000 will prompt too many connections error and not allowed to connect.

Is my understanding correct? Thanks in advance.

2

If this is a web application, there is probably no problem. A web page connects to the database, does its thing, then disconnects.

If you have a thousand users running web pages, you might not actually have more than a couple of dozen actually running at the same time. They will (or at least should) come and go so fast that you don't need a big max_connections.

After you have the system running, check Max_used_connections to see the maximum of how many are actually running "simultaneously".

"Connection pooling" is a minor optimization. MySQL/MariaDB can create (and tear down) a connection so rapidly that "pooling" does not add much performance benefit.

Usually, the problem with a thousand users comes from some query that is slower than it needs to be. This is usually fixed via a better (often 'composite') index and/or reformulating the slow query.

I suggest not worrying about pooling until you have some evidence that there is a real need.

max_connections deals with how many connections; thread_pool_size deals with the number of connections that can be using a CPU core at the same time.

There is very little problem in having a thousand connections sitting idle. (They will occupy some RAM, so it is not without some impact.)

On the other hand, if a thousand connections are running some long-running query, they will be stumbling over each other. It will feel like the system is "frozen", and you will be strongly tempted to reboot. All the queries will eventually finish, but meanwhile "latency" will be terrible. That is, all the web pages (or whatever) will be very slow.

thread_pool_size seems to have no direct relationship to max_connections. Setting thread_pool_size to the number of CPU cores is probably optimal. It is better to get the running queries finished in a hurry before starting new queries. This will decrease the feeling of the system being "frozen".

Is your traffic "bursty"? Or "steady"? Are users "waiting" for the results (eg, waiting for their web page to finish displaying)?

In most situations, focusing on speeding up the queries is the best way to solve all of the above.

Two more settings are important. MySQL/MariaDB's back_log is a cheap way to hang on to connections waiting to get into the max_connections list. The other is in the web server -- the number of "children" or "threads" that it is willing to spawn. Again, if this number is "too high", the system will simply stumble over itself, seeming "frozen". The web server probably also has a waiting room (a la back_log).

1
  • max_connections - how many clients can be connected at the same time. After that new connections will not be accepted - those will get errors*
  • thread_pool_size - how many statements can be executed simultaneously

So with your numbers here's what's going to happen:

  1. 500 of clients will get connected, another 500 will get errors (some of them may get queued up, see at the bottom)
  2. Out of those 500 that connected, if you trigger SQL commands in all of them simultaneously - only 50 will do work. The rest 450 will wait.

*Actually, you will be able to go a little higher - those connection requests will be queued up by OS until DB accepts it (if some existing connection closes) or timeout happens. Go even higher and only then you'll start seeing errors.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.