MySQL learning to share the --Thread pool

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

Just experienced Taobao double 11, really feel the tense atmosphere. Although through DB calm, but in the process of twists and turns, can be struck with fright. The MySQL in a large number of connections (million) scenarios, performance is far less than expected, and the obvious fluctuation, became a very important problem. Although the solution, but to explore the thinking and method of follow-up, continued. The following is to increase the Thread pool in the MySQL layer, to solve a number of problems connected.

1, MySQL Thread Pool: Problem Definition

MySQL enterprise edition in 5.5, the introduction of the Thread pool scheme. This paper mainly analyzes the existing problems and the introduction of Thread mode, pool mode and demand. Mainly to solve the traditional one-to-one MySQL design (a connection using a thread service) there are two aspects of problem:

1.1 CPU resource constraints

Very much in the number of connections, combined with larger, CPU available resources will become the MySQL connection thread bottleneck, resulting in reduced MySQL performance. There are two reasons: one is the concurrent number is large, CPU cache hit rate, and concurrent thread creation consumes memory resources, may cause performance effects of swap MySQL; another reason is combined with larger, will lead to the critical resource, produce a lot of lock contention. These problems are difficult to solve in the system level, only through the application layer solution.

1.2 Innodb mutex lock

In the concurrent is large, will cause the Innodb mutex lock contention. The current solution is through the innodb_thread_concurrency parameter, but this parameter has its own lock contention, also affect the performance of MySQL. In the course of our optimization, also tries to optimize the parameters, to solve the complicated situation, the problem of low performance. But found in the testing process, there will be a large number of connection for kernel mutex locks, but continued stress can lead to MySQL thread running, ultimately leading to MySQL is not available.

Thread pool is mainly from four aspects: reducing SQL concurrency, so that sufficient resources; the use of the thread group, independent management; using a priority queue, limiting the concurrent execution of transactions; avoid deadlock.

In the above described the content, covering almost all the phenomena and problems we encountered, and the performance impact would be so serious, is unexpected for us.

2, MySQL Thread Pool: Scalability solution

Based on the definition above, this paper mainly discusses the Thread scalable pool solution. In order to solve the problem of the number of concurrent connections, usually considered creates a thread pool, but will introduce scalability problem: each connection or thread creation, will lock on the data structure for public use, modify state information or thread connection. This is tantamount to re introduce hot.

In order to avoid the introduction of new problems, Thread pool uses the thread group, each thread group management groups within the connection and thread, the thread group are not affected. And ensure that there is at most one active thread connecting each thread within the group, so the connection and thread is created, only a single thread within a group of data structure locking, the called method.

In response, it is very important to have a problem: the thread group, will appear when the waiting thread group, the request can not enter the blocking connection, and the problem will be solved by the timer mode. Another problem is: through the connection limit each user, and the MySQL layer through the innodb_thread_concurrency parameter control, to control the number of concurrent connections. For the second problem, when applied to certain heavyweight, limiting the number of user connections become more difficult. Assuming there are thousands of application server, application server and 1~2 database connection, will also appear the problem of too many concurrent connections. That is to say connections are limited by the user, not cure.

3, MySQL Thread Pool: Limiting number of concurrent statement executions

The above reply, then the thread group is introduced, the SQL statement may lead to the thread group of waiting, blocking a number of queries. This paper aimed at the problem, put forward to solve the timer mode. When the SQL query execution over timer, thread group will be set to unavailable.

In addition, when the query for the lock is blocked, the thread will wait for a timeout, and then deal with the new query request, so as to ensure the thread group available.

4, MySQL Thread Pool: Limiting number of concurrent transactions

For concurrency control, Thread pool uses a priority queue solution. In order to avoid big transactions cause the thread group is not available, the introduction of the thread_pool_prio_kickup_timer parameters, when the transaction more than parameter setting timeout, the waiting thread group of the query will be moved to the high priority queue. In order to avoid movement, Thread pool limit per thread group in 10ms within a mobile.

The priority queue, which can largely solve because the transaction blocking, and produce a large number of query time-out problems, but also solves the other thread group idle condition.

5, MySQL Thread Pool: When to use?

Principle and Realization of Thread pool were explained in detail in the above article, but what time? In this paper, according to the monitoring of threads_running value, to determine whether the use of Thread pool. When the threads_running value exceeds the processing ability of MySQL, Thread pool will protect MySQL server.

In addition, Thread pool is used together with the innodb_thread_concurrency parameter, the concurrency control can be better. Thread pool mainly in the Server layer, in restrictions before transaction, innodb_thread_concurrency control in a transaction.

In particular, if the query for short queries, Thread pool can be very effective. If the query for long queries, Thread pool has a performance impact.

6, MySQL Thread Pool vs. Connection Pool

For the second question the article 2 reply, this paper explains in detail the differences between both Thread pool and Connection pool. Thread pool MySQL is the main end take the thread of control, for the SQL concurrency limit MySQL actual processing. While Connection pool is in the client application to limit, to connect to the MySQL Server number of threads.

In some cases, Connection pool not all connections are active transaction, but will create a large number of connection in MySQL Server. By Thread pool, can reduce the number of connections to MySQL Server.

7, MySQL Thread Pool: Optimal configuration

This paper mainly introduces the parameters of Thread pool using a detailed description of the, Mainly includes: thread_pool_size specifies the thread pool size, The default value is 16; thread_pool_stall_limit execution thread pool timeout, The default value is 6 (60ms); thread_pool_prio_kickup_timer limit priority queue mobile timeout, The default value is 1000 (1s); thread_pool_algorithm the specified thread pool algorithm, Does not support; thread_pool_max_unused_threads to view the current maximum unused number of threads.

In actual use, according to the performance of the database server, use MySQL Server, and the application of query, adjusting these parameters, so as to achieve the best performance.

8, MySQL Thread Pool: Benchmarking

In this paper, through the pressure test, and compared before and after using Thread pool, performance. By the test results, the use of Thread pool in the concurrent connection number is large, performance can be stable at the higher level, and not due to the increased number of connections, leading to the performance of MySQL.

The official test results as shown below,

 

 



 

Conclusion

The introduction of Thread pool, the MySQL connection solves the traditional design in the way of one-to-one. In addition, the use of thread group, to avoid introducing new scalability problem; use the timer, solve the problems blocking a thread group long query; the priority queue, resolve issues.

From the test results, the Thread pool in the concurrent connection number is large, performance can be stable at the higher level, and not due to the increased number of connections, leading to the performance of MySQL.

Because the MySQL Enterprise Edition is not open source, can only be assessed through black box testing. But the implementation of the principles and strategies for the implementation, assessment of open source Thread pool, are very useful.

Reference resources

1, MySQL Thread Pool: Problem Definition :

2, MySQL Thread Pool: Scalability solution :

3, MySQL Thread Pool: Limiting number of concurrent statement executions :

4, MySQL Thread Pool: Limiting number of concurrent transactions :

5, MySQL Thread Pool: When to use? :

6, MySQL Thread Pool vs. Connection Pool :

7, MySQL Thread Pool: Optimal configuration :

8, MySQL Thread Pool: Benchmarking

 

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

Posted by Harriet at December 11, 2013 - 9:49 AM