Обсуждение: postgres connections in IDLE state..
Hi all:
Our application uses Jboss-hibernate with Postgres as the backend.
I’m seeing intermittently – the DB connections getting stale – and not getting returned to the Hibernate session pool. Some of the connections are as old as 9 days.
The pg_stat shows the connection as IDLE in the DB.
datid | datname | procpid | usesysid | usename | application_name | client_addr | client_port | backend_start | xact_start | query_start | waiting | current_query | ?column?
-------+-----------+---------+----------+---------+------------------+-------------+-------------+-------------------------------+-------------------------------+-------------------------------+---------+-----------------------------------------------------------------------------------+-------------------------
22372 | voyencedb | 15874 | 16384 | voyence | | 127.0.0.1 | 54754 | 2013-09-11 15:38:43.706149-05 | | 2013-10-02 11:43:44.410593-05 | f | <IDLE> | 20 days 20:05:00.704444
22372 | voyencedb | 15875 | 16384 | voyence | | 127.0.0.1 | 54755 | 2013-09-11 15:38:43.706275-05 | | 2013-10-02 11:43:44.410764-05 | f | <IDLE> | 20 days 20:05:00.704489
Is there anyway to correlate the Process id – the query that is used – or map it to the threads in the Java thread dumps? Need some lead to debug these IDLE connections.
Any help in this regard is highly appreciated.
Regards
Jayashree
Rajagopalan, Jayashree wrote > I'm seeing intermittently - the DB connections getting stale - and not > getting returned to the Hibernate session pool. Some of the connections > are as old as 9 days. The whole point of a connection pool is to keep open connections to the database. These connections, when not in use, are "IDLE". I would expect to see a single connection for every permanent pool connection. I don't see any problem with what you show and describe other than I'd expect more than 2 idle connections and I'd expect the supposed "problem" to be persistent. In your example the backend has been alive and in the pool since 9/11 and the most recent time it was used was 10/2. In between those two dates there were many other times the connection was used. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/postgres-connections-in-IDLE-state-tp5773232p5773234.html Sent from the PostgreSQL - admin mailing list archive at Nabble.com.
Thanks. But what is the process id that is attached to the connection? Can I track down or debug/ get more information related tothat process id? Regards Jayashree -----Original Message----- From: pgsql-admin-owner@postgresql.org [mailto:pgsql-admin-owner@postgresql.org] On Behalf Of David Johnston Sent: 03 October 2013 19:44 To: pgsql-admin@postgresql.org Subject: Re: [ADMIN] postgres connections in IDLE state.. Rajagopalan, Jayashree wrote > I'm seeing intermittently - the DB connections getting stale - and not > getting returned to the Hibernate session pool. Some of the > connections are as old as 9 days. The whole point of a connection pool is to keep open connections to the database. These connections, when not in use, are"IDLE". I would expect to see a single connection for every permanent pool connection. I don't see any problem with what you show and describe other than I'd expect more than 2 idle connections and I'd expectthe supposed "problem" to be persistent. In your example the backend has been alive and in the pool since 9/11 and the most recent time it was used was 10/2. Inbetween those two dates there were many other times the connection was used. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/postgres-connections-in-IDLE-state-tp5773232p5773234.html Sent from the PostgreSQL - admin mailing list archive at Nabble.com. -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
El jue, 03-10-2013 a las 16:34 +0000, Rajagopalan, Jayashree escribió: > Thanks. > > But what is the process id that is attached to the connection? You can look at pid field in the pg_stat_activity system view. > Can I track down or debug/ get more information related to that process id? I'd use operating system tools like lsof, it provides you information related to devices, files opened... or if you want to know application information you should use java debuger like jconsole, jvisualvm. > > Regards > Jayashree > > -----Original Message----- > From: pgsql-admin-owner@postgresql.org [mailto:pgsql-admin-owner@postgresql.org] On Behalf Of David Johnston > Sent: 03 October 2013 19:44 > To: pgsql-admin@postgresql.org > Subject: Re: [ADMIN] postgres connections in IDLE state.. > > Rajagopalan, Jayashree wrote > > I'm seeing intermittently - the DB connections getting stale - and not > > getting returned to the Hibernate session pool. Some of the > > connections are as old as 9 days. > > The whole point of a connection pool is to keep open connections to the database. These connections, when not in use,are "IDLE". I would expect to see a single connection for every permanent pool connection. > > I don't see any problem with what you show and describe other than I'd expect more than 2 idle connections and I'd expectthe supposed "problem" to be persistent. > > In your example the backend has been alive and in the pool since 9/11 and the most recent time it was used was 10/2. Inbetween those two dates there were many other times the connection was used. > > David J. > > > > > -- > View this message in context: http://postgresql.1045698.n5.nabble.com/postgres-connections-in-IDLE-state-tp5773232p5773234.html > Sent from the PostgreSQL - admin mailing list archive at Nabble.com. > > > -- > Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-admin > > >