Re: Performance Improvement by reducing WAL for Update Operation

On Wed, Jan 22, 2014 at 12:41 AM, Robert Haas <robertmhaas@gmail.com> wrote:
> On Tue, Jan 21, 2014 at 2:00 AM, Amit Kapila <amit.kapila16@gmail.com> wrote:
>> On Mon, Jan 20, 2014 at 9:49 PM, Robert Haas <robertmhaas@gmail.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
>>
>> 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?

I have tried to improve algorithm in another way so that we can get
benefit of same chunks during find match (something similar to lz).
The main change is to consider chunks at fixed boundary (4 byte)
and after finding match, try to find if there is a longer match than
current chunk. While finding longer match, it still takes care that
next bigger match should be at chunk boundary. I am not
completely sure about the chunk boundary may be 8 or 16 can give
better results.

I think now we can once run with this patch on high end m/c.

Below is the data on my laptop.

Non-Default Settings
checkpoint_segments = 128
checkpoint_timeout = 15min
autovacuum = off

Before Patch

              testname                 | wal_generated |     duration
-----------------------------------------+---------------+------------------
 one short and one long field, no change |    1054922336 | 25.4784970283508
 one short and one long field, no change |    1054914728 | 45.9248871803284
 one short and one long field, no change |    1054911288 | 42.0877709388733
 hundred tiny fields, all changed        |     633946880 | 21.4810841083527
 hundred tiny fields, all changed        |     633943520 | 29.5192229747772
 hundred tiny fields, all changed        |     633943944 | 38.1980679035187
 hundred tiny fields, half changed       |     633946784 | 36.0654091835022
 hundred tiny fields, half changed       |     638136544 |  36.231675863266
 hundred tiny fields, half changed       |     633944072 | 30.7445759773254
 hundred tiny fields, half nulled        |     570130888 | 28.6964628696442
 hundred tiny fields, half nulled        |     569755584 | 32.7119750976562
 hundred tiny fields, half nulled        |     569760312 | 32.4714169502258
(12 rows)


After Patch

                testname                 | wal_generated |     duration
-----------------------------------------+---------------+------------------
 one short and one long field, no change |     662239704 | 22.8768830299377
 one short and one long field, no change |     662896760 |  22.466646194458
 one short and one long field, no change |     662878736 | 17.6034708023071
 hundred tiny fields, all changed        |     633946192 | 24.5791938304901
 hundred tiny fields, all changed        |     634161120 | 25.7798039913177
 hundred tiny fields, all changed        |     633946416 |  23.761885881424
 hundred tiny fields, half changed       |     633945512 | 24.7001428604126
 hundred tiny fields, half changed       |     633947944 | 25.2069280147552
 hundred tiny fields, half changed       |     633946480 | 26.6489980220795
 hundred tiny fields, half nulled        |     492199720 | 28.7052059173584
 hundred tiny fields, half nulled        |     492194576 | 26.6311559677124
 hundred tiny fields, half nulled        |     492449408 | 25.2788209915161
(12 rows)


With above modifications, I could see ~37% WAL reduction for best case
"one short and one long field, no change" and ~13% for
"hundred tiny fields, half nulled". The duration is quite fluctuating in most
runs, so may be running it on some better m/c can give us a clear picture.

Any suggestions?

With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com