How to use pgbench for PostgreSQL benchmark testing

18 November,2019 by Jack Vamvas

Pgbench is used for Postgresql benchmark testing. Pgbench iterates through a bunch of SQL statements .   There are various configurables such as sessions (single or multiple) and clients.   The pgbench approach is based on TPC-B . The TPC-B benchmark focuses on benchmarking as opposed to OLTP type 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. 

There is a default test framework – which involves 5 SELECT , UPDATE, INSERT commands per transaction- there are options to customise different testing approaches

 

 

Notes

  • I normally use pgbench for hardware and infrastructure benchmarking. For applications I use different methodology, based on custom transaction scripts and often application specific
  • Tps – transactions per second
  • Run from a remote server if network latency is trivial

 

Create a test database

CREATE DATABASE mydb

Initialise the pgbench db – this will create the sample tables in the db

pgbench -U postgres -p 5555 -i -s 50  mydb

-I initialize the db. Creates a bunch of default tables

-s  scaling option. i.e take the default rows and x 50 or whatever scaling number you require

This step will create 4 tables - which host the test date rows and other metadata

Schema | Name | Type | Owner
--------+------------------+-------+----------
public | pgbench_accounts | table | postgres
public | pgbench_branches | table | postgres
public | pgbench_history | table | postgres
public | pgbench_tellers | table | postgres

 

Establish a baseline

pgbench -U postgres -p 5555 -c 10 -j 2 -t 10000 mydb

-c number of clients

-j 2 number of threads

-t amount of transactions

These values are 10000 transactions per client. So : 10 x 10000 = 100,000 transactions

Get Results

A sample resultset 

number of clients: 10
number of threads: 2
number of transactions per client: 10000
number of transactions actually processed: 100000/100000
latency average = 7.198 ms
tps = 1389.356369 (including connections establishing)
tps = 1389.464745 (excluding connections establishing)

Read more on benchmark performance testing 

TPC-H generate test data , test queries and sql database benchmark ...

What is acceptable database server performance testing


Author: Jack Vamvas (http://www.dba-ninja.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on How to use pgbench for PostgreSQL benchmark testing


dba-ninja.com