How to Properly Close MySQL Connection Pools in PHP

In PHP web development, MySQL is the most commonly used relational database. To improve performance, developers often use a connection pool to manage database connections, and it is crucial to close connections in the pool correctly to avoid resource waste and performance degradation.

A connection pool is a collection of open database connections that can be reused, reducing the overhead of creating a new connection for each request. MySQL provides functions for creating and closing connections within a pool.

The basic process of creating and closing a connection is as follows:

First, call mysqli_connect() to create a connection:

<code>$conn = mysqli_connect($host, $user, $password, $database);</code>

Here $host is the MySQL server hostname, $user the username, $password the password, and $database the name of the database to connect to.

After performing database operations with this connection, close it using mysqli_close() :

<code>mysqli_close($conn);</code>

This basic closing process works for single connections, but when using a connection pool, the workflow changes slightly.

A pool creates a set of initialized connections when the application starts. Each request obtains a connection from the pool instead of creating a new one, and after use the connection should be returned to the pool rather than closed directly.

In PHP you can implement custom functions to acquire and release connections. Below is an example implementation:

<code>// 创建连接池
$pool = new mysqli_pool($host, $user, $password, $database);

// 获取连接
$conn = $pool->get_connection();

// 执行数据库操作

// 归还连接到连接池
$pool->release_connection($conn);
</code>

In this example, mysqli_pool is a custom pool class. The get_connection() method retrieves an available connection from the pool, while release_connection() returns the connection to the pool for future use.

At the end of a page request, it is important to ensure that all connections are properly closed. A common approach is to use a destructor that automatically closes every connection in the pool when the script terminates:

<code>function __destruct() {
    $this->close_all_connections();
}

function close_all_connections() {
    foreach ($this->pool as $conn) {
        mysqli_close($conn);
    }
}
</code>

The __destruct() method is invoked automatically when the page finishes executing, calling close_all_connections() , which iterates over the pool and closes each connection with mysqli_close() .

In summary, correctly closing connections in a MySQL connection pool is essential for releasing resources and maintaining performance. Using a custom pool class allows better management of connection acquisition and return, leading to more efficient database operations.