Waiting for 8.4 – pgbench with timed execution

Takahiro Itagaki wrote, and Tom Lane committed nice patch, which I personally find really helpful:

Add a duration option to pgbench, so that test length can be specified in seconds instead of by number of transactions to run.  Takahiro Itagaki

You might wonder what's so great nice about it. Well, during my years as dba I've been many times in situation when I had to check scalability of hardware. This usually meant that we had to run some tests with variable number of concurrent sessions, and check how it worked out.

Simple way to do it:

for C in `seq 1 10 101`
do
    pgbench ... -c $C ... > output.$C 2>&1
done

The problem is, that it's quite difficult to predict when the test will finish.

It's especially important since you should be testing each iteration for time longer than couple of seconds (the longer the better).

So, basically I started to use much more complicated scripts, which tried to guess how many transactions (-t) to run on each iteration to make it finish in predictable time – like make the test run on the whole night, but they should finish before 9a.m. next day.

Luckily, with new switch (-T) this task became much easier:

pgbench -i -s 10
for C in `seq 1 5 96`
do
    pgbench ... -c $C -T 2100 ... > output.$C 2>&1
done

Now, I know it will finish in 20 (number of tests) * 2100 seconds, which is 42000 seconds, which is 11 hours 40 minutes.

Great. Simple (I guess) patch, which makes scalability tests much simpler and predictable.