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

On Thursday, August 30, 2012 11:23 PM Robert Haas
[mailto:robertmhaas(at)gmail(dot)com] wrote:
On Fri, Aug 10, 2012 at 1:25 AM, Amit Kapila <amit(dot)kapila(at)huawei(dot)com> wrote:
>>> I think the property that recovery only needs to worry about each
>>> block individually is one that we want to preserve. Supporting this
>>> optimizating only when full_page_writes=off seems ugly,
>
>> I think recovery needs to worry about multiple blocks as well in some
cases.
>> Please see below case and correct me if I am wrong.
>> I think currently also there can be problems in case of
full_page_writes=off
>> for crash recovery.
>> 1. Tuple A on page 1 is updated. The new version, tuple B, is placed on
>> page 2.
>> 2. Page 1 is Partially written to disk.
>> 3. During recovery, it can so appear that there is no need to update XMAX
>> and other related things in Old tuple
>> as LSN is greater than WAL lsn.
>> 4. Now also there can be other problems related to tuple visibility.

> Well, you're only supposed to turn full_page_writes=off if partial
> page writes are impossible on your system. If you turn off
> full_page_writes on a system where partial page writes are impossible,

I think you mean to say "full_page_writes on a system where partial page
writes are possible."
Because if partial page writes are impossible then user should keep
full_page_writes = OFF.

> then you've intentionally broken crash recovery, and you get to keep
> both pieces.

Robert, in broad I got your and Simon's idea that we should do
optimization of WAL (Reduce) in case update happens
on same page. I have implemented the final Patch which does WAL
optimization only in case when updated tuple is on same
page. Also we have observed that with fillfactor 80 the performance
improvement is good.

With Regards,
Amit Kapila.