Date: Thu, 26 Mar 2020 12:52:10 +0900
From: Michael Paquier <michael@paquier.xyz>
To: Andres Freund <andres@anarazel.de>
Cc: Justin King <kingpin867@gmail.com>, Julien Rouhaud <rjuju123@gmail.com>,
	pgsql-general@lists.postgresql.org, kgrittn@gmail.com
Subject: Re: PG12 autovac issues
Message-ID: <20200326035210.GA1471@paquier.xyz>
References: <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>
 <20200326014108.GA2199@paquier.xyz>
 <20200326025956.zqypnlspv5k5pb42@alap3.anarazel.de>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha512;
	protocol="application/pgp-signature"; boundary="xgyAXRrhYN0wYx8y"
Content-Disposition: inline
In-Reply-To: <20200326025956.zqypnlspv5k5pb42@alap3.anarazel.de>
Precedence: bulk


--xgyAXRrhYN0wYx8y
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable

On Wed, Mar 25, 2020 at 07:59:56PM -0700, Andres Freund wrote:
> FWIW, this kind of thing is why I think the added skipping logic is a
> bad idea. Silently skipping things like this (same with the "bogus"
> logic in datfrozenxid computation) is dangerous. I think we should
> seriously consider backing this change out.

That's actually what I would like to do at this stage as a first
step.  It looks pretty clear that it does not help.

> And if not, then we should at least include enough detail in the message
> to be able to debug this.

Sure.  In any attempt I have done until now I was easily able to skip
some jobs, but it should get easier with a higher number of concurrent
workers and a higher number of relations heavily updated.  Thinking
about it, only catalog jobs were getting skipped in my own runs...

>> postgres=3D# SELECT datname, age(datfrozenxid), datfrozenxid FROM
>> pg_database ORDER BY age(datfrozenxid) DESC LIMIT 1;
>>  datname  |    age    | datfrozenxid
>> ----------+-----------+--------------
>>  postgres | 202773709 |   4284570172
>=20
> And why should this lead to anti-wraparound vacuums not happening? This
> is older than the the cutoff age?
>=20
> xid 4284570172 having the age of 202 million xids suggests that
> ReadNewTransactionId() is approx 192376585. Which comports with the log
> saying: oldest xmin: 189591147.

Oops, sorry.  My previous email was incorrect.  It looked strange to
not see datfrozenxid being refreshed.

> Or are you saying that you conclude that the relcache entry is somehow
> out of date? It sure is interesting that all of the tables that hit the
> "skipping redundant vacuum" condition are shared tables.

Yeah, that's actually what I was thinking yesterday.  In
heap_vacuum_rel(), xidFullScanLimit may be calculated right, but an
incorrect value of rd_rel->relminmxid or rd_rel->relfrozenxid could
lead to a job to become not aggressive.  It should be actually easy
enough to check that.
--
Michael

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

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

iQIzBAABCgAdFiEEG72nH6vTowiyblFKnvQgOdbyQH0FAl58JukACgkQnvQgOdby
QH2C5g/+P0u5PcUGXNA/lCQ9iGFKfnnKCP6cZ1faD5MLeDFCIy0Ny+SxeqcCYkjy
MhqOnU/RJbZM66IYiu7DWZZy5RiB97zga3ZjG+Bn58IxPInCbqi9hBeCtI/yqLns
kLQPySBAKXRAb4tMioiXN0/pen0CaghKqs/H536ChYDKyT4AQRRr3jfYMaRSv3sI
kWid1p/QBLUMSUFkCkYgYRNda3X7WcL0dq5JNNnn+Dcr3ACFLjRpPVau6cpZImNh
0UNDj2NYpv9ko+woIy6y1vTAoVgRXJNTMdLKxRRM8CrufsaOU+RwnJt4Kju+DyT+
HI4OLt4BLkhKGENeIEMuQ6t4VGD4xIbIcHX7mxEYknNKCS21MG5hMl50SjxbhIR7
ZapabCP0TfTHihWWUSmcH41z3QZ606rPYpZBphNa/gxkQABZVIKNeF2xySc3tbXo
dZTwQVXtspXOts79d6X1JN/4PyESsqTA9nQhOVAks6CgQK4xQly0bGMBRHnxWrUJ
tjlpCmcDh4vdvfVi6NeX+VuXY3MTwn9b9n+arWsG1WAt164NSLr/S+VarHXLQdLg
zVQ2PJJmG2jGOOeZHvkRrmtIrWwB299r7B1w/ZuT3fnKg1lmYPgrAAkSirrcgJ80
jrJuOBi13AH98aKW11blg5ynJsHmv+PgLYuoBUoxLPY7AASCnFg=
=fSTi
-----END PGP SIGNATURE-----

--xgyAXRrhYN0wYx8y--