Run pgbench on PostgreSQL

Posted on Edited on In , Views:

Install PostgreSQL

Refer to this post to install PostgreSQL.

Intro to pgbench

pgbench is a simple program for running benchmark tests on PostgreSQL. It runs the same sequence of SQL commands over and over, possibly in multiple concurrent database sessions, and then calculates the average transaction rate (transactions per second). By default, pgbench tests a scenario that is loosely based on TPC-B, involving five SELECT, UPDATE, and INSERT commands per transaction. However, it is easy to test other cases by writing your own transaction script files.

Available built-in scripts are: tpcb-like, simple-update and select-only.

Initialize PostgreSQL

You need to create a database prior to initialization. You can adjust the scale of database as needed. For example, -s 5000 will create 500,000,000 rows in the pgbench_accounts table. Roughly, the size is ~63GB.

# pg_server=10.10.10.243
# psql --host=$pg_server --port=5432 --username=postgres -w -c "create database testdb"

# scale=5000; pgbench -h $pg_server -p 5432 -U postgres -i -s $scale testdb


# psql --host=$pg_server --port=5432 --username=postgres -w -c "\l"
# psql --host=$pg_server --port=5432 --username=postgres -w -d testdb -c "\dt+"
                                        List of relations
 Schema |       Name       | Type  |  Owner   | Persistence | Access method |  Size   | Description
--------+------------------+-------+----------+-------------+---------------+---------+-------------
 public | pgbench_accounts | table | postgres | permanent   | heap          | 63 GB   |
 public | pgbench_branches | table | postgres | permanent   | heap          | 216 kB  |
 public | pgbench_history  | table | postgres | permanent   | heap          | 0 bytes |
 public | pgbench_tellers  | table | postgres | permanent   | heap          | 2200 kB |
(4 rows)

Pgbench options

The following are commonly used pgbench options and you can specify more as needed.

-
-c clients

–client=clients

Number of clients simulated, that is, number of concurrent database sessions. Default is 1.

-
-j threads

–jobs=threads

Number of worker threads within pgbench. Using more than one thread can be helpful on multi-CPU machines. Clients are distributed as evenly as possible among available threads. Default is 1.

-
-t transactions

–transactions=transactions

Number of transactions each client runs. Default is 10.

Run pgbench test

You should adjust the option values based on the available system resource. Usually, you have to experiment by gradually changing the values until you get the sweet spot.

# clients=192; threads=96; transactions=50000
# pgbench -h $pg_server -p 5432 -U postgres -c $clients -j $threads -t $transactions testdb -b simple-update
pgbench (15.3)
starting vacuum...end.
transaction type: <builtin: simple update>
scaling factor: 5000
query mode: simple
number of clients: 192
number of threads: 96
maximum number of tries: 1
number of transactions per client: 50000
number of transactions actually processed: 9600000/9600000
number of failed transactions: 0 (0.000%)
latency average = 18.814 ms
initial connection time = 97.392 ms
tps = 10204.933020 (without initial connection time)