Re: Move unused buffers to freelist

On Tuesday, June 25, 2013 10:25 AM Amit Kapila wrote:
> 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.

Configuration Details
O/S - Suse-11
RAM - 128GB
Number of Cores - 16
Server Conf - checkpoint_segments = 300; checkpoint_timeout = 15 min,
synchronous_commit = 0FF, shared_buffers = 14GB, AutoVacuum=off Pgbench -
Select-only Scalefactor - 1200 Time - 30 mins

8C-8T 16C-16T 32C-32T 64C-64T
Head 62403 101810 99516 94707
Patch 62827 101404 99109 94744

On 128GB RAM, if use scalefactor=1200 (database=approx 17GB) and 14GB shared
buffers, this is no major difference.
One of the reasons could be that there is no much swapping in shared buffers
as most data already fits in shared buffers.

I think more readings are need for combinations related to below settings:
scale factor such that database size + shared_buffers = RAM (shared_buffers
= 1/4 of RAM).

I can try varying shared_buffer size.

Kindly let me know your suggestions?

With Regards,
Amit Kapila.