Thread pool operation process:

thread pool contains a certain number of thread groups, each group manages a certain amount of client connections, when mysql When a connection is established, the thread pool assigns it to one of the thread groups in a round-robin fashion.

thread_pool_size specifies the number of thread groups, which also specifies how many statements can be executed at the same time.The value is 1-64, and the maximum number of threads for each thread group is 4096.

Thread pool separates connection and threads, so there is no fixed connection between connection and thread.

Algorithm:

Each thread group has a listening thread, which listens for the filtered statement sent from the connection.When a statement is received, the thread group has two choices; execute it immediately or put it in the queue for later execution.

When only one statement is received, and no other statement is in the queue, or no statement is currently executing, then the statement will be executed immediately, otherwise the statement will be put into the queue.

When the statement is executed immediately, the monitoring thread will perform the secondary task, which means that there is no thread in the monitoring state temporarily in the thread group.If the statement is executed quickly, the thread will return Come back and continue to be in the listening state.Otherwise, the thread pool will create a new thread to listen to when needed.The thread pool has a background thread to periodically monitor the status of the thread group, so as to avoid the thread group being blocked when executing SQL (such as encountering disk io interrupts, etc.)

The value range of the thread_pool_stall_limit parameter is 60ms---- 6s, this value represents the time interval between the end of the statement and the execution of the next statement (when the thread exceeds thread_pool_size, it will wait for thread_pool_stall_limit  ms to create a new one threads, preventing the thread pool from expanding instantaneously and unnecessary thread overhead).Smaller values ​​allow new threads to be started quickly, which also prevents deadlocks.For those statements that execute for a long time, a larger value is suitable to prevent too many statements from being executed at the same time.

A thread pool focused on concurrent short-running statements? ? ?

When the statement encounters disk io, or row lock or table lock, it will cause the thread group to become unavailable.At this time, the thread pool has a callback mechanism to open a new thread in the group to execute other The statement, when the blocked thread returns, the thread pool is used immediately.

For queues: high-priority queue  and low-priority queue

In general, the first statement in a transaction will be placed in the lower-level queue, and other statements will be placed in the higher-level queue.thread_pool_high_priority_connection can put all statements in the high-level queue.

If the statement targets a non-transactional storage engine, or if the storage engine autocommit=1, all statements will be put into the low-level queue,

When the thread group selects the statement in the queue to start executing, the high priority queue is checked first, then the low priority queue, for the found statement, it is removed from the queue and starts executing.

If a statement takes too long in the low-priority queue, the thread pool will put it in the high-priority queue.The thread_pool_prio_kickup_timer parameter controls this time.

The thread pool will choose to reuse the most active threads to make full use of the CPU's cache.This small adjustment has a big impact on performance.

The following is an example when a thread group has multiple threads active:

1.A thread executes a statement, but the time has exceeded the stalled time.The thread group will open a new thread to execute other statements, and set it when the first statement is still being executed..

2.A thread is executing a statement, and then it is blocked and detected by the thread pool.At this time, the thread group allows to open a new thread to execute another statement.

3.A thread is executing a statement, and then it is blocked but not detected by the thread pool.At this time, only after the stalled time can start a new thread to execute a new statement

It is important that some threads are blocked but not detected by the thread pool but do not block the execution of other statements nor cause the thread pool to deadlock.

1.Long-running statement,

2.Binary log dump threads

3.Statements blocked on a row lock, table lock, sleep, or any other blocking activity that has not been reported back to the thread pool by MySQL Server or a storage engine.
 

In the above cases, in order to prevent the occurrence of deadlock, the statement will be placed in the stalled category.

The maximum number of threads is the sum of max_connections and thread_pool_size

This article is from the blog "Technology Achieves Dreams", please keep this source http://weipengfei.blog.51cto.com/1511707/1163228