What is mysql connection pool

In mysql, the connection pool creates a certain number of database connections when the program starts, and puts these connections into a pool for management; the program dynamically applies for, uses, and releases connections. Reasons for using database connection pool: 1. Resource reuse, which increases the stability of system operation on the basis of reducing system resource consumption; 2. Faster response speed; unified connection management to avoid database connection leakage.

The operating environment of this tutorial: windows7 system, mysql8 version, Dell G3 computer.

1. Pooling technology

The connection pool is the buffer of the connection object, which will store some connections. When the program When you need to use a connection, if it exists in the connection pool, it will be obtained directly from the connection pool without re-creating the connection. Connection pooling allows programs to reuse connections.

Pooling technology can reduce the number of object creations and improve the response performance of the program. Especially in high-concurrency scenarios, the effect is more obvious. When the creation of an object requires a large amount of resources, resulting in a long creation time, you can consider using pooling technology to cache it for subsequent reuse. Common pooling components include: memory pool, thread pool, connection pool, etc.

2. What is a database connection pool?

Definition: The database connection pool creates a certain number of database connections when the program starts. These connections are put into a pool for management. The application, use and release of connections are dynamically performed by the program. Note that the database does not only refer to Mysql, but also a connection pool can be designed for Redis.

3. Why use database connection pool

• Resource reuse. Avoiding the performance overhead caused by frequent creation and destruction, reduces system resource consumption, and increases the stability of system operation, mainly reducing memory fragmentation and temporary creation of threads or processes.

• Faster response speed. Since several connections are prepared when the program starts, business requests can be used directly. There is no need to perform operations such as connection creation, permission verification, and destruction in real time, thus reducing the system's response time.

• Uniform connection management to avoid database connection leakage. The timeout period for connection occupation can be preset. If a certain connection is occupied for more than the set value, the connection can be forcibly recycled.

4. The establishment process of Mysql database connection

• The client initiates a connection request, TCP three-way handshake

• Mysql internal permission verification

• SQL execution statement

• Mysql shutdown

• Disconnect, TCP waves four times

4.1 No connection pooling

Every time a SQL statement is executed, a connection needs to be established to perform operations such as TCP three-way handshake, permission verification, database operations, database user logout, four waves, etc.

Advantages: Simple implementation, no need to design a connection pool;
Disadvantages: The application frequently creates and destroys temporary connection objects, resulting in a large amount of memory fragmentation. In addition, during the connection A large number of TIME_WAIT states will appear after closing.

4.2 Use connection pool

When the program starts, it creates a number of backup connections, and each time SQL obtains the available connection operations, quack quick.

##5. The operating principle of the connection pool

• From The connection pool obtains a connection or creates a connection;

• Use the connection and return it to the connection pool after use;

• Close all connections before the system is shut down. And release resources

6. Relationship between thread pool and connection pool

General thread pool The number is consistent with the number of connection pools, and the thread returns the connection after using it.

Difference:

The thread pool actively performs tasks
• The connection pool is used passively. A connection can only be applied for and used by one thread.

[Related recommendations:

mysql video tutorial]

The above is the detailed content of What is mysql connection pool. For more information, please follow other related articles on the PHP Chinese website!