benchmarking journalling file systems, fsync, and buffers.

I have been doing some benchmarking on RedHat 7.2
The machine
Dual PIII 650, 1 Gig RAM.
18G IBM SCSI LVD main drive with /pg_xlog
9G Seagate UW SE with /base.
2 Adaptec SCSI controllers.

Changes to default postgresql.conf:
tcpip_socket = true
max_connections = 128
shared_buffers = 4096
sort_mem = 2048
wal_files = 32
wal_sync_method = fdatasync
random_page_cost = 2

The script: (from Tom)
#! /bin/sh
HOST=slave2
DB=bench
totxacts=10000

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

The client machine was a separate Dual PIII 600 also running RedHat 7.2


The procedure:
RedHat uses ext3 by default. Ext3, if you do not already know, is ext2 with
journalling enabled. This is important because an ext3 file system can be
mounted as an ext2 file system without any changes. Simply specify it as such.

The first two tests used a stock RedHat 7.2 system with ext3.
The next two tests used that same system rebooted and mounting the file systems
as ext2.

ext3.txt are the tests run against the ext3 file systems with fsync set to
false.
ext3.fsync.txt are the tests with fsync set to true.
ext2.txt are the tests run against the database mounted as ext2 and fsync set
to false.
ext2.fsync.txt are the tests run with fsync set to true.

Journallng always affects performance. This is no surprise. If you have fsync
enabled, the affect is less pronounced. (this is also no surprise). One
interesting thing, as the number of concurrent connections goes up, the impact
of journalling and fsync are reduced.

After running these benchmarks, I decided to run a series of benchmarks
changing the number of buffers.

ext2.1024.txt
ext2.2048.txt
(4096 was the default in the previous tests)
ext2.8192.txt

I'm not sure of the digestion of all these numbers, but I thought some of you
guys would be interested in comparing notes.