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@2ndQuadrant.com   Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support  www.2ndQuadrant.us
"PostgreSQL 9.0 High Performance": http://www.2ndQuadrant.com/books