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 220.127.116.11 -U pgbench -p 5432 -i -s 50000
I have configured my pgBouncer as the following:
[databases] pgbench = dbname=pgbench host=18.104.22.168 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 22.214.171.124 -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.