pgbouncer degrades while -T is used

From: Tejaswi K T <tejaswi(dot)kt(at)gmail(dot)com>
To: pgsql-admin(at)lists(dot)postgresql(dot)org
Subject: pgbouncer degrades while -T is used
Date: 2024-04-27 10:19:06
Message-ID: CAFxeN6SMXkJHpjybCinUZHZzsoD_BkkdvZb0usJ=0AEWc5B-=Q@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

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

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message Tejaswi K T 2024-04-27 10:58:47 Re: pgbouncer degrades while -T is used
Previous Message Sunil Jadhav 2024-04-26 11:59:18 Re: Required distinct record with all fields