Re: [WIP] Performance Improvement by reducing WAL for Update Operation

From: pgsql-hackers-owner(at)postgresql(dot)org [pgsql-hackers-owner(at)postgresql(dot)org] on behalf of Amit Kapila [amit(dot)kapila(at)huawei(dot)com]
Sent: Friday, August 24, 2012 9:16 AM
From: Bruce Momjian [mailto:bruce(at)momjian(dot)us]
Sent: Friday, August 24, 2012 2:12 AM
On Wed, Aug 22, 2012 at 07:38:33PM +0530, Amit Kapila wrote:
>> I had made sure no full_page_write happens by making checkpoint interval
and
>> checkpoints segments longer.
>>
>
>
>> Original code - 1.8G Modified code - 1.1G Diff - 63% reduction,
incase of
>> fill factor 100.
>> Original code - 1.6G Modified code - 1.1G Diff - 45% reduction,
incase of
>> fill factor 80.
>
>
>
>> I am still in process of collecting synchronous commit mode on data.

> Wow, that sounds promising.
Thanks you.

> Right now I am collecting the data for Synchronous_commit =on mode; My
> initial observation is that
> incase fsync is off, the results are good(around 50% perf improvement).
> However if fsync is on, the performance results fall down to 3~5%. I am not
> sure even if the data for I/O is reduced,
> Still why there is no big performance gain as in case of Synchronous_commit
> = off or when fsync is off.

The modified pgbench test and testdata for synchronous commit mode is attached with this mail.
The test has shown upto 13% performance improvement in one of the cases.

I am still working on to collect some more performance data for wal_sync_method - OPEN_SYNC and by varying XLOG buffer size.

With Regards,
Amit Kapila.