Fully Understanding PDO ATTR_PERSISTENT

Question

Question:

What are the rules/logic behind persistent connection management when using PDO?

---

Environment:

Web Server

• Windows 7 x64
• Dual-core with 16GB RAM
• Apache 2.2.17
• PHP 5.3.5
• Connecting through DSN string with IP address, port, service name, etc...
• No ODBC for DB conn (been trying to create one for 2 hours now, thanks Oracle!)

DB Server

• Oracle 10g on Linux
• Multi-core with 4GB RAM
• Username specifically created for my web app (yes, it's fake)
  • user: webuser

---

My understanding/observations:

Non-persistent connections

<?php

// Open a new connection
// Session created in Oracle
$dbh = new PDO('DSN', 'webuser', 'password');

// webuser is active in v$session with a SID=1

$dbh = NULL;

// webuser removed from v$session

// Manually calling $dbh = NULL; will remove the session from v$session
// OR
// Wait for script EOL so a kill-session command is sent to Oracle?

?>

• Script reliably takes about ~.09 seconds to execute with framework overhead, etc...

Persistent connections

<?php

// Open a new connection and make it persistent
// Session created in Oracle
// Is Apache maintaining some sort of keep-alive with Oracle here?
// because I thought php.exe is only alive for the duration of the script
$dbh = new PDO('DSN', 'webuser', 'password', array(PDO::ATTR_PERSISTENT => TRUE));

// webuser is active in v$session with a SID=1

$dbh = NULL;

// webuser is still active in v$session with a SID=1

$dbh = new PDO('DSN', 'webuser', 'password', array(PDO::ATTR_PERSISTENT => TRUE));

// webuser is still active in v$session with a SID=1

// Manually calling $dbh = NULL; does not kill session
// OR
// Script EOL does not kill session
// ^^ this is good, just as expected

?>

• Script takes ~.12 seconds to execute upon initial visit with framework overhead, etc...
• Sub-sequent executes take ~.04

---

The issue:

I visit the page and webuser gets a SID=1

My colleague visits the page and webuser gets an additional SID=2 <- rinse, repeat, and increment SID for new computers visiting this page

Shouldn't a new visitor be re-using SID=1?

---

All answers, suggestions, requests for alternate testing, links to reading material are welcomed.

I have RTFM'ed for a while and Googling has only produced meager Advantages of Persistent vs. Non-persistent blogs.

Answer 1

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

Answer 2

Advantages

From the manual page of php for persistent connections on this link:

Persistent connections are links that do not close when the execution of your script ends. When a persistent connection is requested, PHP checks if there's already an identical persistent connection (that remained open from earlier) - and if it exists, it uses it. If it does not exist, it creates the link.

The reason behind using persistent connections is, of course, reducing the number of connections which are rather expensive; Even though they are much faster with MySQL than with most other databases.

Issues

There are some issues with table locking while using persistent connections.

if the script for whatever reason cannot release the lock, then subsequent scripts using the same connection will block indefinitely and may require that you either restart the httpd server or the database server.

Another is that when using transactions by mysql commit.

A transaction block will also carry over to the next script which uses that connection if script execution ends before the transaction block does. In either case, you can use register_shutdown_function() to register a simple cleanup function to unlock your tables or roll back your transactions.

I suggest you read this question about disatvantages of persistent connections.

Answer 3

PDO is kinda funny that way. Even the same user/visitor can cause a second or even third instance to be created. The same thing happened to me on my local machine, while testing the performance of my db queries.

That is nothing to worry about, because these instances will timeout sooner or later, the exact timeout depends on your server configuration.

Why that happens? If the current instance is busy, then a new instance will be created and the older one will timeout sooner or later. At least that seems logical to me.

Answer 4

Here are my observations as I recently experienced an issue similar to yours. MySQL server kept opening new connections and eventually maxed out the number of concurrent connections to MySQL server even though there were a lot of Idle connections that can be used.

Setting PDO::ATTR_PERSISTENT => true does re-use available idle connections. It may not seem like this at first glance when you try to monitor MySQL process because in the time that the report is sent back to you, that idle connection may have become active by another process.

Overall, you should notice a drop in the number of idle connections as opposed to not using a persistent connection. As regards the table locking issue, I decided to use InnoDB Storage Engine for my tables as it uses row-level locking as opposed to table locking with MyISAM Storage Engine.

I have not had an issue yet with concurrency when using this combination of InnoDB Storage Engine and PDO persistent Connection.

Also, as a safeguard on badly executed queries locking tables, keep queries within a try-catch Block.