MySQL Thread Pool Operation Process detailed

Thread Pool operation Process:

Thread pool contains a number of thread groups, each groups manages a certain amount of client connections, and when MySQL establishes connection, thread Pool is assigned to one of the thread groups in a looping manner (Round-robin fashion).

The Thread_pool_size prescribes the number of thread groups, which also stipulates how many statement values can be performed at the same time, and that the maximum number of threads per thread group is 4096.

The thread pool separates the connection from the threads, so connection and thread have no fixed connection.

Algorithm:

Each thread group has a listener thread that listens to the statement that sends filtering from the connection, and when a statement is received, the thread group will have two choices; execute immediately or put in a queue to execute later.

When a statement is accepted, and no other statement is in the queue, or no statement is currently executing, the statement is executed immediately, otherwise the statement is placed in the queue.

When the statement is executed immediately, the monitor thread performs the task, meaning that no thread is temporarily listening in the thread group, and if the statement executes quickly, the thread returns to continue listening. Otherwise, the thread pool will create a new thread to listen to when needed. The thread pool has a background thread to monitor the state of the thread group, so that thread group is blocked when executing SQL (for example, encountering disk IO interrupts, etc.)

The Thread_pool_stall_limit parameter takes a value range of 60ms----6s, which represents the time interval at which the statement will end and the next statement is to be executed (the thread waits for the Thread_pool_ when it exceeds thread_pool_size) Create new threads after Stall_limit ms to prevent thread pools from expanding instantaneously and unnecessary threading overhead. A smaller value allows a new thread to start quickly, which also prevents the deadlock from being created. For those long-running statement, a larger value is appropriate, which prevents too much statement from being executed at the same time.

Thread pool is focused on concurrent short running statement???

When statement encounters disk IO, or row lock or table lock, the thread group becomes unavailable, and the thread pool has a callback mechanism for the group to open a new thread to perform other statement, the thread pool is immediately available when blocked thread returns.

For queues: high-priority Queue and Low-priority queue

In general, the first statement in a transaction are placed in a lower-level queue, and other statements are placed at higher levels. Thread_pool_high_priority_connection can place all statement in a high level queue.

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

When the thread group selects the statements in the queue to start executing, it first checks the high-priority queue and then the low-priority queue, which is removed from the queue for the found statement and begins execution.

If a statement is too long in a low-priority queue, thread pool puts it in a high-priority queue, thread_pool_prio_kickup_timer the parameters to control the time.

The thread pool chooses to reuse the most active threads to take advantage of the CPU cache, a small adjustment that has a significant impact on performance.

Here is an example of when the thread group has multiple threads active:

1, a thread executes a statement, but the time has passed stalled Time,thread group will open a new thread to perform other statement, setting when the first statement is still executing.

2. A thread is executing a statement and then being blocked and detected by the thread pool, at which point the thread group allows a new thread to be opened to execute another statement.

3, a thread is executing a statement, and then is blocked but not detected by the thread pool, this time only wait until stalled time to open a new thread to execute the new statement

This is important when some threads are blocked but not detected by the thread pool but do not prevent the execution of other statements or cause thread pool deadlock.

1, long time operation of the statement,

2, Binary log Dump threads

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

In the above case, in order to prevent the deadlock, the statement will be treated in the stalled category,

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

This article is from the "Technology Achievement Dream" blog, please be sure to keep this source http://weipengfei.blog.51cto.com/1511707/1163228

See more highlights of this column: http://www.bianceng.cnhttp://www.bianceng.cn/database/MySQL/