Skip site navigation (1) Skip section navigation (2)

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 (view raw, whole thread or download thread mbox)
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: Moderate_AV_4Indexes_100FF_SF800_Duration28800s.pdf
Description: application/pdf (248.7 KB)
Attachment: Moderate_AV_4Indexes_100FF_SF1200_Duration28800s_Run2.pdf
Description: application/pdf (225.5 KB)

In response to

Responses

pgsql-hackers by date

Next:From: Andres FreundDate: 2017-03-23 18:53:44
Subject: Re: Hash support for grouping sets
Previous:From: Mark DilgerDate: 2017-03-23 18:46:39
Subject: Re: Hash support for grouping sets

Privacy Policy | About PostgreSQL
Copyright © 1996-2018 The PostgreSQL Global Development Group