How to achieve connection persistence in database in PHP

This article will introduce to you how to achieve connection persistence in the database in PHP. It has certain reference value. Friends in need can refer to it. I hope it will be helpful to everyone.

Database optimization is our top priority in web development, and in many cases we are actually programming for databases. Of course, all user operations and behaviors are saved in the form of data. Among them, is there anything that can be optimized in the database connection creation process? The answer is of course yes. Languages ​​such as Java have connection pool settings, but PHP does not have such a thing as a connection pool in ordinary development. The connection pool technology is often used when multi-threading is involved, so PHP A new connection will be created every time it is run, so in this case, how do we optimize the data connection?

What is database connection persistence

Let’s first look at the definition of database connection persistence.

A persistent database connection refers to a connection that is not closed when the script ends running. When a persistent connection request is received. PHP will check whether there is already an identical persistent connection (that was opened previously). If it exists, the connection will be used directly; if it does not exist, a new connection will be established. The so-called "same" connection refers to a connection to the same host using the same user name and password.

Readers who do not fully understand the work of a web server and distributed load may misunderstand the role of persistent connections. In particular, persistent connections do not provide the ability to establish a "user session" on the same connection, nor do they provide the ability to effectively establish a transaction. In fact, strictly speaking, persistent connections do not provide any special functionality that non-persistent connections cannot provide.

This is connection persistence in PHP, but it also points out that persistent connections do not provide any special features that non-persistent connections cannot provide. This is very confusing. Isn't it said that this solution can improve performance?

What is the use of connection persistence?

Yes, judging from the special functions pointed out in the above definition, persistent connections do not bring new or more advanced functions, but its greatest use is to improve efficiency, that is, performance will Brings improvement.

When the connection cost (Overhead) created by the Web Server to the SQL server is high (for example, it takes a long time and consumes more temporary memory), the persistent connection will be more efficient.

That is to say, when the connection cost is high, the cost of creating a database connection will be greater, and of course the time will be longer. After using persistent connections, each child process only performs a connection operation once in its life cycle, instead of making a connection request to the SQL server every time it processes a page. This means that each child process will establish its own independent persistent connection to the server.

For example, if 20 different child processes run a script to establish a persistent SQL server persistent connection, then 20 different persistent connections are actually established to the SQL server, one for each process.

Efficiency comparison

Without further ado, let’s compare directly through the code. First, we define a statistical function to return the current millisecond time. In addition, we also need to prepare the data connection parameters.

function getmicrotime()
{
    list($usec, $sec) = explode(" ", microtime());
    return ((float) $usec + (float) $sec);
}

$db = [
    'server' => 'localhost:3306',
    'user' => 'root',
    'password' => '',
    'database' => 'blog_test',
];

Next, we first use ordinary mysqli for testing.

$startTime = getmicrotime();
for ($i = 0; $i < 1000; $i++) {
    $mysqli = new mysqli($db["server"], $db["user"], $db["password"], $db["database"]); //持久连接
    $mysqli->close();
}
echo bcsub(getmicrotime(), $startTime, 10), PHP_EOL;
// 6.5814000000

In the process of creating a database connection through 1000 cycles, we spent more than 6 seconds. Next, we use persistent connections to create these 1,000 database connections. Just add a p: before the $host parameter of mysqli.

$startTime = getmicrotime();
for ($i = 0; $i < 1000; $i++) {
    $mysqli = new mysqli(&#39;p:&#39; . $db["server"], $db["user"], $db["password"], $db["database"]); //持久连接
    $mysqli->close();
}
echo bcsub(getmicrotime(), $startTime, 10), PHP_EOL;
// 0.0965000000

From the perspective of mysqli connection, the efficiency improvement is very obvious. Of course, the PDO database connection also provides the property of establishing a persistent connection.

$startTime = getmicrotime();
for ($i = 0; $i < 1000; $i++) {
    $pdo = new PDO("mysql:dbname={$db[&#39;database&#39;]};host={$db[&#39;server&#39;]}", $db[&#39;user&#39;], $db[&#39;password&#39;]);
}
echo bcsub(getmicrotime(), $startTime, 10), PHP_EOL;
// 6.6171000000

$startTime = getmicrotime();
for ($i = 0; $i < 1000; $i++) {
    $pdo = new PDO("mysql:dbname={$db[&#39;database&#39;]};host={$db[&#39;server&#39;]}", $db[&#39;user&#39;], $db[&#39;password&#39;], [PDO::ATTR_PERSISTENT => true]); //持久连接
}
echo bcsub(getmicrotime(), $startTime, 10), PHP_EOL;
// 0.0398000000

When connecting in PDO mode, you need to give a PDO::ATTR_PERSISTENT parameter and set it to true. In this way, the connection established by PDO also becomes a persistent connection.

Note

Since the persistent connection of the database is so powerful, why not default to this persistent connection form, and do we need to manually add parameters to achieve it? PHP developers certainly still have concerns.

If the number of child processes with persistent connections exceeds the set limit on the number of database connections, the system will cause some problems. If the database has a limit of 16 simultaneous connections, and in the case of a busy session, 17 threads try to connect, then one thread will fail to connect. If at this time, an error occurs in the script that prevents the connection from being closed (such as an infinite loop), the 16 connections to the database will be quickly affected.

At the same time, table locks and transactions also need attention.

When using a data table lock in a persistent connection, if the script cannot release the data table lock for any reason, subsequent scripts using the same connection will be permanently blocked, requiring the httpd service or database to be restarted. Service

When using transaction processing, if the script ends before the transaction blocking occurs, the blocking will also affect the next script using the same connection

So, when using table locks and transactions, it is best not to use persistent database connections. Fortunately, persistent connections and ordinary connections can be interchanged at any time. We define two connection forms and use different connections in different situations to solve similar problems.

Recommended: "2021 PHP interview questions summary (collection)" "php video tutorial"

The above is the detailed content of How to achieve connection persistence in database in PHP. For more information, please follow other related articles on the PHP Chinese website!