Ned to understand why all the idle connections
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.
Thanks!
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 backendWhat 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.
Thanks!
And an example of connection pooling is pgBouncer ?
On Thu, Apr 23, 2020 at 2:41 PM David G. Johnston <
david.g.johnston@gmail.com> wrote:
Show quoted text
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 backendWhat 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 Thu, Apr 23, 2020 at 11:52 AM David Gauthier <davegauthierpg@gmail.com>
wrote:
Thanks!
And an example of connection pooling is pgBouncer ?
It does describe itself as being a "Lightweight connection pooler for
PostgreSQL" ...
David J.
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. Is it possible that state_change is
being updated, maybe by the JDBC driver?
Do you recommend using PgBouncer with JDBC?
-----
Si Chen
Open Source Strategies, Inc.
Our Mission: https://www.youtube.com/watch?v=Uc7lmvnuJHY
On Thu, Apr 23, 2020 at 11:41 AM David G. Johnston <
david.g.johnston@gmail.com> wrote:
Show quoted text
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 backendWhat 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 | COMMIT32506 | 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 | COMMIT32506 | 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 | COMMIT32506 | 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 | COMMIT32506 | 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 | COMMITIt 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.
Thanks for answering my questions.
Sorry I didn't mean to "top post" I thought that my other email got lost
because I had sent it to lists.postgresql.org
-----
Si Chen
Open Source Strategies, Inc.
Our Mission: https://www.youtube.com/watch?v=Uc7lmvnuJHY
On Thu, Apr 23, 2020 at 2:31 PM David G. Johnston <
david.g.johnston@gmail.com> wrote:
Show quoted text
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 | COMMIT32506 | 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 | COMMIT32506 | 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 | COMMIT32506 | 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 | COMMIT32506 | 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 | COMMITIt 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.