Message-ID: <4B9FEF65.50005@2ndquadrant.com>
Date: Tue, 16 Mar 2010 16:51:49 -0400
From: Greg Smith <greg@2ndquadrant.com>
User-Agent: Thunderbird 2.0.0.23 (X11/20090817)
MIME-Version: 1.0
To: Pierre C <lists@peufeu.com>
CC: Nikolas Everett <nik9000@gmail.com>, Dave Crooke <dcrooke@gmail.com>,
	Paul McGarry <paul@paulmcgarry.com>, pgsql-performance@postgresql.org
Subject: Re: shared_buffers advice
References: <a056b1d41003101728h2bfe964cg43bf5ef1aabae14d@mail.gmail.com>
	<ca24673e1003152217q117b0fd3q7d2d75ff20185c8b@mail.gmail.com>
	<4B9F3302.4020900@2ndquadrant.com> <op.u9nrbeeteorkce@localhost>
	<d4e11e981003160626o6c76d902t16073cabb4dadabc@mail.gmail.com>
	<op.u9nxzhryeorkce@localhost>
In-Reply-To: <op.u9nxzhryeorkce@localhost>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit

Pierre C wrote:
> Actually, I meant that in the case of a seq scan, PG will try to use 
> just a few buffers (a ring) in shared_buffers instead of thrashing the 
> whole buffers. But if there was actually a lot of free space in 
> shared_buffers, do the pages stay, or do they not ?

Pages inserted into the ring buffer and later re-used for new data do 
not stay behind even if there is room for them.  There's a potential 
improvement possible in that code involving better management of the 
situation where the buffer cache hasn't actually reached full capacity 
yet, but as it's an unusual case it's hard to justify optimizing for.  
Besides, the hope is that in this case the OS cache will end up caching 
everything anyway until it has a reason to evict it.  So if you follow 
the rest of the data suggesting you should not give all the memory to 
PostgreSQL to manage, you end up with a reasonable solution to this 
problem anyway.  Those pages will just live in the OS cache instead of 
the database's, with only a few trickling in and staying behind each 
time you do a sequential scan.

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