Date: Thu, 26 Mar 2020 10:43:36 +0900
From: Michael Paquier <michael@paquier.xyz>
To: Justin King <kingpin867@gmail.com>
Cc: Andres Freund <andres@anarazel.de>, Julien Rouhaud <rjuju123@gmail.com>,
	pgsql-general@lists.postgresql.org, kgrittn@gmail.com
Subject: Re: PG12 autovac issues
Message-ID: <20200326014108.GA2199@paquier.xyz>
References: <20200320190317.gdxwzexdfutymqs6@alap3.anarazel.de>
 <20200323152247.GB52612@nol>
 <20200323162303.s7ay5hjdvimtkz6u@alap3.anarazel.de>
 <20200323194725.GC52612@nol>
 <20200323200051.qzyqvnips7o3ai4g@alap3.anarazel.de>
 <20200324052606.GE1836@paquier.xyz>
 <20200324054039.ulrxbzo2ycy7pu5i@alap3.anarazel.de>
 <20200324061238.GG1836@paquier.xyz>
 <20200324194259.pidupqxtqnquk4wu@alap3.anarazel.de>
 <CAE39h23EoQpGeBN4XDg5MhZZFz_AMbnr7O+h-Q=LXvS1ZuoXyQ@mail.gmail.com>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha512;
	protocol="application/pgp-signature"; boundary="7AUc2qLy4jB3hD7Z"
Content-Disposition: inline
In-Reply-To: 
 <CAE39h23EoQpGeBN4XDg5MhZZFz_AMbnr7O+h-Q=LXvS1ZuoXyQ@mail.gmail.com>
Precedence: bulk


--7AUc2qLy4jB3hD7Z
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline

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?

>  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"?

> Let me know if there's anything else useful I can provide.

Thanks!
--
Michael

--7AUc2qLy4jB3hD7Z
Content-Type: application/pgp-signature; name="signature.asc"

-----BEGIN PGP SIGNATURE-----

iQIzBAABCgAdFiEEG72nH6vTowiyblFKnvQgOdbyQH0FAl58CMgACgkQnvQgOdby
QH3Wsg/9E8kiPl6uW3WnXvd3udj1n43rcRaERL6xJGvUl0WByFgH/mFU6gK0RW0+
wPovJ2nMOFZBfqYIKiphfdTUUc+H9yUVJHiEyBSesZFpO/RopmqvakVIxveSJBiG
5hQIh8g4mVtp809KmRhJFLEJ059GOSx0ywgS2XW2V+ejJ61z2IZbUuPigziRfbPm
LwWDcCR9zDGsAJv4GT/+X1oHqnn6LoZ1jBAfjGpXRas3g9hh/k3xvgiUJUZjMmDN
P6bAE/1L1Si6BxBq6Uq4Kk7t4LDW3IO4cSm5zs0fxdc8hR4Mg8rK6lEZ2U/EUjrt
cEDNVfw7TvcPYf6wbzWM04R8I0Og4r2IBL8576F5x4e1JvBV99TEJy1RfySDvAaa
MxyQzm+a5ed4cv6rQEOkvYwSlDdMhFkT1F0t2i8nxX5uDfLpUTpHj0lYrJH2euFK
L2FszPe/Uo4x2grykAwCrYB7a76zlqT+n/rHzo6ancJ/+Hko6L4qX6XybN+lvn+w
IIEJ8IW0O9sPmB5skVaDX+P2rgwHJYzBZYbEmOtdi+Gx/7FACPw3HThLTTvOGNsE
ZivIXRZHUMHw/lKGbN1MO4rWhWbwANY5U2DGd1ucIxZrKF9IuwYV5ef/N3HPfhsa
Ng3Ec9Obl6se+xXGlubt3NuNizEjaFUrOaPMpzWH6Ca7atSd8Rg=
=DvM9
-----END PGP SIGNATURE-----

--7AUc2qLy4jB3hD7Z--