Database connection persistence in PHP

Database optimization is the top priority of our Web development, even in many cases we are actually database oriented programming. Of course, all operations and behaviors of users are saved in the form of data. Is there anything about the database connection creation process that can be optimized? Of course, the answer is yes, with the connection pool Settings in Java language, and PHP in the normal development and there is no such thing as a connection pool, in the case of involve multithreading often will use the connection pool technology, so each time you run PHP will create a new connection, so in this case, we how to optimize data connection?

What is database connection persistence

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

Persistent database connections are those that do not close when the script ends running. When a persistent connection request is received. PHP checks to see if the same persistent connection already exists. If it exists, the connection is used directly; If not, a new connection is established. An “identical” connection is a connection to the same host with the same username and password.

Readers without a complete understanding of web server work and distributed load may misunderstand the role of persistent connections. In particular, persistent connections do not provide the ability to establish “user sessions” over the same connection, nor do they provide the ability to establish transactions efficiently. In fact, strictly speaking, persistent connections do not provide any special functionality that non-persistent connections do not.

This is connection persistence in PHP, but it also points out that persistent connections do not provide any special functionality that non-persistent connections do not. This is very confusing, isn’t it agreed that this solution will bring performance improvement?

What is the use of connection persistence?

It is true that persistent connections do not offer new or more advanced functionality in terms of the specific capabilities identified in the above definition, but the biggest benefit is that they increase efficiency, that is, performance.

Persistent connections are more efficient when the Web Server has high Overhead to create a connection to the SQL Server.

That is, when the connection cost is high, the cost and time to create a database connection will be higher. With persistent connections, each child process makes a connection only once in its lifetime, rather than 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 connection, there are actually 20 different persistent connections to the SQL server, one for each process.

The efficiency of contrast

So without further ado, let’s go straight to the code. First, we define a statistical function that returns the current millisecond time. In addition, we need to prepare the connection parameters of the data.

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

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

Next, let’s test with plain mysqli.

$startTime = getmicrotime();
for ($i = 0; $i < 1000; $i{+ +)$mysqli = new mysqli($db["server"].$db["user"].$db["password"].$db["database"]); // Persistent connection
    $mysqli->close();
}
echo bcsub(getmicrotime(), $startTime.10), PHP_EOL;
/ / 6.5814000000
Copy the code

It took us more than 6 seconds to create a connection to the database in 1000 cycles. Next we use persistent connections to create 1000 database connections. Just add a p: to the $host parameter of mysqli.

$startTime = getmicrotime();
for ($i = 0; $i < 1000; $i{+ +)$mysqli = new mysqli('p:' . $db["server"].$db["user"].$db["password"].$db["database"]); // Persistent connection
    $mysqli->close();
}
echo bcsub(getmicrotime(), $startTime.10), PHP_EOL;
/ / 0.0965000000
Copy the code

From the mysqli connection, the efficiency improvement is very obvious. Of course, PDO database connections also provide properties to establish persistent connections.

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

$startTime = getmicrotime();
for ($i = 0; $i < 1000; $i{+ +)$pdo = new PDO("mysql:dbname={$db['database']}; host={$db['server']}".$db['user'].$db['password'], [PDO::ATTR_PERSISTENT => true]); // Persistent connection
}
echo bcsub(getmicrotime(), $startTime.10), PHP_EOL;
/ / 0.0398000000
Copy the code

To connect in PDO mode, give a PDO::ATTR_PERSISTENT parameter and set it to true. This makes the connection established by PDO also persistent.

Pay attention to

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

If the number of persistent child processes exceeds the set database connection limit, the system will have some problems. If the database has a simultaneous connection limit of 16 and 17 threads attempt to connect in a busy session, one thread will not be able to connect. If, at this point, an error occurs in the script that prevents the connection from being closed (such as an infinite loop), the database’s 16 connections will be quickly affected.

There are also some caveats to table locks and transactions.

If a script that uses a table lock on a persistent connection fails to release the table lock for whatever reason, subsequent scripts that use the same connection will be permanently blocked, causing the HTTPD service or database service to be restarted

When using transactions, if a script ends before a transaction block occurs, the block also affects the next script using the same connection

Therefore, it is best not to use persistent database connections when using table locks and transactions. However, the good news is that persistent and normal connections can be interchangeable at any time, so we can define two forms of connection and use different connections in different situations to solve similar problems.

conclusion

There are always two sides to a persistent connection. On the one hand, it brings efficiency gains, but on the other hand, it can bring business logic problems that can be very difficult to solve without understanding the mechanism of a persistent connection. Therefore, in daily development, we must understand the relevant functions and characteristics of the situation and then choose the appropriate way to complete the required function development.

Test code:

Github.com/zhangyue050…

Reference Documents:

www.php.net/manual/zh/f…