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

On Friday, September 07, 2012 6:44 PM Amit kapila wrote:
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:

> 2.The intention for taking with below configuration is that, with the defined testcase, memory kept for shared
buffersis less then the recommended. So I wanted  
> to check the impact of it.
> Shared_buffers - 2 GB
> number of clients: <varying from 8 to 64 >
> number of threads: <varying from 8 to 64 >
> transaction type: SELECT only

The results for this test are attached in Results_v2_sharedbuffers_2G.html

> 3. The intention for taking with below configuration is that, with the defined testcase, it will test mix of dml
operationswhere there will be I/O due to dml  
> operations. So I wanted to check the impact of it.
> Shared_buffers - 5GB
> number of clients: <varying from 8 to 64 >
> number of threads: <varying from 8 to 64 >
> transaction type: tpc_b

The results for this test are attached in Results_v2_sharedbuffers_5G_tcp_b.html

Conclusion for data collected till now:
1. When the shared buffers configuration is as per recommendation 25% of RAM, there is good performance improvement for
SELECToperation. 
     It further improves if there is high contention.
2. When the shared buffers configuration is less than recommendation 25% of RAM, there is no performance improvement or
slightdip for SELECT operation. 
     It get stablizes when there are more number of threads.
3. When the shared buffers configuration is as per recommendation 25% of RAM, there is negligible dip for tcp_b
benchmark.

If there is no objection about this performance improvement related to BufferManagement, I shall upload it for coming
CommitFest.
I know that I might need to do much more data collection for validating this patch, however if I get some feedback it
willmake much more sense. 

Suggestions/Opinions?

With Regards,
Amit Kapila.