Received: from magus.postgresql.org (magus.postgresql.org [87.238.57.229])
	by mail.postgresql.org (Postfix) with ESMTP id C8E05C83CAD
	for <pgsql-performance@postgresql.org>;
	Tue, 10 Jul 2012 03:18:06 -0300 (ADT)
Received: from vexbert.mr-paradox.net ([2001:470:b:28:f::1])
	by magus.postgresql.org with esmtp (Exim 4.72)
	(envelope-from <dmk@mr-paradox.net>) id 1SoTlr-00060B-CU
	for pgsql-performance@postgresql.org; Tue, 10 Jul 2012 06:18:05 +0000
Received: from [192.168.200.113] (c-50-137-42-222.hsd1.or.comcast.net
	[50.137.42.222]) (Authenticated sender: dmk)
	by vexbert.mr-paradox.net (Postfix) with ESMTP id 204A284637;
	Tue, 10 Jul 2012 02:17:50 -0400 (EDT)
X-Virus-Status: Clean
X-Virus-Scanned: clamav-milter 0.96 at vexbert.mr-paradox.net
Message-ID: <4FFBC90C.1060205@mr-paradox.net>
Date: Mon, 09 Jul 2012 23:17:48 -0700
From: David Kerr <dmk@mr-paradox.net>
User-Agent: Mozilla/5.0 (Windows NT 6.1; WOW64;
	rv:13.0) Gecko/20120614 Thunderbird/13.0.1
MIME-Version: 1.0
To: Maxim Boguk <maxim.boguk@gmail.com>
CC: pgsql-performance@postgresql.org
Subject: Re: Massive I/O spikes during checkpoint
References: <27C32FD4-0142-44FE-8488-9F366DC75966@mr-paradox.net>
	<CAK-MWwRw-Y7_VhtjKcsC38NQK0eL-J9crHvjT=0GbXZxXdd+JA@mail.gmail.com>
	<07928283-528B-4499-BEC2-7E9098D6D734@mr-paradox.net>
	<CAK-MWwTP8ML8AZKWXa2D6-ghjuBKeGw38w+Kp92oWF2QCd6PFQ@mail.gmail.com>
In-Reply-To: 
 <CAK-MWwTP8ML8AZKWXa2D6-ghjuBKeGw38w+Kp92oWF2QCd6PFQ@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
X-Pg-Spam-Score: -1.9 (-)
X-Archive-Number: 201207/112
X-Sequence-Number: 47293

On 7/9/2012 11:14 PM, Maxim Boguk wrote:
>
>
> On Tue, Jul 10, 2012 at 4:03 PM, David Kerr <dmk@mr-paradox.net
> <mailto:dmk@mr-paradox.net>> wrote:
>
>
>     On Jul 9, 2012, at 10:51 PM, Maxim Boguk wrote:
>
>>
>>
>>         But what appears to be happening is that all of the data is
>>         being written out at the end of the checkpoint.
>>
>>         This happens at every checkpoint while the system is under load.
>>
>>         I get the feeling that this isn't the correct behavior and
>>         i've done something wrong.
>>
>>
>>
>>     It's not an actual checkpoints.
>>     It's is a fsync after checkpoint which create write spikes hurting
>>     server.
>>     You should set sysctl vm.dirty_background_bytes and vm.dirty_bytes
>>     to reasonable low values
>
>     So use bla_bytes instead of bla_ratio?
>
>
> Yes because on 256GB server
> echo 10 > /proc/sys/vm/dirty_ratio
> is equivalent to 26Gb dirty_bytes
>
> and
> echo 5 >/proc/sys/vm/dirty_background_ratio
> is equivalent to 13Gb dirty_background_bytes
>
> It is really huge values.
<sigh> yeah, I never bothered to think that through.

> So kernel doesn't start write any pages out in background before it has
> at least 13Gb dirty pages in kernel memory.
> And at end of the checkpoint kernel trying flush all dirty pages to disk.
>
> Even echo 1 >/proc/sys/vm/dirty_background_ratio  is too high value for
> contemporary server.
> That is why  *_bytes controls added to kernel.

Awesome, Thanks.