Fully Understanding Pdo Attr_Persistent

Fully Understanding PDO ATTR_PERSISTENT

Apaches point of view

Apache has one parent process. This process creates child processes that will handle any requests coming to the web server.
The initial amount of child processes being started when the web server starts is configured by the StartServers directive in the apache configuration. The number goes up as needed with a raising amount of requests hitting the web server until ServerLimit is reached.

PHP and persistent connections

If PHP (ran as mod_php, as CGI all resources are freed at the end of script execution) is now being told to establish a persistent connection with a database for a request, this connection is hold even after the script finishes.
The connection being now hold is a connection between the apache child process which the request was handled by and the database server and can be re-used by any request that is being handled by this exact child process.

If, for some reason (do not ask me exactly why), the child process is being occupied longer than the actual request and another request comes in, the parent apache process redirects this request to a (new) child process which may has not established a connection to the database up to this time. If it has to during the execution of the script, it raises the SID as you have observed. Now there are two connections be hold by two different child processes of apache.

Keep in mind that...

It is important to know, that this can also cause a lot of trouble.
If there is an endless loop or an aborted transaction or some other may be even unpredictable error during the script execution, the connection is blocked and can not be re-used.
Also it could happen that all of the available connections of the database are used, but there is another child process of the apache server trying to access the database.
This process is blocked for the time being until a connection is freed by the database or apache (timeout or voluntarily by termination).
Any further information about this topic on this page: http://www.php.net/manual/en/features.persistent-connections.php

I hope I got all that we have discussed in our comment conversation summarized correctly and did not forget anything.
If so, please, leave me a hint and I will add it. :)

Edit:

I just finished reading the article @MonkeyZeus mentioned in this comment.
It describes the process I summarized above and provides useful information on how to optimize your apache server to work better together with persistent connections.
It can be used with or without oracle database backends, though.
You should give a look: http://www.oracle.com/technetwork/articles/coggeshall-persist-084844.html

Should PDO::ATTR_PERSISTENT be used every time?

The issue with persistant connections is that the number of connections available to MySQL is limited. If something goes wrong and that connection isn't closed, the server is going to leave it open for a long time. If the server runs out of connections, then every single application tied to it is going to be unavailable until someone intervenes.

You can probably expect something to go wrong from time to time, and under the wrong circumstances the problem of resource over-usage can leak into months if not noticed, leaving you with a very gradual degrade in performance and increase in system utilization over time (all for no gain).

Here is one good article that can help you. It focuses on MySQL, but most of the same thoughts can be generalized across the spectrum of DBMS's.

Are PHP persistent connections evil ?

Is there a difference between PDO::exec and PDO::query when using PDO::ATTR_PERSISTENT = true?

If you are using exec() for SELECT then you are already doing something wrong. This function (as is mentioned in the manual) does not fetch results from the database. It can only be used for queries, which produce no result set and which have no variable input. If a query produces a result then you need to fetch this result from MySQL using the same connection.

A persistent connection cannot be reused if it is still in use. This could happen for many reasons, one of which is unfetched result. MySQL will keep the connection open waiting for the client to perform some actions on the result set. Once the result is fetched in its entirety from MySQL server, it can then accept new queries.

This is not only a problem with persistent connections, because unfetched result set can be a problem if you use exec() one after the other with normal connections too.

$options = [
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
];
$pdo = new PDO("mysql:host=localhost;dbname=test;port=3307", "root", "", $options);

// The following will give:
// SQLSTATE[HY000]: General error: 2014 Cannot execute queries while other unbuffered queries are active. Consider using PDOStatement::fetchAll(). Alternatively, if your code is only ever going to run against mysql, you may enable query buffering by setting the PDO::MYSQL_ATTR_USE_BUFFERED_QUERY attribute.
var_dump($pdo->exec("SELECT 1"));
var_dump($pdo->exec("SELECT 1"));

PDOException: SQLSTATE[HY000]: General error: 2014 Cannot execute queries while other unbuffered queries are active. Consider using PDOStatement::fetchAll(). Alternatively, if your code is only ever going to run against mysql, you may enable query buffering by setting the PDO::MYSQL_ATTR_USE_BUFFERED_QUERY attribute. in C:\wamp64\www\test\index.php on line 16

To answer your question from the title: Yes. There is difference between using exec() and query() when using persistent connections, but this difference is also there when not using persistent connections.

tl;dr: Do not use exec() for queries, which produce results. Use prepared statements instead.

What are the disadvantages of using persistent connection in PDO

Please be sure to read this answer below, which details ways to mitigate the problems outlined here.

---

The same drawbacks exist using PDO as with any other PHP database interface that does persistent connections: if your script terminates unexpectedly in the middle of database operations, the next request that gets the left over connection will pick up where the dead script left off. The connection is held open at the process manager level (Apache for mod_php, the current FastCGI process if you're using FastCGI, etc), not at the PHP level, and PHP doesn't tell the parent process to let the connection die when the script terminates abnormally.

If the dead script locked tables, those tables will remain locked until the connection dies or the next script that gets the connection unlocks the tables itself.

If the dead script was in the middle of a transaction, that can block a multitude of tables until the deadlock timer kicks in, and even then, the deadlock timer can kill the newer request instead of the older request that's causing the problem.

If the dead script was in the middle of a transaction, the next script that gets that connection also gets the transaction state. It's very possible (depending on your application design) that the next script might not actually ever try to commit the existing transaction, or will commit when it should not have, or roll back when it should not have.

This is only the tip of the iceberg. It can all be mitigated to an extent by always trying to clean up after a dirty connection on every single script request, but that can be a pain depending on the database. Unless you have identified creating database connections as the one thing that is a bottleneck in your script (this means you've done code profiling using xdebug and/or xhprof), you should not consider persistent connections as a solution to anything.

Further, most modern databases (including PostgreSQL) have their own preferred ways of performing connection pooling that don't have the immediate drawbacks that plain vanilla PHP-based persistent connections do.

---

To clarify a point, we use persistent connections at my workplace, but not by choice. We were encountering weird connection behavior, where the initial connection from our app server to our database server was taking exactly three seconds, when it should have taken a fraction of a fraction of a second. We think it's a kernel bug. We gave up trying to troubleshoot it because it happened randomly and could not be reproduced on demand, and our outsourced IT didn't have the concrete ability to track it down.

Regardless, when the folks in the warehouse are processing a few hundred incoming parts, and each part is taking three and a half seconds instead of a half second, we had to take action before they kidnapped us all and made us help them. So, we flipped a few bits on in our home-grown ERP/CRM/CMS monstrosity and experienced all of the horrors of persistent connections first-hand. It took us weeks to track down all the subtle little problems and bizarre behavior that happened seemingly at random. It turned out that those once-a-week fatal errors that our users diligently squeezed out of our app were leaving locked tables, abandoned transactions and other unfortunate wonky states.

This sob-story has a point: It broke things that we never expected to break, all in the name of performance. The tradeoff wasn't worth it, and we're eagerly awaiting the day we can switch back to normal connections without a riot from our users.

Too many connections with PDO persistent connection?

From the PHP manual ~ http://php.net/manual/en/pdo.connections.php

Many web applications will benefit from making persistent connections to database servers. 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.

So I would advise removing the DbConnection#close() method as you would not want to ever call this.

Also from the manual...

Note:
If you wish to use persistent connections, you must set PDO::ATTR_PERSISTENT in the array of driver options passed to the PDO constructor. If setting this attribute with PDO::setAttribute() after instantiation of the object, the driver will not use persistent connections.

So you'll want (at least)

new \PDO("mysql:host=127.0.0.1;dbname=" . DBNAME, DBUSER, DBPASS, [
    PDO::ATTR_PERSISTENT => true
]);

You can also set your other connection attributes in the constructor.

PHP Keeping a PDO ATTR_PERSISTENT connection alive

I would suggest you another way to handle such situations. Whenever you need to run a query in your long-lasting script ensure that there is a connnection. Otherwise reconnect.

    try {
        echo "Testing connection...\n";
        $old_errlevel = error_reporting(0);
        self::$pdo->query("SELECT 1");
    } catch (PDOException $e) {
        echo "Connection failed, reinitializing...\n";
        self::init();
    }

You can find the full class example here.
I also suggest you to explicitly close the connection in your script when you know you will not use it for a long period.

---

Related Topics