Re: Patch: Write Amplification Reduction Method (WARM)

On Wed, Mar 22, 2017 at 12:30 AM, Bruce Momjian <bruce(at)momjian(dot)us> wrote:

>
>
> Well, it is really a question of how often you want to do a second WARM
> update (not possible) vs. the frequency of lazy vacuum. I assumed that
> would be a 100X or 10kX difference, but I am not sure myself either. My
> initial guess was that only allowing a single WARM update between lazy
> vacuums would show no improvementin in real-world workloads, but maybe I
> am wrong.
>
>
It's quite hard to say that until we see many more benchmarks. As author of
the patch, I might have got repetitive with my benchmarks. But I've seen
over 50% improvement in TPS even without chain conversion (6 indexes on a
12 column table test).

With chain conversion, in my latest tests, I saw over 100% improvement. The
benchmark probably received between 6-8 autovac cycles in an 8hr test. This
was with a large table which doesn't fit in memory or barely fit in memory.
Graphs attached again just in case you missed (x-axis test duration in
seconds, y-axis moving average of TPS)

May be we should run another set with just 2 or 3 indexes on a 12 column
table and see how much that helps, if at all. Or may be do a mix of HOT and
WARM updates. Or even just do HOT updates on small and large tables and
look for any regression. Will try to schedule some of those tests.

Thanks,
Pavan

--
Pavan Deolasee http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services