Re: Performance Improvement by reducing WAL for Update Operation

On Wed, Feb 5, 2014 at 5:29 PM, Heikki Linnakangas
<hlinnakangas@vmware.com> wrote:
> On 01/30/2014 08:53 AM, Amit Kapila wrote:
>>
>> Is it possible to do for both prefix and suffix together, basically
>> the question I
>> have in mind is what will be deciding factor for switching from hash table
>> mechanism to string comparison mode for suffix. Do we switch when we find
>> long enough match?
>
>
> I think you got it backwards. You don't switch from hash table mechanism to
> string comparison. You do the prefix/suffix comparison *first*, and run the
> hash table algorithm only on the "middle" part, between the common prefix
> and suffix.
>
>
>> Can we do this optimization after the basic version is acceptable?
>
>
> I would actually suggest doing that first. Perhaps even ditch the whole
> history table approach and do *only* the scan for prefix and suffix. That's
> very cheap, and already covers a large fraction of UPDATEs that real
> applications do. In particular, it's optimal for the case that you update
> only a single column, something like "UPDATE foo SET bar = bar + 1".
>
> I'm pretty sure the overhead of that would be negligible, so we could always
> enable it. There are certainly a lot of scenarios where prefix/suffix
> detection alone wouldn't help, but so what.
>
> Attached is a quick patch for that, if you want to test it.

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. However for same
case there is both significant WAL reduction and CPU gain with
pgrb_delta_encoding_v8.patch

I have updated  "ten long fields, all changed" such that there is large
suffix match. Updated script is attached.

Unpatched
           testname           | wal_generated |     duration
------------------------------+---------------+------------------
 ten long fields, all changed |    1760986528 | 28.3700430393219
 ten long fields, all changed |    1760981320 |   28.53244805336
 ten long fields, all changed |    1764294992 | 28.6722140312195
(3 rows)


wal-update-prefix-suffix-encode-1.patch
           testname           | wal_generated |     duration
------------------------------+---------------+------------------
 ten long fields, all changed |    1760986016 | 29.4183659553528
 ten long fields, all changed |    1760981904 | 29.7636449337006
 ten long fields, all changed |    1762436104 |  29.508908033371
(3 rows)

pgrb_delta_encoding_v8.patch

           testname           | wal_generated |     duration
------------------------------+---------------+------------------
 ten long fields, all changed |     733969304 |  23.916286945343
 ten long fields, all changed |     733977040 | 23.6019561290741
 ten long fields, all changed |     737384632 | 24.2645490169525


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