ApsaraDB for RDS provides the thread pool feature for higher performance. This feature separates threads from sessions, allowing sessions to share threads and complete more tasks with less threads.

Benefits

By default, each session creates an exclusive thread in MySQL. When a large amount of sessions are active, they will compete for resources and the cache may fail, which results in low performance.

The thread pool of ApsaraDB for RDS grants different priorities to SQL statements and is configured with a concurrency mechanism. By limiting the number of connections, the ApsaraDB for RDS databases can guarantee high performance in multi-connection and high-concurrency scenarios. The benefits of the thread pool are as follows:

  • When a large amount of threads are running concurrently, the thread pool automatically limits the number of concurrent threads to a range that guarantees performance, so that the workload of thread scheduling can be reduced and cache failure can be avoided.
  • When a large amount of transactions are executed concurrently, the thread pool automatically grants different priorities to SQL statements and transactions to control the number of concurrent statements and transactions, so that the resource competition can be reduced.
  • The thread pool grants SQL statements that manage databases a higher priority and ensures these statements are executed preferentially. Even during high system loads, operations such as creating connections, managing, and monitoring data can be stably executed.
  • The thread pool grants complicated SQL statements that query information from databases a lower priority and limits the maximum number of concurrent statements. This prevents excessively complicated SQL statements from exhausting system resources and making the database service unavailable.

Prerequisites

The DB engine version is MySQL 5.7/8.0.

Use the thread pool

You can modify the following parameters corresponding to the thread pool feature in the ApsaraDB for RDS console. For more information, see Reconfigure parameters for an RDS for MySQL instance.

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

Default value: OFF

thread_pool_size The number of groups in the thread pool. Default value: 4. Threads in the thread pool are evenly divided into several groups and managed by group.
thread_pool_oversubscribe The number of active threads allowed in a group. Default value: 16. An active thread is a thread that is executing SQL statements. The thread is not active if the statement is in either of the following states:
  • The SQL statement is waiting for the disk I/O.
  • The SQL statement is waiting for a transaction to be committed.

Query the status of the thread pool

You can use the following statement to query the status of the thread pool.
show status like "thread_pool%";

Example:

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)            

The following table describes the parameters.

Parameter Description
thread_pool_active_threads The number of active threads in the thread pool.
thread_pool_big_threads The number of threads that are executing complicated queries in the thread pool. Complicated queries include subqueries, aggregate functions, and clauses such as GROUP BY or LIMIT.
thread_pool_dml_threads The number of threads that execute DML statements.
thread_pool_idle_threads The number of idle threads in the thread pool.
thread_pool_qry_threads The number of threads that are executing simple queries in the thread pool.
thread_pool_total_threads The total number of threads in the thread pool.
thread_pool_trx_threads The number of threads that are executing transactions in the thread pool.
thread_pool_wait_threads The number of threads that are waiting for disk I/O and transactions to be committed in the thread pool.

Sysbench tests

The following figures compare the performance when the thread pool is enabled or disabled. The thread pool greatly enhances the performance in high-concurrency scenarios.

oltp_update_non_indexoltp_write_onlyoltp_read_onlyoltp_read_writeoltp_point_select