Re: [HACKERS] Vacuum: allow usage of more than 1GB of work mem

From: Alvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)org>
To: Claudio Freire <klaussfreire(at)gmail(dot)com>
Cc: Kyotaro HORIGUCHI <horiguchi(dot)kyotaro(at)lab(dot)ntt(dot)co(dot)jp>, Thomas Munro <thomas(dot)munro(at)enterprisedb(dot)com>, Stephen Frost <sfrost(at)snowman(dot)net>, Michael Paquier <michael(dot)paquier(at)gmail(dot)com>, Daniel Gustafsson <daniel(at)yesql(dot)se>, Andres Freund <andres(at)anarazel(dot)de>, Masahiko Sawada <sawada(dot)mshk(at)gmail(dot)com>, Anastasia Lubennikova <a(dot)lubennikova(at)postgrespro(dot)ru>, Anastasia Lubennikova <lubennikovaav(at)gmail(dot)com>, PostgreSQL-Dev <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: [HACKERS] Vacuum: allow usage of more than 1GB of work mem
Date: 2018-02-07 23:52:26
Message-ID: 20180207235226.zygu4r3yv3yfcnmc@alvherre.pgsql
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Claudio Freire wrote:
> On Wed, Feb 7, 2018 at 7:57 PM, Alvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)org> wrote:
> > Claudio Freire wrote:

> > Hmm, this solution is not very friendly to the goal of reducing test
> > runtime, particularly since the new test creates a nontrivial-sized
> > table. Maybe we can find a better alternative. Can we use some wait
> > logic instead? Maybe something like grab a snapshot of running VXIDs
> > and loop waiting until they're all gone before doing the vacuum?
>
> I'm not sure there's any alternative. I did some tests and any active
> snapshot on any other table, not necessarily on the one being
> vacuumed, distorted the test. And it makes sense, since that snapshot
> makes those deleted tuples unvacuumable.

Sure.

> Waiting as you say would be akin to what the patch does by putting
> vacuum on its own parallel group.

I don't think it's the same. We don't need to wait until all the
concurrent tests are done -- we only need to wait until the transactions
that were current when the delete finished are done, which is very
different since each test runs tons of small transactions rather than
one single big transaction.

> > Also, I don't understand why pg_relation_size() is not a better solution
> > to determining the table size compared to explain.
>
> I was told pg_relation_size can return stale information. I didn't
> check, I took that at face value.

Hmm, it uses stat() on the table files. I think those files would be
truncated at the time the transaction commits, so they shouldn't be
stale. (I don't think the system waits for a checkpoint to flush a
truncation.) Maybe relying on that is not reliable or future-proof
enough. Anyway this is a minor point -- the one above worries me most.

--
Álvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Thomas Munro 2018-02-08 00:04:13 Re: [COMMITTERS] pgsql: Rearm statement_timeout after each executed query.
Previous Message Claudio Freire 2018-02-07 23:45:50 Re: [HACKERS] Vacuum: allow usage of more than 1GB of work mem