MySQL thread pool Series One: What is a thread pool and connection pooling (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.

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.

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

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: What is a thread pool and connection pooling (Thread_pool and Connection_pool)