From: Tom Lane <tgl@sss.pgh.pa.us>
To: Elanchezhiyan Elango <elanelango@gmail.com>
cc: Tomas Vondra <tv@fuzzy.cz>, pgsql-performance@postgresql.org
Subject: Re: Checkpoints and slow queries
In-reply-to: 
 <CALqA5kgoni52uqsSDeZStXZReXdpzGtSQ8iTtHbmx5oKOb1G6Q@mail.gmail.com>
References: 
 <CALqA5kjzDQ2X1Am5zNjg3R5vCmZD-D0_6nrO1ccVD8vOmej22g@mail.gmail.com>
 <535D88B6.5090002@fuzzy.cz>
 <CALqA5kgoni52uqsSDeZStXZReXdpzGtSQ8iTtHbmx5oKOb1G6Q@mail.gmail.com>
Comments: In-reply-to Elanchezhiyan Elango <elanelango@gmail.com>
 message dated "Sun, 27 Apr 2014 22:50:08 -0700"
Date: Mon, 28 Apr 2014 10:07:37 -0400
Message-ID: <10039.1398694057@sss.pgh.pa.us>
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

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.

			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