MySQL Thread pool operation process

Thread pool operation process:

The thread pool contains a certain number of thread groups. Each groups manages a certain amount of client connections. when mysql establishes a connection, the thread pool will be cyclically (round-robin fashion) allocate it to one of the thread groups.

Thread_pool_size specifies the number of thread groups, which specifies the number of statement statements that can be executed at the same time. The maximum number of threads in each thread group is 4096.

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

Algorithm:

Each thread group has a listening thread that listens to statement sent and filtered from the connection. When a statement is received, the thread group has two options; execute immediately or put it into the queue for execution later.

When only one statement is received, and no other statement is in the queue, or no statement is being executed, the statement is executed immediately, otherwise, the statement will be placed in the queue.

When the statement is executed immediately, the monitoring thread will execute the task. This means that no thread in the thread group is temporarily in the listening State. If the statement is executed fast, the thread will return to continue listening. Otherwise, the thread pool will create a new thread for listening as needed. The thread pool has a background thread to periodically monitor the status of the thread group, so as not to block the thread group during SQL Execution (such as disk io interruption)

The value range of thread_pool_stall_limit is 60 ms-6 s. This value indicates the time interval between the statement to end and the next statement to be executed (when the thread exceeds thread_pool_size, A new thread will be created after thread_pool_stall_limit ms to prevent the thread pool from instantly expanding and causing unnecessary thread overhead ). A smaller value allows new threads to start quickly, which can also prevent deadlock. For those statement that are executed for a long time, it is suitable for a large value to prevent too many statement statements from being executed at the same time.

Thread pool focuses on concurrent short-term running statement ???

When 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 enable a new thread in the group to execute other statement. When the blocked thread returns, the thread pool is immediately used.

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

In general, the first statement in the 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 statement in the queue of higher level.

If the statement is for a non-transactional storage engine, or the storage engine autocommit = 1, all the statements will be placed in a low-level queue,

When the thread group selects the statements in the queue to start execution, it first checks the queue with high priority and then the queue with low priority. For the statements found, it is removed from the queue and executed.

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

The thread pool selects to reuse the most active threads to make full use of the CPU cache. This small adjustment has a great impact on performance.

1 2 Next Page