Understanding thread pool and max connection in mariadb

Question

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.

Answer 1

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).

Answer 2

• 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.

Answer 3

• max_connections - how many connections can exists at the same time.
• thread_pool_size - how many statements can be executed simultaneously, in the unreal situations, where each statement takes very short time. This is used for sizing, and roughly it means, how many CPUs do you want keep busy when queries are fast.

However, many more statement will be executed simultaneously, usually, and the number of threads can easily exceed thread_pool_size. If for example, all of your threads are doing SELECT SLEEP(1), or anything that waits, or executes for a long time, many-many more threads than the thread_pool_size will be created. If on the other hand, you're have hundreds of connections running the simplest SQL possible, e.gDO 1 in parallel, the number of threads won't exceed thread_pool_size by a large factor. The pool will grow dynamically as needed, and shrink, if there is nothing to do. If you want to monitor the number of threads, there is a status variable threadpool_threads (if you're using MariaDB's threadpool, or derived one, such as Percona's, is what you do, I believe)

There is another parameter called thread_pool_max_threads, which is the exact upper bound for threads executing simultaneously, and this has an unreal value 65K by default, on Unixes.

On Unixes, if you have many connections all doing long, CPU-intensive queries, not doing much disk IO, not blocking on locks, then the threadpool will slowly converge to have one thread per connection unless you limit thread_pool_max_threads. It will create threads at about (2*thread_pool_size) threads/second (this speed is configurable using thread_pool_stall_limit). If your queries do much disk IO, and are blocking on locks, it will converge faster.