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

Re: Periodically slow inserts

From: Greg Smith <greg(at)2ndquadrant(dot)com>
To: Gael Le Mignot <gael(at)pilotsystems(dot)net>
Cc: Leonardo Francalanci <m_lists(at)yahoo(dot)it>, pgsql-performance(at)postgresql(dot)org
Subject: Re: Periodically slow inserts
Date: 2010-10-21 16:03:32
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-performance
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: 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(at)2ndQuadrant(dot)com Baltimore, MD
PostgreSQL Training, Services and Support
Author, "PostgreSQL 9.0 High Performance"

In response to

pgsql-performance by date

Next:From: Gael Le MignotDate: 2010-10-21 16:07:47
Subject: Re: Periodically slow inserts
Previous:From: Greg SmithDate: 2010-10-21 15:45:11
Subject: Re: New wiki page on write reliability

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