pgBouncer receiving Error Query_Wait_Timeout in log - when running pgBench on Postgres 9.6

Question

I am testing pbBouncer by utilising pgBench with a very large database.

I initially setup pgBench with a large database (731GB) - turns outs its just a huge accounts table (626GB)

./pgbench -h 172.10.7.35 -U pgbench -p 5432 -i -s 50000

I have configured my pgBouncer as the following:

[databases]
pgbench = dbname=pgbench host=172.10.7.35 port=5432

;; Configuration section
[pgbouncer]

logfile = /var/log/pgbouncer/pgbouncer.log
pidfile = /var/run/pgbouncer/pgbouncer.pid

;; IP address or * which means all IPs
listen_addr = *
listen_port = 6432

;; any, trust, plain, md5, cert, hba, pam
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt

;; comma-separated list of users who are allowed to change settings
admin_users = postgres

;; comma-separated list of users who are just allowed to use SHOW command
stats_users = stats, postgres

;; When server connection is released back to pool:
;;   session      - after client disconnects (default)
;;   transaction  - after transaction finishes
;;   statement    - after statement finishes
;pool_mode = session

;; Total number of clients that can connect
max_client_conn = 1000

;; Default pool size.  20 is good number when transaction pooling
;; is in use, in session pooling it needs to be the number of
;; max clients you want to handle at any moment
default_pool_size = 25

; how many additional connection to allow in case of trouble
reserve_pool_size = 5

My max_connections in postgres is configured at 1000, can I reduce this value if I go via pgBouncer?

I was then running some pgBench test scenarios, going through pgbouncer and going direct to postgres.

When running the below via pgBouncer

./pgbench -c 1000 -j 4 -t 1000 pgbench -h 172.10.7.35 -p 6432 -U pgbench

I received alot of query_wait_timeout errors.

client XXX aborted in state 4: ERROR:  query_wait_timeout

I understand the default is 120 seconds but surely this would be enough?

Why cant pgBouncer handle the amount of connections?

I have 5 processors and 40GB of RAM with shared_buffers at 12GB

Is there any recommendations as to why I would be getting this? Would it be a matter of extending the query_wait_timeout

Any help is much appreciated.

Answer 1

You are only allowed to have 25+5 = 30 connections to the real database at once. You are trying to have 1000 connections at once. The rest have to wait a while. They aren't willing to wait long enough.

It isn't clear what you expect to happen here. Do you think the sessions will be shared, even though you are in session-pooling mode and not one of the finer pooling modes? Do you think one session will complete its 1000 transactions and turn its connection over to someone else more quickly than it actually will?