Perform a simple pgbench stress test

In this blog we will perform a simple stress test using pgbench utility

Prereqs: pgbenchdb, pgbenchusr and pgebnch initialization

Please read previous blog for initializing the pgbenchdb: https://oracledbaplanner.blogspot.com/2023/03/how-to-perform-pgbench-initialization.html

Round 1 without making connection for every TX (probably reuse the existing connections):
Command: pgbench -U pgbenchusr -c 10 -j 2 -t 10000 pgbenchdb

-bash-4.2$ pgbench -U pgbenchusr -c 10 -j 2 -t 10000 pgbenchdb pgbench (14.7) starting vacuum...end. transaction type: <builtin: TPC-B (sort of)> scaling factor: 50 query mode: simple number of clients: 10 number of threads: 2 number of transactions per client: 10000 number of transactions actually processed: 100000/100000 latency average = 13.762 ms initial connection time = 39.384 ms tps = 726.645160 (without initial connection time) -bash-4.2$

 
Round 2 with reconnect for each transaction:
command: pgbench -U pgbenchusr -c 10 -j 2 -t 10000 pgbenchdb -C

-bash-4.2$ pgbench -U pgbenchusr -c 10 -j 2 -t 10000 pgbenchdb -C pgbench (14.7) starting vacuum...end. transaction type: <builtin: TPC-B (sort of)> scaling factor: 50 query mode: simple number of clients: 10 number of threads: 2 number of transactions per client: 10000 number of transactions actually processed: 100000/100000 latency average = 93.096 ms average connection time = 16.177 ms tps = 107.416354 (including reconnection times) -bash-4.2$

 

The throughput declined by 7 times. I noticed the disk IO was around 12 to 30MB/s for first test vs in the second test the disk IO throughput didn’t cross 2MB/s or so.

Later to confirm the connection observation, I reran the test and found the connection time staying same for each TX without the -C flag vs with -C flag:

without -C flag: [backend_start is different from xact_start]

postgres=# select backend_start,xact_start from pg_stat_activity where datname='pgbenchdb' order by 1;

         backend_start         |          xact_start
-------------------------------+-------------------------------
 2023-03-22 20:51:34.820891+00 | 2023-03-22 20:54:03.948251+00
 2023-03-22 20:51:34.825184+00 | 2023-03-22 20:54:03.93858+00
 2023-03-22 20:51:34.829658+00 | 2023-03-22 20:54:03.939736+00
 2023-03-22 20:51:34.833159+00 | 2023-03-22 20:54:03.94329+00
 2023-03-22 20:51:34.836543+00 | 2023-03-22 20:54:03.939366+00
 2023-03-22 20:51:34.840321+00 |
 2023-03-22 20:51:34.844846+00 | 2023-03-22 20:54:03.944635+00
 2023-03-22 20:51:34.848401+00 | 2023-03-22 20:54:03.93928+00
 2023-03-22 20:51:34.851873+00 | 2023-03-22 20:54:03.938626+00
 2023-03-22 20:51:34.854958+00 | 2023-03-22 20:54:03.939234+00
(10 rows)

with -C flag: [backend_start is same as xact_start]

postgres=# select backend_start,xact_start from pg_stat_activity where datname='pgbenchdb' order by 1;

         backend_start         |          xact_start
-------------------------------+-------------------------------
 2023-03-22 21:12:27.528602+00 | 2023-03-22 21:12:27.551797+00
 2023-03-22 21:12:27.552594+00 | 2023-03-22 21:12:27.573826+00
 2023-03-22 21:12:27.56344+00  | 2023-03-22 21:12:27.573684+00
 2023-03-22 21:12:27.574332+00 | 2023-03-22 21:12:27.59264+00
 2023-03-22 21:12:27.578761+00 | 2023-03-22 21:12:27.59277+00
 2023-03-22 21:12:27.593158+00 |
 2023-03-22 21:12:27.609819+00 |
(7 rows)

Part of this benchmark test I ran the test in PG14 and PG15 - same machine one before and one after upgrade immediately.

without -C flag:
TPS: 654 [PG15] vs 726 [PG14] - 72 units reduced

with -C flag:
TPS: 91 [PG15] vs 107 [PG14] - 16 units reduced

Not sure where is the impact coming from, same machine, disk. Only change is version.

May be you can comment ;)

Thank you!