Thread: Hwo to set timeout for PHP persistent connections
Ciao guys, I need to set a reasonable low value of timeout for inactive connections, as they seem to stay up when a PHP script calls a pg_pconnect on the database server. As its main purpose is to serve a Web application, I'd love connections to get close after 10-15 seconds of inactivity. Is there a run-time configuration I can set in Postgres? Ciao and thanks -Gabriele -- Gabriele Bartolini - Web Programmer Comune di Prato - Prato - Tuscany - Italy g.bartol@comune.prato.it | http://www.comune.prato.it > find bin/laden -name osama -exec rm {} ;
Attachment
On 3 Mar 2003, Gabriele Bartolini wrote: > Ciao guys, > > I need to set a reasonable low value of timeout for inactive > connections, as they seem to stay up when a PHP script calls a > pg_pconnect on the database server. > > As its main purpose is to serve a Web application, I'd love > connections to get close after 10-15 seconds of inactivity. > > Is there a run-time configuration I can set in Postgres? Bad news, the is no timeout. You should expect there to be as many persistant connections as there are apachc children times the number of connections in each child that can happen. Therefore, it's usually a good idea to configure apache to have fewer max children than postgresql. i.e. configure postgresql to handle 128 backends, and apache to run 64 children. Then you could have two connects per scripts and you wouldn't quite run out. The better option is to stop using persistant connects unless you have proof that connection time is a major impact on the performance of your machine. My main box, a Dual PIII 750 can pg_connect about 1,000 times a second. It can pg_pconnect about 1,000,000 times a second. Either way, all the scripts I run take way more than a millisecond to run, and all the queries I run take hundreds of milliseconds to run as well, so the 1,000th of a second of a pg_connect may be 1,000 times slower than a pg_pconnect, but they're both drops in the bucket so to speak, compared to the script itself.
Short answer - check for the timeout settings in posgresql.conf. Long answer: It's not a bug it's a feature. PHP can be set to maintain persistent database connections. Persistent connections can improve performance by eliminating the overhead of setting up and tearing down the connection to the database on a use-by-use basis. This can be good (doing a bunch of simple inserts on an open-do inserts-close vs. doing an open-insert-close for each insert generally gives me a 10x boost in performance) and is appropriate if 1) lots of your traffic requires database access and 2) you have sufficient resources to maintain enough open connections (typically one per web server process so look at your max processes setting in your web server). I'm not a PHP guru but I think there is a way to either explicitely close the connection or to turn off persistence which may be better than having PostgreSQL drop the connection. You could ask on the PHP list. If you need performance then you really need to look into connection pooling (like in aolserver or in Java app servers like jboss or check out one of the postgresql pooling solutions). With a connection pool you can set up a "pool" of persistent database connections that are shared by your various web server threads. Whenever a process needs to access the database it gets an already established connection from the pool and "returns" it when done. You only need enough connections to satisfy you max need for simultaneous database connections as the web process will not tie up a connection when not needed (say when serving up an image file or sitting idle) and you get the speed boost of having an established connection ready and waiting. Many pooling systems will automatically increase and decrease the number of connections in the pool as needed by the application. Cheers, Steve On Monday 03 March 2003 8:27 am, Gabriele Bartolini wrote: > Ciao guys, > > I need to set a reasonable low value of timeout for inactive > connections, as they seem to stay up when a PHP script calls a > pg_pconnect on the database server. > > As its main purpose is to serve a Web application, I'd love > connections to get close after 10-15 seconds of inactivity. > > Is there a run-time configuration I can set in Postgres? > > Ciao and thanks > -Gabriele
On 3 Mar 2003, Gabriele Bartolini wrote: > Ciao guys, > > I need to set a reasonable low value of timeout for inactive > connections, as they seem to stay up when a PHP script calls a > pg_pconnect on the database server. > > As its main purpose is to serve a Web application, I'd love > connections to get close after 10-15 seconds of inactivity. > > Is there a run-time configuration I can set in Postgres? As an addendum to my previous post, you can set the max number of requests an apache child will process before it is recycled, the setting is called MaxRequestsPerChild and if you set it to 0 (the default) then apache processes are never killed unless you have more than the max sitting idle. Setting it something fairly low, like 20 or 50 or 100 means that the connections won't last forever, as eventually that child will be killed off and it's connection will die. Also, setting StartServers to something low, like 1 will result in more processes getting recycled.
On Mon, 3 Mar 2003, scott.marlowe wrote: > On 3 Mar 2003, Gabriele Bartolini wrote: > > > Ciao guys, > > > > I need to set a reasonable low value of timeout for inactive > > connections, as they seem to stay up when a PHP script calls a > > pg_pconnect on the database server. > > > > As its main purpose is to serve a Web application, I'd love > > connections to get close after 10-15 seconds of inactivity. > > > > Is there a run-time configuration I can set in Postgres? > > As an addendum to my previous post, you can set the max number of requests > an apache child will process before it is recycled, the setting is called > MaxRequestsPerChild and if you set it to 0 (the default) then apache > processes are never killed unless you have more than the max sitting idle. > > Setting it something fairly low, like 20 or 50 or 100 means that the > connections won't last forever, as eventually that child will be killed > off and it's connection will die. > > > Also, setting StartServers to something low, like 1 will result in more > processes getting recycled. Man, I've got serious fat fingers today, that should be spareservers, not start servers...