Persistent Connections vs. normal connections with PHP

[pablogosse]

Hi all.

As far as I understand it, the advantage to persistent connections is that if a request is made to connect to a PostgreSQL database via a persistent connection, then if there already exists in the pool a connection with the same database, username, and password, this connection will be reused instead of opening up a new one.

However, I've also read some information which suggests that using persistent connections increases the load on the server.

Since I'm running four applications which use PostgreSQL, and since two of these often have multiple users connected at once that will be using the same username and password to connect to a database, am I not better off using persistent connections to reduce the number of active connections for the same username and password?

From what I've read it seems that persistent connections are in this case the way to go, but I've read some other posts/articles which suggest the opposite, so while I would tend to lean towards the side of persistent connections I'm not certain.

On the two applications I have which are currently accessed the most, I think I could use persistent or non-persistent connections and it might not make that much of a difference. But the application I'm currently developing (a db-driven content management system) will have MANY users connected at the same time, so efficiency in pooling connections is of utmost importance.

Any advice is greatly appreciated.

Thanks much in advance,
Pablo

[Sxooter]

First and foremost, it's important to understand that under with apache / PHP, persistant connections are NOT connection pooling persistant connections. Each persistant connection is maintained by a different backend apache server process. Each connection to a different database or with a different set of connection parameters will initiate a persistant backend connect to the database.

Apache should be set to about 1/2 as many max children as postgresql is set to if you want it to work well, otherwise postgresql will run out of connects.

My super quick and dirty benchmark on persistant versus non-persistant connects is that opening a previously opened persistant connect was happening at about 500,000 times a second in a tight loop (i.e. 2 uSeconds, very fast) while non-persistant connects were happening at about 10,000 a second, or 100 uSeconds.

While the savings seems substantial, it's noise for most applications. i.e. even your simples query will likely take milliseconds to execute, so the savings is just not that big.

Now, if you're using a database that has REALLY slow connection speed, like say the old versions of postgresql, or you're connecting to postgresql via ssl, the savings might well be much greater, but you should test to two, both under heavy parallel load and lighter load to get a feel for it. My feeling is that you're likely to get more from optimizing the indexes in your database and tuning postgresql in the backend (shared_buffers, random_page_cost, cpu_tuple_cost, cpu_index_tuple_cost, effective_cache_memory etc... along with regular vacuuming and analyzing make a much bigger difference here.