Re: [GENERAL] db server processes hanging around
thank you yes we are continuing to observe connection count. so far we have made changed TCP stack configuration and will continue to observe. mr On Wed, Jun 20, 2012 at 12:53 AM, Sumit Raja sumit.r...@raja-consulting.co.uk wrote: Or ask your Java devs to investigate why the shut down does not close the physical connection properly. Does IDEA claim to shut down Tomcat but actually it is still running because of a threads not being cleaned up? Are you sure this isn't happening during normal operation of the application? If its bad connection/thread management, something like this might show up in production. - Sumit On 19 June 2012 18:28, Steve Crawford scrawf...@pinpointresearch.com wrote: On 06/19/2012 09:29 AM, Mark Rostron wrote: hi we are running out of database connections. we are using pg 9.0.6 on linux centos 5.7 64bit. we are not using any go-between connection pools such as pgbouncer or pgpool - connections occur directly from client to database. the connection setup on the client (java) is default, only providing (user,password,dbhost,dbname). we have about 10 developers developing java thru IDEA who start/stop the local tomcat server frequently. i have observed that tomcat doesn't disconnect from pg cleanly when they cycle, and the server processes persist for a long time. I have had them reduce their local connection factory pool size to 1 (this helped) and increased our max_connection value to 1000. yet the problem persists. I have noticed that the server processes do die after some time - due to inactivity? we are looking for a way to control server processes better than we are doing now. thnx for your time. mr I am unaware of any system setting like max_connection_idle_time (though it might be a useful addition). I have not had to mess with tcp_keepalive settings but you might be able to alter those (perhaps at the OS instead of PostgreSQL) to reduce the delay before the backend terminates. But this won't work for socket connections. You could hack together a tailored solution by having cron run a script that would query pg_stat_activity for queries equal to IDLE and with a backend_start age greater than whatever you find reasonable and then execute pg_terminate_backend() on those PIDs. You could even have a table of developer IP addresses and only terminate those processes. Alternately, if Tomcat connected to a different port you could only kill those. Cheers, Steve -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Raja Consulting Ltd. Incorporated in England and Wales No. 06454814, Registered Office: 4 Calder Court, Shorebury Point, Amy Johnson Way, Blackpool FY4 2RH -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] db server processes hanging around
Or ask your Java devs to investigate why the shut down does not close the physical connection properly. Does IDEA claim to shut down Tomcat but actually it is still running because of a threads not being cleaned up? Are you sure this isn't happening during normal operation of the application? If its bad connection/thread management, something like this might show up in production. - Sumit On 19 June 2012 18:28, Steve Crawford scrawf...@pinpointresearch.com wrote: On 06/19/2012 09:29 AM, Mark Rostron wrote: hi we are running out of database connections. we are using pg 9.0.6 on linux centos 5.7 64bit. we are not using any go-between connection pools such as pgbouncer or pgpool - connections occur directly from client to database. the connection setup on the client (java) is default, only providing (user,password,dbhost,dbname). we have about 10 developers developing java thru IDEA who start/stop the local tomcat server frequently. i have observed that tomcat doesn't disconnect from pg cleanly when they cycle, and the server processes persist for a long time. I have had them reduce their local connection factory pool size to 1 (this helped) and increased our max_connection value to 1000. yet the problem persists. I have noticed that the server processes do die after some time - due to inactivity? we are looking for a way to control server processes better than we are doing now. thnx for your time. mr I am unaware of any system setting like max_connection_idle_time (though it might be a useful addition). I have not had to mess with tcp_keepalive settings but you might be able to alter those (perhaps at the OS instead of PostgreSQL) to reduce the delay before the backend terminates. But this won't work for socket connections. You could hack together a tailored solution by having cron run a script that would query pg_stat_activity for queries equal to IDLE and with a backend_start age greater than whatever you find reasonable and then execute pg_terminate_backend() on those PIDs. You could even have a table of developer IP addresses and only terminate those processes. Alternately, if Tomcat connected to a different port you could only kill those. Cheers, Steve -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Raja Consulting Ltd. Incorporated in England and Wales No. 06454814, Registered Office: 4 Calder Court, Shorebury Point, Amy Johnson Way, Blackpool FY4 2RH -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] db server processes hanging around
Em 20/06/2012 04:53, Sumit Raja escreveu: Or ask your Java devs to investigate why the shut down does not close the physical connection properly. Does IDEA claim to shut down Tomcat but actually it is still running because of a threads not being cleaned up? Are you sure this isn't happening during normal operation of the application? If its bad connection/thread management, something like this might show up in production. - Sumit On 19 June 2012 18:28, Steve Crawford scrawf...@pinpointresearch.com wrote: On 06/19/2012 09:29 AM, Mark Rostron wrote: hi we are running out of database connections. we are using pg 9.0.6 on linux centos 5.7 64bit. we are not using any go-between connection pools such as pgbouncer or pgpool - connections occur directly from client to database. the connection setup on the client (java) is default, only providing (user,password,dbhost,dbname). we have about 10 developers developing java thru IDEA who start/stop the local tomcat server frequently. i have observed that tomcat doesn't disconnect from pg cleanly when they cycle, and the server processes persist for a long time. I have had them reduce their local connection factory pool size to 1 (this helped) and increased our max_connection value to 1000. yet the problem persists. I have noticed that the server processes do die after some time - due to inactivity? we are looking for a way to control server processes better than we are doing now. thnx for your time. mr I am unaware of any system setting like max_connection_idle_time (though it might be a useful addition). I have not had to mess with tcp_keepalive settings but you might be able to alter those (perhaps at the OS instead of PostgreSQL) to reduce the delay before the backend terminates. But this won't work for socket connections. You could hack together a tailored solution by having cron run a script that would query pg_stat_activity for queries equal to IDLE and with a backend_start age greater than whatever you find reasonable and then execute pg_terminate_backend() on those PIDs. You could even have a table of developer IP addresses and only terminate those processes. Alternately, if Tomcat connected to a different port you could only kill those. Cheers, Steve -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general I've been working with Tomcat and PostgreSQL (and NetBeans + Eclipse IDEs) for about 4 years now, without such problem. When Tomcat is stopped, all database connections are closed (if you are using the great Database Pooling that comes with Tomcat). But then, you must check your application. There are times when you create a thread (and forget to set Deamon as true), and then when you Shutdown Tomcat, it remains running (you check confirm that looking for the java processess in memory. If you are running IDEA + Tomcat, you will see (at minimum) 2 Java process running. When you stop Tomcat, then you should see only one Java process running (otherwise, you have a hang thread in your application). One idea is to create a Application Lifecycle listener, and make it shutdown all your Threads (and possible database connections) before leaving. Regards, Edson -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] db server processes hanging around
On 06/19/2012 09:29 AM, Mark Rostron wrote: hi we are running out of database connections. we are using pg 9.0.6 on linux centos 5.7 64bit. we are not using any go-between connection pools such as pgbouncer or pgpool - connections occur directly from client to database. the connection setup on the client (java) is default, only providing (user,password,dbhost,dbname). we have about 10 developers developing java thru IDEA who start/stop the local tomcat server frequently. i have observed that tomcat doesn't disconnect from pg cleanly when they cycle, and the server processes persist for a long time. I have had them reduce their local connection factory pool size to 1 (this helped) and increased our max_connection value to 1000. yet the problem persists. I have noticed that the server processes do die after some time - due to inactivity? we are looking for a way to control server processes better than we are doing now. thnx for your time. mr I am unaware of any system setting like max_connection_idle_time (though it might be a useful addition). I have not had to mess with tcp_keepalive settings but you might be able to alter those (perhaps at the OS instead of PostgreSQL) to reduce the delay before the backend terminates. But this won't work for socket connections. You could hack together a tailored solution by having cron run a script that would query pg_stat_activity for queries equal to IDLE and with a backend_start age greater than whatever you find reasonable and then execute pg_terminate_backend() on those PIDs. You could even have a table of developer IP addresses and only terminate those processes. Alternately, if Tomcat connected to a different port you could only kill those. Cheers, Steve -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
