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.Kreienkamp@la-z-boy.com
From: Nicola Contu [mailto:nicola.contu@gmail.com]
Sent: Tuesday, October 16, 2018 8:12 AM
To: pgsql-general@lists.postgresql.org
Cc: Alessandro Aste <Alessandro.aste@gtt.net>
Subject: Re: Pgbouncer discard all
Hello,
is this normal? can anyone help?
Thanks a lot for your help in advance.
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 :
This is our pgbouncer config :
dev = host=10.10.10.1 port=5432 dbname=dev auth_user=pgbouncer pool_size=120
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
Do you think this can depend on the server_idle_timeout default config value?
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.