Re: [WIP PATCH] for Performance Improvement in Buffer Management

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 any data
> 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(at)2ndQuadrant(dot)com Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.com