Connection Thread Pool

Introduction

When there are a large number of concurrent database connections, a large number of resources are occupied, and the performance of the MySQL server deteriorates significantly. RDS for MySQL provides a connection thread pool that uses a few active threads to serve a large number of database connections. This decouples connections from execution and improves database performance in high-concurrency scenarios.

Characteristics

RDS for MySQL connection thread pool provides the following benefits:

• A large number of database connections can be processed, and resource contention and context switches are reduced.
• The number of concurrent transactions is limited. When the database load is heavy, transactions that are being executed are preferentially guaranteed.
• Connections are processed quickly to prevent thread exceptions.
• When a transaction is waiting for I/Os and locks, CPU resources and other connections are released.

Thread Pool Operations

• Querying thread pool parameters

  Run show variables to query thread pool parameters.

  show variables like 'threadpool%';
  +-----------------------------------+------------+
  | Variable_name                     | Value      |
  +-----------------------------------+------------+
  | threadpool_enabled                | ON         | 
  | threadpool_high_prio_tickets      | 4294967295 | 
  | threadpool_idle_timeout           | 60         |
  | threadpool_long_conn_time         | 2          |
  | threadpool_max_threads            | 100000     | 
  | threadpool_oversubscribe          | 3          |
  | threadpool_prio_kickup_timer      | 1000       | 
  | threadpool_rec_launch_time        | ON         | 
  | threadpool_size                   | 128        | 
  | threadpool_slow_conn_log          | ON         |
  | threadpool_slow_conn_log_interval | 30         |
  | threadpool_slow_launch_time       | 2          | 
  | threadpool_stall_limit            | 500        | 
  +-----------------------------------+------------+

  Table 1 Thread pool parameters

  Parameter	Description
  threadpool_enabled	Enables or disables thread pools.
  threadpool_high_prio_tickets	Number of tickets held by a high-priority thread.
  threadpool_idle_timeout	Idle time before a thread is destroyed, in seconds.
  threadpool_long_conn_time	If the login time exceeds the value of this parameter, the login information is printed in logs.
  threadpool_max_threads	Maximum number of threads that can be created in a thread pool.
  threadpool_oversubscribe	Maximum number of extra threads that can be created in a thread group.
  threadpool_prio_kickup_timer	Maximum duration (in milliseconds) in a low-priority queue.
  threadpool_rec_launch_time	Records the thread launch time.
  threadpool_size	Number of thread groups.
  threadpool_slow_conn_log	Whether to record slow logins in error logs.
  threadpool_slow_conn_log_interval	Recording frequency. After a slow login is recorded, the system does not record logins within this interval.
  threadpool_slow_launch_time	If the login or query time is greater than the value of this parameter, the value of threadpool_slow_launch_request in status increases by 1.
  threadpool_stall_limit	Interval for checking whether a thread group is busy.

  Table 2 Thread pool parameters that can be modified

  Parameter	Dynamic Parameter	Data Type	Value Range	Description
  threadpool_enabled	Yes	boolean	[ON,OFF]	ON: Enables the thread pool. OFF: Disables the thread pool.
  threadpool_oversubscribe	Yes	integer	[1,50]	Maximum number of extra threads that can be created in a thread group.
  threadpool_size	Yes	integer	[1,512]	Number of thread groups.

• Querying thread pool status

  Run show status to query the thread pool status.

  show status like 'threadpool%';
  +------------------------------------+------------+
  | Variable_name                      | Value      |
  +------------------------------------+------------+
  | Threadpool_active_connections      | 65         | 
  | Threadpool_active_threads          | 5          | 
  | Threadpool_avg_launch_time         | 0          | 
  | Threadpool_dump_threads            | 0          | 
  | Threadpool_idle_threads            | 63         |
  | Threadpool_running                 | ON         | 
  | Threadpool_slow_launch_request     | 0          | 
  | Threadpool_threads                 | 237        | 
  | Threadpool_threads_high_water_mark | 1075       | 
  | Threadpool_waiting_threads         | 57         | 
  | Threadpool_worst_launch_time       | 692548     | 
  +------------------------------------+------------+

  Table 3 Thread pool status

  Status	Description
  Threadpool_active_connections	Number of active connections in a thread pool
  Threadpool_active_threads	Number of active threads in a thread pool
  Threadpool_avg_launch_time	Average waiting time, in milliseconds
  Threadpool_dump_threads	Number of dump threads
  Threadpool_idle_thread	Number of idle threads in a thread pool
  Threadpool_running	Whether a thread pool is running
  Threadpool_slow_launch_request	Number of times that the slow_launch_request is exceeded
  Threadpool_threads	Total number of connections in a thread pool
  Threadpool_threads_high_water_mark	Number of historical high threads
  Threadpool_waiting_threads	Status of the waiting thread pool
  Threadpool_worst_launch_time	Worst launch time, in milliseconds

Performance Tests