8

MariaDB thread pool
MySQL thread pool
I've read official document of MySQL/MariaDB thread pool,but I'm still confused about it; As I understand, thread pool has two advantages:
1. limit too many connections, too many connection could lead to too many context switches, but it seems option "--max-connections" also could limit connections~
2.reuse connected old thread:when a new connection is coming, unused old thread could be reused,but it seems option "--thread_cache_size" also could reuse old thread

so,what's the advantage of thread pool?

7

The main advantage of the thread pool is that it generally limits the number of running operations to the value of thread_pool_size which is generally set to the number of CPUs on the server or how many MySQL will be using. It prevents context switching and contention issues with other internal structures. It is particularly helpful when there are many more queries to run than there are available CPUs.

Limiting connections with max-connections would limit the number of running queries by limiting the number of connections but any connections over that limit are denied with an error. When using the thread pool more connections are allowed but the queries are queued by the thread pool. Another thing to consider is that connections aren't always running queries so limiting the connections does not effectively limit running queries.

To the point about the thread cache, it does help when using thread per connection but the thread pool does its own thing with reusing threads.

max-connections can still be used to limit the connections but as more of a safeguard with a value of something like 10000 rather than a limiter for the amount of work for the server.

For example if you have 100 clients that want to run queries with a desired limit of 10 queries running concurrently, setting the connections limit to 10 will only allow 10 client to connect with the others being rejected with an error. There would be at most 10 queries running at the same time but it is likely that on average there will be less than 10 queries running at a time. Without limiting the connections, if all 100 of the clients wanted to run a query at the same time, there would be 100 threads executing at the same time which could cause contention issues leading to less throughput than with 10 connections.

If you were to use a thread pool size of 10 instead, all 100 clients would be able to connect and run their queries. If all of the clients try to run a query at the same time, the thread pool will queue the queries and generally execute 10 in parallel which can have a higher throughput than the 100 concurrent queries in the previous example.

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.