Re: VACUUM's ancillary tasks

On Thu, Oct 6, 2016 at 2:46 PM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:

>
> > Also, I think that doing more counts which get amalgamated into the same
> > threshold, rather than introducing another parameter, is a bad thing. I
> > have insert-mostly, most of the time, tables which are never going to
> > benefit from index-only-scans, and I don't want to pay the cost of them
> > getting vacuumed just because of some iOn Thu, Oct 6, 2016 at 3:56 PM,
> Jeff Janes <jeff(dot)janes(at)gmail(dot)com> wrote:
> >> Sure, I could handle each case separately, but the goal of this patch
>
> nserts, when I will never get a
> > benefit out of it. I could turn autovacuum off for those tables, but
> then I
> > would have to remember to manually intervene on the rare occasion they do
> > get updates or deletes. I want to be able to manually pick which tables
> I
> > sign up for this feature (and then forget it), not manually pick which
> ones
> > to exempt from it and have to constantly review that.
>
> Of course, if you do that, then what will happen is eventually it will
> be time to advance relfrozenxid for that relation, and you'll get a
> giant soul-crushing VACUUM of doom, rather than a bunch of small,
> hopefully-innocuous VACUUMs.

I think I will get the soul-crushing vacuum of doom anyway, if the database
lasts that long. For one reason, in my case while updates and deletes are
rare, they are common enough that the frozen vm bits from early vacuums
will be mostly cleared before the vacuum of doom comes around.

For a second reason, I don't think the frozen bit in the vm actually gets
set by much of anything other than a autovacuum-for-wraparound or a manual
VACUUM FREEZE.

In commit 37484ad2aacef5ec7, you changed the way that frozen tuples were
represented, so that we could make freezing more aggressive without losing
forensic evidence. But I don't think we ever did anything to actually make
the freezing more aggressive.

When I applied the up-thread patch so that pgbench_history gets autovac,
those autovacs don't actually cause any pages to get frozen until the wrap
around kicks in, even when all the tuples on the early pages should be well
beyond vacuum_freeze_min_age.

Cheers,

Jeff