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();
}

Answer 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);
}
?>

Answer 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

Answer 3

From what I know from persistent connection you probably doesn't need it:

1. you are on localhost so the connection is very fast and you wont save a lot from caching of your connection
2. because of the underlying principal of Apache you have many thread that answer the client request and because of these multi-threading the connection is persistent in a single thread, not shared on all of them so you will see your number of connection go up in mysql until it's reach the ThreadLimit of apache
3. there is a few risk that persistent connection cause problem to your app in the kind of dbLock or tableLock

Now you might wanna do a bit more research about persistent connections if you still think that you really need it