MySQL Pluggable Thread Pool

Application scenario: In high-concurrency load scenarios, the performance of the MySQL database deteriorates sharply, or even the MySQL database cannot provide services. The MySQL pluggable thread pool feature can be used to ensure stable service processing performance.

Technical principle: In high-concurrency scenarios, there are many threads, and the CPU is consumed by resource contention and frequent switchovers. By enabling the thread pool, all tasks are queued for execution based on the system execution capability. The number of tasks processed by each CPU at a time is limited (2 to 5 for the best), which is to ensure stable service processing capabilities. In addition, the thread pool provides the thread-NUMA affinity function. This reduces the probability of cross-NUMA memory access in scenarios where one session is used to query specific data only and thereby improves query performance.

Performance metric:

In the OLTP TPC-C scenario, before enabling the thread pool, the performance of running 10,000 concurrent tasks is only about 10% of the optimal performance. After this feature is enabled, the performance is maintained at 85% of the optimal performance.

Advantages of a pluggable thread pool:

• Compiled SO dynamic libraries are used, without requiring source code compilation.
• Maintenance without shutdown and dynamic loading/unloading
• More status views available