Re: pgbouncer degrades while -T is used

Thanks friends,
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(at)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(at)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(at)oracle-VirtualBox:~$

On Sat, Apr 27, 2024 at 12:19 PM Tejaswi K T <tejaswi(dot)kt(at)gmail(dot)com> 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 pgbouncer
> pgbench -c 20 -t 100 -S mydatabase -h 127.0.0.1 -p 5432 -U myuser -C -f
> mysql.sql
> pgbench -c 20 -t 100 -S mydatabase -h 127.0.0.1 -p 6432 -U myuser -C -f
> mysql.sql
> ---works *good *for pgbouncer
> pgbench -c 20 -t 100 -S mydatabase -h 127.0.0.1 -p 5432 -U myuser -C
> pgbench -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 *pgbouncer
> pgbench -c 20 -T 60 -S mydatabase -h 127.0.0.1 -p 5432 -U myuser
> pgbench -c 20 -T 60 -S mydatabase -h 127.0.0.1 -p 6432 -U myuser
>
> ---
>
> postgres(at)oracle-VirtualBox:~$ pgbench -c 20 -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: 474592
> number of failed transactions: 0 (0.000%)
> latency average = 2.521 ms
> initial connection time = 246.002 ms
> tps = 7933.834914 (without initial connection time)
>
>
> With pgbouncer below TPS is degraded
> postgres(at)oracle-VirtualBox:~$ pgbench -c 20 -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: 367541
> number of failed transactions: 0 (0.000%)
> latency average = 3.258 ms
> initial connection time = 133.730 ms
> tps = 6138.123161 (without initial connection time)
>
> Thanks in advance
> Tejas
>