PolarDB for MySQL provides the thread pool feature to optimize database performance and resource utilization. This feature decouples threads from sessions. Instead of one thread per session, this feature uses a pool of threads to execute tasks across active sessions.
Benefits
In the traditional MySQL architecture, a thread is created and maintained for each session. When a large number of sessions are present, high resource competition, excessive thread scheduling, and cache invalidation may occur, which significantly degrades cluster performance.
The thread pool of PolarDB allows different types of SQL operations to have different priorities, implements concurrency control, and limits the number of connections to the optimal number. This ensures that the PolarDB cluster maintains high performance even under high connection concurrency. The thread pool feature brings the following benefits:
When a large number of threads concurrently run, the thread pool automatically adjusts the number of concurrent threads within a reasonable range. This helps avoid excessive thread scheduling and reduces the risk of cache invalidation.
When a large number of tasks are concurrently executed, the thread pool separately prioritizes and manages statements and transactions and controls the number of concurrent statements and transactions, which mitigates resource competition and optimizes system performance.
The thread pool grants higher priorities to administration-related SQL statements. This ensures that operations such as connection establishment, database management, and database monitoring are reliably executed even under high system load.
The thread pool grants lower priorities to complex SQL statements and limits the maximum currency of these statements. This prevents excessive consumption of system resources by complex queries and ensures that the entire database system remains operational.
How to use the thread pool
You can configure the thread pool feature in the PolarDB console by using the parameters described in the following table. For more information, see Configure cluster and node parameters.
Parameter | Description |
loose_thread_pool_enabled | Specifies whether to enable the thread pool feature. Valid values:
Default value: OFF Note You do not need to restart the cluster after you enable or disable the thread pool feature. |
loose_thread_pool_size | The number of thread groups in the thread pool. The valid values vary based on the number of CPU cores in the primary node. Valid values: DBNodeClassCPU to DBNodeClassCPU × 10. Default value: DBNodeClassCPU × 2 Note
Examples:
|
loose_thread_pool_high_prio_mode | The type of SQL queries that you want to add to the high-priority queue. Valid values:
Default value: transactions. Note This parameter is supported only for PolarDB for MySQL 5.6 and 5.7 clusters. |
loose_thread_pool_high_prio_tickets | The maximum number of tickets assigned to the high-priority queue. Valid values: 0 to 4294967295. Default value: 4294967295. Note This parameter is supported only for PolarDB for MySQL 5.6 and 5.7 clusters. |
loose_thread_pool_idle_timeout | The timeout threshold after which idle threads are released. Valid values: 0 to 31536000. Default value: 60 Unit: seconds. Note This parameter is supported only for PolarDB for MySQL 5.6 and 5.7 clusters. |
loose_thread_pool_oversubscribe | The maximum number of active threads allowed in each thread group. An active thread is a thread that is executing a SQL statement. A thread is not active if the statement executed by the thread is in the following status:
Valid values: 1 to 1000. Default value: 10. |
loose_thread_pool_stall_limit | The timeout threshold after which the thread pool enters the congested state. When the thread pool enters the congested state, the system creates new threads to execute SQL statements. Valid values: 1 to 18446744073709551615. Default value: 5. Unit: milliseconds. Note For a cluster whose Database Engine is MySQL 5.6, the default value is 30 milliseconds. |
loose_bypass_thread_pool_ips | Client IP addresses that are allowed to bypass the limits imposed by the thread pool. The clients with these IP addresses can still execute management SQL statements even if the thread pool is fully occupied. Example: Note This parameter is supported only for PolarDB for MySQL 8.0.1.1.19 and later clusters. |
loose_bypass_thread_pool_check_ignore_proxy | Specifies whether to ignore the specification of the IP addresses in the
Default value: ON. Note This parameter is supported only for PolarDB for MySQL 8.0.1.1.19 and later clusters. |
loose_thread_pool_high_priority_users | The high-priority database accounts. Requests from these accounts are placed in the high-priority queue of the thread pool and processed first. Example: Note
|
loose_thread_pool_mark_ddl_thread_timeout_sec | The timeout threshold for DDL operations in the thread pool. When the threshold is reached, the DDL operation is labeled as timed out, and the system automatically creates a new thread to execute the operation. Valid values: 0 to 864000. Default value: 600. Unit: seconds. Note This parameter is supported only for PolarDB for MySQL 8.0.1.1.19 and later clusters. |
loose_thread_pool_mark_ddl_thread_timeout_immediately | Specifies whether to immediately label the DDL statements as timed out and create new threads to process the statements when the thread pool is under high load and the low priority queue are piled up. This parameter is applicable to scenarios in which a large number of DDL statements need to be executed. Valid values:
Default value: OFF Note This parameter is supported only for PolarDB for MySQL 8.0.1.1.19 and later clusters. |
Query the status of the thread pool
You can execute the following statement to query the status of the thread pool.
select * from information_schema.THREAD_POOL_STATUS;Sample output:
mysql> select * from information_schema.THREAD_POOL_STATUS;
+----+--------------+---------------------+----------------------+-------------------+---------------------------+------------------+-----------------+------------------+
| ID | THREAD_COUNT | ACTIVE_THREAD_COUNT | WAITING_THREAD_COUNT | DUMP_THREAD_COUNT | SLOW_THREAD_TIMEOUT_COUNT | CONNECTION_COUNT | LOW_QUEUE_COUNT | HIGH_QUEUE_COUNT |
+----+--------------+---------------------+----------------------+-------------------+---------------------------+------------------+-----------------+------------------+
| 0 | 2 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| 1 | 2 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| 2 | 2 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| 3 | 2 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| 4 | 2 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| 5 | 2 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| 6 | 2 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| 7 | 2 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| 8 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| 9 | 2 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| 10 | 2 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| 11 | 2 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| 12 | 2 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| 13 | 2 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| 14 | 2 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| 15 | 2 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| 16 | 2 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| 17 | 2 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| 18 | 2 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| 19 | 2 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| 20 | 2 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| 21 | 2 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| 22 | 2 | 1 | 0 | 0 | 0 | 1 | 0 | 0 |
| 23 | 2 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| 24 | 2 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| 25 | 2 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| 26 | 2 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| 27 | 2 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| 28 | 2 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| 29 | 2 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| 30 | 2 | 0 | 0 | 0 | 0 | 1 | 0 | 0 |
| 31 | 2 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| 32 | 2 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| 33 | 2 | 0 | 0 | 0 | 0 | 1 | 0 | 0 |
| 34 | 2 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| 35 | 2 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| 36 | 2 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| 37 | 2 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| 38 | 2 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| 39 | 2 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| 40 | 2 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| 41 | 2 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| 42 | 2 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| 43 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| 44 | 2 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| 45 | 2 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| 46 | 2 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| 47 | 3 | 1 | 0 | 0 | 0 | 1 | 0 | 0 |
| 48 | 2 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| 49 | 3 | 1 | 0 | 0 | 0 | 1 | 0 | 0 |
| 50 | 2 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| 51 | 2 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| 52 | 2 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| 53 | 2 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| 54 | 2 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| 55 | 2 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| 56 | 2 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| 57 | 2 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| 58 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| 59 | 2 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| 60 | 2 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| 61 | 2 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| 62 | 2 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| 63 | 2 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
+----+--------------+---------------------+----------------------+-------------------+---------------------------+------------------+-----------------+------------------+
64 rows in set (0.00 sec)The following table describes the parameters.
Parameter | Description |
ID | The ID of the thread pool. |
THREAD_COUNT | The number of threads in the thread pool. |
ACTIVE_THREAD_COUNT | The number of active threads in the thread pool. |
WAITING_THREAD_COUNT | The number of threads that are pending for disk I/O operations to complete and pending for transactions to be committed in the thread pool. |
DUMP_THREAD_COUNT | The number of persistent connections of the DUMP class in the thread pool. |
SLOW_THREAD_TIMEOUT_COUNT | The number of threads that are labeled as timed out in the thread pool. |
CONNECTION_COUNT | The number of user connections established in the thread pool. |
LOW_QUEUE_COUNT | The number of pending requests in the lower-priority queue in the thread pool. |
HIGH_QUEUE_COUNT | The number of pending requests in the high-priority queue in the thread pool. |
Sysbench tests
This section compares the database performance when the thread pool feature is enabled and disabled. The test results indicate that the database delivers significantly higher performance under high concurrency conditions when the thread pool feature is enabled.
Figure 1. Performance of the online transaction processing (OLTP) system to handle update operations on tables that do not have indexes
Figure 2. Performance of the OLTP system to handle write-only operations
Figure 3. Performance of the OLTP system to handle read-only operations
Figure 4. Performance of the OLTP system to handle read-write operations