Re: Performance Improvement by reducing WAL for Update Operation - Mailing list pgsql-hackers

On Thu, Jan 30, 2014 at 12:23 PM, Amit Kapila <amit.kapila16@gmail.com> wrote:
> On Wed, Jan 29, 2014 at 8:13 PM, Heikki Linnakangas
> <hlinnakangas@vmware.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