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

Re: Long Running Commits - Not Checkpoints

From: Greg Smith <gsmith(at)gregsmith(dot)com>
To: Brad Nicholson <bnichols(at)ca(dot)afilias(dot)info>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Long Running Commits - Not Checkpoints
Date: 2007-09-13 16:12:06
Message-ID: Pine.GSO.4.64.0709131149390.20144@westnet.com (view raw or flat)
Thread:
Lists: pgsql-performance
On Thu, 13 Sep 2007, Brad Nicholson wrote:

> One big difference though is that the old array had 16 GB of cache, the 
> new one has 4 GB.
>
> We have enough IO to spare that we have the bgwriter cranked up pretty 
> high, dirty buffers are getting quickly.

If your system is very active, running the bgwriter very aggressively will 
result in the cache on the disk array being almost continuously filled 
with pending writes that then trickle their way onto real disk eventually. 
If the typical working set on this system results in the amount of 
background writer cached writes regularly being >4GB but <16GB, that would 
explain what you're seeing.  The resolution is actually unexpected by most 
people:  you make the background writer less aggressive so that it's 
spewing less redundant writes clogging the array's cache, leaving more 
cache to buffer the actual commits so they don't block.  This will 
increase the odds that there will be a checkpoint block instead, but if 
you're seeing none of them right now you may have some margin there to 
reduce the BGW activity without aggrevating checkpoints.

Since you're probably not monitoring I/O waits and similar statistics on 
how the disk array's cache is being used, whether this is happening or not 
to you won't be obvious from what the operating system is reporting.  One 
or two blocked writes every couple of minutes won't even show up on the 
gross statistics for operating system I/O waits; on average, they'll still 
be 0.  So it's possible you may think you have plenty of I/O to spare, 
when in fact you don't quite have enough--what you've got is enough cache 
that the OS can't see where the real I/O bottleneck is.

I'd be curious to see how you've got your background writer configured to 
see if it matches situations like this I've seen in the past.  The 
parameters controlling the all scan are the ones you'd might consider 
turning down, definately the percentage and possibly the maxpages as well.

--
* Greg Smith gsmith(at)gregsmith(dot)com http://www.gregsmith.com Baltimore, MD

In response to

Responses

pgsql-performance by date

Next:From: Brad NicholsonDate: 2007-09-13 16:19:45
Subject: Re: Long Running Commits - Not Checkpoints
Previous:From: Erik JonesDate: 2007-09-13 16:03:20
Subject: Re: [Again] Postgres performance problem

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