Analysis of MARIADB Thread pool

For MySQL5.5, only the Enterprise version contains the Thread Pool, but fortunately mariadb 5.1 already exists, and MARIADB 5.5 has been improved.

This article introduces the FAQ: later will release its working principle and use.

The thread handling model (thread pool) has been added to 5.5.16 in the commercial version to address multiple customer connectivity issues. Instead of a session one thread of the problem. This feature has been implemented in MARIADB 5.1-5.3, optimized in MARIADB 5.5, dynamic thread pool, etc., this and commercial version has a little difference, specifically view https://kb.askmonty.org/en/thread-pool-in-mariadb-55/

Applicable threadpool Scenario: The query is relatively short, or the CPU is more tight. Or you can use ThreadPool to limit the number of threads to save memory.

Installation conditions: Linux kernel requires more than 2.6.9 version

The effect may not be ideal in some situations:

1, a sudden workload, a long period of inactivity and a lot of short-term high active connection, this is to be able to adjust the waiting time to deal with such a situation. Thread_pool_idle_timeout

2, a lot of concurrency, and a long time to execute the query, a thread execution is recreated, and will not wait for ThreadPool release resources, this appearance in the Data warehouse scene. long-running queries block the execution of other queries, with blocking monitoring or other preemption mechanisms that can be blocked for a fixed length of time

3. Some simple queries are always executed quickly. No matter how high your machine is loaded, query may queue up when you have threadpool, such as select 1; it may consume more time than thread-per-connection.

FAQ:

Problem solved by thread pool:

1, too many thread stacks, almost to the CPU cache useless, thread pool can reduce CPU footprint memory space.

2. Creating too many threads can lead to too much context switching, and the solution is to maintain a group of less than the number of client connections, balance the MySQL connection and maintain high performance.

3, too many transaction concurrent execution causes the resource contention, lengthens the hot lock contention

How does the thread pool limit the control of concurrent session threads and transactions to achieve optimal performance and throughput?

The thread pool uses a step-by-step approach to limit and balance the concurrency, which separates the connections from the threads, so that thread executes the statement accepted from connections, and the two are not bound together. The thread pool handles the connection based on the configured thread group that is prioritized.

What is the difference between a thread pool and a client connection pool:

The duties of both are not the same:

Client connection pooling: On the client side, clients are not constantly connected or disconnected from the server, and are designed to cache idle connections for use by other connections. Reduce the cost of establishing and dismantling connections. Client pool is not visible to query processing power and the load on the backend database. In contrast, the thread pool is at the end of MySQL. They are designed to manage already established connections and received query

The thread pool is about how to dynamically expand when there are too many connections, how to shrink when the link is reduced, how the thread pool itself is controlled, whether it can take advantage of the OS native line pool directly to provide services, and so on.

Threads waiting on each other. If there is a long link and a short link exists, then the short link may have to wait until the long link has done all the work before it can be round and the execution time is not controllable.

MARIADB fully considered and optimized the implementation of thread pool, and provided a series of parameters for the user to adjust the configuration of the thread pool (thread_pool_size, Thread_pool_stall_limit, Thread_pool_idle_ Timeout, thread_pool_oversubscribe) and some state values (threadpool_threads,threadpool_idle_threads) are provided to the user for query thread pool usage. In terms of implementation, the MARIADB relative to the MySQL enterprise thread pool uses the thread pool provided by the native system and optimizes the use of the multipath io (IO multiplexing) interface, taking into account the characteristics of each system OS. Specific information can refer to: http://kb.askmonty.org/en/thread-pool-in-mariadb-55/

This article is from the "Technology Achievement Dream" blog, please be sure to keep this source http://weipengfei.blog.51cto.com/1511707/1163017

See more highlights of this column: http://www.bianceng.cnhttp://www.bianceng.cn/database/extra/