Re: Performance Improvement by reducing WAL for Update Operation

Hello, I took the perfomance figures for this patch.

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

A. Vanilla pgbench, postgres is HEAD
B. Vanilla pgbench, postgres is with this patch (wal_update_changes_lz_v5)
C. Modified pgbench(Long text), postgres is HEAD
D. Modified pgbench(Long text), postgres is with this patch

Running doing pgbench -s 10 -i, pgbench -c 20 -T 2400
  #trans/s  WAL MB  WAL kB/tran
1A      437                      1723     1.68
1B      435 (<1% slower than A)  1645     1.61   (96% of A)
1C      149                      5073    14.6
1D      174 (17% faster than C)  5232    12.8    (88% of C)

Restoring with the wal archives yielded during the first test.
   Recv sec  s/trans
2A      61     0.0581
2B      62     0.0594  (2% slower than A)
2C     287     0.805
2D     314     0.750   (7% faster than C)

For vanilla pgbench, WAL size shrinks slightly and performance
seems very slightly worse than unpatched postgres(1A vs. 1B). It
can be safely say that no harm on performance even outside of the
effective range of this patch. On the other hand, the performance
gain becomes 17% within the effective range (1C vs. 1D).

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

As a whole, this patch brings very large gain in its effective
range - e.g. updates of relatively small portions of tuples, but
negligible loss of performance is observed outside of its
effective range.

I'll mark this patch as 'Ready for Committer' as soon as I get
finished confirming the mod patch.



==========
> I think new naming I have done are more meaningful, do you think I should
> revert to previous patch one's.

New naming is more meaningful, and a bit long. I don't think it
should be reverted.

> > Looking into wal_update_changes_mod_lz_v6.patch, I understand
> > that this patch experimentally adds literal data segment which
> > have more than single byte in PG-LZ algorithm.  According to
> > pglz_find_match, memCMP is slower than 'while(*s && *s == *d)' if
> > len < 16 and I suppose it is probably true at least for 4 byte
> > length data. This is also applied on encoding side. If this mod
> > does no harm to performance, I want to see this applied also to
> > pglz_comress.
> 
> Where in pglz_comress(),  do you want to see similar usage?
> Or do you want to see such use in function
> heap_attr_get_length_and_check_equals(), where it compares 2 attributes.

My point was the format for literal segments. It seems to reduce
about an eighth of literal segments. But the effectiveness under
real environment does not promising.. Forget it. It's just a
fancy.

regards,


-- 
Kyotaro Horiguchi
NTT Open Source Software Center