Shulou(Shulou.com)06/02 Report--

This article introduces how to achieve database connection persistence in PHP, the content is very detailed, interested friends can refer to, hope to be helpful to you.

Database connection persistence in PHP

Database optimization is the top priority for us to do web development, and in many cases we are actually programming for the database. Of course, all the actions and behaviors of users are saved in the form of data. Is there anything that can be optimized in the connection creation process of the database? Of course, the answer is yes. Java and other languages have connection pooling settings, but PHP does not have connection pooling in general development, and connection pooling technology is often used when multithreading is involved, so PHP creates new connections every time it runs, so in this case, how can we optimize data connections?

What is database connection persistence

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

Persistent database connections are connections that are not closed at the end of the script. When a request for a persistent connection is received. PHP will check to see if an identical persistent connection (which was previously opened) already exists. If it exists, the connection will be used directly; if it does not, a new connection will be established. The so-called "same" connection is a connection to the same host with the same user name and password.

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

This is connection persistence in PHP, but it also points out that persistent connections do not provide any special functionality that non-persistent connections cannot. This is very confusing, isn't it said that this scheme can bring about a performance improvement?

What is the use of connection persistence?

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

Persistent connections are more efficient when the connection that Web Server creates to the SQL server is expensive (Overhead), such as taking longer and consuming more temporary memory.

In other words, when the connection is expensive, the more expensive it is to create a database connection, and the longer it takes. After using persistent connections, each child process makes a connection operation only once in its life cycle, rather than making a connection request to the SQL server each time a page is processed. That is, each child process will establish its own independent persistent connection to the server.

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

Efficiency comparison

Without saying much, let's compare it directly through the code. First, we define a statistical function that returns the current millisecond time. In addition, we also 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'

]

Next, let's test it with a normal mysqli.

$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

It took us more than 6 seconds to create a connection to the database in 1000 cycles. Next, we use persistent connections to create these 1000 database connections. You just need to add a p: before 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"]); //持久连接 $mysqli->

Close ()

}

Echo bcsub (getmicrotime (), $startTime, 10), PHP_EOL

/ / 0.0965000000

From the point of view of the connection of mysqli, the efficiency improvement is very obvious. Of course, PDO-style database connections also provide properties for establishing 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

When connecting in PDO mode, you need to give a PDO::ATTR_PERSISTENT parameter and set it to true. This makes the connection established by PDO a persistent connection.

Be careful

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

If the number of child processes of persistent connections exceeds the set limit of database connections, the system will have some problems. If the number of simultaneous connections to the database is limited to 16, and in the case of a busy session, 17 threads try to connect, then one thread will be unable to connect. If there is an error 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 to be noted.

When using a datasheet lock in a persistent connection, if the script cannot release the Datasheet lock for whatever reason, the subsequent script that uses the same connection will be persistently blocked, making it necessary to restart the httpd service or database service.

When using a transaction, if the script ends before the transaction blocking occurs, the blocking also affects the next script that uses the same connection

Therefore, in the case of table locks and transactions, it is best not to use persistent database connections. But the good news is that persistent connections and ordinary connections are interchangeable at any time. We define two forms of connections, and we can solve similar problems by using different connections in different situations.

On how to achieve database connection persistence in PHP to share here, I hope the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.