Date: Sat, 28 Mar 2020 17:47:19 +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: <20200328084719.GA29155@paquier.xyz>
References: <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>
 <20200326014108.GA2199@paquier.xyz>
 <CAE39h20kVBn+wHGBapOyoDUSAq=6vTyO4KuoX4sHQtZfdpVi+A@mail.gmail.com>
 <20200327050622.GB2133@paquier.xyz>
 <CAE39h20JGOjuwnOzSPhK_0xdscc5awwtxxf_Qe7W-xMxeNuuTQ@mail.gmail.com>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha512;
	protocol="application/pgp-signature"; boundary="SUOF0GtieIMvvwua"
Content-Disposition: inline
In-Reply-To: 
 <CAE39h20JGOjuwnOzSPhK_0xdscc5awwtxxf_Qe7W-xMxeNuuTQ@mail.gmail.com>
Precedence: bulk


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

On Fri, Mar 27, 2020 at 05:10:03PM -0500, Justin King wrote:
> Sounds great.  I will email you directly with a link!

Thanks.  From the logs, the infinite loop on which autovacuum jobs are
stuck is clear.  We have a repetitive number of anti-wraparound and
non-aggressive jobs happening for 7 shared relations every 5 seconds
or so and autovacuum is not able to do something else:
pg_auth_members
pg_authid
pg_database
pg_replication_origin
pg_shseclabel
pg_subscription
pg_tablespace

However I have found a correlation between all the tests I have done
on my own laptop, all the tests Julien and I have done in our internal
company buildfarm, and your logs.  We have 10 shared catalogs in the
system and the problem does not happen with the three following ones,
unable to trigger an anti-wraparound non-aggressive job:
pg_db_role_setting
pg_shdepend
pg_shdescription

Not sure yet what that is, but it is hard to believe that it is just a
coincidence.

> This is encouraging. As I mentioned, we have a workaround in place for
> the moment, but don't hesitate if you need anything else from me.
> Thanks for jumping in on the thread, it was nice validation to know
> that I wasn't the only one seeing the issue!

Yes.  We have been able to confirm that 2aa6e33 is a direct cause of
your problem.
--
Michael

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

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

iQIzBAABCgAdFiEEG72nH6vTowiyblFKnvQgOdbyQH0FAl5/DxcACgkQnvQgOdby
QH1IVw//TFRZTVvjhEfTrvt99uHztb6v1ceOUI6ArUAlPEFtvnUKc4kXgkz6SXns
BEYT7crhO6kqVf6Ms3xTXQuZblS5aGjRjoRUo/3+llxUvl/fsjX3WXgpSRq/UEpw
RuUAsOGyWouLOWQMeIchRh9GsKS9SFpG3FExqf5NstEedociApDJvLD1SMSX7MZb
nh7QXUz9z75TRhCZtz/I3w6Sk6wWc2TWgRVgCeNNw2WgNqR6bcOxl9u8iYZENR6b
7shkRElAQ1J5X6MfBSYQpF3RVOzpvRCFFCTDMJWp0L+DNJUMkrqSfULzoN19h+Tr
BMcyZJulQho3rhg4AlnTXtOwUjZFWs7ZnOG4YxppFEs/Gx8E4sOAcV8KInmYzQOX
yurxWx6CEgmsb7dYPGl6WeCdlCN3i86ZnYZZMdgeOjIbR+cmoh2aP87O2P45ch4S
uRozIuZQMl76sDtK5jNpcn0vKGyn+6M+JNAoPo6ED3p1AJ4yfu4Z3fD9WcObS3pJ
yYYGGpTxYTQEz5uC3KQh6l4bzvVdByJXTAOO8QvJjC8newhXLh0DqGPp1UE/GArU
jRO2WFJu9S4EACUZT58mo80ju1XHzuDp4+3qvvoDlz1ttjzPZUGUMWo0buAnKlCB
tBQ/8MhUC+qcGhGI6weNsOXAtQDGSl6nb2a/c3qvrFlnXE24oYs=
=U4Tr
-----END PGP SIGNATURE-----

--SUOF0GtieIMvvwua--