Re: reloption to prevent VACUUM from truncating empty pages at the end of relation

On Tue, Apr 17, 2018 at 11:05 PM, Fujii Masao <masao(dot)fujii(at)gmail(dot)com> wrote:

> Hi,
>
> I'd like to propose to add $SUBJECT for performance improvement.
>
> When VACUUM tries to truncate the trailing empty pages, it scans
> shared_buffers
> to invalidate the pages-to-truncate during holding an AccessExclusive lock
> on
> the relation. So if shared_buffers is huge, other transactions need to
> wait for
> a very long time before accessing to the relation. Which would cause the
> response-time spikes, for example, I observed such spikes several times on
> the server with shared_buffers = 300GB while running the benchmark.
> Therefore, I'm thinking to propose $SUBJECT and enable it to avoid such
> spikes
> for that relation.
>

Alvaro reminded me that we already have a mechanism in place which forces
VACUUM to give up the exclusive lock if another backend is waiting on the
lock for more than certain pre-defined duration. AFAICS we give up the
lock, but again retry truncation from the previously left off position.
What if we make that lock-wait duration configurable on a per-table basis?
And may be a special value to never truncate (though it seems quite
excessive to me and a possible footgun)

I was actually thinking in the other direction. So between the time VACUUM
figures out it can possibly truncate last K pages, some backend may insert
a tuple in some page and make the truncation impossible. What if we
truncate the FSM before starting the backward scan so that new inserts go
into the pages prior to the truncation point, if possible. That will
increase the chances of VACUUM being able to truncate all the empty pages.
Though I think in some cases it might lead to unnecessary further extension
of the relation. May be we use some heuristic based on available free space
in the table prior to the truncation point?

Thanks,
Pavan

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