Re: Performance Improvement by reducing WAL for Update Operation

On Wed, Feb 5, 2014 at 5:29 PM, Heikki Linnakangas
<hlinnakangas(at)vmware(dot)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