The arrow of time

The results of running PostgreSQL 8.4 on a 8 core (2x quad Xeon 5405), 4 GB machine, with FreeBSD 8.0 AMD64 with pgbench scale of "70" (a bit over 1.2 GB database) are given in this graph:

I tried three combinations: TPC-B-like test on the "md" device (memory device / ramdrive with UFS mounted "async" on top of it), TPC-B-like on tmpfs and SELECT-only test on tmpfs.

Tmpfs was consistently a bit faster than md (even for the SELECT-only variant which is not present in this graph) so I guess it's mature enough. Though the tmpfs in 8.1 contains some small bugs AFAIK, the one which will be in 9 doesn't (or at least passes internal fsx tests).

So there it is - given an infinitely fast hard drive, PostgreSQL on this hardware and this OS would pull a bit over 6000 TPS (write transactions) or a bit over 40,000 read-only transactions. I think the write transaction rate is lower than it should be but I cannot think of any tuning to do which would affect a database on tmpfs (while at the same time keeping the configuration similar to "production"-ready, i.e. no cheating like turning off fsync).

Then there is the matter of falling performance after 8 clients (8 being the number of CPUs in the system). At the same time, the system idle time rises sharply to around 60% so we have the paradox of less performance with more idle time - it could be a sign of some bad lock contention.