To maximize the performance of PolarDB for MySQL, PolarDB provides the thread pool feature. The thread pool separates threads and sessions, allows sessions to share threads, and completes active tasks with a few threads.

Benefits

By default, each session creates an exclusive thread in MySQL. A large number of active sessions compete for resources. 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. By limiting the number of connections, PolarDB databases can ensure high performance in case of large connections and high concurrency. The following sections list the benefits of the thread pool:

  • When a large number of threads are running concurrently, the thread pool automatically limits the number of concurrent threads within a proper range. Then, the workload of thread scheduling can be reduced and cache invalidation can be avoided.
  • When a large number of transactions are executed concurrently, the thread pool grants different priorities to SQL statements and transactions to control the number of concurrent statements and transactions. This allows you to reduce the competition for resources.
  • The thread pool grants higher priorities to SQL statements that are used to manage databases. This ensures that operations such as connection creation, database management, and database monitoring can be performed as expected.
  • The thread pool grants lower priorities to complex SQL statements and limits the maximum number of concurrencies. This allows you to prevent too many complex SQL statements from exhausting system resources, which makes 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 Set cluster parameters.

Parameter 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 that have transactions enabled are added to the queue with a higher priority and given tickets. The number of tickets equal 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 higher priority.
  • none: All SQL statements are not added to the high priority queue.

Default value: transactions.

Note Only PolarDB for MySQL 5.6 and 5.7 supports this parameter.
loose_thread_pool_high_prio_tickets The maximum umber of tickets for the high priority queue.

Valid values: 0 to 4294967295.

Default value: 4294967295.

Note Only PolarDB for MySQL 5.6 and 5.7 supports this parameter.
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 Only PolarDB for MySQL 5.6 and 5.7 supports this parameter.
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 Only PolarDB for MySQL 5.6 and 5.7 supports this parameter.
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:

  • 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 Only PolarDB for MySQL 5.6 and 5.7 supports this parameter.

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 lists 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.

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/write
OLTP read/write