Message-ID: <4E93E746.10807@2ndQuadrant.com>
Date: Tue, 11 Oct 2011 02:50:46 -0400
From: Greg Smith <greg@2ndQuadrant.com>
User-Agent: Mozilla/5.0 (X11; U; Linux x86_64; en-US;
	rv:1.9.1.16) Gecko/20111004 Icedove/3.0.11
MIME-Version: 1.0
To: pgsql-performance@postgresql.org
Subject: Re: Adding more memory = hugh cpu load
References: <4E92F294.9010405@gmail.com> <4E92FB87.1030703@peak6.com>
	<1318259675.10344.YahooMailNeo@web29011.mail.ird.yahoo.com>
	<4E93119C.8090407@peak6.com>
	<1318263277.56967.YahooMailNeo@web29013.mail.ird.yahoo.com>
In-Reply-To: <1318263277.56967.YahooMailNeo@web29013.mail.ird.yahoo.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

On 10/10/2011 12:14 PM, Leonardo Francalanci wrote:
>
>> database makes the fsync call, and suddenly the OS wants to flush 2-6GB of data
>> straight to disk. Without that background trickle, you now have a flood that
>> only the highest-end disk controller or a backing-store full of SSDs or PCIe
>> NVRAM could ever hope to absorb.
>>      
>
> Isn't checkpoint_completion_target supposed to deal exactly with that problem?
>    

checkpoint_completion_targets spreads out the writes to disk.  
PostgreSQL doesn't make any attempt yet to spread out the sync calls.  
On a busy server, what can happen is that the whole OS write cache fills 
with dirty data--none of which is written out to disk because of the 
high kernel threshold--and then it all slams onto disk fast once the 
checkpoint starts executing sync calls.  Lowering the size of the Linux 
write cache helps with that a lot, but can't quite eliminate the problem.

> Plus: if 2-6GB is too much, why not decrease checkpoint_segments? Or
> checkpoint_timeout?
>    

Making checkpoints really frequent increases total disk I/O, both to the 
database and to the WAL, significantly.  You don't want to do that if 
there's another way to achieve the same goal without those costs, which 
is what some kernel tuning can do here.  Just need to be careful not to 
go too far; some write caching at the OS level helps a lot, too.


> I'm not saying that those kernel parameters are "useless"; I'm saying 
> they are used
> in  the same way as the checkpoint_segments, checkpoint_timeout and
> checkpoint_completion_target are used by postgresql; and on a postgresql-only system
> I would rather have postgresql look after the fsync calls, not the OS.
>    

Except that PostgreSQL doesn't look after the fsync calls yet.  I wrote 
a patch for 9.1 that spread out the sync calls, similarly to how the 
writes are spread out now.  I wasn't able to prove an improvement 
sufficient to commit the result.  In the Linux case, the OS has more 
information to work with about how to schedule I/O efficiently given how 
the hardware is acting, and it's not possible for PostgreSQL to know all 
that--not without duplicating a large portion of the kernel development 
work at least.  Right now, relying the kernel means that any 
improvements there magically apply to any PostgreSQL version.  So far 
the results there have been beating out improvements made to the 
database fast enough that it's hard to innovate in this area within 
Postgres.

-- 
Greg Smith   2ndQuadrant US    greg@2ndQuadrant.com   Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support  www.2ndQuadrant.us