How to use Persistent Connection of PDO?

Question

I have the following code and freshed this webpage in Firefox for 5 times, then the MySQL showed me 5 connections. According to the PDO Manual,

Persistent connections are not closed at the end of the script, but are cached and re-used when another script requests a connection using the same credentials. The persistent connection cache allows you to avoid the overhead of establishing a new connection every time a script needs to talk to a database, resulting in a faster web application.

I have used the same credentials, but the number of MYSQL connection keep increasing. Even trying to close connection with $db = null could not close the connections. What's wrong of my code?

<?php
try {
 $dbh = new PDO('mysql:host=127.0.0.1;dbname=lingtong', 'root', 'xxxxxx', array(PDO::ATTR_PERSISTENT => true));
 foreach ($dbh->query('SELECT * from agent') as $row) 
  print_r($row);
 $dbh = null;
} catch (PDOException $e) {
 print "Error! : " . $e->getMessage() . "<br/>";
 die();
}

Solution 1

This question is very old but it will be okay if I contribute. I think you need to implement a singleton class for handling database connections I will write a sample class below ..

<?php
class DB{

//set the connection property to private to prevent direct access 
private static $conn;

//now since we dont want to reinstiate the class anytime we need it, lets also set the constructor to private 
private function __construct(){}

//now lets create our method for connecting to the database 
public static function connect(){

//now lets check if a connection exists already in our $conn property, then we should return it instead of recreating a new connection 
if(!empty(self::$conn)){
return self::$conn;
}//end if 

//upon reaching here means the $conn property is empty so lets create a new connection 

try {
 $dbh = new PDO('mysql:host=127.0.0.1;dbname=lingtong', 'root', 'xxxxxx', array(PDO::ATTR_PERSISTENT => true));

//lets now assign the database connection to our $conn property 
self::$conn = $dbh;

//return the connection 
return $dbh;

} catch (PDOException $e) {
 print "Error! : " . $e->getMessage() . "<br/>";
 die();
}

}//end method 

}//end class

?>

Our singleton class can make only one connection and reuse it,let see how we can use our class

<?php 
$dbh = DB::connect();

foreach ($dbh->query('SELECT * from agent') as $row){ 
  print_r($row);
}
?>

Solution 2

It seems you need to close the cursor and release (assign null) to the last pdo statement object as well to close the connection.

Also, what's important to understand about persistent connections is that they persist, but you are not guaranteed that you will neither:

• Be given back your last connection handler in further script executions
• Nor be reusing a previous connection, if it's still "busy". Busy may mean in a script, timing out etc... Connections instanciation may go on and on... See Fully Understanding PDO ATTR_PERSISTENT

Comments

[Mjh]

So what did you contribute with? You just created a singleton without explaining why nor did you touch on the subject of persistent connection. What does your code help with? Do you know what persistent connections are, why they are used and how does singleton help with that?

[Jonatan Cloutier]

@Mjh I'm I mistaken or you downvoted the wrong answer?

[Big Zak]

@Mjh I know what persistent connection is ,besides I added persistent support to the class, and more over , persistent connection and singleton class all aims at scalability , we are here to help each other with whats best and what might help others , again , the codes has been explained , thats i commented the codes, Thank you

[Mjh]

@JonatanCloutier - I didn't downvote the wrong answer. I downvoted yours because you don't know what persistent connection is, you assumed OP is using Apache and you provided a wrong answer. No offense meant.

[Mjh]

@razzbee - you implemented a singleton for handling database connection in the app. You didn't explain why it solves the problem that OP has. You didn't mention what a persistent connection is and how your class helps with the problem. I didn't vote on your answer because it's incomplete. Dropping some code without explaining why it's useful isn't really helpful, is it?

[Mjh]

Reasons for downvote: it's not relevant if someone is on localhost or not. Non-persistent connections spend file descriptors. Non-persistent connections initiate the connection handshake on every http request. 2) Apache or nginx persist the connection per thread and they perform the handshake once - that means every subsequent request won't have to go through this overhead. That's what you want. The reason why connections go up is because Apache is a really badly written piece of software that can't clean up properly. 3) WTF?! You just dropped random statement with 0 facts.

[Big Zak]

@Mjh , I get your point .. will do my best to explain my codes next time, besides I thought the comments would help

[Mjh]

Just update the answer with explanation and that's it, it's not a bad answer in itself :)

[Jonatan Cloutier]

@Mjh thanks, at least I can understand why it's downvoted, while I couldn't without comment

[Mjh]

The answer can be improved and edited, in which case you will have my upvote, and I suggest you add a few extra bits of information to it. The problem is that nothing is black and white, persistent connections have their use, non-persistent ones do as well. Persistent connections are best used with php-fpm, while non-persistent should be used with Apache and mod_php because Apache tends to screw up otherwise. FastCGI applications such as php-fpm are fast(er) because of persistent connections, not only towards MySQL.

[Jonatan Cloutier]

I just have to refresh my mind, I haven't work at this level for a few years. In the mean time for people searching this subject, this express the main part of my answer stackoverflow.com/questions/50303/…

[Mjh]

Well that answer is 8 years old now, and it's not even correct. People usually read what they want to be true and upvote, without taking facts into account. Nowadays, applications are built much better and with more forward-thinking in mind. If you are going for a fast application, you'll use a persistent connection because it's always quicker to connect once instead of connect/disconnect upon every request (that actually really rapes the db and kills performance).

[Jonatan Cloutier]

well this one was 4 years old

[Eric]

Regardless of whether this answered OPs answer I found this useful, thank you.

[Derrick Miller]

I've been scouring other Stack Overflow answers and this is the best PDO class that I've found. I disagree with others above that it is lacking in explanation. The comments in the code are clear. I found it very helpful.

[MrMesees]

Surely if it's using persistent connections, it won't need a singleton. You could call the DB connect as many times as you like, it should attempt to connect or fail just like a non-persistent but without the overhead