Today I am going to write about a bench marking tool that has been part of the postgres contrib module for a couple of years now and i have found it very useful for a couple of reasons:
–> If you have a specific batch of queries that you would like to benchmark it can be done using pgbench.
–> Upgrading to the new postgres version and would like to set a couple of benchmark jobs to check the performance.
–> Thinking about changing memory parameters and would like to run some tests before you can implement it in production.
–> Testing new hardware
First lets talk about the install process:
I am assuming you have postgres setup via source and source is available for install of pgbench.
Change working directory to contrib/pgbench
postgres@debian:~$make postgres@debian:~$make install
Once you have pgbench installed its time to initialize pgbench on the database you would like to run pgbench on.
postgres@debian:~$pgbench -i test
-i will initialize pgbench as in it creates tables and functions in the pgbench schema that will be used for bench marking tests.
next we can tell pgbench to use number of transactions(say 10), number of clients(10) to connect for this test.
postgres@debian:~$pgbench -c 10 -t 10 test
if you are getting the error
ERROR: relation "pgbench_branches" does not exist at character 22
you might need to initialize pgbench for the database you are running the test on.
By the end of the test you will receive the result set as below:
number of clients: 10 number of transactions per client: 10 number of transactions actually processed: 100/100 tps = 964.822569 (including connections establishing) tps = 1145.317940 (excluding connections establishing)
A more realistic result would be by increasing the number of transactions and the clients connected with the number that matches your current production standards. Run the test a couple of times and take an average to find what kind of results you can expect.
Apart from the standard test using pgbench you can also use it to test some of your current queries:
postgres@debian:~$pgbench -f testscript.sh test
test script can have any production sql in there and pgbench will execute them and provide the time taken to run the statements.
There are many more options like how long the test should last. If vacuum should run before the benchmark test or not, scale factor, select only tests etc. You can use any of those options to get the desired test results.
If you have any questions then leave a comment and I will be sure to get back.