Skip index cleanup if autovacuum did not do any work
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.
For this very specific usecase I would like to discuss whether or not this
is
a sane and/or good idea.
regards,
Feike
Attachments:
0001-skip_cleanup_for_stale_relation.patchapplication/octet-stream; name=0001-skip_cleanup_for_stale_relation.patchDownload
[1mdiff --git a/src/backend/commands/vacuumlazy.c b/src/backend/commands/vacuumlazy.c[m
[1mindex 6587db77ac..e6c30f435d 100644[m
[1m--- a/src/backend/commands/vacuumlazy.c[m
[1m+++ b/src/backend/commands/vacuumlazy.c[m
[36m@@ -1329,9 +1329,15 @@[m [mlazy_scan_heap(Relation onerel, int options, LVRelStats *vacrelstats,[m
pgstat_progress_update_param(PROGRESS_VACUUM_PHASE,[m
PROGRESS_VACUUM_PHASE_INDEX_CLEANUP);[m
[m
[31m- /* Do post-vacuum cleanup and statistics update for each index */[m
[31m- for (i = 0; i < nindexes; i++)[m
[31m- lazy_cleanup_index(Irel[i], indstats[i], vacrelstats);[m
[32m+[m [32m/*[m
[32m+[m [32m * Do post-vacuum cleanup and statistics update for each index.[m
[32m+[m [32m * We can skip cleaning up iff not a single tuple was vacuumed[m
[32m+[m [32m */[m
[32m+[m [32mif (tups_vacuumed > 0)[m
[32m+[m [32m{[m
[32m+[m [32mfor (i = 0; i < nindexes; i++)[m
[32m+[m [32mlazy_cleanup_index(Irel[i], indstats[i], vacrelstats);[m
[32m+[m [32m}[m
[m
/* If no indexes, make log report that lazy_vacuum_heap would've made */[m
if (vacuumed_pages)[m
Sorry, I didn't attach a good patch, this one should be better
Attachments:
0002-skip_cleanup_for_stale_relation.patchapplication/octet-stream; name=0002-skip_cleanup_for_stale_relation.patchDownload
diff --git a/src/backend/commands/vacuumlazy.c b/src/backend/commands/vacuumlazy.c
index 6587db77ac..e6c30f435d 100644
--- a/src/backend/commands/vacuumlazy.c
+++ b/src/backend/commands/vacuumlazy.c
@@ -1329,9 +1329,15 @@ lazy_scan_heap(Relation onerel, int options, LVRelStats *vacrelstats,
pgstat_progress_update_param(PROGRESS_VACUUM_PHASE,
PROGRESS_VACUUM_PHASE_INDEX_CLEANUP);
- /* Do post-vacuum cleanup and statistics update for each index */
- for (i = 0; i < nindexes; i++)
- lazy_cleanup_index(Irel[i], indstats[i], vacrelstats);
+ /*
+ * Do post-vacuum cleanup and statistics update for each index.
+ * We can skip cleaning up iff not a single tuple was vacuumed
+ */
+ if (tups_vacuumed > 0)
+ {
+ for (i = 0; i < nindexes; i++)
+ lazy_cleanup_index(Irel[i], indstats[i], vacrelstats);
+ }
/* If no indexes, make log report that lazy_vacuum_heap would've made */
if (vacuumed_pages)
--
2.13.6 (Apple Git-96)
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