Thread

Linux filesystem performance and checkpoint sorting

Greg Smith <greg@2ndquadrant.com> — 2011-02-04T18:31:44Z

Switching to a new thread for this summary since there's some much more 
generic info here...at this point I've finished exploring the major 
Linux filesystem and tuning options I wanted to, as part of examining 
changes to the checkpoint code.  You can find all the raw data at 
http://www.2ndquadrant.us/pgbench-results/index.htm  Here are some 
highlights of what's been demonstrated there recently, with a summary of 
some of the more subtle and interesting data in the attached CSV file too:

-On ext3, tuning the newish kernel tunables dirty_bytes and 
dirty_background_bytes down to a lower level than was possible using the 
older dirty_*ratio ones shows a significant reduction in maximum latency 
on ext3; it drops to about 1/4 of the worst-case behavior.  
Unfortunately transactions per second takes a 10-15% hit in the 
process.  Not shown in the data there is that the VACUUM cleanup time 
between tests was really slowed down, too, running at around half the 
speed of when the system has a full-size write cache.

-Switching from ext3 to xfs gave over a 3X speedup on the smaller test 
set:  from the 600-700 TPS range to around 2200 TPS.  TPS rate on the 
larger data set actually slowed down a touch on XFS, around 10%.  Still, 
such a huge win when it's better makes it easy to excuse the occasional 
cases where it's a bit slower.  And the latency situation is just wildly 
better, the main thing that drove me toward using XFS more in the first 
place.  Anywhere from 1/6 to 1/25 of the worst-case latency seen on 
ext3.  With abusively high client counts for this hardware, you can 
still see >10 second pauses, but you don't see >40 second ones at 
moderate client counts like ext3 experiences.

-Switching to the lower possible dirty_*bytes parameters on XFS was 
negative in every way.  TPS was cut in half, and maximum latency 
actually went up.  Between this and the nasty VACUUM slowdown, I don't 
really see that much potential for these new tunables.  They do lower 
latency on ext3 a lot, but even there the penalty you pay for that is 
quite high.  VACUUM in particular seems to really, really benefit from 
having a giant write cache to dump its work into--possibly due to the 
way the ring buffer implementation avoids using the database's own cache 
for that work.

-Since earlier tests suggested sorting checkpoints gave little change on 
ext3, I started testing that with XFS instead.  The result is a bit 
messy.  At the lower scale, TPS went up a bit, but so did maximum 
latency.  At the higher scale, TPS dropped in some cases (typically less 
than 1%), but most latency results were better too.

At this point I would say checkpoint sorting remains a wash:  you can 
find workloads it benefits a little, and others it penalizes a little.  
I would say that it's neutral enough on average that if it makes sense 
to include for other purposes, that's unlikely to be a really bad change 
for anyone.  But I wouldn't want to see it committed by itself; there 
needs to be some additional benefit from the sorting before it's really 
worthwhile.

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

Re: Linux filesystem performance and checkpoint sorting

Josh Berkus <josh@agliodbs.com> — 2011-02-04T23:05:23Z

Greg,

Thanks for doing these tests!

So: Linux flavor?  Kernel version?  Disk system and PG directory layout?


-- 
                                  -- Josh Berkus
                                     PostgreSQL Experts Inc.
                                     http://www.pgexperts.com

Re: Linux filesystem performance and checkpoint sorting

Mark Kirkwood <mark.kirkwood@catalyst.net.nz> — 2011-02-04T23:31:51Z

On 05/02/11 07:31, Greg Smith wrote:
> Switching to a new thread for this summary since there's some much 
> more generic info here...at this point I've finished exploring the 
> major Linux filesystem and tuning options I wanted to, as part of 
> examining changes to the checkpoint code.  You can find all the raw 
> data at http://www.2ndquadrant.us/pgbench-results/index.htm

Awesome! Very useful results.

Are you going to do some runs with ext4? I'd be very interested to see 
how it compares (assuming that you are on a kernel version 2.6.32 or 
later so ext4 is reasonably stable...).

Cheers

Mark

Re: Linux filesystem performance and checkpoint sorting

Stephen J. Butler <stephen.butler@gmail.com> — 2011-02-04T23:55:13Z

On Fri, Feb 4, 2011 at 12:31 PM, Greg Smith <greg@2ndquadrant.com> wrote:
> -Switching from ext3 to xfs gave over a 3X speedup on the smaller test set:
>  from the 600-700 TPS range to around 2200 TPS.  TPS rate on the larger data
> set actually slowed down a touch on XFS, around 10%.  Still, such a huge win
> when it's better makes it easy to excuse the occasional cases where it's a
> bit slower.

Did you see that they improved XFS scalability in 2.6.37?

http://kernelnewbies.org/Linux_2_6_37#head-dfa29df2b21f5a72fb17f041a7356deeea3d159e

Looks like there's more XFS improvements in store for 2.6.38.

Re: Linux filesystem performance and checkpoint sorting

Greg Smith <greg@2ndquadrant.com> — 2011-02-05T04:47:11Z

Mark Kirkwood wrote:
> Are you going to do some runs with ext4? I'd be very interested to see 
> how it compares (assuming that you are on a kernel version 2.6.32 or 
> later so ext4 is reasonably stable...).

Yes, before I touch this system significantly I'll do ext4 as well, and 
this is running the Ubuntu 10.04 2.6.32 kernel so ext4 should be stable 
enough.  I have some PostgreSQL work that needs to get finished first 
though.

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

Re: Linux filesystem performance and checkpoint sorting

Greg Smith <greg@2ndquadrant.com> — 2011-02-05T05:36:39Z

Josh Berkus wrote:
> So: Linux flavor?  Kernel version?  Disk system and PG directory layout?
>   

OS configuration and PostgreSQL settings are saved into the output from 
the later runs (I added that somewhere in the middle):

http://www.2ndquadrant.us/pgbench-results/294/pg_settings.txt

That's Ubuntu 10.04, kernel 2.6.32. 

There is a test rig bug that queries the wrong PostgreSQL settings in 
the later ones, but they didn't change after #294 here.  The kernel 
configuration stuff is accurate through, which confirms exactly what 
settings for the dirty_* parameters was effective for each during the 
tests I was changing those around.

16GB of RAM, 8 Hyperthreaded cores (4 real ones) via Intel i7-870.  
Areca ARC-1210 controller, 256MB of cache.

Filesystem           1K-blocks      Used Available Use% Mounted on
/dev/sda1              40G  7.5G   30G  20% /
/dev/md1              838G   15G  824G   2% /stripe
/dev/sdd1             149G  2.1G  147G   2% /xlog

/stripe is a 3 disk RAID0, setup to only use the first section of the 
drive ("short-stroked").  That makes its performance a little more like 
a small SAS disk, rather than the cheapo 7200RPM SATA drives they 
actually are (Western Digital 640GB WD6400AAKS-65A7B).  /xlog is a 
single disk, 160GB WD1600AAJS-00WAA.  OS, server logs, and test results 
information all go to the root filesystem on a different drive.  My aim 
was to get similar performance to what someone with an 8-disk RAID10 
array might see, except without the redundancy.  Basic entry-level 
database server here in 2011.

bonnie++ on the main database disk:  read 301MB/s write 215MB/s, seeks 
423.4/second.  Measured around 10K small commits/second to prove the 
battery-backed write cache works fine.

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