PostgreSQL – Using pgbench for Benchmark Testing

The measurement is based on transactions per second.  In other words – you’ll define how many transactions to execute per client and this will result in a rate per second.

Using pgbench for Benchmark Testing

1. Create a sample database

CREATE DATABASE example;

We now have an empty database named example. Let’s return to our bash shell to execute pgbench commands. We can do this by issuing the \q (quit) command.

2. Initialize the pgbench DB. This will create the sample tables in the DB

pgbench -h 10.190.30.209 -p 5432 -U jvalencia -i -s 50 example

In the command above, we ran pgbench with the -i option and the -s option followed by the database name (example).

The -i (initialize) option will tell pgbench to initialize the database specified. This will create the following tables within the example database.

The -s is the scaling option.  It is used to multiply the number of rows entered into each table. It will take the default rows and multiply it by 50 or whatever scaling number you entered.

3. Establish a baseline.

pgbench -h 10.190.30.209 -p 5432 -U jvalencia -c 10 -j 2 -t 10000 example

-c number of clients

-j number of threads

-t amount of transactions

These values are 10000 transactions per client (10 x 10000 = 100,000 transactions).

3. Get results

Below is a sample result set.

The part of the output that we are most interested in is the following:

tps = 2077.939220 (without initial connection time)

This indicates that our baseline is 2077 database transactions per second.

Cheers!