PolarDB provides the Thread Pool feature to maximize the performance of PolarDB for MySQL. This feature separates threads from sessions. It allows sessions to share threads and complete more tasks with fewer threads.
Benefits
By default, each session creates a dedicated thread in MySQL. A large number of active sessions compete for resources and reduce the performance. The scheduling of a large number of threads and cache expiration can also cause performance decreases.
The thread pool of PolarDB grants different priorities to Structure Query Language (SQL) statements and is configured with a concurrency mechanism. PolarDB databases can ensure high performance in case of large connections and high concurrency by limiting the number of connections. The thread pool has the following benefits:
- When a large number of concurrent threads are running, the thread pool automatically limits the number of concurrent threads to a proper range. This reduces the workload of thread scheduling and avoids cache invalidation.
- When a large number of transactions are concurrently executed, the thread pool automatically grants different priorities to SQL statements and transactions. It also limits the number of concurrent statements and transactions separately. This mitigates resource competition.
- The thread pool grants higher priorities to SQL statements that are used to manage databases. This ensures that operations such as connection establishment, database management, and database monitoring can be performed as expected in scenarios with high loads.
- The thread pool grants lower priorities to complex SQL statements and limits the maximum number of concurrent statements. This prevents a large number of complex SQL statements from exhausting system resources, which causes the database service unavailable.
How to use the thread pool
You can specify the following parameters of the thread pool in the PolarDB console. For more information, see Specify cluster 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 instance after you enable or disable the thread pool
feature.
|
| loose_thread_pool_high_prio_mode | The high priority queue mode of the thread pool. Valid values:
Default value: transactions. Note This parameter is supported in only PolarDB for MySQL 5.6 and 5.7.
|
| loose_thread_pool_high_prio_tickets | The maximum number of tickets for the queue with a high priority.
Valid values: 0 to 4294967295. Default value: 4294967295. Note This parameter is supported in only PolarDB for MySQL 5.6 and 5.7.
|
| loose_thread_pool_idle_timeout | The time threshold for releasing idle threads from the thread pool. When this threshold
is reached, idle threads are released.
Valid values: 0 to 31536000. Unit: seconds. Default value: 60. Note This parameter is supported in only PolarDB for MySQL 5.6 and 5.7.
|
| loose_thread_pool_max_threads | The maximum number of active threads supported in the thread pool.
Valid values: 1 to 100000. Default value: 100000. Note This parameter is supported in only PolarDB for MySQL 5.6 and 5.7.
|
| loose_thread_pool_oversubscribe | The number of active threads supported in each group.
An active thread is a thread that is executing a SQL statement. The thread is not active if the statement is in the following status:
Valid values: 1 to 1000. Default value: 10. |
| loose_thread_pool_stall_limit | The time threshold to determine whether the thread pool is congested.
When the thread pool is congested, the system creates a new thread to execute SQL statements. Valid values: 1 to 18446744073709551615. Unit: milliseconds. Default value: 30. Note This parameter is supported in only PolarDB for MySQL 5.6 and 5.7.
|
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;The following example shows the output.
mysql>select * from information_schema.THREAD_POOL_STATUS;
+--------------+------------------------+-------------------------------+--------------------------------+-----------------------------+----------------------------+---------------------------+----------------------------+
| ID | THREAD_COUNT | ACTIVE_THREAD_COUNT | WAITING_THREAD_COUNT | DUMP_THREAD_COUNT | CONNECTION_COUNT | LOW_QUEUE_COUNT | HIGH_QUEUE_COUNT |
+--------------+------------------------+-------------------------------+--------------------------------+-----------------------------+----------------------------+---------------------------+----------------------------+
| 0 | 2 | 1 | 0 | 0 | 1 | 0 | 0 |
| 1 | 2 | 0 | 0 | 0 | 0 | 0 | 0 |
| 2 | 2 | 0 | 0 | 0 | 1 | 0 | 0 |
| 3 | 2 | 0 | 0 | 0 | 1 | 0 | 0 |
| 4 | 2 | 0 | 0 | 0 | 1 | 0 | 0 |
+--------------+------------------------+-------------------------------+--------------------------------+-----------------------------+----------------------------+---------------------------+----------------------------+
The number of returned rows: [5]. Elapsed time: 7 ms.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 and transactions to be committed in the thread pool. |
| DUMP_THREAD_COUNT | The number of persistent connections identified by DUMP 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 queue with a lower priority in the thread pool. |
| HIGH_QUEUE_COUNT | The number of pending requests in the queue with a higher priority in the thread pool. |
Use Sysbench to run a benchmark test
The following figures compare the database performance before and after the thread pool is enabled. The thread pool allows you to enhance the performance in high concurrency scenarios.
