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.kapila@huawei.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
whereI/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
wasnot 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.