Thanks friends,
postgres@oracle-VirtualBox:~$ pgbench -c 20 -C -T 60 -S mydatabase -h 127.0.0.1 -p 6432 -U myuser
I found why it was not working for -T .
Since I was not using -C in my last command, the performance showed as poor.
After adding -C, I see good performance .
postgres@oracle-VirtualBox:~$ pgbench -c 20 -C -T 60 -S mydatabase -h 127.0.0.1 -p 5432 -U myuser
Password:
pgbench (16.1 (Ubuntu 16.1-1.pgdg20.04+1))
starting vacuum...end.
transaction type: <builtin: select only>
scaling factor: 10
query mode: simple
number of clients: 20
number of threads: 1
maximum number of tries: 1
duration: 60 s
number of transactions actually processed: 4916
number of failed transactions: 0 (0.000%)
latency average = 244.210 ms
average connection time = 11.240 ms
tps = 81.896637 (including reconnection times)
postgres@oracle-VirtualBox:~$ pgbench -c 20 -C -T 60 -S mydatabase -h 127.0.0.1 -p 6432 -U myuser
Password:
pgbench (16.1 (Ubuntu 16.1-1.pgdg20.04+1))
starting vacuum...end.
transaction type: <builtin: select only>
scaling factor: 10
query mode: simple
number of clients: 20
number of threads: 1
maximum number of tries: 1
duration: 60 s
number of transactions actually processed: 11473
number of failed transactions: 0 (0.000%)
latency average = 104.606 ms
average connection time = 4.914 ms
tps = 191.193934 (including reconnection times)
postgres@oracle-VirtualBox:~$
On Sat, Apr 27, 2024 at 12:19 PM Tejaswi K T <tejaswi.kt@xxxxxxxxx> wrote:
Hi Friends, new to postgres -
installed pgbouncer and was trying some options there.
Tested with below commands and pgbouncer excels the direct postgres connections
can someone explain why pgbouncer degrades while -T is used---works good for pgbouncerpgbench -c 20 -t 100 -S mydatabase -h 127.0.0.1 -p 5432 -U myuser -C -f mysql.sqlpgbench -c 20 -t 100 -S mydatabase -h 127.0.0.1 -p 6432 -U myuser -C -f mysql.sql---works good for pgbouncerpgbench -c 20 -t 100 -S mydatabase -h 127.0.0.1 -p 5432 -U myuser -Cpgbench -c 20 -t 100 -S mydatabase -h 127.0.0.1 -p 6432 -U myuser -C
But pgbouncer fails while using -T (duration) when pgbench testing:
---works good without pgbouncerpgbench -c 20 -T 60 -S mydatabase -h 127.0.0.1 -p 5432 -U myuserpgbench -c 20 -T 60 -S mydatabase -h 127.0.0.1 -p 6432 -U myuser
---
postgres@oracle-VirtualBox:~$ pgbench -c 20 -T 60 -S mydatabase -h 127.0.0.1 -p 5432 -U myuserPassword:pgbench (16.1 (Ubuntu 16.1-1.pgdg20.04+1))starting vacuum...end.transaction type: <builtin: select only>scaling factor: 10query mode: simplenumber of clients: 20number of threads: 1maximum number of tries: 1duration: 60 snumber of transactions actually processed: 474592number of failed transactions: 0 (0.000%)latency average = 2.521 msinitial connection time = 246.002 mstps = 7933.834914 (without initial connection time)
With pgbouncer below TPS is degradedpostgres@oracle-VirtualBox:~$ pgbench -c 20 -T 60 -S mydatabase -h 127.0.0.1 -p 6432 -U myuserPassword:pgbench (16.1 (Ubuntu 16.1-1.pgdg20.04+1))starting vacuum...end.transaction type: <builtin: select only>scaling factor: 10query mode: simplenumber of clients: 20number of threads: 1maximum number of tries: 1duration: 60 snumber of transactions actually processed: 367541number of failed transactions: 0 (0.000%)latency average = 3.258 msinitial connection time = 133.730 mstps = 6138.123161 (without initial connection time)
Thanks in advance
Tejas
