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.
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.
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.
|thread_pool_enabled||Specifies whether to enable the thread pool feature. Valid values:
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:
Query the status of the thread pool
show status like "thread_pool%";
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.
|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.|
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.