Threadpool in MariaDB details and usage _ MySQL

This article mainly introduces threadpool in MariaDB and how to use it. threadpool is a good solution for high-concurrency environments. For more information, see What is Thread pool?

MySQL creates a thread for each connection to execute the statement. In this way, a new thread will be created for each new connection, which puts a lot of pressure on MySQL itself. Threadpool is a model that provides a thread proxy to execute each connection statement. MySQL maintains the total number of threads that may be accepted internally, reducing the stress on CPU switching and other aspects of too many threads.

Advantages of using Threadpool

1. the thread proxy model can be used to create a thread in advance and maintain it using the threadpool technology of the operating system when the number of connections is known to be controllable. this reduces a lot of overhead.

2. use the thread management of the operating system as much as possible to minimize the thread management overhead. 3. Exploitation
Thread_pool_max_threads controls resource usage

Under what circumstances does the Thread Pool play a relatively small role:

1. too high concurrency and long statement operations. For example, the total number of internal threadpools is 10, and 1000 requests are concurrently sent outside. Currently, all these 10 internal threads are competing for use, which is also a heavy overhead. Similarly, when threadpool is increased, innodb's internal concurrency will also be limited.

2. burst connections. In this case, threadpool has a certain effect, but the effect is not obvious. To prevent a large number of connections. You can increase the value of thread_pool_min_threads and thread_pool_idle_timeout to maintain a large threadpool.

3. in a highly concurrent environment, simple queries may also slow down. Although select 1; such a query should be completed very quickly. However, under the threadpool, you also need to queue up and wait for the thread available from theadpool to be executed.

Usage:

Currently, threadpool is supported by MariaDB, Percona, and a charging function of MySQL. Enable configuration.

In my. cnf

The code is as follows:

[Mysqld]
# Thread pool
Thread_handling = pool-of-threads