pgbench

A utility for running benchmark tests

pgbench is a core utility which runs benchmarking tests on PostgreSQL.

pgbench was added in PostgreSQL 7.0.

Usage

pgbench is able to generate a sample set of data and run a default set of queries against that data(loosely based on TPC-B); alternatively, user-supplied custom scripts can be executed.

Default data and queries

If using the built-in benchmarking functionality, pgbench needs to be executed twice, once to initialise the data, once to run the benchmark queries.

The initialisation step is executed with pgbench -i; four tables will be created (existing tables with the same name will be dropped by default). Three of the tables will be pre-populated with generated data; the amount of data is controlled by the -s/--scale option (default: 1). The following table shows the tables generated and the number of rows inserted at different scale factors:

  1 10 100
pgbench_branches 1 10 100
pgbench_tellers 10 100 1,000
pgbench_accounts 100,000 1,000,000 10,000,000
pgbench_history 0 0 0

By default the rows are inserted as individual INSERT statements generated by the client. If the option -I/--init-steps includes the G flag, the data will be created using generate_series().

After generating the data, pgbench can be executed a second time to run the built-in TPC-B-like transaction multiple times. Each transaction runs the following commands::

\set aid random(1, 100000 * :scale)
\set bid random(1, 1 * :scale)
\set tid random(1, 10 * :scale)
\set delta random(-5000, 5000)
BEGIN;
UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid;
SELECT abalance FROM pgbench_accounts WHERE aid = :aid;
UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid;
UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid;
INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);
END;

Change history

work-in-progress

Examples

Default tests

The default tests provided with pgbench require the installation of a set of sample tables and data into the target database; this is done with:

pgbench -i  [ other-options ] dbname

where other-options should include the same connection parameters used by psql and other libpq-based clients.

Executing pgbench again without the -i option will cause a basic test to be executed, e.g.:

postgres:~$ pgbench -U postgres testdb
starting vacuum...end.
transaction type: TPC-B (sort of)
scaling factor: 1
query mode: simple
number of clients: 1
number of threads: 1
number of transactions per client: 10
number of transactions actually processed: 10/10
tps = 108.049703 (including connections establishing)
tps = 110.750557 (excluding connections establishing)

The default options do not provide particularly useful output; the most useful options for tuning pgbench are:

  • -c (number of clients)
  • -t (number of transactions)
  • -T (time limit)
  • PostgreSQL documentation: pgbench
  • PostgreSQL community wiki: Pgbench

Categories

Contrib module, Performance