Thread: Skip index cleanup if autovacuum did not do any work

Sorry, I didn't attach a good patch, this one should be better

On Tue, Nov 28, 2017 at 1:36 PM, Feike Steenbergen
<feikesteenbergen@gmail.com> wrote:
> On a server with a very frequent xid wraparound I can see that the
> anti-wraparound vacuum is finished very quickly with the heap, yet it still
> scans all the indexes, which causes it to still have to read a lot of data,
> which takes a considerable amount of time.
>
> I dove into the code a bit and as far as I can tell, all the time spent for
> doing this is is in lazy_cleanup_index.
>
> For the very specific use case of all-frozen, basically read-only tables,
> would it be ok to skip the lazy_cleanup_index call? As we are sure we did
> not touch the heap or the index, I'd say a cleanup may not be necessary.

There is a patch in the ongoing CF to do this:

https://commitfest.postgresql.org/15/952/

It's a lot harder to do this correctly than it first appears.

-- 
Peter Geoghegan

On 28 November 2017 at 22:48, Peter Geoghegan <pg@bowt.ie> wrote:

> There is a patch in the ongoing CF to do this:

Ah, thanks, I'll probably review that one then

> It's a lot harder to do this correctly than it first appears.

I already thought my naive approach would not suffice

Feike Steenbergen <feikesteenbergen@gmail.com> writes:
> On a server with a very frequent xid wraparound I can see that the
> anti-wraparound vacuum is finished very quickly with the heap, yet it still
> scans all the indexes, which causes it to still have to read a lot of data,
> which takes a considerable amount of time.

> I dove into the code a bit and as far as I can tell, all the time spent for
> doing this is is in lazy_cleanup_index.

> For the very specific use case of all-frozen, basically read-only tables,
> would it be ok to skip the lazy_cleanup_index call? As we are sure we did
> not touch the heap or the index, I'd say a cleanup may not be necessary.

It's intentional that we call the index AM in any case.  Your patch
entirely forecloses the possibility that the AM might have work it
wants to do at VACUUM time independently of whether there were any
dead heap tuples.  (A handy example is GIN pushing stuff from its
fast-insert queue to the main index structure.)

It might be okay to put such a short-circuit in at a lower level,
eg within the btree AM.  I don't remember at the moment whether
a btree vacuum scan accomplishes anything much if there are no dead
tuples.

One thing that I think it does do is update the index's pg_class stats
(relpages/reltuples).  Maybe it's okay to skip that in this particular
scenario, trusting that auto-analyze would fix it, but that seems
worthy of debate.
        regards, tom lane

On Tue, Nov 28, 2017 at 1:55 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> It might be okay to put such a short-circuit in at a lower level,
> eg within the btree AM.  I don't remember at the moment whether
> a btree vacuum scan accomplishes anything much if there are no dead
> tuples.
>
> One thing that I think it does do is update the index's pg_class stats
> (relpages/reltuples).  Maybe it's okay to skip that in this particular
> scenario, trusting that auto-analyze would fix it, but that seems
> worthy of debate.

One of the sticking points with Sawada-san's patch, that I tried to
work through with him, is that we store XIDs in deleted B-Tree pages,
for the RecentGlobalXmin recycling interlock thing. The assumption is
that anti-wraparound VACUUM doesn't have to worry about those Xids as
a special case, because VACUUM will get around to actually reclaiming
them for the FSM the next time around (maybe that's the VACUUM that
enables advancing the epoch).

We cannot break that assumption, and no easy fixes suggest themselves.

-- 
Peter Geoghegan

On Tue, Nov 28, 2017 at 1:36 PM, Feike Steenbergen
<feikesteenbergen@gmail.com> wrote:
> On a server with a very frequent xid wraparound I can see that the
> anti-wraparound vacuum is finished very quickly with the heap, yet it still
> scans all the indexes, which causes it to still have to read a lot of data,
> which takes a considerable amount of time.

BTW, a good short term solution for you might be to change the vacuum
cost delay settings. They're pretty conservative by default.

There is a good chance that your indexes are mostly in memory even on
large tables, and B-Tree indexes are read sequentially during VACUUM.
Often, autovacuum runs at a much slower rate than is actually
possible, which isn't necessarily the right trade-off.

-- 
Peter Geoghegan

On 28 November 2017 at 23:17, Peter Geoghegan <pg@bowt.ie> wrote:
> BTW, a good short term solution for you might be to change the vacuum
> cost delay settings. They're pretty conservative by default.
>
> There is a good chance that your indexes are mostly in memory even on
> large tables, and B-Tree indexes are read sequentially during VACUUM.

Unfortunately we have already tuned autovacuum to be more aggressive.
Our indexes are large (more than 1TB of indexes for a given table) and
the very frequent wraparound requires us to be doing > 25MB/s just to
do a lot of index vacuuming, which in turn is a significant chunk of the I/O
of the whole system, and is filling memory with unused pages.

I was hoping to find a silver bullet, but it seems it's a bit more complex.

Perhaps there is a way to skip any index maintenance if we can determine
that the heap is all-frozen, all-visible, so for stale partitions, even if only
limited to btree indexes. I'll see if I can review the patch in progress.

Thanks for the advice,

regards,

Feike