From: Andres Freund <andres@2ndquadrant.com>
Organization: 2ndquadrant.com
To: pgsql-performance@postgresql.org
Subject: Re: Massive I/O spikes during checkpoint
Date: Tue, 10 Jul 2012 14:44:14 +0200
User-Agent: KMail/1.13.7 (Linux/3.4.0-andres; KDE/4.8.4; x86_64; ; )
Cc: Maxim Boguk <maxim.boguk@gmail.com>,
 David Kerr <dmk@mr-paradox.net>
References: <27C32FD4-0142-44FE-8488-9F366DC75966@mr-paradox.net>
	<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>
MIME-Version: 1.0
Content-Type: Text/Plain;
  charset="iso-8859-15"
Content-Transfer-Encoding: 7bit
Message-Id: <201207101444.14887.andres@2ndquadrant.com>

On Tuesday, July 10, 2012 08:14:00 AM Maxim Boguk wrote:
> On Tue, Jul 10, 2012 at 4:03 PM, David Kerr <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.
> 
> 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.
Thast not entirely true. The kernel will also writeout pages which haven't 
been written to for dirty_expire_centisecs.

But yes, adjusting dirty_* is definitely a good idea.

Andres
-- 
 Andres Freund	                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services