Run pgbench on 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.
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)
The following are commonly used pgbench options and you can specify more as needed.
Number of clients simulated, that is, number of concurrent database sessions. Default is 1.
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.
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)