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

Re: Re: [COMMITTERS] pgsql: Revert "commit_delay" change; just add comment that we don't hav

From: Greg Smith <greg(at)2ndQuadrant(dot)com>
To: Peter Geoghegan <peter(at)2ndquadrant(dot)com>
Cc: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, pgsql-hackers(at)postgresql(dot)org, Bruce Momjian <bruce(at)momjian(dot)us>
Subject: Re: Re: [COMMITTERS] pgsql: Revert "commit_delay" change; just add comment that we don't hav
Date: 2012-09-06 03:43:45
Message-ID: 50481BF1.9010601@2ndQuadrant.com (view raw or flat)
Thread:
Lists: pgsql-committerspgsql-hackers
On 08/15/2012 11:41 AM, Peter Geoghegan wrote:
> I know that someone is going to point out that in some particularly benchmark,
> they can get another relatively modest increase in throughput (perhaps
> 2%-3%) by splitting the difference between two adjoining millisecond
> integer values. In that scenario, I'd be tempted to point out that
> that increase is quite unlikely to carry over to real-world benefits,
> because the setting is then right on the cusp of where increasing
> commit_delay stops helping throughput and starts hurting it.

You guessed right on that.  I just responded to your survey over on 
pgsql-performance with two cases where older versions found optimal 
performance with commit_delay in the <=10 usec range.  Those are all in 
the BBWC case that I don't think you've been testing much of yet.

I recall Jignesh Shah reported his seeing that was from slightly better 
chunking of writes to disk, with a small but measurable drop in disk I/O 
operations (such as IOPS) relative to TPS.  The average throughput was 
no different; the number of *operations* was smaller though.  Less 8K 
I/O requests, more 16K+ ones.  Like a lot of these situations, adding 
some latency to every transactions can make them batch better.  And that 
can unexpectedly boost throughput enough that net latency is actually 
faster.  It's similar to how adding input queue latency with a pooler, 
limiting active connections, can actually make latency better by 
increasing efficiency.

On higher-end storage you can reach a point where IOPS gets high enough 
that the per-operation overhead becomes a problem, on top of the usual 
"is there enough write throughput?" question.  I suspect this situation 
might even be more common now, given IOPS issues like this are commonly 
highlighted when people do SSD reviews.

I still don't know that it's a widely popular situation.  But this 
particular use case has been one of the more persistent ones arguing to 
keep the parameter around until now.  Making sub-microsecond resolution 
on the parameter go away would effectively trash it just when it might 
get even more useful than before.

-- 
Greg Smith   2ndQuadrant US    greg(at)2ndQuadrant(dot)com   Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.com


In response to

pgsql-hackers by date

Next:From: Stephen FrostDate: 2012-09-06 03:44:20
Subject: Re: Draft release notes complete
Previous:From: Andrew DunstanDate: 2012-09-06 03:33:35
Subject: Re: Draft release notes complete

pgsql-committers by date

Next:From: Peter EisentrautDate: 2012-09-06 04:53:08
Subject: pgsql: Translation updates
Previous:From: Tom LaneDate: 2012-09-06 03:37:53
Subject: pgsql: Fix typo in information_schema documentation.

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