MySQL learning to share the --Thread pool implementation

Recommended for you: Get network issues from WhatsUp Gold. Not end users.

Based on the "MySQL --Thread pool" learning to share on Thread pool architecture design details, this paper implements the Thread pool were analyzed, and according to Mariadb and Percona to provide open source implementation, compare and evaluate the simple.

1, The Thread Pool Plugin

MySQL official documents, on the Thread pool are introduced in detail, mainly from the implementation and use of angle are described. MySQL Enterprise Edition realized through plug-ins, no invasion to the source logic, is a friendly way. The mechanism to achieve document describes the architecture design, with no entry.

Among them, the thread_pool_size thread of control group size, thread_pool_stall_limit mark SQL abnormalities, thread_pool_prio_kickup_timer control user thread priority transformation.

According to the document to provide information, as well as the thought of architecture design, abstracted the following processing logic:

1.1 No executing and empty queue

In the current thread group, has no SQL statement for the current execution, no waiting for the user thread in the queue, the current user request SQL immediately the thread of execution within a group of monitor thread. The specific logic as shown below:

 

Figure 1.1 No executing and empty queue

The picture is a thread within the group process, each thread group has two priority queue (high/low priority queue), no SQL current execution and the queue is empty, the new user connection requests, the thread of monitoring will be the immediate implementation of SQL to DB.

1.2 Low priority queue

The high priority queue thread within the group is empty, the low priority queue is not empty, the thread of monitoring will request the user from the low priority queue, the implementation of SQL in the DB. The processing logic as shown below:

 

Figure 1.2 Low priority queue

In particular, if a transaction with a plurality of SQL, when the first SQL from low priority queue starts, other SQL statements in the transaction, all entered the high priority queue.

1.3 High priority queue

In the current thread group, if the high priority queue user requests, monitor thread priority of the user from the high priority queue request, give priority to the implementation of SQL to DB. High priority queue is executed, and then from low priority queue of user requests.

 

Figure 1.3 High priority queue

In addition, the parameter thread_pool_high_priority_connection will all current SQL session migration to the high priority queue.

1.4 Priority kickup timer

In the low priority queue of user requests over the thread_pool_prio_kickup_timer time, will be moved to a high priority queue from low priority queue. Processing logic specific as shown below:

 

Figure 1.4 Priority kickup timer

1.5 Stall limit

When the thread of monitoring due to the large transaction blocking, more than the thread_pool_stall_limit parameter limit, monitoring thread to continue, at the same time to start a new thread of monitoring, from the queue to continue execution. The logic is as follows:

 

Figure 1.5 Stall limit

Through the above analysis, the thread_pool_stall_limit and thread_pool_prio_kickup_timer parameters are the main parameters of performance optimization. If thread_pool_stall_limit is set and a business scenario does not match, will lead to a single thread group within a large number of threads of execution, it will lead to continued to create the listener thread, and the thread switch amount. The thread_pool_prio_kickup_timer parameter setting is not reasonable, will result in frequent from low priority queue migration to high priority queue, consume a large amount of system resources. The performance optimization, need to test and set according to the business model and scene.

2, Thread pool in MariaDB 5.5

MariaDB 5.5 introduces Thread pool, design thought and MySQL Enterprise Edition almost unanimously. MariaDB embedded in the Server layer, without the use of plug-in way, this is a major difference to the implementation of. MariaDB through the thread_handling set the thread type transformation method of thread pool and one-to-one mode. From the implementation point of view, through the configuration file to set the parameters, can not be dynamically adjusted.

The design adopts the thread group, but the MariaDB will achieve a further refinement, maximum thread parameter added thread_pool_max_threads control thread pool number. In addition, according to the different system, using the IO reuse in different ways, to improve performance.

Both the thread_pool_stall_limit parameter to avoid high transaction blocking problem. No Low/High priority queue however, no transformation between thread_pool_prio_kickup_timer parameters affecting the queue. MariaDB through thread_pool_stall_limit to avoid blocking high transaction, concurrency and does not limit the thread, so do not need priority queue, to prevent hunger affairs continues to not be executed. From this point of view, MySQL design more reasonable point, when blocking in large transaction, concurrency number is more, because to seize resources reduced performance, and the small business as blocking, not responding to.

MariaDB into the extra-port and extra-max-connections parameters, is used to prevent the not connected resources, super users can log on to carry on the management operation.

3, Thread pool

Percona Thread pool is based on MariaDB, and the design idea of MySQL, optimized and improved.

Percona reference MySQL priority queue, the queue is divided into a high priority queue and queue. The introduction of thread_pool_high_prio_tickets parameters, when some conditions are satisfied, the user request from a common queue moved to the high priority queue. It is the MariaDB analysis, the goal is to reduce the number of concurrent user connections.

Conclusion

Through the above analysis, the Thread pool, design basically consistent. From the implementation point of view, MySQL Enterprise Edition is realized by using Plugin mode, can the dynamic operation, does not affect the normal logic. While MariaDB and Percona adopt buildin mode, through the configuration file, not flexible enough. From the implementation details, MariaDB apart from the priority queue, and MySQL enterprise edition thought the same, but in many ways are optimized and fine. Percona implementation based on MariaDB, refer to the MySQL enterprise edition of the priority queue, optimized and improved.

Overall, the Percona Thread pool is an open source implementation of several problems mentioned, the design idea and MySQL Enterprise Edition, solve are very good, should be to use and understand the best choice for Thread pool implementation.

Reference resources

1, The thread pool plugin : http://mikaelronstrom.blogspot.com/2011/10/mysql-thread-pool-problem-definition.html

2, Thread pool operation : http://mikaelronstrom.blogspot.com/2011/10/mysql-thread-pool-scalability-solution.html

3, Thread pool tuning : http://mikaelronstrom.blogspot.com/2011/10/mysql-thread-pool-limiting-number-of.html

4, Thread pool in MariaDB 5.5 :

5, Thread pool

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download

Posted by Sheila at January 25, 2014 - 2:19 AM