MySQL thread pool summary

MySQL thread pool summary

Thread pool is a core function of MySQL5.6. For server applications, high concurrency requests are always a topic that cannot be bypassed, whether it is web Application Service or DB service. When there are a large number of concurrent requests for access, resources must be created and released continuously, resulting in low resource utilization and reduced service quality. A thread pool is a common technology. By creating a certain number of threads in advance, the thread pool allocates a thread to provide services when requests arrive. After the request ends, this thread serves other requests again. In this way, frequent creation and release of threads and memory objects are avoided, server concurrency is reduced, context switching and resource competition are reduced, and resource utilization efficiency is improved. The thread pool of all services is essentially a bit to improve resource utilization efficiency, and the implementation method is also roughly the same. This article describes the implementation principle of the MySQL thread pool.

Before MySQL5.6 appeared, MySQL handled the Connection by One-Connection-Per-Thread, that is, for each database Connection, MySQL-Server will create an independent Thread service. After the request ends, destroys a thread. If you have another connection request, create another connection and then destroy it. This method causes frequent thread creation and release in the case of high concurrency. Of course, with thread-cache, We Can cache the thread for the next use to avoid frequent creation and release, but it cannot solve the problem of high connections. The One-Connection-Per-Thread mode requires the creation of the same number of service threads as the number of connections increases. High-concurrency threads mean high memory consumption and more context switches (lower cpu cache hit rate) and more resource competition, resulting in service jitter. Compared with the One-Thread-Per-Connection mode, a Thread corresponds to a Connection. In the Thread-Pool implementation mode, the minimum unit of Thread processing is statement (statement ), A thread can process multiple connection requests. In this way, when the hardware resources are fully utilized (the thread pool size is reasonably set), server Jitter Caused by an instant increase in the number of connections can be avoided.

Scheduling implementation

MySQL-Server supports three Connection management methods, including No-Threads, One-Thread-Per-Connection, and Pool-Threads. No-Threads indicates that the main Thread is used to process the Connection, and No additional Threads are created. This method is mainly used for debugging. One-Thread-Per-Connection is the most commonly used method before the Thread pool appears, create a thread service for each connection; Pool-Threads is the thread Pool method discussed in this article. MySQL-Server supports three connection management methods at the same time through a set of function pointers. For specific methods, the function pointer is set to a specific callback function, and the connection management mode is controlled by the thread_handling parameter, the Code is as follows:

If (thread_handling <= SCHEDULER_ONE_THREAD_PER_CONNECTION)
One_thread_per_connection_scheduler (thread_scheduler,
& Max_connections,
& Connection_count );
Else if (thread_handling = SCHEDULER_NO_THREADS)
One_thread_scheduler (thread_scheduler );
Else
Pool_of_threads_scheduler (thread_scheduler, & max_connections, & connection_count );

Connection Management Process

1. Use poll to listen to mysql port connection requests
2. After receiving the connection, call the accept interface to create a communication socket
3. initialize thd instances and vio objects.
4. initialize the sched function pointer of the thd instance according to the thread_handling method.
5. Call the add_connection function specified by scheduler to create a connection.

The following code demonstrates the implementation of the template callback function by the scheduler_functions template and thread pool. This is the core of multiple connection management.

Struct scheduler_functions
{
Uint max_threads;

Uint * connection_count;
 
Ulong * max_connections;
 
Bool (* init) (void );
 
Bool (* init_new_connection_thread) (void );

Void (* add_connection) (THD * thd );

Void (* thd_wait_begin) (THD * thd, int wait_type );

Void (* thd_wait_end) (THD * thd );

Void (* post_kill_notification) (THD * thd );

Bool (* end_thread) (THD * thd, bool cache_thread );

Void (* end) (void );
};

 

Static scheduler_functions tp_scheduler_functions =

{
0, // max_threads
NULL,
NULL,
Tp_init, // init
NULL, // init_new_connection_thread
Tp_add_connection, // add_connection
Tp_wait_begin, // thd_wait_begin
Tp_wait_end, // thd_wait_end
Tp_post_kill_notification, // post_kill_notification
NULL, // end_thread
Tp_end // end

};

Thread Pool Parameters
1. thread_handling: indicates the thread pool model.
2. thread_pool_size: number of groups in the thread pool. It is generally set to the current number of CPU cores. Ideally, a group has an active working thread to make full use of the CPU.
3. thread_pool_stall_limit: used by the timer thread to regularly check whether the group is "stuck". The parameter indicates the interval of detection.
4. thread_pool_idle_timeout: when a worker is idle for a period of time, it automatically exits. This ensures that the worker thread in the thread pool remains relatively low when the request is met.
5. thread_pool_oversubscribe: this parameter is used to control the number of threads with "overclock" on the CPU core. This parameter value does not include the listen thread count.
6. threadpool_high_prio_mode: indicates the mode of the priority queue.

Thread Pool implementation

The preceding section describes how Mysql-Server manages connections. This section describes the implementation framework of the thread pool and key interfaces. 1

Each green box represents a group, and the number of groups is determined by the thread_pool_size parameter. Each group contains a priority queue and a common queue, including a listener thread and several worker threads. The listener thread and worker thread can be dynamically converted. The number of worker threads is determined by the workload, it is also affected by the thread_pool_oversubscribe settings. In addition, the entire thread pool has a timer thread monitoring group to prevent the group from being "stuck ".

Key Interfaces

1. tp_add_connection [process new connection]

1) create a connection object

2) determine the group to which the connection is allocated based on thread_id % group_count.

3) Put the connection into the queue of the corresponding group

4) if the number of active threads is 0, a working thread is created.

2. worker_main [working thread]

1) Call get_event to obtain the request

2) If a request exists, handle_event is called for processing.

3) Otherwise, it indicates that there is no request in the queue and the exit ends.

3. get_event [GET request]

1) Get a connection request

2) If yes, return immediately and end

3) if there is no listener in the group at this time, the thread is converted to the listener thread, blocking the wait

4) if listener exists, the thread is added to the waiting queue header.

5) the specified Thread sleep time (thread_pool_idle_timeout)

6) if the thread is still not woken up and timed out, the thread ends and exits.

7) Otherwise, a connection request in the queue will arrive and jump to 1.

Note: before obtaining a connection request, the system checks whether the number of active threads has exceeded.

Thread_pool_oversubscribe + 1. If it is exceeded, the thread enters the sleep state.

4. handle_event [processing requests]

1) check whether the connection is verified. If not, perform logon verification.

2) Associate thd instance information

3) Obtain network data packets and analyze requests

4) Call the do_command function to process requests cyclically.

5) obtain the socket handle of the thd instance and determine whether the handle is in the epoll listener list.

6) If no, call epoll_ctl for association.

7) End

5. listener [Listening thread]

1) Call epoll_wait to listen on the socket associated with the group, blocking wait

2) If the request arrives, it will be restored from blocking.

3) based on the priority of the connection, determine whether to put it into a common queue or a priority queue.

4) check whether the tasks in the queue are empty.

5) if the queue is empty, the listener is converted to the worker thread.

6) If there is no active thread in the group, wake up a thread.

Note: epoll_wait listens to all connected sockets in the group, and then listens to the connections

Push the request to the queue. The worker thread obtains the task from the queue and then executes the task.

6. timer_thread [Monitoring thread]

1) if there is no listener thread and there is no io_event event recently

2) create a wake-up or a working thread.

3) if the group has not processed the request in the recent period and there are requests in the queue

4) indicates that the group has been stall, then the thread is awakened or created

Note: timer is used to prevent the group from being in the stall state.

7. tp_wait_begin [enters the waiting state process]

1) Reduce the number of active threads by 1, and wait for the number of threads to increase by 1

2) If the number of active threads is 0 and the task queue is not empty or there is no listening thread

3) Wake up or create a thread

Note: waiting_threads the thread here is a idle thread, not a waiting thread, so it is null

A idle thread is a thread that can process tasks at any time, while a waiting thread is waiting for a lock or io operation.

Tasks cannot be processed.

8. tp_wait_end [end wait state process]

1) set the waiting status of connection to false.

2) Increase the number of active threads by 1, and decrease the number of waiting threads by 1

Thread Pool and Connection Pool

The connection pool is usually implemented on the Client side, which means that an application (user side) creates a certain number of connections in advance and uses these connections to serve all DB requests of the Client. If the number of idle connections is smaller than the number of DB requests at a certain time point, requests need to be queued and waiting for idle connections to be processed. You can reuse connections through the connection pool to avoid frequent connection creation and release, thus reducing the average response time of requests. when requests are busy, the impact of applications on the database can be buffered through request queuing. The thread pool is implemented on the server side. By creating a certain number of thread service DB requests, the thread service is connected to one-conection-per-thread, the minimum unit of the thread pool service is a statement, that is, a thread can correspond to multiple active connections. Through the thread pool, the number of service threads on the server can be controlled within a certain range, reducing the competition for system resources and the consumption of thread context switching, it also avoids high concurrency problems caused by high connections. The connection pool and thread pool complement each other. The connection pool can reduce the creation and release of connections, increase the average request response time, and control the number of DB connections of an application, however, the number of connections of the entire application cluster cannot be controlled, resulting in a high number of connections. The thread pool can effectively cope with the high number of connections and ensure the server can provide stable services. As shown in figure 2, each web-server maintains three connection pools. Each connection in the connection pool is not an exclusive worker of db-server, but may be shared with other connections. Assume that db-server has only three groups, each group has only one worker, and each worker processes two connection requests.

Figure 2 (framework of connection pool and thread pool)

Thread Pool Optimization

1. priority queue

Because a group processes multiple connections at the same time, multiple connections are not equal. For example, some connections send requests for the first time, while some connections have enabled transactions and hold some lock resources. In order to reduce lock resource contention, the latter should obviously take precedence over the former to release lock resources as soon as possible. Therefore, you can add a priority queue in the group to put the requests initiated by connections with locks in the priority queue. The worker thread first obtains the tasks from the priority queue for execution.

2. Big Query Processing

In a scenario where connections in a group are large queries, the number of worker threads in the group will soon reach the value set by the thread_pool_oversubscribe parameter. For subsequent connection requests, the response is not timely (no more connections are available), and stall occurs in the group. According to the previous analysis, the timer thread regularly checks this situation and creates a new worker thread to process requests. If a long query comes from a service request, all groups are faced with this problem. In this case, the host may be overloaded, resulting in hang. In this case, the thread pool itself is powerless, because the source may be bad SQL concurrency, or the SQL does not follow the execution plan, through other methods, for example, SQL high/low water level throttling or SQL filtering can be used for emergency handling. However, another case is the dump task. Many downstream databases rely on the original data of the database. Generally, the data is pulled to the downstream using the dump command. This dump task usually takes a long time, so it can be considered as a large query. If a dump task is concentrated in a group and other normal business requests cannot respond immediately, this is intolerable because the database has no pressure at this time, but the thread pool policy is adopted, the request response is not timely. To solve this problem, we will not include the threads processing dump tasks in the group into the cumulative value of thread_pool_oversubscribe to avoid the above problem.

This article permanently updates the link address: