Re: PG12 autovac issues

On Wed, Mar 25, 2020 at 8:43 PM Michael Paquier <michael@paquier.xyz> wrote:
>
> On Wed, Mar 25, 2020 at 10:39:17AM -0500, Justin King wrote:
> > This started happening again.  DEBUG1 is enabled:
>
> Thanks for enabling DEBUG1 logs while this happened.
>
> > Mar 25 14:48:26 cowtn postgres[39875]: [35298-1] 2020-03-25
> > 14:48:26.329 GMT [39875] DEBUG:  skipping redundant vacuum to prevent
> > wraparound of table "postgres.pg_catalog.pg_tablespace"
> > Mar 25 14:48:26 cowtn postgres[39875]: [35299-1] 2020-03-25
> > 14:48:26.339 GMT [39875] DEBUG:  skipping redundant vacuum to prevent
> > wraparound of table "postgres.pg_catalog.pg_auth_members"
> > Mar 25 14:48:26 cowtn postgres[39875]: [35300-1] 2020-03-25
> > 14:48:26.350 GMT [39875] DEBUG:  skipping redundant vacuum to prevent
> > wraparound of table "postgres.pg_catalog.pg_replication_origin"
>
> Are you seeing such log entries happening multiple times for the same
> relations, meaning that autovacuum workers are just looping on the
> same relations all over again?  This part of the logs point to catalog
> tables, but are there other tables within your system facing the same
> logs, particularly the database "feedi" with some of your own tables?

Nope, it was just these tables that were looping over and over while
nothing else was getting autovac'd.  I'm happy to share the full log
if you'd like.

>
> >  postgres=# SELECT oid::regclass, age(relfrozenxid), relfrozenxid FROM
> > pg_class WHERE relfrozenxid <> 0 ORDER BY age(relfrozenxid) DESC LIMIT
> > 1;
> >     oid    |    age    | relfrozenxid
> > -----------+-----------+--------------
> >  pg_authid | 202793549 |   4284570172
>
> Ugh.  I think that this is exactly the thing I was suspecting
> previously:
> - The database stats look sane.
> - The relation stats don't look good and visibly are put in such a
> state that only one type of jobs gets triggered (non-aggressive but
> anti-wraparound), which just keep being skipped and the relation stats
> don't get refreshed.  (Such autovacuum jobs should never happen and we
> have some underlying issues that will need separate care).
>
> If you still have the cluster in this current state (perhaps you are
> not able to keep it longer), could you provide more data about
> pg_class.relfrozenxid for the tables which are mentioned in the logs
> of the type "skipping redundant vacuum to prevent of table"?

I did have to remove it from this state, but I can undo my workaround
and, undoubtedly, it'll end up back there.  Let me know if there's
something specific you'd like me to provide when it happens!

>
> > Let me know if there's anything else useful I can provide.
>
> Thanks!
> --
> Michael