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


  1. Note, tested upon Ubuntu Maverick, with kernel version Linux version 2.6.35-28-server.

  2. Thanks for th tests, interesting ext4 at +/- 2% difference might be within margin of error.

  3. To find an upper limit for the possible performance of the computer, you could see how it runs with the postgres data files placed on /dev/shm. I did this at my last work where we were running migration queries that took 3 hours using hard drives, and then only 1/2 hour in /dev/shm.

  4. Thanks, that's an interesting point.
    Next time I run this bank of tests, I'll include a ram disk option.