I have been working on PostgreSQL tuning. Trying to come up with some reliable
and repeatable tested settings. The idea is that with some reliable numbers,
you can measure actual proformance improvement. 

pgbench is a pretty poor tool for doing this. It fluctuates a great deal. Just
from run to run. It is virtualy impossible to get reasonable numbers out of it. 

I have my benchmarking database set to a scale of 100. Here is my script:

#! /bin/sh
HOST=postgres
DB=bench
totxacts=10000
export PATH=$PATH:/usr/local/pgsql/bin

for c in 25 25 50 50 100 100
do
        ssh root@$HOST "sync;sync;sync;sleep 1"
        t=`expr $totxacts / $c`
        psql -h $HOST -c 'vacuum ' $DB
        psql -h $HOST -c 'checkpoint' $DB
        echo "===== sync ======" 1>&2
        ssh root@$HOST "sync;sync;sync;sleep 1"
        echo $c concurrent users... 1>&2
        ./pgbench -n -t $t -h $HOST -c $c $DB
done

Here are my results:
VACUUM
CHECKPOINT
===== sync ======
25 concurrent users...
transaction type: TPC-B (sort of)
scaling factor: 100
number of clients: 25
number of transactions per client: 400
number of transactions actually processed: 10000/10000
tps = 88.409581(including connections establishing)
tps = 88.617577(excluding connections establishing)
VACUUM
CHECKPOINT
===== sync ======
25 concurrent users...
transaction type: TPC-B (sort of)
scaling factor: 100
number of clients: 25
number of transactions per client: 400
number of transactions actually processed: 10000/10000
tps = 102.325257(including connections establishing)
tps = 102.606724(excluding connections establishing)
VACUUM
CHECKPOINT
===== sync ======
50 concurrent users...
transaction type: TPC-B (sort of)
scaling factor: 100
number of clients: 50
number of transactions per client: 200
number of transactions actually processed: 10000/10000
tps = 116.379559(including connections establishing)
tps = 117.103796(excluding connections establishing)
VACUUM
CHECKPOINT
===== sync ======
50 concurrent users...
transaction type: TPC-B (sort of)
scaling factor: 100
number of clients: 50
number of transactions per client: 200
number of transactions actually processed: 10000/10000
tps = 106.869515(including connections establishing)
tps = 107.479233(excluding connections establishing)
VACUUM
CHECKPOINT
===== sync ======
100 concurrent users...
transaction type: TPC-B (sort of)
scaling factor: 100
number of clients: 100
number of transactions per client: 100
number of transactions actually processed: 10000/10000
tps = 129.923876(including connections establishing)
tps = 131.757784(excluding connections establishing)
VACUUM
CHECKPOINT
===== sync ======
100 concurrent users...
transaction type: TPC-B (sort of)
scaling factor: 100
number of clients: 100
number of transactions per client: 100
number of transactions actually processed: 10000/10000
tps = 110.506228(including connections establishing)
tps = 111.858151(excluding connections establishing)

Tue, 12 Mar 2002 10:29:17 -0500
mlw <markw@mohawksoft.com>
pgbench consistency