Thread: Ned to understand why all the idle connections

On Thursday, April 23, 2020, David Gauthier <davegauthierpg@gmail.com> wrote:

Hi:

psql (9.6.7, server 11.3) on linux

I have what appear to be a log of idle connections to my DB.  Query of pg_stat_activity indicates well over half (127/206) are like this...

dvdb=# select state_change,wait_event_type,wait_event,state,backend_type from pg_stat_activity where query = '';
         state_change          | wait_event_type |     wait_event      | state |         backend_type        
-------------------------------+-----------------+---------------------+-------+------------------------------
 2020-04-23 12:57:58.215854-04 | Client          | ClientRead          | idle  | client backend

What does this indicate?

The vast majority of the connections are through perl/dbi.   If a connection is made, and is currently not doing anything, does it appear in pg_stat_activity as "idle" ?  If, in DBI, $dbh->disconnect is used whenever the DB is no longer needed, will it disconnect from the DB and NOT appear as an idle in pg_stat_activity ?

If there are any other column s in pg_stat-activity you'd like to see, or any other query in any of the system tables, please advise.

If the server has an active, authenticated, connection/process running it shows up here.  Yes, idle is the state used to denote the the session is “not doing anything”...

$dbh->disconnect typically will indeed close the connection.  There can be exceptions if your architecture uses connection pooling.

David J.

On Thursday, April 23, 2020, David Gauthier <davegauthierpg@gmail.com> wrote:

Hi:

psql (9.6.7, server 11.3) on linux

I have what appear to be a log of idle connections to my DB.  Query of pg_stat_activity indicates well over half (127/206) are like this...

dvdb=# select state_change,wait_event_type,wait_event,state,backend_type from pg_stat_activity where query = '';
         state_change          | wait_event_type |     wait_event      | state |         backend_type        
-------------------------------+-----------------+---------------------+-------+------------------------------
 2020-04-23 12:57:58.215854-04 | Client          | ClientRead          | idle  | client backend

What does this indicate?

The vast majority of the connections are through perl/dbi.   If a connection is made, and is currently not doing anything, does it appear in pg_stat_activity as "idle" ?  If, in DBI, $dbh->disconnect is used whenever the DB is no longer needed, will it disconnect from the DB and NOT appear as an idle in pg_stat_activity ?

If there are any other column s in pg_stat-activity you'd like to see, or any other query in any of the system tables, please advise.

If the server has an active, authenticated, connection/process running it shows up here.  Yes, idle is the state used to denote the the session is “not doing anything”...

$dbh->disconnect typically will indeed close the connection.  There can be exceptions if your architecture uses connection pooling.

David J.

Please don't top-post; and this is a fairly rude hijack posting given that you already have a thread going, from today no less, where you've basically asked this very same question.

On Thu, Apr 23, 2020 at 2:18 PM Si Chen <sichen@opensourcestrategies.com> wrote:

Hello David & David,

I have a similar problem -- a lot of idle transactions.  I'm using the PostgreSQL JDBC driver.  The connections look like this:

 pid  | wait_event |         state_change          |         backend_start         | xact_start |          query_start          |    ?column?     | query  

-------+------------+-------------------------------+-------------------------------+------------+-------------------------------+-----------------+--------

 32506 | ClientRead | 2020-04-23 09:29:05.6793-07   | 2020-04-23 01:00:19.612478-07 |            | 2020-04-23 09:29:05.679275-07 | 00:00:00.000025 | COMMIT

 32506 | ClientRead | 2020-04-23 09:30:33.247119-07 | 2020-04-23 01:00:19.612478-07 |                               | 2020-04-23 09:30:33.247109-07 | 00:00:00.00001  | COMMIT

 32506 | ClientRead | 2020-04-23 09:31:31.506914-07 | 2020-04-23 01:00:19.612478-07 |                               | 2020-04-23 09:31:31.506905-07 | 00:00:00.000009 | COMMIT

 32506 | ClientRead | 2020-04-23 09:32:32.06656-07  | 2020-04-23 01:00:19.612478-07 |            | 2020-04-23 09:32:32.066552-07 | 00:00:00.000008 | COMMIT

 32506 | ClientRead | 2020-04-23 09:36:51.579939-07 | 2020-04-23 01:00:19.612478-07 |            | 2020-04-23 09:36:51.579931-07 | 00:00:00.000008 | COMMIT

It seems like they haven't been doing anything for a long time, but the state_change keeps getting updated.

If the state_change timestamp keeps changing then by definition they are doing something......

 

  Is it possible that state_change is being updated, maybe by the JDBC driver?

Directly, no, that particular field is read-only by the user and so nothing is going to directly update it.  However, as soon as the session changes state it will change as well.

The most likely answer is that your setup for JDBC includes a connection pool that is periodically checking to see if its session is still active.  You should work on trying to prove or disprove that assumption.

Do you recommend using PgBouncer with JDBC?

I try to avoid making recommendations without knowing the situation in which something is operating.  Given the level of expertise demonstrated here I would, however, advise against adding another architectural component to your setup until your understand completely what you are already working with.  If at that point you can define a problem that you want to solve, and pgBouncer would constitute a solution, then you could consider adding it.

David J.