Re: Speeding up an in-progress wraparound-preventing vacuum

On Mon, Dec 8, 2014 at 4:58 PM, Vincent de Phily <
vincent(dot)dephily(at)mobile-devices(dot)fr> wrote:

> On Monday 08 December 2014 10:17:37 Jeff Janes wrote:
> > On Mon, Dec 8, 2014 at 4:54 AM, Vincent de Phily <
>
> > I don't think that routine vacuums even attempts to update relfrozenxid,
> or
> > at least doesn't try very hard.
>
> AFAIU it does (the 6th paragraph in 23.1.4 seems to imply it), and
> vacuum_freeze_min_age controls when. Perhaps lowering that value would help
> prepare a lot of the anti-wraparound work.
>

There are two things that make autovac do an anti-wraparound vacuum. One
of them is if the table is already going to be vacuumed anyway
and vacuum_freeze_table_age is exceeded. In that case, the vacuum that was
going to happen anyway gets promoted to be an anti-wraparound vacuum. The
other case if when autovacuum_freeze_max_age is exceeded. Then it forces a
vacuum to happen even when there is no other reason for it to occur. I
think the language in the 6th paragraph is contrasting these two ways to
get an anti-wraparound vacuum, not contrasting ordinary vacuum with
anti-wraparound vacuum.

>
> Pity there's no "frozen pages map" (or is there ?) to keep track of pages
> will
> all-frozen tuples, it could speed up that anti-wraparound vacuum.
>

But not speed it up by much. As you discovered, most of the time is spent
dealing with the indexes.

>
> > Are you sure that routine vacuums have been running to completion on this
> > table, as opposed to getting interrupted by something before finishing
> each
> > time?
>
> I checked pg_stat_user_tables.last_{auto_,}vacuum for this table and they
> are
> both null. This is seriously worrying. I've seen autovacuum take a few
> days on
> this table but always assumed that it finished properly. And I'm pretty
> sure
> vacuuming does some work, otherwise my disk usage woul grow much faster. I
> have no idea what could cause vacuuming to systematically stop before the
> end.
> Maybe I'll find something in the logs.
>

If it gets interrupted by another process needing the lock, it will be in
the logs. But more recent versions than 9.1 will log more useful
information.

If it is interrupted by a database shutdown or restart, it only logs that
it was shutdown, it doesn't log what it was in the middle of doing at the
time. (Perhaps that should be changed?).

Cheers,

Jeff