Re: Compression of full-page-writes

On Tue, Dec 9, 2014 at 10:45 AM, Amit Kapila <amit(dot)kapila16(at)gmail(dot)com>
wrote:
> On Mon, Dec 8, 2014 at 3:17 PM, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
> >
> > On 8 December 2014 at 11:46, Michael Paquier <michael(dot)paquier(at)gmail(dot)com>
wrote:
> > > I don't really like those new names, but I'd prefer
> > > wal_compression_level if we go down that road with 'none' as default
> > > value. We may still decide in the future to support compression at the
> > > record level instead of context level, particularly if we have an API
> > > able to do palloc_return_null_at_oom, so the idea of WAL compression
> > > is not related only to FPIs IMHO.
> >
> > We may yet decide, but the pglz implementation is not effective on
> > smaller record lengths. Nor has any testing been done to show that is
> > even desirable.
> >
>
> It's even much worse for non-compressible (or less-compressible)
> WAL data.

To check the actual effect, I have ran few tests with the patch
(0001-Move-pg_lzcompress.c-to-src-common,
0002-Support-compression-for-full-page-writes-in-WAL) and the
data shows that for worst case (9 short and 1 long, short changed)
there is dip of ~56% in runtime where the compression is less (~20%)
and a ~35% of dip in runtime for the small record size
(two short fields, no change) where compression is ~28%. For best
case (one short and one long field, no change), the compression is
more than 2 times and there is an improvement in runtime of ~4%.
Note that in worst case, I am using random string due to which the
compression is less and it seems to me that worst is not by far the
worst because we see some compression in that case as well. I
think this might not be the best test to measure the effect of this
patch, but still it has data for various compression ratio's which
could indicate the value of this patch. Test case used to take
below data is attached with this mail.

Seeing this data, one way to mitigate the cases where it can cause
performance impact is to have a table level compression flag which
we have discussed last year during development of WAL compression
for Update operation as well.

Performance Data
-----------------------------
m/c configuration -
IBM POWER-8 24 cores, 192 hardware threads
RAM = 492GB
Non-default parameters -
checkpoint_segments - 256
checkpoint_timeout - 15 min

wal_compression=off

testname | wal_generated | duration
-----------------------------------------+---------------+------------------
two short fields, no change | 540055720 | 12.1288201808929
two short fields, no change | 542911816 | 11.8804960250854
two short fields, no change | 540063400 | 11.7856659889221
two short fields, one changed | 540055792 | 11.9835240840912
two short fields, one changed | 540056624 | 11.9008920192719
two short fields, one changed | 540059560 | 12.064150094986
two short fields, both changed | 581813832 | 10.2909409999847
two short fields, both changed | 579823384 | 12.4431331157684
two short fields, both changed | 579896448 | 12.5214929580688
one short and one long field, no change | 320058048 | 5.04950094223022
one short and one long field, no change | 321150040 | 5.24907302856445
one short and one long field, no change | 320055072 | 5.07368278503418
ten tiny fields, all changed | 620765680 | 14.2868521213531
ten tiny fields, all changed | 620681176 | 14.2786719799042
ten tiny fields, all changed | 620684600 | 14.216343164444
hundred tiny fields, all changed | 306317512 | 6.98173499107361
hundred tiny fields, all changed | 308039000 | 7.03955984115601
hundred tiny fields, all changed | 307117016 | 7.11708188056946
hundred tiny fields, half changed | 306483392 | 7.06978106498718
hundred tiny fields, half changed | 309336056 | 7.07678198814392
hundred tiny fields, half changed | 306317432 | 7.02817606925964
hundred tiny fields, half nulled | 219931376 | 6.29952597618103
hundred tiny fields, half nulled | 221001240 | 6.34559392929077
hundred tiny fields, half nulled | 219933072 | 6.36759996414185
9 short and 1 long, short changed | 253761248 | 4.37235498428345
9 short and 1 long, short changed | 253763040 | 4.34973502159119
9 short and 1 long, short changed | 253760280 | 4.34902000427246
(27 rows)

wal_compression = on

testname | wal_generated | duration
-----------------------------------------+---------------+------------------
two short fields, no change | 420569264 | 18.1419389247894
two short fields, no change | 423401960 | 16.0569458007812
two short fields, no change | 420568240 | 15.9060699939728
two short fields, one changed | 420769880 | 15.4179458618164
two short fields, one changed | 420769768 | 15.8254570960999
two short fields, one changed | 420771760 | 15.7606999874115
two short fields, both changed | 464684816 | 15.6395478248596
two short fields, both changed | 460885392 | 16.4674611091614
two short fields, both changed | 460908256 | 16.5107719898224
one short and one long field, no change | 86536912 | 4.87007188796997
one short and one long field, no change | 85008896 | 4.87805414199829
one short and one long field, no change | 85016024 | 4.91748881340027
ten tiny fields, all changed | 461562256 | 16.7471029758453
ten tiny fields, all changed | 461924064 | 19.1157128810883
ten tiny fields, all changed | 461526872 | 18.746591091156
hundred tiny fields, all changed | 188909640 | 8.3099319934845
hundred tiny fields, all changed | 191173832 | 8.34689402580261
hundred tiny fields, all changed | 190272920 | 8.3138701915741
hundred tiny fields, half changed | 189411656 | 8.24592804908752
hundred tiny fields, half changed | 188907888 | 8.23570513725281
hundred tiny fields, half changed | 191874520 | 8.23411083221436
hundred tiny fields, half nulled | 106529504 | 7.44415497779846
hundred tiny fields, half nulled | 103855064 | 7.48734498023987
hundred tiny fields, half nulled | 103858984 | 7.45094799995422
9 short and 1 long, short changed | 210281512 | 6.79501819610596
9 short and 1 long, short changed | 210285808 | 6.79907608032227
9 short and 1 long, short changed | 211485728 | 6.79275107383728
(27 rows)

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