How to use pgbench

Posted on In

pgbench is a benchmarking tool bundled with PostgreSQL, designed to simulate a TPC-B-like workload, not a full TPC-C

1. Initialize the Test Database

This sets up the schema and populates data.

1
pgbench -i -s 10 mydb
  • -i: Initialize the database.
  • -s 10: Scale factor. Each scale unit ~100,000 rows in the pgbench_accounts table.
  • mydb: The database to test.

2. Run a Simple Benchmark Test

1
pgbench -c 10 -j 2 -T 60 mydb
  • -c 10: 10 concurrent clients.
  • -j 2: 2 threads.
  • -T 60: Run for 60 seconds.
  • mydb: Target database.

It will output something like:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
[postgres@iZ2ze4mflpfiplp0evcw8gZ root]$ pgbench -c 10 -j 2 -T 60 mydb
pgbench (18devel)
starting vacuum...end.
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 10
query mode: simple
number of clients: 10
number of threads: 2
maximum number of tries: 1
duration: 60 s
number of transactions actually processed: 12841
number of failed transactions: 0 (0.000%)
latency average = 46.726 ms
initial connection time = 39.072 ms
tps = 214.013767 (without initial connection time)
[postgres@iZ2ze4mflpfiplp0evcw8gZ root]$

3. Run Custom SQL Scripts

You can benchmark with custom SQL transactions:

1
pgbench -f myscript.sql -c 10 -T 60 mydb

Where myscript.sql contains something like:

1
2
3
4
BEGIN;
SELECT * FROM pgbench_accounts WHERE aid = :aid;
UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid;
END;

Use :variable for substitution. We can define variables using -D:

1
pgbench -f myscript.sql -D aid=12345 -D delta=50 -c 10 -T 60 mydb