Pgbouncer performance query - Mailing list pgsql-general
| From | KK CHN |
|---|---|
| Subject | Pgbouncer performance query |
| Date | |
| Msg-id | CAKgGyB_fxnmsMq-a4KxH+PQjL6_5m14UvpEb5ejbuiJy7H_iFA@mail.gmail.com Whole thread Raw |
| Responses |
Re: Pgbouncer performance query
Re: Pgbouncer performance query Re: Pgbouncer performance query |
| List | pgsql-general |
List,
[root@pgbouncer ~]# pgbench -c 300 -j 2 -t 100000 -h localhost -p 5444 -U recoil -S recoil
Password:
pgbench (16.9, server 16.3.0)
starting vacuum...end.
transaction type: <builtin: select only>
scaling factor: 500
query mode: simple
number of clients: 300
number of threads: 2
maximum number of tries: 1
number of transactions per client: 100000
number of transactions actually processed: 30000000/30000000
number of failed transactions: 0 (0.000%)
latency average = 11.949 ms
initial connection time = 26.699 ms
tps = 25107.166425 (without initial connection time)
[root@pgbouncer ~]# date
Thu Jan 22 22:13:46 IST 2026
[root@pgbouncer ~]#
[root@pgbouncer ~]#
I am trying pgbouncer for inhouse deployment for PostgreSQL 16.
My setup is PostgreSQL VM : 5444 <=> Pgbouncer VM:5444 <===> Clients
(PostgreSQL with IP 10.12.0.2 and Pgbouncer 10.12.0.35 )
Conducted an inhouse benchmark test (pgbench) performed, I have seen the following results.
On direct hit the DB server handled tps = 162252.508744 (without initial connection time)
latency average = 1.233 ms (Total time taken around 2 Minutes to complete)
Through Pgbouncer it handled tps = 25107.166425 only ( without initial connection time)
latency average = 11.949 ms ( Total time taken around 20 Minutes to complete )
Could someone shed some light on improving the total time taken by pgbouncer in this scenario ? How can I improve the total time taken from 20 Minutes to any reasonably good value, say 5 Minutes is it possible ?
I agree when I have increased the concurrent connections to 300 (pgbench -c 300 ) then Direct hit on DB server fails with Error too many clients as follows
[root@pgbouncer ~]# pgbench -c 300 -j 2 -t 100000 -h 10.12.0.2 -p 5444 -U recoil -S recoil
Password:
pgbench (16.9, server 16.3.0)
starting vacuum...end.
pgbench: error: connection to server at "10.12.0.2", port 5444 failed: FATAL: sorry, too many clients already
pgbench: error: could not create connection for client 134
[root@pgbouncer ~]#
Password:
pgbench (16.9, server 16.3.0)
starting vacuum...end.
pgbench: error: connection to server at "10.12.0.2", port 5444 failed: FATAL: sorry, too many clients already
pgbench: error: could not create connection for client 134
[root@pgbouncer ~]#
I have followed this link for benchmark tests ( https://www.thediscoblog.com/supercharging-postgres-with-pgbouncer)
on Direct hit on DB Server without pgbouncer RESULTS:
[root@pgbouncer ~]# pgbench -c 200 -j 2 -t 100000 -h 10.12.0.2 -p 5444 -U recoil -S recoil
Password:
pgbench (16.9, server 16.3.0)
starting vacuum...end.
transaction type: <builtin: select only>
scaling factor: 500
query mode: simple
number of clients: 200
number of threads: 2
maximum number of tries: 1
number of transactions per client: 100000
number of transactions actually processed: 20000000/20000000
number of failed transactions: 0 (0.000%)
latency average = 1.233 ms
initial connection time = 1549.421 ms
tps = 162252.508744 (without initial connection time)
[root@pgbouncer ~]#
Password:
pgbench (16.9, server 16.3.0)
starting vacuum...end.
transaction type: <builtin: select only>
scaling factor: 500
query mode: simple
number of clients: 200
number of threads: 2
maximum number of tries: 1
number of transactions per client: 100000
number of transactions actually processed: 20000000/20000000
number of failed transactions: 0 (0.000%)
latency average = 1.233 ms
initial connection time = 1549.421 ms
tps = 162252.508744 (without initial connection time)
[root@pgbouncer ~]#
Through Pgbouncer to DB Server
[root@pgbouncer ~]# pgbench -c 300 -j 2 -t 100000 -h localhost -p 5444 -U recoil -S recoil
Password:
pgbench (16.9, server 16.3.0)
starting vacuum...end.
transaction type: <builtin: select only>
scaling factor: 500
query mode: simple
number of clients: 300
number of threads: 2
maximum number of tries: 1
number of transactions per client: 100000
number of transactions actually processed: 30000000/30000000
number of failed transactions: 0 (0.000%)
latency average = 11.949 ms
initial connection time = 26.699 ms
tps = 25107.166425 (without initial connection time)
[root@pgbouncer ~]# date
Thu Jan 22 22:13:46 IST 2026
[root@pgbouncer ~]#
But this takes around 20 Minutes to finish. Is this usual behavior ?
my DB VM(RHEL9.4) is 16vCPU, 16GB RAM and
Pgbouncer VM(FreeBSD 14.3) is 8 vCPU and Ram is [root@pgbouncer ~]# sysctl -h hw.physmem
hw.physmem: 17143681024[root@pgbouncer ~]#
TOP usage statistics of pgbouncer vm with 200 clients
last pid: 10020; load averages: 1.23, 0.83, 0.59 up 187+22:53:33 22:59:41
27 processes: 1 running, 20 sleeping, 6 stopped
CPU: 0.0% user, 0.0% nice, 0.0% system, 0.0% interrupt, 100% idle
Mem: 79M Active, 1063M Inact, 1410M Wired, 1030M Buf, 13G Free
Swap: 7068M Total, 7068M Free
27 processes: 1 running, 20 sleeping, 6 stopped
CPU: 0.0% user, 0.0% nice, 0.0% system, 0.0% interrupt, 100% idle
Mem: 79M Active, 1063M Inact, 1410M Wired, 1030M Buf, 13G Free
Swap: 7068M Total, 7068M Free
pgbouncer.ini
[root@pgbouncer ~]# grep ^[^\;\;] /usr/local/etc/pgbouncer.ini
[databases]
recoil = host=dbmain.mydomain.in port=5444 dbname=recoil
[users]
[pgbouncer]
logfile = /var/log/pgbouncer/pgbouncer.log
pidfile = /var/run/pgbouncer/pgbouncer.pid
listen_addr = *
listen_port = 5444
auth_type = md5
auth_file = /usr/local/etc/pgbouncer.users
admin_users = myuser
stats_users = myuser,
pool_mode = transaction // Is this the pool_mode I have to use ?
max_prepared_statements = 100
server_reset_query = DISCARD ALL
server_reset_query_always = 1
ignore_startup_parameters = extra_float_digits, options, statement_timeout, idle_in_transaction_session_timeout
max_client_conn = 5000
default_pool_size = 40
min_pool_size = 20
reserve_pool_size = 10
reserve_pool_timeout = 5
max_db_connections = 900
max_user_connections = 800
server_lifetime = 3600
server_idle_timeout = 60000
[root@pgbouncer ~]#
[databases]
recoil = host=dbmain.mydomain.in port=5444 dbname=recoil
[users]
[pgbouncer]
logfile = /var/log/pgbouncer/pgbouncer.log
pidfile = /var/run/pgbouncer/pgbouncer.pid
listen_addr = *
listen_port = 5444
auth_type = md5
auth_file = /usr/local/etc/pgbouncer.users
admin_users = myuser
stats_users = myuser,
pool_mode = transaction // Is this the pool_mode I have to use ?
max_prepared_statements = 100
server_reset_query = DISCARD ALL
server_reset_query_always = 1
ignore_startup_parameters = extra_float_digits, options, statement_timeout, idle_in_transaction_session_timeout
max_client_conn = 5000
default_pool_size = 40
min_pool_size = 20
reserve_pool_size = 10
reserve_pool_timeout = 5
max_db_connections = 900
max_user_connections = 800
server_lifetime = 3600
server_idle_timeout = 60000
[root@pgbouncer ~]#
Any parameters do I need to adjust for better performance in terms of latency time improvement, kindly guide me
Best regards,
Krishane
pgsql-general by date: