I have an Apache/PHP app on one server and a PostgreSQL database on a second server. They are configured to connect via SSL. Everything works, except that even though $db->setAttribute(PDO::ATTR_PERSISTENT, true); is in the script the TCP connections are always closed. PHP.INI has pgsql.allow_persistent = On pgsql.auto_reset_persistent = Off pgsql.max_persistent = 30 and pgsql.max_links = 30. Using sudo netstat -nap I can see that there are no ESTABLISHED connections between the two servers. What else do I need to do to keep the connections open? New connection overhead is 10-12 ms per request.
-
Use a connection pool like pgBouncer. Always.Frank Heikens– Frank Heikens2016-07-06 11:40:17 +00:00Commented Jul 6, 2016 at 11:40
Add a comment
|
1 Answer
I found this note a fair way down one of the php manual pages:
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.
That's where I went wrong. I was using this:
$host = explode('.',$_SERVER['SERVER_NAME']);
$db = new PDO($host[0]);
$db->setAttribute(PDO::ATTR_PERSISTENT, true);
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$db->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);
When I should have been doing this:
$host = explode('.', $_SERVER['SERVER_NAME']);
$db = new PDO($host[0],null,null,array(PDO::ATTR_PERSISTENT=>true,
PDO::ATTR_ERRMODE=>PDO::ERRMODE_EXCEPTION,
PDO::ATTR_DEFAULT_FETCH_MODE=>PDO::FETCH_ASSOC));
The DSN's are in a pdo.ini file. The null user and password are fortunately ignored or else this wouldn't work either. Now new PDO takes 0.02 ms instead of 10 ms.