English
English
In web development, especially when working with PHP and MySQL databases, the mysql_close() function is used to close database connections. While closing a connection releases resources, frequent calls to mysql_close()—particularly under high concurrency with many requests in a short time—can cause unnecessary performance overhead, as it leads to repetitive connection creation and termination, impacting system response time.
To address this issue, the connection pooling mechanism has become a common optimization approach. This article explains how to reduce frequent mysql_close() calls in PHP web applications through the use of connection pooling, thereby improving system performance and stability.
Each time a database connection is established, it involves network communication, authentication, and resource allocation. When the connection is closed and reestablished for the next request, it results in wasted time and resources. A high volume of requests within a short timeframe can lead to:
Frequent connection creation and teardown, increasing CPU and memory load.
Added stress on the database server, potentially exhausting the connection pool.
Slower response times, degrading user experience.
Therefore, reducing the number of mysql_close() calls and reusing database connections efficiently is critical.
A connection pool is a container that maintains a predefined number of database connections ready for reuse. Applications obtain a connection from the pool, and instead of closing it after use, return it to the pool for the next request to reuse.
Benefits of using a connection pool include:
Reduced frequency of connection creation and closure.
Lower server load on the database.
Faster response times and improved concurrency handling.
PHP’s typical web request model is short-lived, with all resources, including database connections, released at the end of each request. This nature inherently conflicts with connection pooling. Possible solutions include:
Persistent Connections: PHP offers the mysql_pconnect() function to create persistent connections, avoiding repeated connection setup and teardown.
Middleware or Extensions: Use PDO with middleware that supports connection pooling, or frameworks like Swoole and Workerman that support long-lived connections.
Application-Level Pooling: Manage connections using caching systems or shared memory to simulate pooling.
The following section focuses on using mysql_pconnect() to create simple persistent connections and reduce mysql_close() calls.
<?php
// Connect to the database using a persistent connection
$link = mysql_pconnect('m66.net', 'username', 'password');
if (!$link) {
die('Could not connect: ' . mysql_error());
}
<p>// Select the database<br>
mysql_select_db('database_name', $link);</p>
<p>// Execute a query<br>
$result = mysql_query('SELECT * FROM table_name', $link);</p>
<p>while ($row = mysql_fetch_assoc($result)) {<br>
echo $row['column_name'] . '<br>';<br>
}</p>
<p>// Note: With persistent connections, there is no need to call mysql_close()<br>
// mysql_close($link); // Not calling this allows the connection to be reused<br>
?><br>
In the example above, mysql_pconnect() creates a persistent connection that is not closed at the end of the script. Instead, PHP manages and reuses it for subsequent requests, avoiding repeated calls to mysql_close() and unnecessary reconnections.
Resource Management: While persistent connections improve performance, they can lead to connections occupying database resources for extended periods. Proper database configuration for maximum connections is essential.
Compatibility: The mysql_* functions are deprecated. It is recommended to use mysqli or PDO, which also support persistent connections.
Advanced Pooling Solutions: For projects with high concurrency demands, it is advisable to use middleware or server-side frameworks like Swoole that support efficient connection pooling in conjunction with PDO.
Example: Using PDO with persistent connections
<?php
$dsn = 'mysql:host=m66.net;dbname=database_name;charset=utf8';
$options = [
PDO::ATTR_PERSISTENT => true, // Enable persistent connection
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
];
<p>try {<br>
$pdo = new PDO($dsn, 'username', 'password', $options);<br>
$stmt = $pdo->query('SELECT * FROM table_name');<br>
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {<br>
echo $row['column_name'] . '<br>';<br>
}<br>
} catch (PDOException $e) {<br>
echo 'Connection failed: ' . $e->getMessage();<br>
}<br>
?><br>
By using connection pooling mechanisms—especially persistent connections—PHP web applications can significantly reduce the frequency of mysql_close() calls. This improves connection reuse, lowers system overhead, and enhances performance and scalability.
Although mysql_pconnect() provides a simple way to implement persistent connections, for real-world projects it’s better to use modern extensions like mysqli or PDO in conjunction with middleware or server frameworks to achieve more robust connection pool management.
