Updated on 2024-07-02 GMT+08:00

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

Table 4 Performance tests of different threads

Model

Threads

Thread Pool Enabled

QPS

Latency (ms)

oltp_update_non_index

32

Yes

5932.47

7.84

oltp_update_non_index

64

Yes

10074.11

9.39

oltp_update_non_index

128

Yes

18079.61

10.65

oltp_update_non_index

256

Yes

27439.38

14.46

oltp_update_non_index

512

Yes

33007.96

28.16

oltp_update_non_index

1024

Yes

30282.13

51.94

oltp_update_non_index

2048

Yes

29836.86

95.81