Re: [HACKERS] [PATCH] Vacuum: Update FSM more frequently

From: Claudio Freire <klaussfreire(at)gmail(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Masahiko Sawada <sawada(dot)mshk(at)gmail(dot)com>, PostgreSQL-Dev <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: [HACKERS] [PATCH] Vacuum: Update FSM more frequently
Date: 2018-04-03 13:37:38
Message-ID: CAGTBQpaCx++yQC6hXjCqf=EUkfW_6g+UcVcwcJt+aR=pKxS70A@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox
Thread:
Lists: pgsql-hackers

On Tue, Apr 3, 2018 at 10:19 AM, Claudio Freire <klaussfreire(at)gmail(dot)com> wrote:
> On Thu, Mar 29, 2018 at 2:09 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> Claudio Freire <klaussfreire(at)gmail(dot)com> writes:
>>> On Wed, Mar 28, 2018 at 6:54 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>>>> After 0001,
>>>> there's no reason to assume that vacuum is particularly likely to get
>>>> cancelled between having made cleanups and having updated the upper FSM
>>>> levels. (Maybe the odds are a bit more for the no-indexes case, but
>>>> that doesn't seem like it's common enough to justify a special mechanism
>>>> either.)
>>
>>> Why not?
>>
>>> Any kind of DDL (even those that don't rewrite the heap) would cancel
>>> autovacuum.
>>
>>> You might think DDL isn't common enough to worry about, but I've seen
>>> cases where regular reindex were required to keep index bloat in check
>>> (and were cron'd), and those cancel autovacuum all the time.
>>
>> If you've got a situation where every vacuum gets canceled partway
>> through, you've got bloat problems regardless, because the heap tuples are
>> never getting removed in the first place; worrying about whether the FSM
>> is up to date is pretty pointless. The 0001 patch basically updates the
>> FSM as soon as it can after the tuples are actually deleted, so I think
>> we've made the window as small as practical, and I don't really see a need
>> to do extra work (and add substantial extra complexity) to deal with
>> missed cleanup a bit sooner.
>>
>> People who are dealing with this sort of scenario a lot might be well
>> advised to reduce autovacuum's maintenance_work_mem, so that the cleanup
>> cycles happen more often. That's kind of costly in terms of the number
>> of index scans, but it reduces the amount of cleanup work that can be
>> lost to a cancel.
>>
>> (I'd also argue that a setup such as you describe is very possibly making
>> things worse not better. Perhaps the 0001 patch will go some way towards
>> making it less necessary to do that.)
>
> Alright, so we just drop 2.

So, that's it then.

Thanks

In response to

Browse pgsql-hackers by date

  From Date Subject
Next Message Tom Lane 2018-04-03 13:56:24 Re: Changing WAL Header to reduce contention during ReserveXLogInsertLocation()
Previous Message Anthony Iliopoulos 2018-04-03 13:36:47 Re: PostgreSQL's handling of fsync() errors is unsafe and risks data loss at least on XFS