Re: Blocking excessively in FOR UPDATE

Claudio Freire <klaussfreire@gmail.com> wrote:
> On Fri, Nov 4, 2011 at 1:26 PM, Kevin Grittner
> <Kevin.Grittner@wicourts.gov> wrote:
>> As already pointed out, SELECT FOR UPDATE will require a disk
>> write of the tuple(s) read.  If these are glutting, increasing
>> shared_buffers would tend to make things worse.
>
> I thought shared_buffers improved write caching.
> We do tend to write onto the same rows over and over.

PostgreSQL is very aggressive about holding on to dirty buffers as
long as possible, in hopes of reducing duplicate page writes.  This
can work against the goal of consistent latency.  In our shop we
needed to make the background writer more aggressive and keep shared
buffers in the 0.5GB to 2GB range (depending on hardware and
workload) to prevent write gluts leading to latency spikes.  In the
mildly surprising department, the OS seemed to do a decent job of
spotting pages receiving repeated writes and hold back on an OS
level write, while pushing other pages out in a more timely fashion
-- there was no discernible increase in OS write activity from
making these changes.  I know other people have had other
experiences, based on their workloads (and OS versions?).

Before anything else, you might want to make sure you've spread your
checkpoint activity as much as possible by setting
checkpoint_completion_target = 0.9.

-Kevin