Shulou(Shulou.com)05/31 Report--

This article shows you how to analyze the internal implementation mechanism of MySQL thread pool, the content is concise and easy to understand, it will definitely brighten your eyes. I hope you can get something through the detailed introduction of this article.

Abstract

In MySQL, thread pool refers to a mechanism used to manage threads that handle MySQL client connection tasks. The percona version used in our factory already integrates thread pool, and you only need to open it with the following parameters.

Thread_handling=pool-of-threads

Based on the introduction of the core parameters of MySQL thread pool, the internal implementation mechanism of thread pool is further introduced.

Thread pool guide

Introduction to Thread Pool

Before we move on to MySQL thread pooling, we need to understand why the introduction of thread pooling can help MySQL improve performance, and what is the role of thread pooling beyond performance? If the thread is regarded as the system resource, then the thread pool is essentially the management of the system resource. for the operating system, the creation and destruction of threads consume system resources. Frequent creation and destruction of threads will inevitably bring unnecessary waste of resources to the system, especially in the case of high load, which seriously affects the resource use efficiency of the system, thus affecting the performance and throughput of the system. On the other hand, too many threads will cause overload consumption of system resources and bring about relatively frequent context switching between threads. System resources are valuable, and I think performance is closely related to resource utilization:

Homogeneity of resource utilization and performance

They tend to develop in one direction, and good resource utilization can usually bring better performance. on the one hand, thread pool technology can reduce the overhead of thread repeated creation and destruction, so as to make better use of the thread resources that have been created. on the other hand, it can also control the creation of threads and the load of the system, and some scenarios protect the system.

How to understand MySQL thread pool

It is a good way to learn what parameters MySQL has, and to deeply understand the meaning of each parameter and how these parameters affect MySQL. In addition, it is helpful to better understand MySQL thread pool technology on the basis of understanding the basic implementation principle of MySQL thread pool and thinking about the deficiency of MySQL thread pool and the areas that can be improved.

Thread pool core parameters

The MySQL thread pool opens some parameters to users, and users can modify these parameters to affect the behavior of the thread pool. These core parameters are described below.

Thread_pool_size

This parameter refers to the thread group size, which defaults to the number of CPU cores. Thread groups are generated according to this number when the thread pool is initialized, and each thread group initializes a poolfd handle.

Thread_pool_stall_limit

The interval between Timer Thread iterations. The default is 500ms.

Thread_pool_oversubscribe

Used to calculate whether the thread group is too active or too busy, that is, the load of the system, to decide whether a new worker thread is created and whether the task is processed in a certain scenario. This value defaults to 3.

Thread_pool_max_threads

The number of threads allowed in the thread pool. The default is 10000.

Thread_pool_idle_timeout

The idle time of the worker thread. If the worker thread is idle beyond this number, it will exit. The default value is 60 seconds.

Thread_pool_high_prio_mode

This parameter can be used to control the use of task queues and can take three values:

Transactions

Statements

None

When the value is statements, the thread group only uses the priority queue, and when the value is none, only the normal queue is used. When the value is transactions, it takes effect with the parameter thread_pool_high_prio_tickets, which is used to control the number of times tasks are placed in the priority queue.

Thread_pool_high_prio_tickets

When thread_pool_high_prio_mode=transactions, each connected task is placed in the priority queue up to thread_pool_high_prio_tickets times, and each time it is decremented until it is placed in the normal queue when it is less than or equal to 0. The default value is 4294967295.

Insider implementation of MySQL thread pool

Overall thread pool architecture

Unlike the thread pool of JAVA, there is a concept of thread group in Java thread pool while MySQL thread pool has a thread group concept. The internal level of thread group is worker thread. First, take a look at the general structure of MySQL thread pool:

Thread pool architecture

The above figure can roughly see the internal structure of the thread pool, the thread group is a larger component that we focus on, the coordination of each component within the thread group constitutes a thread group, and each thread group works well to form a thread pool. Within the thread pool, what I think is worth knowing mainly includes the following aspects:

Thread group

Worker thread

Check Stall mechanism

Task queue

Listener thread

The aspects listed above will be introduced later.

Thread group

During initialization, the MySQL thread pool sets the thread_pool_size according to the number of CPU cores of the host, which is the number of thread groups in the thread pool. After initialization, each thread group is assigned a network special handle associated with it through the underlying IO library. Through this handle, the IO library can listen for socket handle-ready IO tasks bound to it. The structure of the thread group is defined as follows:

Struct thread_group_t {mysql_mutex_t mutex; connection_queue_t queue;// low priority task queue connection_queue_t high_prio_queue;// high priority task queue worker_list_t waiting_threads; / / represents that when the current thread does not have a task, it enters the waiting queue worker_thread_t * listener;// reads the network task thread pthread_attr_t * pthread_attr; int pollfd / / Special handle the number of threads in the int thread_count;// thread group int active_thread_count;// the currently active thread int connection_count;// the connection int waiting_thread_count;// assigned to the current thread group represents that the current thread is waiting while executing the command / * Stats for the deadlock detection timer routine.*/ int io_event_count / / number of tasks to be processed. Get the number of network tasks removed from the queue by int queue_event_count;// from the handle, which means that network tasks are processed. The last time ulonglong last_thread_creation_time;// created a worker thread, int shutdown_pipe [2]; bool shutdown; bool stalled;} MY_ALIGNED.

The thread pool consists of multiple thread groups, and the details of the thread pool are basically within the thread group.

Worker thread

There are 0 or more threads in the thread group, which is somewhat different from Netty. There are fixed threads in Netty to train IO events in rotation, and the worker thread is only responsible for handling IO tasks. In MySQL thread pool, listener is only a role, and the role of each thread can be listener or worker. When the worker thread is listener, it is responsible for reading ready IO tasks from poolfd, and when it is in the worker role, it is responsible for handling these IO tasks. We need to distinguish between the following states of the worker thread:

Active state: when the worker thread is in the state of processing the task and is not blocked, this means that the worker thread will consume CPU and increase the load on the system. If the worker thread sets itself to listener, it does not count as the number of active thread states in the thread group.

Idle state: the state of being idle because there is no task to process.

Wait status: if the worker thread needs to wait due to IO, lock, condition, sleep, etc., during the execution of the command, the thread pool will be notified and record these worker threads as waiting state.

In a thread group, the count of threads has the following relationship:

Thread_count = active_thread_count + waiting_thread_count + waiting_threads.length + listener.length

Thread_count represents the number of buses in the thread group, active_thread_count represents the number of threads that are currently working and not blocked, waiting_thread_count represents the number of blocked threads in the course of the worker task, and waiting_threads represents the list of idle threads.

In the MySQL thread pool, the number of threads of busy in the thread group is the sum of active_thread_count and waiting_thread_count, because none of these threads can handle new tasks at this time, so they are considered busy. If the number of threads in the busy state is greater than a certain value, the thread group is too too many active by the task, which will be used to determine whether the normal priority task can be processed in a timely manner. This value is defined as:

Thread_pool_oversubscribe + 1

The default value is 4. If the number of active_thread_count is greater than or equal to a certain value (the same algorithm is 4), the thread group is considered to be too active (too busy), which means that the CPU load may be too heavy. This indicator is used to determine whether the thread group continues to perform normal priority tasks. The logical summary above is as follows:

Under normal working conditions, when the worker thread retrieves tasks, if the thread group is too active (too many active), even if there is a task worker thread will not execute, if not too busy (too busy) will consider high-priority tasks, for low-priority tasks will only be executed when the thread group is not too busy (too busy).

Pay attention to the above conditions, so the thread pool has a certain protective effect on the system load, so the question is, if there are some time-consuming tasks (such as time-consuming queries), will the later tasks be delayed? Do you sometimes think that SQL is not a problem, but it is inexplicable Long SQL? This is the Check Stall mechanism that will be described below.

Check Stall mechanism

What if later IO tasks are affected by previous tasks that have been executed for too long? This will inevitably cause some innocent tasks (or a simple INSERT operation, the example of INSERT because INSERT is usually very quick) to be affected, and the result may be delayed processing. There is a Timer Thread in the thread pool, similar to the Timeout Thread thread in many of our systems. This thread iterates at regular intervals, and what it does in the iteration includes the following two parts:

Check the load of the thread group to wake up and create the worker thread.

Check for client connections with processing timeouts.

This article mainly introduces * part of the work, that is, the Check Stall mechanism. Timer Thread periodically checks whether threads in the thread group are blocked (stall). Blocking means that there are new tasks but no threads in the thread group to handle. Timer Thread determines whether the thread group is blocked by whether the queue_event_count and IO task queues are empty. Each time the worker thread retrieves the task, the queue_event_count accumulates, which means that the task is processed normally, the worker thread is working normally, and the queue_event_count is cleared after each check_stall, so if in the next iteration after a certain time interval (stall_limit) If the IO task queue is not empty and queue_event_count is empty, it means that there are no worker threads to process IO tasks during this time interval. Then the Check Stall mechanism will try to wake up or create a worker thread. The logic of waking up a thread is very simple. If there is an idle thread in the waiting_threads, wake up an idle thread, otherwise you need to try to create a worker thread, which may not be created successfully. Let's look at the conditions for creating a thread:

If there are no idle threads and no active threads are created immediately, it may be because no worker thread is blocked or because there is a potential deadlock.

Otherwise, if the time since the last creation is greater than a certain threshold, this threshold is determined by the number of threads in the thread group.

The relationship between the threshold and the number of threads in the thread group is as follows:

Thread threshold

= 16200 * 1000

The threshold mechanism can effectively prevent threads from being created too frequently. The remaining question is, why does the threshold depend on the number of threads in the thread pool? Can the threshold depend on the value of thread_pool_stall_limit? the Check Stall mechanism can be thought of as a dedicated thread to do something special, after all, the logic within the thread group is also quite confusing.

Task queue

The task queue, that is, the ready tasks trained by listener from poolfd rotation, is divided into priority task queue (high_prio_queue) and normal task queue (queue). The IO tasks in the priority queue are processed first, and then the tasks in the ordinary queue can be processed. So what kind of task is considered a priority? Officials have listed two conditions:

The connection is in a transaction.

The priority tickets value associated with the connection is greater than 0.

The parameter priority tickets (thread_pool_high_prio_tickets) is designed to prevent high-priority tasks from always being processed, while some non-high-priority tasks are hungry for a long time. After all, the creation of worker threads is conditional. Every time a high-priority task is placed in a high-priority queue, the value of priority tickets will be reduced by one, so the value of priority tickets must be less than or equal to 0 for a certain number of times. As a result, the problem of high priority is avoided. In addition, the use of the queue is affected by the parameter thread_pool_high_prio_mode, so please refer to the introduction to the parameter thread_pool_high_prio_mode. The io_event_count is accumulated when the ready IO task is rotationally trained and placed in the queue, and the queue_event_coun is counted when the IO task is fetched from the queue.

Listener thread

What Listener does is to train the ready IO event of the socket handle bound to it in turn from poolfd, and the event is put into the task queue in the form of a task and handled accordingly. What if listener reads some IO tasks? The following answers are based on two questions:

Should listener handle these tasks on its own? Or put these tasks in a queue and let the worker thread handle them?

How many worker threads do we need to wake up if the task queue is not empty?

For * questions, we usually don't want to change the wait and wake-up state of listener often, because listener has just been awakened, so we prefer to let listener use its time slice to do some work. If listener does not handle the work on its own, this means that other threads will be awakened to do the work, which is obviously not good. The potential problem with letting listener do the task is that the thread group may not be able to process the network task in time for some time, which is not the main problem, because the stall will be checked by Timer Thread. However, it is not good to always rely on Timer Thread, because stall_limit may be set up for a long time. We use the following strategy, if the task queue is not empty, we may have more network tasks at this time, let other threads handle the task, otherwise listener handles the task itself.

For the second question, we usually keep one active thread for each thread group (the active thread includes the thread that is doing the task), so the condition for waking up a worker thread is that the number of active leads is 0. If no thread is awakened, we can only rely on Timer Thread to check the stall and wake up.

As can be seen above, if the task queue is not empty, there will not necessarily be threads to process the task in a timely manner, which results in time-consuming tasks affecting the execution of subsequent tasks. in the future, network tasks may not be processed for a long time by abandoning the rule of keeping only one active thread per thread group.

The use of MySQL thread pool can improve the performance of the database. Designers carefully design the thread pool creation and task processing mechanism, but it also brings some potential problems. The most obvious is the impact of time-consuming tasks on other task scheduling. Although there are shortcomings, users can still master the internal details of thread pool and deeply understand the meaning of open parameters. The use of MySQL thread pool is optimized to some extent by adjusting the parameters. To put what you have learned into practice, can you use some of the knowledge introduced above to solve some practical problems here?

The above is how to parse the internal implementation mechanism of MySQL thread pool. have you learned any knowledge or skills? If you want to learn more skills or enrich your knowledge reserve, you are welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.