Effects of filesystems and mount options upon PostgreSQL performance

I have done some testing on different filesystems and mount options and how they effect PostgreSQL performance.

The performance was measured with pgbench, using the following script:


createdb bench
/usr/lib/postgresql/8.4/bin/pgbench -i -s 10 bench
sleep 5
/usr/lib/postgresql/8.4/bin/pgbench -c 10 -T 60 bench
dropdb bench


The tests were performed on a 3GHz quad-core Intel i7 CPU with a triplet of 7200rpm SATA drives in a RAID-0 configuration.

These are the results -- note that test runs varied slightly between runs even of the same options.. by 2-3 units on the ext4 tests, and probably the same percentage (1%) on others.

Score is in average transactions-per-second. Higher values are better.

ext4:
235   data=ordered,strictatime
231   data=ordered,relatime
235   data=ordered,noatime
231   data=writeback,strictatime
235   data=writeback,relatime
238   data=writeback,noatime
235   data=writeback,noatime,commit=999
2392  data=writeback,noatime,barrier=0

xfs:
231   relatime,noquota
227   noatime,noquota
2439  noatime,noquota,nobarrier

btrfs:
62    defaults
67    nodatacow
69    nodatacow,noatime

My conclusions are:
1) btrfs sucks for database use, even with the recommended flags set for database use.

2) ext4 performs best with the recommended-for-db-use flags set (ie. data=writeback,noatime).
However the gains aren't massive over the defaults (which are data=ordered,relatime).

3) Disabling barriers gives a MAHOOSIVE performance increase, although it's noted that you should only do this if you have drives or raid cards with battery-backed-up write caches. (I don't, but your production systems usually do. Obviously, confirm this and test it by pulling the plug before actually changing this option in production though!)