MySQL thread pool series one (Thread_pool and Connection_pool)

Thread_pool and Connection_pool

When the client requests a large amount of data, using the thread pool can save a lot of system resources, so that more CPU time and memory can be effectively utilized. And the use of database connection pool will greatly improve the efficiency of the program, at the same time, we can through its own management mechanism to monitor the number of database connections, usage, and so on. In this article we mainly introduce the principle of thread pool and database connection pool, and then we will look at this part of the content.

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

The principle of thread pool:

In fact, the thread pool principle is very simple, similar to the concept of buffer in the operating system, its flow is as follows: Start a number of threads, and let these threads are asleep, when the client has a new request, will wake up a thread pool of a sleep thread, let it handle the client's request, When the request is processed, the thread is asleep again. Maybe you might ask: why bother if I create a new thread whenever the client has a new request? This may be a good idea because it makes it easier for you to write code, but you ignore an important question? That's performance! Take my unit, my unit is a centralized data center of the bank network, the peak of the client requests per second more than 100 concurrent, if you create a new thread for each client request, then the CPU time and memory will be amazing, if the use of a thread pool with 200 threads, That would save a lot of system resources, allowing more CPU time and memory to handle real business applications, rather than frequent thread creation and destruction.

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.

thread pool improve?
According to Oracle MySQL's official performance test

* show global Status like '%threads_running% '; the value is MySQL The number of paths that the server is currently concurrently executing statements, and if this value is kept at around 40, 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 work is made up of a lot of short connections, then using a thread pool is useful.

The thread pool solves several problems:?

* 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.

What is the difference between MySQL thread pool and client side connection pool?
Connection pool of client segments: 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 operation of the thread pool is on the MySQL server sideAnd is designed to manage the current concurrent connections and queries.

Connection pool: On the client side

Thread pool: On the MySQL server side

The principle of connection pool:

Database connectivity is a critical, limited, and expensive resource that is particularly prominent in multi-user Web applications.

A database Connection object corresponds to a physical database connection, each of which opens a physical connection and closes the connection after use, which results in poor performance of the system. The solution to a database connection pool is to establish enough database connections when the application starts, and to make these connections a pool of connections (simply put a lot of semi-finished database join objects in a "pool"), and the application dynamically requests, uses, and frees the connections in the pool. For concurrent requests that are more than the number of connections in the connection pool, they should be queued in the request queue. And the application can dynamically increase or decrease the number of connections in the pool based on the utilization of the connections in the pool.

Connection pooling technology uses as much memory resources as possible, greatly saving memory, improving Server service efficiency, and supporting more customer service. The use of connection pooling will greatly improve the efficiency of the program, while we can monitor the number of database connections, usage, etc. through its own management mechanism.

1) The minimum number of connections is the connection pool has been maintained database connection, so if the application to the database connection is not used, there will be a large number of database connection resources are wasted;

2) The maximum number of connections is the maximum number of connections the connection pool can request, and if the database connection request exceeds this number, subsequent database connection requests are added to the wait queue, which affects subsequent database operations.

This article is from the "Crazy_sir" blog, make sure to keep this source http://douya.blog.51cto.com/6173221/1597207

MySQL thread pool series one (Thread_pool and Connection_pool)