Re: Move unused buffers to freelist

On Monday, June 24, 2013 11:00 PM Robert Haas wrote:
> On Thu, Jun 6, 2013 at 3:01 AM, Amit Kapila <amit(dot)kapila(at)huawei(dot)com>
> wrote:
> > To avoid above 3 factors in test readings, I used below steps:
> > 1. Initialize the database with scale factor such that database size
> +
> > shared_buffers = RAM (shared_buffers = 1/4 of RAM).
> > For example:
> > Example -1
> > if RAM = 128G, then initialize db with scale factor =
> 6700
> > and shared_buffers = 32GB.
> > Database size (98 GB) + shared_buffers (32GB) = 130
> (which
> > is approximately equal to total RAM)
> > Example -2 (this is based on your test m/c)
> > If RAM = 64GB, then initialize db with scale factor =
> 3400
> > and shared_buffers = 16GB.
> > 2. reboot m/c
> > 3. Load all buffers with data (tables/indexes of pgbench) using
> pg_prewarm.
> > I had loaded 3 times, so that usage count of buffers will be
> approximately
> > 3.
>
> Hmm. I don't think the usage count will actually end up being 3,
> though, because the amount of data you're loading is sized to 3/4 of
> RAM, and shared_buffers is just 1/4 of RAM, so I think that each run
> of pg_prewarm will end up turning over the entire cache and you'll
> never get any usage counts more than 1 this way. Am I confused?

The way I am pre-warming is that loading the data of relation (table/index)
continuously 3 times, so mostly the buffers will contain the data of
relations loaded in last
which are indexes and also they got accessed more during scans. So usage
count should be 3.
Can you please once see load_all_buffers.sql, may be my understanding has
some gap.

Now about the question why then load all the relations.
Apart from PostgreSQL shared buffers, loading data this way can also
make sure OS buffers will have the data with higher usage count which can
lead to better OS scheduling.

> I wonder if it would be beneficial to test the case where the database
> size is just a little more than shared_buffers. I think that would
> lead to a situation where the usage counts are high most of the time,
> which - now that you mention it - seems like the sweet spot for this
> patch.

I will check this case and take the readings for same. Thanks for your
suggestions.

With Regards,
Amit Kapila.