PolarDB provides the thread pool feature to maximize the performance of PolarDB for MySQL. This feature separates threads from sessions, which 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, which reduces the performance of your cluster. The scheduling of a large number of threads and cache invalidation can also degrade cluster performance.

The thread pool of PolarDB grants different priorities to 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 executed concurrently, the thread pool automatically grants different priorities to SQL statements and transactions. It also limits the number of concurrent statements and transactions separately, which 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 to ensure that the database service is available.

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.

Command Description
loose_thread_pool_enabled Specifies whether to enable the thread pool feature. Valid values:
  • ON
  • OFF

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:
  • transactions: The SQL statements allowed in transactions are added to the queue with a higher priority and given tickets. The number of tickets equals the value of the thread_pool_high_prio_tickets parameter. SQL statements are queued until all tickets are exhausted.
  • statements: All SQL statements are added to the queue with a high priority.
  • none: All SQL statements are not added to the queue with a high priority.

Default value: transactions.

Note This parameter is supported in only PolarDB for MySQL 5.6 and 5.7 clusters.
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 clusters.
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 clusters.
loose_thread_pool_oversubscribe The number of active threads supported in each thread group.

An active thread is a thread that is executing a SQL statement. A thread is not active if the statement that the thread is executing is in the following status:

  • The SQL statement is pending for disk input/output (I/O).
  • The SQL statement is pending for transactions to be committed.

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 clusters.
loose_bypass_thread_pool_ips Client IP addresses that are not blocked by the thread pool. You can execute SQL statements to manage databases even if the thread pool is full.
Examples:
10.69.96.16,10.69.96.17
Note This parameter is supported in only PolarDB for MySQL 8.0.1.1.9 and later clusters.
loose_bypass_thread_pool_check_ignore_proxy Specifies whether to ignore the IP addresses of clients that are connected to the database through PolarProxy by using the loose_bypass_thread_pool_ips parameter. Valid values:
  • ON: indicates the IP addresses of clients that are connected to the database through PolarProxy are blocked by the thread pool even if these IP addresses are configured in the loose_bypass_thread_pool_ips parameter.
  • OFF: indicates the IP addresses of clients that are connected to the database through PolarProxy are not blocked by the thread pool if these IP addresses are configured in the loose_bypass_thread_pool_ips parameter.

Default value: ON.

Note
  • This parameter is supported in only PolarDB for MySQL 8.0.1.1.9 and later clusters.
  • This parameter cannot be modified. If you need to modify this parameter, submit a ticket to contact technical support.
loose_thread_pool_high_priority_users Database account with a high priority. After you configure this parameter, requests from these accounts will be placed in the high priority queues of the thread pool and processed first.
Examples:
root1, root2
Note
  • This parameter is supported in only PolarDB for MySQL 8.0.1.1.9 and later clusters.
  • After you configure this parameter, it takes effect only for new database connections.
  • We recommend that you do not configure too many high-priority accounts.
loose_thread_pool_mark_ddl_thread_timeout_sec The timeout threshold to determine whether new threads should be created to execute timed out Data Definition Language (DDL) statements in the thread pool. When the threshold is reached, the DDL statements are marked as timeout, and the system automatically creates a new thread to execute the statements.

Valid values: 0 to 864000.

Unit: seconds. Default value: 600.

Note This parameter is supported in only PolarDB for MySQL 8.0.1.1.9 and later clusters.
loose_thread_pool_mark_ddl_thread_timeout_immediately Specifies whether to immediately mark the DDL statements as timeout when the thread pool is under high load and the low priority queues are piled up. In this case, the system automatically creates a new thread to execute the statements. This parameter is applicable to scenarios where a large number of DDL statements are executed. Valid values:
  • ON
  • OFF

Default value: OFF

Note
  • This parameter is supported in only PolarDB for MySQL 8.0.1.1.9 and later clusters.
  • This parameter cannot be modified. If you need to modify this parameter, submit a ticket to contact technical support.

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 | 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 and transactions to be committed in the thread pool.
DUMP_THREAD_COUNT The number of persistent connections identified by DUMP in the thread pool.
SLOW_THREAD_TIMEOUT_COUNT The number of threads that are marked as timeout 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 perform a benchmark

The following figures compare the database performance before and after the thread pool is enabled. The thread pool helps enhance the performance of the database in high concurrency scenarios.

Figure 1. On-Line Transaction Processing (OLTP) without index updates
On-Line Transaction Processing (OLTP) without index updates
Figure 2. OLTP write-only
OLTP write-only
Figure 3. OLTP read-only
OLTP read-only
Figure 4. OLTP read and write
OLTP read and write