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

On Thursday, September 06, 2012 2:38 PM Amit kapila wrote:
On Tuesday, September 04, 2012 6:55 PM Amit kapila wrote:
On Tuesday, September 04, 2012 12:42 AM Jeff Janes wrote:
On Mon, Sep 3, 2012 at 7:15 AM, Amit kapila <amit(dot)kapila(at)huawei(dot)com> wrote:
>>> This patch is based on below Todo Item:
>
>>> Consider adding buffers the background writer finds reusable to the free
>>> list

> The results for the updated code is attached with this mail.
> The scenario is same as in original mail.
> 1. Load all the files in to OS buffers (using pg_prewarm with 'read' operation) of all tables and indexes.
> 2. Try to load all buffers with "pgbench_accounts" table and "pgbench_accounts_pkey" pages (using pg_prewarm with 'buffers' operation).
> 3. Run the pgbench with select only for 20 minutes.

> Platform details:
> Operating System: Suse-Linux 10.2 x86_64
> Hardware : 4 core (Intel(R) Xeon(R) CPU L5408 @ 2.13GHz)
> RAM : 24GB

> Server Configuration:
> shared_buffers = 5GB (1/4 th of RAM size)
> Total data size = 16GB
> Pgbench configuration:
> transaction type: SELECT only
> scaling factor: 1200
> query mode: simple
> number of clients: <varying from 8 to 64 >
> number of threads: <varying from 8 to 64 >
> duration: 1200 s

> I shall take further readings for following configurations and post the same:
> 1. The intention for taking with below configuration is that, with the defined testcase, there will be some cases where I/O can happen. So I wanted to check the
> impact of it.

> Shared_buffers - 7 GB
> number of clients: <varying from 8 to 64 >
> number of threads: <varying from 8 to 64 >
> transaction type: SELECT only

The data for shared_buffers = 7GB is attached with this mail. I have also attached scripts used to take this data.

Note - I am using a utility pg_prewarm to warm up buffers which was developed by Robert Haas in last commit fest but it was not Committed. I think some other utility can also be used to warm up the buffers if required.

> One problem I could see with proposed change is that in some cases the usage count will get decrement for > a buffer allocated
> from free list immediately as it can be nextvictimbuffer.
> However there can be solution to this problem.

With Regards,
Amit Kapila.