From: Greg Smith <gsmith@gregsmith.com>
To: Peter Childs <peterachilds@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Long Running Commits - Not Checkpoints
Date: Fri, 14 Sep 2007 12:22:03 -0400 (EDT)
Message-ID: <Pine.GSO.4.64.0709141203300.17876@westnet.com> (raw)
In-Reply-To: <a2de01dd0709140002m54946c6bte398944bee8b2eaa@mail.gmail.com>
References: <1189692915.8624.22.camel@bnicholson-desktop>
<Pine.GSO.4.64.0709131149390.20144@westnet.com>
<1189700385.8624.32.camel@bnicholson-desktop>
<Pine.GSO.4.64.0709131339450.21671@westnet.com>
<a2de01dd0709140002m54946c6bte398944bee8b2eaa@mail.gmail.com>
On Fri, 14 Sep 2007, Peter Childs wrote:
> Are you suggesting that reducing bgwriter_delay and bg_writer_percent
> would reduce the time spent doing commits? I get quite a few commits
> that take over 500ms (the point when i start logging queries).
One very common cause for transactions blocking for as much as several
seconds is hitting a checkpoint, which in current versions causes a large
amount of data to be written and synchronized to the physical disk. If
you're already tracking long transactions and trying to figure out what's
causing them, you should set checkpoint_warning to a high value (the
maximum of 3600 is generally fine). That will give you a harmless warning
every time a checkpoint occurs. If the slow transactions line up with
checkpoints, then you might consider tuning or re-tuning the background
writer to elimite the delay.
In this particular case, their system had already been tuned so
aggressively that I wondered if the background writer was being a problem
rather than a solution. Reducing the percentage would turn it down a bit;
so would *increasing* the delay--they had already decreased it
considerably, making it 4X as active as the default.
--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: gsmith@gregsmith.com, peterachilds@gmail.com
Subject: Re: Long Running Commits - Not Checkpoints
In-Reply-To: <Pine.GSO.4.64.0709141203300.17876@westnet.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox