Re: Performance Improvement by reducing WAL for Update Operation

On Thu, Jan 30, 2014 at 12:23 PM, Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> wrote:
> On Wed, Jan 29, 2014 at 8:13 PM, Heikki Linnakangas
> <hlinnakangas(at)vmware(dot)com> wrote:
>
> After basic verification of back-to-pglz-like-delta-encoding-1, I will
> take the data with both the patches and report the same.

I have corrected the problems reported in back-to-pglz-like-delta-encoding-1
and removed hindex from pgrb_delta_encoding_v6 and attached are
new versions of both patches.

I/O Reduction Data
-----------------------------
Non-Default settings
autovacuum = off
checkpoitnt_segments = 256
checkpoint_timeout =15min

Unpatched
------------------
testname | wal_generated | duration
-----------------------------------------+---------------+------------------
one short and one long field, no change | 1054917904 | 14.6407959461212
one short and one long field, no change | 1054917840 | 14.2938411235809
one short and one long field, no change | 1054916032 | 14.6062371730804
hundred tiny fields, all changed | 633950304 | 15.6165988445282
hundred tiny fields, all changed | 633943184 | 15.7330548763275
hundred tiny fields, all changed | 633943536 | 16.2008850574493
hundred tiny fields, half changed | 633946056 | 15.9042718410492
hundred tiny fields, half changed | 633949992 | 15.9494590759277
hundred tiny fields, half changed | 633948448 | 17.1421928405762
hundred tiny fields, half nulled | 569757992 | 16.0392069816589
hundred tiny fields, half nulled | 569758848 | 15.7891688346863
hundred tiny fields, half nulled | 569755144 | 16.2466349601746

Patch pgrb_delta_encoding_v7
------------------------------------------------
testname | wal_generated | duration
-----------------------------------------+---------------+------------------
one short and one long field, no change | 662240016 | 12.0052649974823
one short and one long field, no change | 662570640 | 11.5202040672302
one short and one long field, no change | 662231656 | 12.2640421390533
hundred tiny fields, all changed | 633947296 | 17.0527350902557
hundred tiny fields, all changed | 633945824 | 17.1216440200806
hundred tiny fields, all changed | 633948904 | 16.8881120681763
hundred tiny fields, half changed | 633944656 | 18.0734100341797
hundred tiny fields, half changed | 633944472 | 17.0183899402618
hundred tiny fields, half changed | 633945112 | 16.6483509540558
hundred tiny fields, half nulled | 499946000 | 18.9340658187866
hundred tiny fields, half nulled | 499952408 | 18.7714779376984
hundred tiny fields, half nulled | 499953432 | 18.690948009491
(12 rows)

Patch back-to-pglz-like-delta-encoding-2
----------------------------------------------------------
testname | wal_generated | duration
-----------------------------------------+---------------+------------------
one short and one long field, no change | 662242872 | 12.7399699687958
one short and one long field, no change | 662233440 | 12.7010321617126
one short and one long field, no change | 663938992 | 13.1172158718109
hundred tiny fields, all changed | 635451832 | 17.918673992157
hundred tiny fields, all changed | 633946736 | 17.1329951286316
hundred tiny fields, all changed | 633943480 | 17.0818238258362
hundred tiny fields, half changed | 634762208 | 17.0016329288483
hundred tiny fields, half changed | 633946560 | 17.3154718875885
hundred tiny fields, half changed | 633943240 | 17.1657249927521
hundred tiny fields, half nulled | 492017488 | 27.3930599689484
hundred tiny fields, half nulled | 492016776 | 26.7517058849335
hundred tiny fields, half nulled | 493848424 | 26.6423358917236
(12 rows)

Observations
--------------------
1. With both the patches WAL reduction is similar i.e ~37% for
"one short and one long field, no change" and 12% for
"hundred tiny fields, half nulled"
2. With pgrb_delta_encoding_v7, there is ~19% CPU reduction for best
case "one short and one long field, no change".
3. With pgrb_delta_encoding_v7, there is approximately 8~9% overhead
for cases where there is no match
4. With pgrb_delta_encoding_v7, there is approximately 15~18% overhead
for "hundred tiny fields, half nulled" case
5. With back-to-pglz-like-delta-encoding-2, the data is mostly similar except
for "hundred tiny fields, half nulled" where CPU overhead is much more.

The case ("hundred tiny fields, half nulled") where CPU overhead is visible
is due to repetitive data and if take some random or different data, it will not
be there. I think the main reason for overhead is that we store last offset
of matching data in history at front, so during match, it has to traverse back
many times to find longest possible match and in real world it won't be the
case that most of history entries contain same hash index, so it should not
effect.
Finally if any user is concerned much about CPU overhead due to it, there
is a table level knob which he can use to avoid it.

Please let me know your suggestions.

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