MySQL thread pool and connection pool

thread pool and connection pool

Connection pooling is typically implemented on the client side, meaning that the application (client) creates a pre-created connection that leverages these connections to serve all DB requests from the client. If, at some point, the number of idle connections is less than the number of DB requests, you need to queue the request and wait for the idle connection to process. Connection pooling allows you to reuse connections, avoid frequent creation and release of connections, reduce the average response time of requests, and, when requests are busy, queue up requests to buffer the application's impact on the db. thread pool implementation on the server side, by creating a certain number of thread service DB requests, the minimum unit of the thread pool service is a statement, that is, a thread can correspond to multiple active connections, relative to the one-conection-per-thread of a thread serving a connection. Through the thread pool, you can control the number of server-side service threads to a certain extent, reduce the competition of system resources and the consumption of thread context switch, and avoid high concurrency problems caused by high connection number. Connection pooling and thread pooling complement each other through connection pooling to reduce the creation and release of connections, increase the average response time of requests, and control the number of DB connections for an application, but with no control over the size of connections across the entire application cluster, resulting in high connections and a good deal of high connections through the thread pool, Ensure the server side can provide stable service. As shown in 2, each Web-server side maintains 3 connected pools of connections, and each connection to the connection pool is not actually a worker of the exclusive db-server, but may be shared with other connections. This assumes that Db-server has only 3 group, each group has only one worker, and each worker processes 2 connection requests.

Http://www.111cn.net/database/mysql/66375.htm

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 and connection pool