Variable (degrading) perfomance

Hi all,

It seems that I have an issue with the performance of a PostgreSQL server.

I'm running write-intensive, TPC-C like tests. The workload consist of
150 to 200 thousand transactions. The performance varies dramatically,
between 5 and more than 9 hours (I don't have the exact figure for the
longest experiment). Initially the server is relatively fast. It
finishes the first batch of 50k transactions in an hour. This is
probably due to the fact that the database is RAM-resident during this
interval. As soon as the database grows bigger than the RAM the
performance, not surprisingly, degrades, because of the slow disks.
My problem is that the performance is rather variable, and to me
non-deterministic. A 150k test can finish in approx. 3h30mins but
conversely it can take more than 5h to complete.
Preferably I would like to see *steady-state* performance (where my
interpretation of the steady-state is that the average
throughput/response time does not change over time). Is the steady-state
achievable despite the MVCC and the inherent non-determinism between
experiments? What could be the reasons for the variable performance?
- misconfiguration of the PG parameters (e.g. autovacuum does not cope
with the dead tuples on the MVCC architecture)
- file fragmentation
- index bloat
- ???
The initial size of the database (actually the output of the 'du -h'
command) is ~ 400 MB. The size increases dramatically, somewhere between
600MB and 1.1GB

I have doubted the client application at some point too. However, other
server combinations using different DBMS exhibit steady state
performance.As a matter of fact when PG is paired with Firebird, through
statement-based replication middleware, the performance of the pair is
steady too.

The hardware configuration:
Client machine
- 1.5 GHz CPU Pentium 4
- 1GB Rambus RAM
- Seagate st340810a IDE disk (40GB), 5400 rpms

Server machine
- 1.5 GHz CPU Pentium 4
- 640 MB Rambus RAM
- Seagate Barracuda 7200.9 rpms
- Seagate st340810a IDE disk (40GB) - the WAL is stored on an ext2 partition

The Software configuration:
The client application is a multi-threaded Java client running on Win
2000 Pro sp4
The database server version is 8.1.5 running on Fedora Core 6.
Please find attached:
1 - the output of vmstat taken after the first 60k transactions were
executed
2 - the postgresql.conf file

Any help would be appreciated.

Best regards,
Vladimir
--

Vladimir Stankovic T: +44 20 7040 0273
Research Student/Research Assistant F: +44 20 7040 8585
Centre for Software Reliability E: V(dot)Stankovic(at)city(dot)ac(dot)uk
City University
Northampton Square, London EC1V 0HB