Re: Performance Improvement by reducing WAL for Update Operation

On Friday, December 14, 2012 2:32 PM Kyotaro HORIGUCHI wrote:
> 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.

Thank you very much.

With Regards,
Amit Kapila.