Re: Performance Improvement by reducing WAL for Update Operation

On Tue, Jan 21, 2014 at 2:00 AM, Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> wrote:
> On Mon, Jan 20, 2014 at 9:49 PM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
>> I ran Heikki's test suit on latest master and latest master plus
>> pgrb_delta_encoding_v4.patch on a PPC64 machine, but the results
>> didn't look too good. The only tests where the WAL volume changed by
>> more than half a percent were the "one short and one long field, no
>> change" test, where it dropped by 17%, but at the expense of an
>> increase in duration of 38%; and the "hundred tiny fields, half
>> nulled" test, where it dropped by 2% without a change in runtime.
>
>> Unfortunately, some of the tests where WAL didn't change significantly
>> took a runtime hit - in particular, "hundred tiny fields, half
>> changed" slowed down by 10% and "hundred tiny fields, all changed" by
>> 8%.
>
> I think this part of result is positive, as with earlier approaches here the
> dip was > 20%. Refer the result posted at link:
> http://www.postgresql.org/message-id/51366323.8070606@vmware.com
>
>
>> I've attached the full results in OpenOffice format.
>
>> Profiling the "one short and one long field, no change" test turns up
>> the following:
>>
>> 51.38% postgres pgrb_delta_encode
>> 23.58% postgres XLogInsert
>> 2.54% postgres heap_update
>> 1.09% postgres LWLockRelease
>> 0.90% postgres LWLockAcquire
>> 0.89% postgres palloc0
>> 0.88% postgres log_heap_update
>> 0.84% postgres HeapTupleSatisfiesMVCC
>> 0.75% postgres ExecModifyTable
>> 0.73% postgres hash_search_with_hash_value
>>
>> Yipes. That's a lot more than I remember this costing before. And I
>> don't understand why I'm seeing such a large time hit on this test
>> where you actually saw a significant time *reduction*. One
>> possibility is that you may have been running with a default
>> checkpoint_segments value or one that's low enough to force
>> checkpointing activity during the test. I ran with
>> checkpoint_segments=300.
>
> I ran with checkpoint_segments = 128 and when I ran with v4, I also
> see similar WAL reduction as you are seeing, except that in my case
> runtime for both are almost similar (i think in your case disk writes are
> fast, so CPU overhead is more visible).
> I think the major difference in above test is due to below part of code:
>
> pgrb_find_match()
> {
> ..
> + /* if (match_chunk)
> + {
> + while (*ip == *hp)
> + {
> + matchlen++;
> + ip++;
> + hp++;
> + }
> + } */
> }
>
> Basically if we don't go for longer match, then for test where most data
> ("one short and one long field, no change") is similar, it has to do below
> extra steps with no advantage:
> a. copy extra tags
> b. calculation for rolling hash
> c. finding the match
> I think here major cost is due to 'a', but others might also not be free.
> To confirm the theory, if we run the test by just un-commenting above
> code, there can be significant change in both WAL reduction and
> runtime for this test.
>
> I have one idea to avoid the overhead of step a) which is to combine
> the tags, means don't write the tag until it founds any un-matching data.
> When any un-matched data is found, then combine all the previously
> matched data and write it as one tag.
> This should eliminate the overhead due to step a.

I think that's a good thing to try. Can you code it up?

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company