Thursday, April 21, 2011

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.

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

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

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!)