MySQL thread pool

http://mysql.taobao.org/monthly/2016/02/09/

Overview

MySQL Original thread scheduling method has one thread per connection (one-thread-per-connection) and all connections to one thread (no-threads).

No-threads generally used for debugging, production environment generally used one-thread-per-connection way. The one-thread-per-connection is suitable for environments with low concurrent long connections, and in high concurrency or a large number of short-connected environments, a large number of creation and destruction of threads, as well as thread context switching, can severely impact performance. In addition, one-thread-per-connection can also affect performance for large number of connection extensions.

To address these issues, MariaDB, Percona, and Oracle MySQL have rolled out thread pooling schemes that are broadly similar in their implementation, taking Percona as an example to briefly introduce the implementation principle and introduce some of the improvements that we have built on it.

Realize

Under the thread pool scenario, each connection to a user no longer corresponds to a thread. The thread pool consists of a series of worker threads, which are grouped into thread_pool_size group. The user's connection is mapped to the appropriate group in a round-robin way, and a connection can be handled by one or more worker threads in a group.

2. Listener Threads
   There is one listener thread in each group that listens to the connected events in this group in a epoll way. The listener thread is also a worker thread, and the listener thread is not fixed.
   Listener Line Cheng When a connection event is heard, the event is placed in the priority queue, and the listener thread also handles some connection events as a worker thread to reduce context switching.
   The listener thread checks if the priority queue is empty, and if NULL indicates that the network is idle, the listener thread handles the first listener event as the worker thread, and the other events are still placed in the priority queue.
   Also, when there is no active line, listener wakes up a thread, and if no thread can wake, and the current group has only one thread and is listener, a thread is created.

4. Priority queue
   Divided into high priority queue and normal queue, already open transaction and tickets not 0, put high priority queue, otherwise put into normal queue. Each connection is thread_pool_high_prio_tickets moved to the normal queue after it is placed in the priority queue. The worker thread takes event processing from the high-priority queue first, and only takes event processing from the normal queue when the high-priority queue is empty.
   Priority queues allow transactions or short transactions that are already open to be prioritized, and resources such as release locks are submitted in a timely manner.

6. Worker thread
   The worker thread is responsible for taking event handling from the priority queue. If the event is not taken, an attempt is taken from the epoll to take one, and if no more wait is taken, the thread_pool_idle_timeout worker thread exits if it waits.

8. Timer thread
   thread_pool_stall_limitcheck once every time.
   • Listener not receive new events, listener is waiting to be called wake_or_create_thread , re-create listener;
   • From the last check, the worker thread did not receive a new event, and the queue is not empty, it is considered that a stall occurred, need to wake up or create a worker thread;
   • Checks if the timeout is timed out and net_wait_timeout exits the connection instead of exiting the worker thread.
10. When to wake or create worker threads
    • When an event is removed from the queue when no active thread is found;
    • When a worker thread waits and no active thread occurs;
    • The timer thread thinks the stall has occurred;

Important parameter Analysis

1. thread_pool_oversubscribe
   A thread_pool_oversubscribe new thread is not created when the active thread and the waiting thread in a group exceed.
   This parameter can control the concurrency of the system, and can prevent deadlock on the schedule, consider the following situation, A, B, c three transactions, a, b wait for C to commit. A, b to get the dispatch, while the number of active threads reached the thread_pool_max_threads upper limit, then C continues to commit, there is no thread to handle C-commit, resulting in a, B has been waiting. thread_pool_oversubscribethis is prevented by controlling the total number of active threads and waiting threads in the group.

2. thread_pool_stall_limit
   Timer thread detection interval. This parameter setting is too small to cause too many threads to be created, resulting in more thread context switching, but the lock-waiting scene can be handled in a timely manner to avoid deadlocks. Parameters are too large to be useful for long statements, but will block execution of short statements. The parameter setting depends on the situation, for example, 99% of the statement can be completed in 10ms, then we can thread_pool_stall_limit set to 10ms

Some improvements

2. Lock tables Read processing
   For events that declare an explicit declaration of a table lock, such as lock tables read, put a high-priority queue.

4. Processing of Binlog Dump threads
   The Binlog dump thread is a typical long-transaction scenario, and when multiple Binlog dump threads are assigned to the same group, the threads in group can easily exceed the thread_pool_oversubscribe limit, resulting in performance degradation.
   The optimization method is to modify the Binlog dump thread without thread_pool_oversubscribe restriction.

5. Extensive diagnostic informationinformation_schema.thread_group_status

    ShowCreateTable Thread_group_status\g ***************************1.Row ***************************Table:thread_group_statusCreateTable:CREATETemporaryTABLE' Thread_group_status ' (' ID 'int) unsignedNotNullDEFAULT' 0 ',' Thread_count 'int) unsignedNotNullDEFAULT' 0 ',' Active_thread_count 'int) unsignedNotNullDEFAULT' 0 ',' Connection_count 'int) unsignedNotNullDEFAULT' 0 ',' Waiting_thread_count 'int) unsignedNotNullDEFAULT' 0 ',' Dump_count 'bigint (21) unsigned NOT null default  ' 0 ',  ' low_queue_count ' bigint (21) unsigned not null DEFAULT  ' 0 ',  ' high_queue_count ' bigint (21) unsigned not null default  ' 0 ') engine=memory default charset=utf8        

7. thread pool scheduling exception, unable to connect processing
   For locally logged-on users, take the old one_thread_per_connection logic to resolve the situation where the connection cannot be connected.

Difference between connection pool and thread pool

Finally, the difference between the connection pool and the thread pool. The connection pool and thread pool are two independent concepts, the connection pool is optimized on the client, the connection is cached by the client, and the connection is avoided repeatedly created and destroyed. The thread pool is the server-side optimization. The two optimization angles are different and irrelevant, so both optimizations can be used simultaneously.

MySQL thread pool