MySQL Thread pool optimization notes

MySQL thread pooling series one (thread pool FAQ)

First, what is the MySQL thread pool, and what is it for?
The use of thread pooling can be achieved in the following two main purposes:
1, in the large concurrency, performance will not be due to overload and rapid decline.
2, reduce performance jitter

How does the thread pool work?
Thread pooling uses a divide-and-conquer approach to limit and balance concurrency. Unlike the default thread_handling, the thread pool divides connections and threads, so the number of connections and the number of threads executing the statement is no longer a fixed relationship, and the thread pool can be
Configure the thread group to manage the connection, and then determine whether to prioritize or queue execution based on the keywords for each statement.

What is the difference between the MySQL thread pool and the client side of the connection pool?
Connection pool: Connection pooling is primarily used to manage client connections, avoid repetitive connection/disconnect operations, and instead cache idle connections for reuse. This reduces the overhead and cost of connecting to the MySQL server/disconnect MySQL server, thereby improving performance.
But MySQL's connection pool does not have access to MySQL server's query processing capabilities and current load conditions.
Thread pool: The thread pools operate on the MySQL server side and are designed to manage the current concurrent connections and queries.

How much performance can the thread pool improve?
According to Oracle MySQL official performance test
After the concurrency reaches 128 connections. mysql performance without a thread pool can quickly degrade. After using the thread pool, performance will not fluctuate and will remain in good condition.
In read-write mode, after 128 connections, MySQL on the wired pool is 60 times times more than MySQL without a thread pool.
In read-only mode, after 512 connections, MySQL on the wired pool is 18 times times more efficient than MySQL without a thread pool.

When can you consider using Thread_pool?
* Show global status like '%threads_running% ' is the value of the current number of concurrent execution statements for MySQL server, and if this value is kept at around 40, consider using thread pool.
* If you use the Innodb_thread_concurrency parameter to control the amount of concurrent things, then using the thread pool will achieve better results.
* If your job is composed of a lot of short connections, it is useful to use a thread pool.

Say the limitations of the Oracle MySQL thread pool plug-in:
1, Oracle MySQL Enterprise 6.10 version added, that is, less than this version of the Enterprise version is not supported, currently all Oracle MySQL community version is not supported.
2, if it is a Windows system, need to be Vista or later version, if it is Linux, need 2.6.9 after the kernel.

MySQL thread Pooling Series II (Installation and rationale for Oracle MySQL thread pool)

Components and installation of thread pool
The thread pool exists as a plug-in, and after the thread pool plug-in is installed, some information_schema tables and related parameter variables are added.
The Information_schema table contains:
Tp_thread_state
Tp_thread_group_state
Tp_thread_group_stats

Newly added parameter variable:
Thread_handling adds a value, loaded-dynamically, which is the value when the thread pool plugin is successfully loaded.
Thread_pool_algorithm:
Thread_pool_high_priority_connection:
Thread_pool_prio_kickup_timer:
Thread_pool_max_unused_threads:
Thread_pool_size:
Thread_pool_stall_limit:
If these values are not set correctly, the plugin will fail to initialize when you start MySQL and the plugin will not load.
The specific settings for these variables are described in detail in the next optimization section.

The object library for the thread pool plug-in must be placed in the directory corresponding to the PLUGIN_DIR variable. For the thread pool to take effect, you can use the –plugin-load option when you start MySQL. or modify the My.cnf file. Add the following information in the [MYSQLD] section
[Mysqld]
Plugin-load=thread_pool.so

The principle of thread pool
The thread pool contains several thread groups, and each thread group manages a set of client connections. When the connection is established, thread pool assigns them to the thread group in a polling manner.
The number of thread group is obtained by Thread_pool_size configuration, by default is 16, the largest 64, the smallest 1.
Each thread group can have a maximum of 4,096 threads.

The thread pool separates the connection from the thread, so the connection and thread are not fixed, and the thread executes the statements received from connections, which differs from the default thread_handling mode.

Thread_handling parameters
In the original version there is a thread_handling parameter, you can set the working mode of thread, there are two values,
One is no-threads, which means that at any time at most only one connection can be connected to the MySQL server, generally for debugging. The other is one-thread-per-connection, which is to create a thread for each connection to process all requests for this connection until the connection is disconnected and the thread ends. This is also the default value for Thread_handling.
This shows that, by default, how many threads will be generated by the number of connections, the greater the concurrency, the more threads, the more competitive resources between threads, the lower performance.

The thread pool plug-in provides an additional thread_handling method for effectively managing the execution thread to connect to a large number of clients, thereby improving performance.
Several issues addressed by thread pooling:
* High concurrent multi-threaded stacks result in almost no CPU cache, thread pooling facilitates the reuse of threads, and reduces CPU slow memory.
* Too many threads concurrent execution, context switching overhead is very high, which is a challenge to the operating system's task scheduling, the thread pool can control the MySQL active concurrent threads in a level that is suitable for MySQL server to run.
* Too many concurrent execution of transactions will increase resource contention, in the InnoDB engine, will increase the time to get the mutexes, the thread pool can control the concurrency of the transaction.

The thread pool tries to ensure that every thread in each thread group executes as many statements as possible, but sometimes more threads are allowed to perform temporary tasks to improve performance. The algorithms work in the following ways:
* Each Trhead group has a listener, the listener is responsible for listening to the Statements,thread group assigned to thread group for two kinds of execution, one for immediate execution and one for queued execution.
* Immediate execution condition is that only one statement is currently received and no statements is currently executing.
* Queued execution occurs when no immediate execution is possible
* When execution occurs immediately, it is executed by the listener thread, which means that listener is performing some temporary statements, and if the immediately executed statement completes soon, the thread will change back to the listener thread. If otherwise, thread pool would consider opening a new listener thread to replace it, and whether a listener thread was to be created is monitored and executed by the background thread of thread pool.
When the thread pool plug-in is started, each thread group creates a listener thread, plus a background thread, that other threads are created on demand.

Thread_pool_stall_limit the meaning of the system variable can be understood as the time required to complete a statement, the default is that stalled time is 60ms, the maximum can be set to 6s. Configuring this parameter allows you to balance the workload of the server. The smaller the thread starts, the smaller the value can avoid deadlocks, and the larger values are often used in many long queries to avoid booting too many threads.

The thread pool's focus is on limiting the number of concurrent short query statements, preventing other statements from starting execution when a statement execution time is not reached stall, and continuing if a statement execution exceeds stall time, However, it does not prevent other statement from starting. In this way, thread pool attempts to ensure that each thread group never has more than one short-running statement, although there will be multiple long-running statement. It is undesirable to allow long-running statements to block the execution of other statements, because there is no limit to the maximum time to wait. For example, in a replication master, a thread keeps sending binlog to slave.

A statement because I/O operations or user-level locks are blocked, this blocking will cause thread group to be invalid, so the callback function notifies the thread pool of confirmation and thread pool will immediately be in this thread Group to start a new thread to perform other statement. When the blocked thread returns, the Thrad pool allows you to reboot immediately.

There are two types of queues (queue), a high-priority queue (high-priority queue), and a low-priority queue (low-priority queue). The first statement in a transaction is assigned to a lower-priority queue. The remaining statement will be assigned to a high-priority queue (if the transaction has already started), or it is assigned to a lower-priority queue.
The allocation of queues is affected by thread_pool_high_priority_connection system variables, the default value of this parameter is 0, which means that both low-priority and high-priority queues are used, and if the value is set to 1, all queued Statements are assigned directly to high-priority queues.

The statements of a transaction-less storage engine, or the autocommit storage engine, is placed in a low-priority queue because each statement is a transaction. Therefore, using the InnoDB and MyISAM hybrid engine database, thread pool believes that InnoDB priority is higher than MyISAM priority unless InnoDB is turned on. If Autocommit is turned on, all statements are of low priority.

When thread group selects a statement execution in a queue, it will first look in the high priority queue before it is found in the lower-priority queue, and if Tatement is located, He would remove the statement from the queue and start executing it.

If a statement waits long in a low-priority queue, it will be moved by the thread pool to a high-priority queue. The waiting time is decided by the Thread_pool_prio_kickup_timer.

Thread pool's reuse of active threads makes it possible to use CPU caches better. This small adjustment is a great help for performance improvement.

Thread group allocates multiple threads to perform statement:
* A thread starts executing a statement, but after the execution time reaches stalled, thread group allows other threads to start executing other statement, before the thread continues to execute the statement.
* A thread starts executing a statement, but the thread is blocked, and after reporting to the thread pool, thread group allows other threads to start executing other statement.
* A thread begins to execute a statement, but the thread is blocked and is not reported to the thread pool because the blocking does not occur at the code level. When the blocking time reaches stall, thread group allows other threads to perform other statement.

The design of a thread can be extensible for an increasing number of connections, and his design can avoid deadlocks by limiting concurrent threads. But note that thread pool does not block other threads if it does not report the thread pool.
This situation may cause a thread pool deadlock.
* Long run statments, very few statements will use all the resources, which will cause the server to deny all other accesses.
The *binary log dump thread reads the Binlog and sends it to slave, a long-running "statement" that does not prevent other statements from running.
*statement can be blocked by row-level, table-level locks, or by locks on other causes such as sleep, or by other blocked thread that does not report thread pool.

Each of these cases is designed to prevent deadlocks, and statement that do not perform quickly will be moved to the stalled category, so the thread group allows other statement to begin execution. Because of this design, when threads are executing or blocked, thread pool puts these threads
Marked as the stalled type, the remaining statement will be executed, and it does not reject the execution of other statments.

The maximum number of threads can reach Max_connections and thread_pool_size, a situation where all connections are executed at the same time, and each thread group opens a listen thread to monitor the new statement. This is a difficult situation, but it exists theoretically.

MySQL thread Pool Series III (Oracle MySQL threads pool tuning)

The first clear tuning is to improve the TPS.

Thread_pool_size:
is a very important parameter that controls the performance of the thread pool, which is represented by the number of thread group. Can only be set before server starts, we test the results of the thread pool as follows:
* If the primary storage engine is innodb,thread_pool_size set between 16 and 36, most cases are set at 24 to 36, we have not found anything that needs to be set to more than 36, and only some special environments need to be set less than 16.
When testing with DBT2 or Sysbench, the InnoDB engine is typically set to 36, which can be set to a smaller number if it is written in a particularly high number of environments.
* If the primary storage engine is myisam,thread_pool_size need to be set lower, we recommend a value of 4 to 8, and higher values may negatively affect performance.

Thread_pool_stall_limit:
This parameter is important for handling blocking and long-running statements. This time is from a statement from the start to the execution of the total time spent, if more than the set value is identified as stalled, at this point the thread pool is also beginning to allow the execution of additional
A statement. The unit of this value is 10 milliseconds, the default value is 6, and the default interval is 60ms, and a statement executes more than 60MS, which is considered to be stalled. The maximum value is 600, which is 6 seconds. Typically this value is set to the time that your 99% statement can run. Like I slow the query settings
is 0.1, then this is set to 10. In addition, you can pass
SELECT SUM (stalled_queries_executed)/sum (queries_executed) from INFORMATION_SCHEMA. Tp_thread_group_stats to get the ratio of stalled, this value as small as possible, in order to avoid stall, you can increase the Thread_pool_stall_limit value.

Thread_pool_prio_kickup_timer:
This value affects the lower-priority statements queue. The unit of the parameter value is milliseconds, and the statement of the lower priority need to wait for the number of milliseconds to be moved to the high priority queue. The default is 1000, or 1 seconds, and the range of values is (0-4294967294).

Thread_pool_high_priority_connection:
This parameter mainly determines the execution priority of the new statements. The default value is 0, which means that both low-prority queue and high-priority queue are used. If set to 1, all statement will be assigned to the high-priority queue.

Thread_pool_max_unused_threads:
This parameter restricts the maximum number of sleeping thread in the thread pool. Thus restricting the use of sleeping thread for memory.
If the value of the parameter is 0, it is also the default value, which means there is no limit to sleeping thread. Assuming that the value is N, when n is greater than 0, it means 1 consumer thread and N-1 reserve thread. In other words, when a thread executes a statement and is about to become a sleeping state, then the sleeping state
The number of threads has reached the maximum allowable sleeping thread, and the thread will exit.
About consumer thread:sleeping thread is made up of consumer thread and reserve thread, which allows a consumer thread to be sleeping thread in the thread pool. When a thread is to be converted to sleepling thread, if no consumer thread exists, then
This thread will be converted to consumer thread. When a sleeping thread is awakened, if there is consumer thread, then the first wake-up consumer thread, if consumer Thread does not exist, so wake up the reserve thread.

Thread_pool_algorithm:
This parameter determines which algorithm the thread pool uses. The default value is 0, which means that using a lower concurrency algorithm works well in most test and production environments.
Another value is 1, more actively increase the number of concurrent algorithms, sometimes better than the optimal number of threads performance, but as the connection increases, performance will gradually decline. So this parameter is mainly used in the experimental environment.