Skip site navigation (1) Skip section navigation (2)

Linux filesystem performance and checkpoint sorting

From: Greg Smith <greg(at)2ndquadrant(dot)com>
To: PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>
Subject: Linux filesystem performance and checkpoint sorting
Date: 2011-02-04 18:31:44
Message-ID: 4D4C4610.1030109@2ndquadrant.com (view raw or flat)
Thread:
Lists: pgsql-hackers
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(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


Attachment: FilesystemFsync.csv
Description: text/csv (867 bytes)

Responses

pgsql-hackers by date

Next:From: Greg SmithDate: 2011-02-04 18:43:23
Subject: Re: Spread checkpoint sync
Previous:From: Robert HaasDate: 2011-02-04 18:18:28
Subject: Re: multiset patch review

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group