Message-ID: <535EBCEC.4000501@fuzzy.cz>
Date: Mon, 28 Apr 2014 22:41:16 +0200
From: Tomas Vondra <tv@fuzzy.cz>
User-Agent: Mozilla/5.0 (X11; Linux x86_64;
 rv:24.0) Gecko/20100101 Thunderbird/24.4.0
MIME-Version: 1.0
To: pgsql-performance@postgresql.org
Subject: Re: Checkpoints and slow queries
References: 
 <CALqA5kjzDQ2X1Am5zNjg3R5vCmZD-D0_6nrO1ccVD8vOmej22g@mail.gmail.com>
 <535D88B6.5090002@fuzzy.cz>
 <CALqA5kgoni52uqsSDeZStXZReXdpzGtSQ8iTtHbmx5oKOb1G6Q@mail.gmail.com>
 <10039.1398694057@sss.pgh.pa.us>
In-Reply-To: <10039.1398694057@sss.pgh.pa.us>
Content-Type: text/plain; charset=ISO-8859-2
Content-Transfer-Encoding: 7bit
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

On 28.4.2014 16:07, Tom Lane wrote:
> Elanchezhiyan Elango <elanelango@gmail.com> writes:
>>> The problem is that while this makes the checkpoints less
>>> frequent, it accumulates more changes that need to be written to
>>> disk during the checkpoint. Which means the impact more severe.
> 
>> True. But the checkpoints finish in approximately 5-10 minutes
>> every time (even with checkpoint_completion_target of 0.9).
> 
> There's something wrong with that.  I wonder whether you need to
> kick checkpoint_segments up some more to keep the checkpoint from
> being run too fast.
> 
> Even so, though, a checkpoint spread over 5-10 minutes ought to
> provide the kernel with enough breathing room to flush things.  It
> sounds like the kernel is just sitting on the dirty buffers until it
> gets hit with fsyncs, and then it's dumping them as fast as it can.
> So you need some more work on tuning the kernel parameters.

There's certainly something fishy, because although this is the supposed
configuration:

checkpoint_segments = 250
checkpoint_timeout = 1h
checkpoint_completion_target = 0.9

the checkpoint logs typically finish in much shorter periods of time.
Like this, for example:


> 
> regards, tom lane
> 
> 


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance