Overall Thread Pool Framework

When the thread pool connector module is used, as shown in Figure 1, it takes over the connection establishment and scheduling. With a dynamically scalable and multi-group thread pool, the server can deliver optimal performance even when there are a large number of client connections. In the thread pool solution, the listener thread in each group listens to network tasks and allocates triggered tasks to a high-priority or low-priority queue. Then idle worker threads obtain tasks from the queue based on priorities. Each CPU can process a limited number of tasks at the same time. Generally, two to five tasks can be processed concurrently while maintaining stable service performance.

As shown in Figure 2, a thread pool consists of multiple thread groups and one timer thread. The number of thread groups is the number of logical CPU cores on the server by default. You can modify the number of thread groups in the configuration file or by running related commands. For details, see thread_pool_size. User connections are allocated to the corresponding thread group in polling mode. All query requests in the connections are processed by the bound thread group. When the client creates a connection and sends SQL statements through the connection, the thread group allocates a worker process to the connection to execute the SQL statements. After the SQL statements are executed, the thread group reclaims the worker thread. The thread pool retains the number of worker threads within a proper range by a policy to ensure high service performance.

Each thread group in a thread pool contains:

• A pollfd, which is the poll descriptor returned by epoll_create.
• Zero or one listener thread. epoll_wait waits for network readable events.
• A common queue that stores connection objects (including TPC connection information and SQL execution context status) with network readable events to be processed by worker threads.
• A priority queue that stores connection objects that have network readable events and are in the middle of a transaction. Such connections will be processed by the worker threads first.
• Zero or more worker threads that obtain connection objects with network readable events, process login verification of connections, receive and execute SQL statements, and return results. If there is no listener thread in a thread group, the first idle worker thread that enters the sleep state becomes a listener thread.
• A waiting queue. When a worker thread has no task to process, it enters the awaiting sleep state and is placed in the waiting queue. After the thread is woken up by an external signal or is automatically woken up due to waiting timeout, the thread status is re-marked as active to process tasks or the thread just exits.
• A mutex that protects resources in a thread group from being simultaneously accessed by multiple threads.

All thread groups share one timer thread. A timer thread detects whether a task in a thread group is suspended, that is, whether no new task is generated in a period of time or no task is consumed when the task queue is not empty.

A thread pool supports the following functions:

• The number of thread groups can be system-defined or user-defined.
• For better performance, priority queues and common queues process transactions, lock connections, and common query statement connections separately. For details, see thread_pool_high_prio_mode and thread_pool_high_prio_tickets.
• The number of worker threads is dynamically scaled to ensure that the number of running threads is within a proper range for efficient processing.
• Thread pool lockups or starvation is prevented.
• Four status information tables are added to information_schema to monitor the thread pool status in real time.
• Performance stability and transaction priority are optimized.

For details about the function configuration, see Parameters.