From: Greg Smith <greg@2ndquadrant.com>
To: Gael Le Mignot <gael@pilotsystems.net>
Cc: Leonardo Francalanci <m_lists@yahoo.it>
Cc: pgsql-performance@postgresql.org
Subject: Re: Periodically slow inserts
Date: Thu, 21 Oct 2010 12:03:32 -0400
Message-ID: <4CC06454.1000802@2ndquadrant.com> (raw)
In-Reply-To: <plop87tykfr3dq.fsf@aoskar.kilobug.org>
References: <plop8762wvspfb.fsf@aoskar.kilobug.org>
<857928.20870.qm@web29002.mail.ird.yahoo.com>
<plop87tykfr3dq.fsf@aoskar.kilobug.org>
Gael Le Mignot wrote:
> The delay is the delay of the "sync" part of
> the checkpoints :
>
> 2010-10-21 16:39:15 CEST LOG: checkpoint complete: wrote 365 buffers
> (11.9%); 0 transaction log file(s) added, 0 removed, 3 recycled;
> write=0.403 s, sync=21.312 s, total=21.829 s
>
> Maybe there is something I misunderstood, but aren't the checkpoints
> supposed to run smoothly over the checkpoint_completion_target interval ?
>
Well, first off you have to get the checkpoints spaced out in time
enough for that to work. Both checkpoint_segments and possibly
checkpoint_timeout may also need to be increased in order for the
checkpoint write spreading code to work. When I start seeing long sync
times, I'll usually shoot for >64 segments and >10 minutes for the
timeout to give that smoothing work some room to do what it's supposed to.
However, only the main checkpoint writes are spread over time. The hope
is that by the time the sync phase starts, the operating system will
have already written most of them out. Sometimes, particularly in
servers with lots of RAM for caching writes, this doesn't happen. In
that case, you can have gigabytes of data queued up at the beginning of
the sync phase--which is not spread out at all.
We are currently working on a "spread sync" feature for PostgreSQL that
makes this problem better on platforms/filesystems it's possible to
improve behavior on (you can't do anything about this issue on ext3 for
example). I'll be talking about that development at the PgWest
conference in a two weeks:
https://www.postgresqlconference.org/content/righting-your-writes and
hope to submit a patch with a first version of this feature to the
November development CommitFest, in hopes of it making it into version 9.1.
If you can't come up with any solution here and need help with your
current version sooner than that, we've already backported this
improvement all the way to V8.3; drop me an off-list note if you want to
discuss consulting services in this area we have available. If you're
lucky, just adjusting the segment and timeout values may be enough for you.
--
Greg Smith, 2ndQuadrant US greg@2ndQuadrant.com Baltimore, MD
PostgreSQL Training, Services and Support www.2ndQuadrant.us
Author, "PostgreSQL 9.0 High Performance"
http://www.2ndquadrant.com/books
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: greg@2ndquadrant.com, gael@pilotsystems.net, m_lists@yahoo.it
Subject: Re: Periodically slow inserts
In-Reply-To: <4CC06454.1000802@2ndquadrant.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