Simple Benchmark

Atyar sudhi aapde joyu k short lived connection mate PgBouncer use karvu bau vadhare benefit provide kare 6e.

aa vaat ne sabit karva mate, aapde ek benchmark run karsu je darek request mate ek new connection open karse. basically aapde niche rahela command ne loop ma rakhsu.

SELECT 1;

so aena mate aapde aa query ne ek select.sql file ma save karsu.

Aapde ae measure karsu k without and with pgbouncer aapde connection mate ketlo time lage 6e.

aa test run karva mate aapde pgbench command use kari sakie, je postgresql nu benchmark test karva mate khub famous 6e. aa module postgresql sathe j aave 6e.

pehla aapde database ne initialize kari levo joie. jenathi benchmark mate required tables create thase.

pgbench -i postgres -p 5433

have aapde actually ma benchmark perform karsu.

pgbench -c 20 -t 1000 -S postgres -C -f /tmp/test_db/select.sql -p 5433

aa command ek sathe 20 concurrent clients run karse and ae darek 1000 single transaction execute karse. -C no matlab 6e k darek transaction pa6i connection close kari dese.

Aa ek typical website no case rehse jenathi aapde website par pool na user karta hoi aevo rehse jema darek page ek separate connection rehse.

then -f ae file no path aapva mate 6e. and pa6i -p ae port mate 6e.

mane aa result malyu.

pgbench (16.10 (Ubuntu 16.10-0ubuntu0.24.04.1))
starting vacuum...end.
transaction type: multiple scripts
scaling factor: 1
query mode: simple
number of clients: 20
number of threads: 1
maximum number of tries: 1
number of transactions per client: 1000
number of transactions actually processed: 20000/20000
number of failed transactions: 0 (0.000%)
latency average = 25.076 ms
average connection time = 1.240 ms
tps = 797.577438 (including reconnection times)
SQL script 1: <builtin: select only>
 - weight: 1 (targets 50.0% of total)
 - 10003 transactions (50.0% of total, tps = 398.908356)
 - number of failed transactions: 0 (0.000%)
 - latency average = 10.325 ms
 - latency stddev = 6.747 ms
SQL script 2: /tmp/test_db/select.sql
 - weight: 1 (targets 50.0% of total)
 - 9997 transactions (50.0% of total, tps = 398.669083)
 - number of failed transactions: 0 (0.000%)
 - latency average = 10.372 ms
 - latency stddev = 6.751 ms

means, jo aapde reconnect karie dark vakhte to aapde approx. 797 per seconds j kari saksu.

have appde PgBouncer wada connection sathe try karie.

pgbench -c 20 -t 1000 -S test_db -C -f /tmp/test_db/select.sql -p 6432

aa aapdne aa result aapse.

pgbench (16.10 (Ubuntu 16.10-0ubuntu0.24.04.1))
starting vacuum...end.
transaction type: multiple scripts
scaling factor: 1
query mode: simple
number of clients: 20
number of threads: 1
maximum number of tries: 1
number of transactions per client: 1000
number of transactions actually processed: 20000/20000
number of failed transactions: 0 (0.000%)
latency average = 11.283 ms
average connection time = 0.550 ms
tps = 1772.522289 (including reconnection times)
SQL script 1: <builtin: select only>
 - weight: 1 (targets 50.0% of total)
 - 9997 transactions (50.0% of total, tps = 885.995266)
 - number of failed transactions: 0 (0.000%)
 - latency average = 5.195 ms
 - latency stddev = 3.804 ms
SQL script 2: /tmp/test_db/select.sql
 - weight: 1 (targets 50.0% of total)
 - 10003 transactions (50.0% of total, tps = 886.527023)
 - number of failed transactions: 0 (0.000%)
 - latency average = 5.133 ms
 - latency stddev = 3.721 ms

aapde joi sakie 6ie k ahi tps ni value 6e 1772. means ek reconnection karsu to bhi aapde 1772 transaction per second perform kari saksu.

nahi matra tps, aapde joi sakie 6ie k latency average bhi ghani sari 6e. without PgBouncer ma 25.076 ms hatu jyare with PgBouncer 11.283 ms 6e.

Average connection time ma pan ghano fark 6e. without PgBouncer 1.240 ms hatu jyare with PgBouncer 0.550 ms 6e.