Re: Performance Improvement by reducing WAL for Update Operation

On Thu, Feb 6, 2014 at 9:13 AM, Amit Kapila <amit.kapila16@gmail.com> wrote:
> On Wed, Feb 5, 2014 at 8:50 PM, Heikki Linnakangas
> <hlinnakangas@vmware.com> wrote:
>> On 02/05/2014 04:48 PM, Amit Kapila wrote:
>>>
>>> I have done one test where there is a large suffix match, but
>>> not large enough that it can compress more than 75% of string,
>>> the CPU overhead with wal-update-prefix-suffix-encode-1.patch is
>>> not much, but there is no I/O reduction as well.
>>
>>
>> Hmm, it's supposed to compress if you save at least 25%, not 75%. Apparently
>> I got that backwards in the patch...
>
> So If I understand the code correctly, the new check should be
>
> if (prefixlen + suffixlen < (slen * need_rate) / 100)
>     return false;
>
> rather than
>
> if (slen - prefixlen - suffixlen > (slen * need_rate) / 100)
> return false;

Considering above change as correct, I have tried to see the worst
case overhead for this patch by having new tuple such that after
25% or so of suffix/prefix match, there is a small change in tuple
and kept rest of tuple same as old tuple and it shows overhead
for this patch as well.

Updated test script is attached.

Unpatched
             testname             | wal_generated |     duration
----------------------------------+---------------+------------------
 ten long fields, 8 bytes changed |     348843824 | 5.56866788864136
 ten long fields, 8 bytes changed |     348844800 | 5.84434294700623
 ten long fields, 8 bytes changed |     350500000 | 5.92329406738281
(3 rows)



wal-update-prefix-suffix-encode-1.patch

             testname             | wal_generated |     duration
----------------------------------+---------------+------------------
 ten long fields, 8 bytes changed |     348845624 | 6.92243480682373
 ten long fields, 8 bytes changed |     348847000 | 8.35828399658203
 ten long fields, 8 bytes changed |     350204752 | 7.61826491355896
(3 rows)

One minor point, can we avoid having prefix tag if prefixlen is 0.

+ /* output prefix as a tag */
+ pgrb_out_tag(ctrlp, ctrlb, ctrl, bp, prefixlen, hlen);



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