Running benchmarks is an efficient method to gain optimal performance of the PostgreSQL DBMS. It can also bring valuable insights on the tuning and assists in growth and scalability. There are numerous benchmarking tools available for PostgreSQL, one of the most popular is pgbench coming with PostgreSQL installation.
To profile the database performance, it runs the same batch of SQL commands multiple times in concurrent database sessions and estimates the average transactions per second value. By default, pgbench uses the batch of five SELECT, UPDATE, and INSERT commands, but it can be customized by writing your own transaction scripts.
First step to use pgbench is to create a database where all tests will be run:
createdb mybenchmarks
The default testing transactions require specific tables to be set up, so the next step is to create and populate these tables using this command:
pgbench -i mybenchmarks
You may also need -h, -U and -p options to specify PostgreSQL host, username and password (similar to calling command line client psql). The step above is not required if you are testing a custom script. Instead, perform all setup prerequisites that are necessary for your tests.
This command creates 4 test tables 'pgbench_accounts', 'pgbench_branches', 'pgbench_history' and 'pgbench_tellers' with corresponding numbers of rows 100000, 1, 0, 10. If you need more valuable test, use scale factor option '-s' to multiply the number of generated rows.
Finally, to run default tests just type:
pgbench mybenchmarks
Typical output of running pgbench tests looks like:
transaction type: <builtin: 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 number of failed transactions: 0 (0.000%) latency average = 0.735 ms tps = 896.967014 (without initial connection time)
The first 6 lines of this report contain configuration parameters of pgbench, and the next lines reports statistics of testing such as number of processed/failed transactions, etc. The last line of the output is the number of transactions per second.
Have more questions? Contact us