Обсуждение: Managing autovacuum freezing

On Thu, Feb 11, 2021 at 9:13 AM Don Seiler <don@seiler.us> wrote:
> My understanding is that these manual VACUUM ANALYZE jobs are not freezing rows that regular autovacuuming would
otherwisebe doing, which leads up to the big anti-wraparound job.
 

They will freeze rows, but not aggressively. The antiwraparound vacuum
might block on acquiring buffer pins, low level stuff like that.

Perhaps you should change the vacuum_index_cleanup reloption to 'off'
for the table, but make the scripted overnight vacuums directly
specify INDEX_CLEANUP=on. That way index cleanup would still be
performed for the vacuums that run overnight, though not for the
antiwraparound vacuums, where the overhead may be a real issue.

-- 
Peter Geoghegan

On Thu, Feb 11, 2021 at 11:06 AM Don Seiler <don@seiler.us> wrote:
> Thanks for the response, Peter. This table *does* have 14 indexes on it as well, including on GIN index (rest are
btree,some are partial indexes). I've had a separate task on the back burner to try to identify any redundant ones.
 
>
> In the scenario you describe, would we re-enable the routine autovacuuming? I'm assuming so but wanted to make it
clear.

I'm not sure that you should re-enable av, actually -- you should at
least be careful with combing it with vacuum_index_cleanup=off. The
problem with the vacuum_index_cleanup table storage param that
controls this behavior is that it will apply generally -- unless you
override it using the VACUUM option each time. I strongly doubt that
it could ever make sense to completely avoid index vacuuming forever
here, so you certainly don't want to let that happen. The
vacuum_index_cleanup table param makes that extreme approach a
possibility, at least on Postgres 12+, but it's probably only
something that makes sense with an append-only table.

It might well not have made sense to disable AV here (it's often not a
good idea I find), though running VACUUM at night time probably was a
good idea. But vacuum_index_cleanup doesn't have granular options
about when and how skipping indexes applies as a matter of policy,
which makes it a bit tricky.

--
Peter Geoghegan

On Thu, Feb 11, 2021 at 11:23 AM Don Seiler <don@seiler.us> wrote:
> Right. I was thinking we'd still do the nightly manual vacuum, adding in the INDEX_CLEANUP option after disabling the
attributeat the table level. But was wondering if we should still consider enabling autovacuum on that table to
hopefullylessen the work needed by the wraparound-prevention aggressive autovac. 

Regular autovacuum (any VACUUM) will freeze tuples, so that certainly
makes sense to me. Sometimes it can seem like it's skipping the work
done in an anti-wraparound vacuum entirely because only the latter
insists on doing cleanup of every heap page, if necessary by waiting
to get a buffer pin -- and that could be harder during a busy period.
And also because the visibility bits in the VM can allow regular
vacuum to skip a lot more work than anti-wraparound vacuum, which can
only skip pages with the freeze bit set.

An anti-wraparound vacuum is actually less different to a regular
autovacuum than most users intuit. Though I get it why it seems that
way -- the practical difference can be really noticeable in
production, even though the theoretical differences seem rather small
to me.

--
Peter Geoghegan