Re: Performance Improvement by reducing WAL for Update Operation

Hello, I saw this patch and confirmed that

 - Coding style looks good.
 - Appliable onto HEAD.
 - Some mis-codings are fixed.

And took the performance figures for 4 types of modification
versus 2 benchmarks.

I've see small performace gain (4-8% for execution, and 6-12% for
recovery) and 16% WAL shrink for modified pgbench enhances the
benefit of this patch.

On the other hand I've found no significant loss of performance
for execution and 4% reduction of WAL for original pgbench, but
there might be 4-8% performance loss for recovery.

Attached patches are listed below.

wal_update_changes_lz_v5.patch

  Rather straight implement of wal compression using existing
  pg_lz compress format.

wal_update_changes_mod_lz_v6_2.patch

  Modify pg_lz to have bulk literal segment format which is
  available only for WAL compression. Misplaced comment fixed.



The detail of performance follows.
=====
I've tested involving the mod patch and 'modified' mod
patch.

CentOS6.3/Core i7
wal_level = archive, checkpoint_segments = 30 / 5min

wal_update_changes_mod_lz_v6+ is the version in which memcpy for
segment shorter than 16 bytes to be copied by while(*s)
*d++=*s++.


      postgres                    pgbench
A.    HEAD                        Original
B. wal_update_changes_lz_v5       Original
C. wal_update_changes_mod_lz_v6   Original
D. wal_update_changes_mod_lz_v6+  Original
E.    HEAD                        attached with this patch
F. wal_update_changes_lz_v5       attached with this patch
G. wal_update_changes_mod_lz_v6   attached with this patch
H. wal_update_changes_mod_lz_v6+  attached with this patch

Running doing pgbench -s 10 -i, pgbench -c 10 -j 10 -T 1200

   #trans/s                    WAL MB  WAL kB/tran
1A      346                      760     1.87
1B      347                      730     1.80   (96% of A)
1C      346                      729     1.80   (96% of A)
1D      347                      730     1.80   (96% of A)

1E      192                     2790     6.20
1F      200 (4% faster than E)  2431     5.19   (84% of D)
1G      207 (8% faster than E)  2563     5.28   (85% of D)
1H      199 (4% faster than E)  2421     5.19   (84% of D)

Recovery time

    Recv sec   us/trans
2A      26       62.6
2B      27       64.8  (4% slower than A)
2C      28       67.4  (8% slower than A)
2D      26       62.4  (same as A)

2E     130      629
2F     149      579    ( 8% faster than E)
2G     128      592    ( 6% faster than E)
2H     130      553    (12% faster than E)

For vanilla pgbench, WAL size shrinks slightly and performance
seems same as unpatched postgres(1A vs. 1B, 1C, 1D). For modified
pgbench, WAL size shrinks by about 17% and performance seems to
have a gain by several percent.

Recovery performance looks to have the same tendency. It looks to
produce very small loss outside of the effective range (2A
vs. 2B, 2C) and significant gain within (2E vs. 2F, 2G, 2H).

As a whole, this patch brings very large gain in its effective
range - e.g. updates of relatively small portions in a tuple, but
negligible loss of performance is observed outside of its
effective range on the test machine. I suppose the losses will be
emphasized by the more higher performance of seq write of WAL
devices


regards,

-- 
Kyotaro Horiguchi
NTT Open Source Software Center