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

Re: Blocking excessively in FOR UPDATE

From: Claudio Freire <klaussfreire(at)gmail(dot)com>
To: Kevin Grittner <Kevin(dot)Grittner(at)wicourts(dot)gov>
Cc: postgres performance list <pgsql-performance(at)postgresql(dot)org>, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Subject: Re: Blocking excessively in FOR UPDATE
Date: 2011-11-04 17:22:42
Message-ID: CAGTBQpYU069d+WWw5GUgBD_oAYiONR-BXC_YOeUVwBm+wD-BVg@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
On Fri, Nov 4, 2011 at 2:07 PM, Kevin Grittner
<Kevin(dot)Grittner(at)wicourts(dot)gov> wrote:
> 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.

We have

shared_buffers = 2G
bgwriter_delay = 1000ms
effective_io_concurrency=8
synchronous_commit=off
wal_buffers=16M
wal_writer_delay=2000ms
commit_delay=10000
checkpoint_segments=72
checkpoint_timeout=60min
checkpoint_completion_target=0.8

I'm thinking bgwriter_delay and wal_writer_delay might not be working
as I expected, and that maybe checkpoint_segments=72 is a bit too
high, but we were having much worse I/O storms before I pushed it that
high. Looking at checkpoint logging for the last few days, it goes
almost always like:

checkpoint complete: wrote 589 buffers (3.6%); 0 transaction log
file(s) added, 0 removed, 8 recycled; write=590.325 s, sync=0.055 s,
total=590.417 s

590s seems an awful lot for 589 buffers.

In response to

Responses

pgsql-performance by date

Next:From: Shaun ThomasDate: 2011-11-04 18:26:39
Subject: Re: Blocking excessively in FOR UPDATE
Previous:From: Kevin GrittnerDate: 2011-11-04 17:07:49
Subject: Re: Blocking excessively in FOR UPDATE

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