Re: Poor performance of btrfs with Postgresql

On 04/21/2011 02:22 AM, Toby Corkindale wrote:
> I also tested btrfs, and was disappointed to see it performed
> *dreadfully* - even with the recommended options for database loads.
>
> Best TPS I could get out of ext4 on the test machine was 2392 TPS, but
> btrfs gave me just 69! This is appalling performance. (And that was
> with nodatacow and noatime set)

I don't run database performance tests until I've tested the performance
of the system doing fsync calls, what I call its raw commit rate.
That's how fast a single comitting process will be able to execute
individual database INSERT statements for example. Whether or not
barriers are turned on or not is the biggest impact on that, and from
what you're describing it sounds like the main issue here is that you
weren't able to get btrfs+nobarrier performing as expected.

If you grab
http://projects.2ndquadrant.it/sites/default/files/bottom-up-benchmarking.pdf
page 26 will show you how to measure fsync rate directly using
sysbench. Other slides cover how to get sysbench working right, you'll
need to get a development snapshot to compile on your Ubuntu system.

General fsync issues around btrfs are still plentiful it seems.
Installing packages with dpkg sometimes does that (I haven't been
following exactly which versions of Ubuntu do and don't fsync), so there
are bug reports like
https://bugs.launchpad.net/ubuntu/+source/dpkg/+bug/570805 and
https://bugs.launchpad.net/ubuntu/+source/dpkg/+bug/607632

One interesting thing from there is an idea I'd never though of: you
can link in an alternate system library that just ignore fsync if you
want to test turning it off above the filesystem level. Someone has
released a package to do just that, libeatmydata:
http://www.flamingspork.com/projects/libeatmydata/

--
Greg Smith 2ndQuadrant US greg(at)2ndQuadrant(dot)com Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us
"PostgreSQL 9.0 High Performance": http://www.2ndQuadrant.com/books