RE: Pgbouncer discard all

Are you sure they’re actually waiting? Don’t forget 10.5 will show the last query executed even if the connection is idle. I believe discard all would be the last command the pgbouncer would send to the database when the client is done as it resets the connection for the next client. So what you’re describing would seem to be expected behavior.

Try this to see if the queries are actually waiting:

select * from pg_stat_activity where wait_event_type is not null or wait_event is not null;

Scot Kreienkamp |Senior Systems Engineer | La-Z-Boy Corporate
One La-Z-Boy Drive| Monroe, Michigan 48162 | Office: 734-384-6403 | | Mobile: 7349151444 | Email: Scot(dot)Kreienkamp(at)la-z-boy(dot)com
From: Nicola Contu [mailto:nicola(dot)contu(at)gmail(dot)com]
Sent: Tuesday, October 16, 2018 8:12 AM
To: pgsql-general(at)lists(dot)postgresql(dot)org
Cc: Alessandro Aste <Alessandro(dot)aste(at)gtt(dot)net>
Subject: Re: Pgbouncer discard all

Hello,
is this normal? can anyone help?

Thanks a lot for your help in advance.

Nicola

Il giorno mer 10 ott 2018 alle ore 17:03 Nicola Contu <nicola(dot)contu(at)gmail(dot)com<mailto:nicola(dot)contu(at)gmail(dot)com>> ha scritto:
Hello,
we are running pgbouncer 1.9.1 connected to postgres 10.5

Sometimes we are seeing a lot of waiting connections with this query :

DISCARD ALL

This is our pgbouncer config :

[databases]
dev = host=10.10.10.1 port=5432 dbname=dev auth_user=pgbouncer pool_size=120

[pgbouncer]
listen_port = 6543
listen_addr = *
auth_type = md5
auth_file = /etc/pgbouncer/users.txt
auth_query = select uname,phash from user_lookup($1)
logfile = /var/log/pgbouncer.log
pidfile = /home/postgres/pgbouncer.pid
admin_users = admin
user = postgres
max_db_connections = 220
log_connections = 0
log_disconnections = 0

Do you think this can depend on the server_idle_timeout default config value?

Thanks a lot,
Nicola

This message is intended only for the individual or entity to which it is addressed. It may contain privileged, confidential information which is exempt from disclosure under applicable laws. If you are not the intended recipient, you are strictly prohibited from disseminating or distributing this information (other than to the intended recipient) or copying this information. If you have received this communication in error, please notify us immediately by e-mail or by telephone at the above number. Thank you.