MySQL Thread pool optimization notes

MySQL thread pool optimization I was summed up a webmaster's 3 articles, here I put it together this article is divided into three optimization sections, the following to see.

MySQL thread pool series one (thread pool FAQ)

First of all, what is MySQL thread pool and what does it do?
Using a thread pool can achieve the following two purposes:
1, in 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 statements is no longer a fixed relationship, and the thread pool can be
Configure thread groups to manage connections, and then determine whether to prioritize or queue execution based on the keywords for each statement.

What is the difference between MySQL thread pool and client side connection pool?
Client Segment Connection pool: The connection pool is primarily used to manage client connections, avoid duplicate connection/disconnection operations, and instead cache idle connections for reuse. This reduces the cost and cost of connecting MySQL server/to the MySQL server, thereby improving performance.
But MySQL's connection pool cannot get the query processing power of MySQL server and the current load situation.
Thread pool: The thread pool operation is on the MySQL server side and is designed to manage current concurrent connections and queries.

How much performance can the thread pool improve?
Based on Oracle MySQL's official performance test
After 128 connections are reached. mysql without a thread pool can be degraded quickly. With the thread pool, performance does not fluctuate and remains in a better state.
In read-write mode, after 128 connections, MySQL on the thread pool is 60 times times higher than MySQL without a thread pool.
In read-only mode, after 512 connections, MySQL on the thread pool is 18 times times higher than the MySQL performance without a thread pool.

When can I consider using Thread_pool?
* Show global status like '%threads_running% ', the value of the current number of concurrent execution statements MySQL server trajectory, if this value has been kept in the interval of around 40, then consider using the thread pool.
* If you use the Innodb_thread_concurrency parameter to control the amount of concurrent things, then using the thread pool will get better results.
* If your job is composed of a lot of short connections, then using the thread pool is beneficial.

Describe the limitations of the Oracle MySQL thread pool plugin:
1, the Oracle MySQL Enterprise 6.10 version added, that is, less than this version of Corporate Edition is not supported, currently all of the 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, requires 2.6.9 after the kernel.

MySQL thread pool Series II (Installation and rationale for the Oracle MySQL threads pools)

Components and installation of the thread pool
The thread pool exists as a plug-in, and after installing the thread pool plug-in, 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

The 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 set incorrectly, the plugin will fail to initialize when you start MySQL, and the plugin will not load.
The specific setting of these variables is described in detail in the next optimization section.

The object library of the thread pool plug-in must be placed in the corresponding directory of 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 a number of thread groups, each of which manages a set of client connections. When the connection is established, the thread pool assigns them to the thread group in a polling manner.
The number of thread group is configured by Thread_pool_size, the default is 16, the maximum is 64, the minimum is 1.
Each thread group can have a maximum of 4,096 threads.

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

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

The thread pool plug-in provides an additional thread_handling method that effectively manages the execution thread to connect to a large number of clients, improving performance.
Several issues that the thread pool solves:
* High concurrent multithreaded stacks cause the CPU cache to almost fail, and the thread pool facilitates threading stack reuse, reducing the amount of CPU memory.
* Too many threads executing concurrently, the context switching overhead is a big challenge to the operating system's task scheduling, and the thread pool can control MySQL's active concurrent threads at a level appropriate for MySQL server.
* Too many transactions concurrent execution increases resource contention, and in the InnoDB engine, the time to get central mutexes is increased, and the thread pool can control the concurrency of transactions.

The thread pool tries to ensure that every thread in the thread group executes as many statements as possible, but there are times when more threads are allowed to perform some temporary tasks to improve performance. The algorithm works in the following ways:
* Each Trhead group has a listener, the listener is responsible for listening to the Statements,thread group assigned to the thread group there are two execution scenarios, one is immediate execution, one is queued execution.
* The immediate 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 immediate execution occurs, it is performed by the listener thread, which means that listener executes some temporary statements, and if the immediate execution of the statement is done quickly, the thread will be back to the listener thread, If the thread pool is in another situation, consider replacing it with a new listener thread, whether it is necessary to create a listener thread that is monitored and executed by the thread pool's background thread.
When the thread pool plug-in is started, each thread group creates a listener thread, plus the background thread, and other threads are created as needed.

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. This value sets the smaller the thread starts faster, the smaller the value is better to avoid deadlocks, and the larger value is usually used in many long queries, in order to avoid starting too many threads.

The thread pool focuses on limiting the number of concurrent short query statements, preventing other statements from starting execution when a statement execution time does not reach stall, and if a statement execution exceeds stall time, it will continue to execute, But does not prevent other statement from starting execution. In this way, the thread pool tries to ensure that each thread group never has more than one short-running statement, although there are multiple long-running statement. It is undesirable to allow long-executed statements to block execution of other statements, because there is no limit to the maximum time to wait. For example, in a replication master, a thread has been sending Binlog to slave.

A statement because I/O operations or user-level locks are blocked, this blocking will cause the thread group to be invalid, so the callback function notifies the thread pool to confirm, and the thread pool will immediately be in this thread The group starts a new thread to execute the other statement. When the blocked thread returns, the Thrad pool allows a reboot to be initiated immediately.

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

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

When the thread group chooses statement execution in a queue, it takes precedence over the high-priority queue and then finds it in a low-priority queue, if tatement is found, He will remove the statement from the queue and start executing it.

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

Thread pool reuse of active threads allows for better use of CPU caches. This small adjustment can be a great help in improving performance.

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

The design of a thread can be extended to an increasing number of connections, and his design can avoid deadlocks as much as possible by restricting concurrent threads. Note, however, that if a blocked thread does not report the thread pool, the thread pool does not block other threads from running.
This situation can cause the thread pool to deadlock.
* For long-running 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 Binlog and sends it to slave, a long-running "statement" that does not prevent other statements from running.
The *statement can be blocked by a row-level, table-level lock, or blocked by other causes such as sleep, or other thread blocked without reporting the thread pool.

In each case, the statement will be moved to the stalled classification, so that the thread group allows other statement to begin execution, in order to prevent deadlocks. Because of this design, when threads are executing or blocked, thread pool takes these threads
Marked as stalled type, then the remaining statement will be executed, and it does not reject the execution of the other statments.

The maximum number of threads can reach Max_connections and thread_pool_size, in which case only all connections are executed simultaneously, and each thread group opens a listen thread to monitor the new statement. This situation is difficult to happen, but it is theoretically present.

MySQL thread Pool Series III (Oracle MySQL threading pools tuning)

The purpose of 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, as specified by the number of thread group. Only set before server startup, we test the thread pool with the following results:
* If the primary storage engine is innodb,thread_pool_size set between 16 and 36, most of the 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 particularly many environments.
* If the primary storage engine is myisam,thread_pool_size need to be set lower, we recommend a value of 4 to 8, a higher value may have a negative impact on performance.

Thread_pool_stall_limit:
This parameter is important for handling blocking and long-executing statements. This time is the time from the beginning of a statement execution to the completion of the execution, if the value is more than set to be considered as stalled, the thread pool also begins to allow the execution of additional
A statement. The unit of this value is 10 milliseconds, the default value is 6, or 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. This value is typically set to the time that your 99% statement can run. For example, my slow query settings
is 0.1, then this is set to 10. Also available through
SELECT SUM (stalled_queries_executed)/sum (queries_executed) from INFORMATION_SCHEMA. Tp_thread_group_stats, to obtain the proportion of stalled, this value as small as possible, in order to avoid stall, you can increase the value of thread_pool_stall_limit.

Thread_pool_prio_kickup_timer:
This value affects the low-priority statements queue. The value of the parameter is in milliseconds, and the low-priority statement needs to wait for a few milliseconds before it can be moved to a high-priority queue. The default is 1000, which is 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 the low-prority queue and the 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 limits the maximum number of sleeping thread in the thread pool. This restricts 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 restriction on the sleeping thread. Assuming a value of N, when n is greater than 0, it means 1 consumer thread and N-1 reserve thread. This means that when a thread finishes executing a statement and is going to sleeping state, the sleeping state
The number of threads has reached the maximum allowable sleeping thread, then the thread will exit.
About consumer thread:sleeping thread consists of consumer thread and reserve thread, thread pool allows sleeping thread to have a consumer thread, 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 to be awakened, if consumer thread is present, the priority is to wake consumer thread, if consumer Thread does not exist, then wake 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, and more aggressively increases the number of concurrent algorithms, sometimes better than the optimal number of threads, but as the connection increases, performance decreases. So this parameter is mainly used in the experimental environment.

MySQL thread pool tuning notes