Re: Patch: Write Amplification Reduction Method (WARM)

From: Pavan Deolasee <pavan(dot)deolasee(at)gmail(dot)com>
To: Bruce Momjian <bruce(at)momjian(dot)us>
Cc: Robert Haas <robertmhaas(at)gmail(dot)com>, Amit Kapila <amit(dot)kapila16(at)gmail(dot)com>, Alvaro Herrera <alvherre(at)2ndquadrant(dot)com>, Jaime Casanova <jaime(dot)casanova(at)2ndquadrant(dot)com>, Haribabu Kommi <kommi(dot)haribabu(at)gmail(dot)com>, Tomas Vondra <tomas(dot)vondra(at)2ndquadrant(dot)com>, pgsql-hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: Patch: Write Amplification Reduction Method (WARM)
Date: 2017-03-23 18:47:08
Message-ID: CABOikdPpm2AeUsS9=BEs=cUoRV7LS6cMQ+WHBjUzfvO+pctBtw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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

Attachment Content-Type Size
Moderate_AV_4Indexes_100FF_SF800_Duration28800s.pdf application/pdf 248.7 KB
Moderate_AV_4Indexes_100FF_SF1200_Duration28800s_Run2.pdf application/pdf 225.5 KB

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Andres Freund 2017-03-23 18:53:44 Re: Hash support for grouping sets
Previous Message Mark Dilger 2017-03-23 18:46:39 Re: Hash support for grouping sets