Re: [WIP PATCH] for Performance Improvement in Buffer Management - Mailing list pgsql-hackers

From Greg Smith
Subject Re: [WIP PATCH] for Performance Improvement in Buffer Management
Date
Msg-id 50C7C75E.1010800@2ndQuadrant.com
Whole thread Raw
In response to Re: [WIP PATCH] for Performance Improvement in Buffer Management  (Amit kapila <amit.kapila@huawei.com>)
Responses Re: [WIP PATCH] for Performance Improvement in Buffer Management
List pgsql-hackers
On 11/23/12 5:57 AM, Amit kapila wrote:
> Let us try to see by example:
> Total RAM - 22G
> Database size - 16G
>...
> Case -2 (Shared Buffers - 10G)
> a. Load all the files in OS buffers. In best case OS buffers can contain10-12G data as OS has 12G of memory
available.
> b. Try to load all in Shared buffers. Last 10G will be there in shared buffers.
> c. Now as there is no direct correlation of data between Shared Buffers and OS buffers, so whenever PG has to access
anydata
 
>     which is not there in Shared Buffers, good chances are there that it can lead to IO.

I don't think either of these examples are very representative of 
real-world behavior.  The idea of "load all the files in OS buffers" 
assumes someone has used a utility like pg_prewarm or pgfincore.  It's 
not something that happens in normal use.  Being able to re-populate all 
of RAM using those utilities isn't realistic anyway.  Anyone who tries 
to load more than (memory - shared_buffers) that way is likely to be 
disappointed by the result.

Similarly, the problem you're describing here has been described as the 
"double buffering" one for a while now.  The old suggestion that 
shared_buffers not be set about 25% of RAM comes from this sort of 
concern.  If triggering a problem requires doing that, essentially 
misconfiguring the server, it's hard to get too excited about it.

Anyway, none of that impacts on me mixing testing for this into what I'm 
working on.  The way most pgbench tests happen, it's hard to *not* have 
the important data in cache.  Once you run the init step, you have to 
either reboot or drop the OS cache to get those pages out of RAM.  That 
means the sort of cached setup you're using pg_prewarm to 
simulate--things are in the OS cache, but not the PostgreSQL one--is one 
that anyone running an init/test pair will often create.  You don't need 
pg_prewarm to do it.  If you initialize the database, then restart the 
server to clear shared_buffers, the result will be similar to what 
you're doing.

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



pgsql-hackers by date:

Previous
From: Andres Freund
Date:
Subject: Logical decoding & exported base snapshot
Next
From: Andres Freund
Date:
Subject: Re: Re: [PATCH 02/14] Add support for a generic wal reading facility dubbed XLogReader