pgbench allows us to run a benchmark test on PostgreSQL based on TPC-B
1. Prepare pgbench
below I’m using default postgres db, but ideally use another database which can be specified as the very last argument:
Usage: pgbench [OPTION]… [DBNAME]
-i is for initialise command to run
-s is the scale factor so how many rows to create = s * 100,000
cd /usr/pgsql-16/bin
./pgbench -i -s 10This creates and populates 4 tables with primary keys in the postgres database, with 1000,000 rows
output:
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)…
100000 of 100000 tuples (100%) done (elapsed 0.29 s, remaining 0.00 s)
vacuuming…
creating primary keys…
done in 0.74 s (drop tables 0.01 s, create tables 0.02 s, client-side generate 0.41 s, vacuum 0.12 s, primary keys 0.18 s).
generating data (client-side)…
1000000 of 1000000 tuples (100%) done (elapsed 3.36 s, remaining 0.00 s)
vacuuming…
creating primary keys…
done in 5.38 s (drop tables 0.06 s, create tables 0.01 s, client-side generate 3.47 s, vacuum 0.45 s, primary keys 1.40 s).
2. Launch test
-c specifies number of concurrent connections default 1
-T specifies time for test to run
./pgbench -c 20 -T 120 3. Monitor or review performance using pgadmin or PEM
PEM example below:
