By default, MySQL creates one dedicated thread per session. Under high concurrency, this leads to excessive thread scheduling overhead and frequent cache invalidation, both of which degrade performance. The Thread Pool feature decouples threads from sessions so that multiple sessions share a smaller pool of threads, keeping scheduling overhead low and cache hit rates high. It works best for OLTP workloads with short, CPU-bound queries and a high number of concurrent connections.
Prerequisites
Before you begin, ensure that you have:
An RDS instance running MySQL 5.6, 5.7, or 8.0
How it works
The thread pool addresses the one-thread-per-session bottleneck with two mechanisms:
Priority-based scheduling: Data management SQL statements (connection setup, monitoring, administrative commands) receive high priority and run ahead of lower-priority queries. Complicated queries—those with subqueries, aggregate functions, or GROUP BY and LIMIT clauses—receive low priority, preventing them from starving other workloads.
Concurrency control: Threads are divided into groups. Each group caps the number of simultaneously active threads, keeping scheduling overhead within a manageable range and preserving cache effectiveness.
Together, these mechanisms keep the database stable under bursts of concurrent connections.
Configure the thread pool
Configure thread pool parameters in the ApsaraDB for RDS console. For details, see Modify the parameters of an ApsaraDB RDS for MySQL instance.
| Parameter | Default | Description |
|---|---|---|
thread_pool_enabled | ON | Enables or disables the thread pool. Valid values: ON, OFF. The deprecated thread_handling parameter has no effect. Enabling or disabling this parameter does not require an instance restart. |
thread_pool_size | 4 | Number of thread groups. Threads in the thread pool are evenly divided into groups and managed by group. |
thread_pool_oversubscribe | 32 | Maximum number of active threads per group. A thread is active while executing a SQL statement. Threads waiting for disk I/O or a transaction commit are inactive and do not count toward this limit. |
Monitor thread pool activity
Run the following command to check the current state of the thread pool:
SHOW STATUS LIKE 'thread_pool%';Example output:
mysql> SHOW STATUS LIKE 'thread_pool%';
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| thread_pool_active_threads | 1 |
| thread_pool_big_threads | 0 |
| thread_pool_dml_threads | 0 |
| thread_pool_idle_threads | 19 |
| thread_pool_qry_threads | 0 |
| thread_pool_total_threads | 20 |
| thread_pool_trx_threads | 0 |
| thread_pool_wait_threads | 0 |
+----------------------------+-------+
8 rows in set (0.00 sec)| Status variable | Description |
|---|---|
thread_pool_active_threads | Threads currently executing a SQL statement. |
thread_pool_big_threads | Threads executing complicated SQL statements (those with subqueries, aggregate functions, or GROUP BY and LIMIT clauses). |
thread_pool_dml_threads | Threads executing data manipulation language (DML) statements. |
thread_pool_idle_threads | Threads with no work assigned. |
thread_pool_qry_threads | Threads executing simple SQL statements. |
thread_pool_total_threads | Total threads in the pool. |
thread_pool_trx_threads | Threads executing transactions. |
thread_pool_wait_threads | Threads waiting for disk I/O or for a transaction to commit. |
Performance benchmarks
The following SysBench test results show throughput with the thread pool enabled versus disabled across several concurrent-session scenarios. The thread pool consistently delivers higher throughput as the number of concurrent sessions increases.
