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!

How to perform pgbench initialization into a new database using a nondefault user

For the database pgbenchdb creation refer to https://oracledbaplanner.blogspot.com/2023/03/how-to-create-postgresql-database-from.html

In this blog we will initialize the tables used for stress testing using pgbench utility

Command: pgbench -U pgbenchusr -i -s 50 pgbenchdb

pgbench - binary that comes by default with postgresqlserver,lib,PGDG install (not even contrib is needed).
-U is user to connect to for loading the data
-i is to perform initialization step
-s is the scale factor
pgbenchdb is the db where we will be loading the data

Actual command output:

-bash-4.2$ pgbench -U pgbenchusr -i -s 50 pgbenchdb dropping old tables... NOTICE:  table "pgbench_accounts" does not exist, skipping NOTICE:  table "pgbench_branches" does not exist, skipping NOTICE:  table "pgbench_history" does not exist, skipping NOTICE:  table "pgbench_tellers" does not exist, skipping creating tables... generating data (client-side)... 5000000 of 5000000 tuples (100%) done (elapsed 22.13 s, remaining 0.00 s) vacuuming... creating primary keys... done in 33.64 s (drop tables 0.00 s, create tables 0.02 s, client-side generate 22.22 s, vacuum 2.76 s, primary keys 8.65 s). -bash-4.2$

Verify the data load:

postgres=# \l+                                                                      List of databases    Name    |   Owner    | Encoding |   Collate   |    Ctype    |   Access privileges   |  Size   | Tablespace |      Description -----------+------------+----------+-------------+-------------+-----------------------+---------+------------+----------- ---------------------------------  pgbenchdb | pgbenchusr | UTF8     | en_US.UTF-8 | en_US.UTF-8 |                       | 756 MB  | pg_default |  postgres  | postgres   | UTF8     | en_US.UTF-8 | en_US.UTF-8 |                       | 8769 kB | pg_default | default ad ministrative connection database  template0 | postgres   | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +| 8617 kB | pg_default | unmodifiab le empty database            |            |          |             |             | postgres=CTc/postgres |         |            |  template1 | postgres   | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +| 8617 kB | pg_default | default te mplate for new databases            |            |          |             |             | postgres=CTc/postgres |         |            | (4 rows)   postgres=#   postgres=# \c pgbenchdb You are now connected to database "pgbenchdb" as user "postgres". pgbenchdb=# \dt+                                           List of relations  Schema |       Name       | Type  |   Owner    | Persistence | Access method |  Size   | Description --------+------------------+-------+------------+-------------+---------------+---------+-------------  public | pgbench_accounts | table | pgbenchusr | permanent   | heap          | 641 MB  |  public | pgbench_branches | table | pgbenchusr | permanent   | heap          | 40 kB   |  public | pgbench_history  | table | pgbenchusr | permanent   | heap          | 0 bytes |  public | pgbench_tellers  | table | pgbenchusr | permanent   | heap          | 56 kB   | (4 rows)   pgbenchdb=# select relname,relpages,reltuples from pg_class where relname like 'pgbench%' and relkind='r';      relname      | relpages | reltuples ------------------+----------+-----------  pgbench_accounts |    81968 |     5e+06  pgbench_branches |        1 |        50  pgbench_history  |        0 |         0  pgbench_tellers  |        3 |       500 (4 rows)   pgbenchdb=#

Verify mountpoint utilization:

-bash-4.2$ df -h /pgdata

Filesystem                          Size  Used Avail Use% Mounted on

/dev/mapper/appdata--vg-pgdata--lv   10G   84M   10G   1% /pgdata

-bash-4.2$

 

After load:

-bash-4.2$ df -h /pgdata

Filesystem                          Size  Used Avail Use% Mounted on

/dev/mapper/appdata--vg-pgdata--lv   10G  1.3G  8.8G  13% /pgdata

-bash-4.2$

Thank you!

How to create a postgresql database from template0 with owner assigned to another user than the creator

Please find below steps in creating a new db from template 0 and assign it a new owner during creation step itself.

Requirement for such need: [from postgresql guide]

https://www.postgresql.org/docs/current/manage-ag-templatedbs.html

"By instructing CREATE DATABASE to copy template0 instead of template1, you can create a “pristine” user database (one where no user-defined objects exist and where the system objects have not been altered) that contains none of the site-local additions in template1. This is particularly handy when restoring a pg_dump dump: the dump script should be restored in a pristine database to ensure that one recreates the correct contents of the dumped database, without conflicting with objects that might have been added to template1 later on"

Before:
postgres=# \l+
                                                                    List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   |  Size   | Tablespace |                Description
-----------+----------+----------+-------------+-------------+-----------------------+---------+------------+--------------------------------------------
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |                       | 8769 kB | pg_default | default administrative connection database
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +| 8617 kB | pg_default | unmodifiable empty database
           |          |          |             |             | postgres=CTc/postgres |         |            |
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +| 8617 kB | pg_default | default template for new databases
           |          |          |             |             | postgres=CTc/postgres |         |            |
(3 rows)

Creating the database:
postgres=# create database pgbenchdb owner pgbenchusr template template0;
CREATE DATABASE

Post create validation:
postgres=# \l+
                                                                     List of databases
   Name    |   Owner    | Encoding |   Collate   |    Ctype    |   Access privileges   |  Size   | Tablespace |                Description
-----------+------------+----------+-------------+-------------+-----------------------+---------+------------+--------------------------------------------
 pgbenchdb | pgbenchusr | UTF8     | en_US.UTF-8 | en_US.UTF-8 |                       | 8617 kB | pg_default |
 postgres  | postgres   | UTF8     | en_US.UTF-8 | en_US.UTF-8 |                       | 8769 kB | pg_default | default administrative connection database
 template0 | postgres   | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +| 8617 kB | pg_default | unmodifiable empty database
           |            |          |             |             | postgres=CTc/postgres |         |            |
 template1 | postgres   | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +| 8617 kB | pg_default | default template for new databases
           |            |          |             |             | postgres=CTc/postgres |         |            |
(4 rows)
postgres=#

Thanks