Pgbench

pgbench

pgbench is a popular benchmarking tool used by many developers and hackers to do quick performance test with PostgreSQL on a system setup.

On this page are recommendations from community on best ways to test with pgbench on a relatively bigger scale so that the frame of reference is consistent for the rest of the community reading into the results of pgbench

pgbench options

• -s = At minimum should be greater than the number of concurrent clients (-c) option and typically be bigger so as to be more meaningful
• -c = Generally pgbench client becomes cpu limited somewhere between 150-250 concurrent users. Also using less than 2 will not generally show contentions in lock much
• -t = Should be reasonably high. For example "HOT" benefit of limiting bloat in 8.3 is not clearly visible if the benchmark ran for short duration of time

Additional helpful information about parameters

• Determining the right pgbench database size scale (archived article from 2009)

postgresql.conf parameters which need to be bumped up from defaults

• wal_buffers
• shared_buffers
• max_connections

Setup Tuning

For a relatively bigger setup, it is recommended to run pgbench client (the program pgbench itself) from a separate system than the system being tested. This would mean that network between the two system needs to be optimized. Monitor pgbench client itself to see that it does not become CPU saturated process (either via top or prstat -am)

Disk Tuning

• Separate out Log (pg_xlog) to different file system)

install note

In the 9.1 package install for ubuntu pgbench is not wrapped for user, you can use the following to make it accessible

sudo ln -s ../share/postgresql-common/pg_wrapper /usr/bin/pgbench