2

I have a single Postgres server and a set of PgBouncers configured to help me with connection pooling.

Recently, I discovered that the database connections are getting accumulated in "idle" state over time.

=> SELECT COUNT(*), state FROM pg_stat_activity GROUP BY state;

 count | state
-------+--------
     9 |
     1 | active
    11 | idle
(3 rows)

I executed the following query on the admin console of each PgBouncer to check whether I have any server connection from PgBouncers. I got the same result as below for every PgBouncer.

pgbouncer=# SHOW SERVERS;

 type | user | database | state | addr | port | local_addr | local_port | connect_time | request_time | wait | wait_us | close_needed | ptr | link | remote_pid | tls
------+------+----------+-------+------+------+------------+------------+--------------+--------------+------+---------+--------------+-----+------+------------+-----
(0 rows)

Here are the details of the connections which are in "idle" state.

=> SELECT * FROM pg_stat_activity WHERE state='idle';

-[ RECORD 1 ]----+---------------------------------------------------------------------
datid            | 16498
datname          | users
pid              | 337260
usesysid         | 14419
usename          | novadeadmin
application_name | psql
client_addr      | x.x.x.x
client_hostname  |
client_port      | 38850
backend_start    | 2021-11-20 14:26:53.648459+00
xact_start       |
query_start      | 2021-11-20 14:27:12.585782+00
state_change     | 2021-11-20 14:27:12.585782+00
wait_event_type  | Client
wait_event       | ClientRead
state            | idle
backend_xid      |
backend_xmin     |
query            | SELECT name FROM workspaces WHERE id='wnujb0lh37tsou44k7b1o';
backend_type     | client backend

-[ RECORD 2 ]----+---------------------------------------------------------------------
datid            | 376313
datname          | wnujb0lh37tsou44k7b1o
pid              | 363348
usesysid         | 14419
usename          | novadeadmin
application_name |
client_addr      | x.x.x.x
client_hostname  |
client_port      | 12992
backend_start    | 2021-11-19 19:24:03.696311+00
xact_start       |
query_start      | 2021-11-19 19:26:41.16366+00
state_change     | 2021-11-19 19:26:41.16366+00
wait_event_type  | Client
wait_event       | ClientRead
state            | idle
backend_xid      |
backend_xmin     |
query            | SELECT "currentVersion"."updatedAt" AS "currentVersion_updatedAt", "currentVersion"."deletedAt" AS "currentVersion_deletedAt", "currentVersion"."updatedBy" AS "currentVersion_updatedBy", "currentVersion"."id" AS "currentVersion_id", "currentVersion"."fileName" AS "currentVersion_fileName", "currentVersion"."drawingID" AS "currentVersion_drawingID", "currentVersion"."versionNumber" AS "currentVersion_versionNumber", "currentVersion"."issuedAt" AS "currentVersion_issuedAt", "currentVersion"."createdBy" AS "currentVersion_createdBy", "currentVersion"."createdAt" AS "currentVersion_createdAt", "currentVersion"."mime" AS "currentVersion_mime", "currentVersion"."size" AS "currentVersion_size" FROM "drawingVersions" "currentVersion" INNER JOIN "drawings" "Drawing" ON "Drawing"."currentVersionID" = "currentVersion"."id" AND "Drawing"."deletedAt" IS NULL WHERE ( "Drawing"."id" IN ($1) ) AND ( "currentVersion"."deletedAt" IS NULL )
backend_type     | client backend

--More--
  1. The query within the first record was executed by myself yesterday (2021-11-20) through a psql client (not through a PgBouncer). I'm not entirely sure whether I forgot to close the session in psql. So this might be my mistake.

  2. The query values from the other records are all the same. They were all executed from my backend application (using NodeJS TypeORM) through PgBouncers. But as I showed earlier, there are no active connections to Postgres server from PgBouncers.

The reason for having the same client_addr in all the records is because the psql client I used and the PgBouncers are in the same Kubernetes cluster.

Could you help me understand how 1. and 2. are happening? I would also like a mechanism to stop having long-lived "idle" connections.

Thanks in advance 🙏!


Update:

I see the following netstat result in PgBouncer host (y.y.y.y is the IP of my Postgres server):

/ $ netstat -tp | grep "y.y.y.y"
tcp        0      0 api-7659758786-2tpfv:57250 y.y.y.y:postgresql  ESTABLISHED -
tcp        0      0 api-7659758786-2tpfv:40576 y.y.y.y:postgresql  ESTABLISHED -
tcp        0      0 api-7659758786-2tpfv:40570 y.y.y.y:postgresql  ESTABLISHED -
tcp        0      0 api-7659758786-2tpfv:40580 y.y.y.y:postgresql  ESTABLISHED -
tcp        0      0 api-7659758786-2tpfv:40572 y.y.y.y:postgresql  ESTABLISHED -
tcp        0      0 api-7659758786-2tpfv:40566 y.y.y.y:postgresql  ESTABLISHED -
tcp        0      0 api-7659758786-2tpfv:40574 y.y.y.y:postgresql  ESTABLISHED -
tcp        0      0 api-7659758786-2tpfv:37988 y.y.y.y:postgresql  ESTABLISHED 1/pgbouncer
tcp        0      0 api-7659758786-2tpfv:40568 y.y.y.y:postgresql  ESTABLISHED -
tcp        0      0 api-7659758786-2tpfv:57252 y.y.y.y:postgresql  ESTABLISHED -
tcp        0      0 api-7659758786-2tpfv:40578 y.y.y.y:postgresql  ESTABLISHED -
tcp        0      0 api-7659758786-2tpfv:46606 y.y.y.y:postgresql  ESTABLISHED 92/psql
tcp        0      0 api-7659758786-2tpfv:51548 y.y.y.y:postgresql  ESTABLISHED 1/pgbouncer

This is the result from PgBouncer admin console for open server connections:

pgbouncer=# SHOW SERVERS;
 type |                  user                  |  database  | state |    addr | port |  local_addr  | local_port |      connect_time       |      request_time       | wait | wait_us | close_needed |      ptr       | link | remote_pid |                             tls
------+----------------------------------------+------------+-------+---------+------+--------------+------------+-------------------------+-------------------------+------+---------+--------------+----------------+------+------------+--------------------------------------------------------------
 S    | novadeadmin@psql-frc-stage-novade-lite | novadelite | idle  | y.y.y.y | 5432 | 10.244.4.196 |      51548 | 2021-11-22 05:01:42 UTC | 2021-11-22 05:14:34 UTC |    0 |       0 |            0 | 0x562d3d78aba0 |      |     900512 | TLSv1.2/ECDHE-RSA-AES256-GCM-SHA384/ECDH=secp384r1/OCSP=good
 S    | novadeadmin@psql-frc-stage-novade-lite | users      | used  | y.y.y.y | 5432 | 10.244.4.196 |      37988 | 2021-11-22 04:38:56 UTC | 2021-11-22 05:09:35 UTC |    0 |       0 |            0 | 0x562d3d78a740 |      |     879848 | TLSv1.2/ECDHE-RSA-AES256-GCM-SHA384/ECDH=secp384r1/OCSP=good
(2 rows)
12
  • Perhaps they will be reused? Opening a connection is costly. "Persistent connections" are good for performance. Nov 21, 2021 at 8:23
  • @GerardH.Pille The problem is there is no client connected. If a client wants to connect, they will have to create a new connection. Therefore, these idle connections should be removed. Nov 21, 2021 at 12:43
  • Not when working with a "connection pool". Normally these keep a number of connections ready for when a client needs them, so as to be able to hand them over immediately. Nov 21, 2021 at 13:31
  • Yes, I agree. But as you can see in my question, I use PgBouncer as the connection pooler. And SHOW SERVERS in the PgBouncer admin console is supposed to give me the information about the connections it made with the Postgres server. But in my scenario, I see nothing which means that there are no connections between the connection pooler (PgBouncer) and the Postgres server. Therefore, if a client wants to connect to a database, PgBouncer will have to create a new connection with the Postgres server and then hand it over to the client. Nov 21, 2021 at 13:38
  • In which case - I don't know anything about PgBouncer - it is not showing you those connections, or abandoning them unclosed. I nicety I've only encountered on µ$oft. Did you try with "lsof" if it was possible to see who's on the other side? Is PgBouncer logging anything? Nov 21, 2021 at 13:44

1 Answer 1

1

I feel like PgBouncer for some reason closed the connections with the server (gracefully or ungracefully). But Postgres server did not close the connection. Probably it doesn't know that there is no client.

With the connections being in a wait_event of 'ClientRead', they would notice immediately if the client closed the other end of the connection, and would respond by closing its own end and shutting down that backend process. So whatever is going on, it isn't that.

You said you used lsof and netstat to find connections and couldn't. Could you describe specifically what you looked for and saw? Did you just look on the pgbouncer host, or did you look on the database server host as well?

4
  • Thank you for the reply. I looked inside the PgBouncer host (container) for any open TCP connections. I don't have access to the host of Postgres server since it is managed by Azure. For some reason, I didn't see any open connections from netstat command at that time. I just executed netstat -tp and I see a bunch of connections now. 😵‍💫 I guess I might have executed netstat command with the wrong options yesterday. I'll update the question with the information. Maybe you can find something there. Nov 22, 2021 at 5:10
  • Let me know if you need more info :-) Nov 22, 2021 at 5:23
  • I'd completely overlooked the fact that you're dealing with "The Klowd". Nov 22, 2021 at 6:20
  • No problem. I didn't mention it as well at first. :-) Thank you for your insight Nov 22, 2021 at 6:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.