Received: from malur.postgresql.org ([217.196.149.56])
	by arkaria.postgresql.org with esmtps
 (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256)
	(Exim 4.92)
	(envelope-from <pgsql-docs-owner+archive@lists.postgresql.org>)
	id 1lwVQP-00066O-5a
	for pgsql-docs@arkaria.postgresql.org; Thu, 24 Jun 2021 19:54:09 +0000
Received: from localhost ([127.0.0.1] helo=malur.postgresql.org)
	by malur.postgresql.org with esmtp (Exim 4.92)
	(envelope-from <pgsql-docs-owner+archive@lists.postgresql.org>)
	id 1lwVPr-0001GM-Oq
	for pgsql-docs@arkaria.postgresql.org; Thu, 24 Jun 2021 19:53:35 +0000
Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29])
	by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256)
	(Exim 4.92)
	(envelope-from <bruce@momjian.us>)
	id 1lwVPr-0001GE-J2
	for pgsql-docs@lists.postgresql.org; Thu, 24 Jun 2021 19:53:35 +0000
Received: from momjian.us ([72.94.173.45])
	by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256)
	(Exim 4.92)
	(envelope-from <bruce@momjian.us>)
	id 1lwVPp-000113-7M
	for pgsql-docs@lists.postgresql.org; Thu, 24 Jun 2021 19:53:35 +0000
Received: from bruce by momjian.us with local (Exim 4.92)
	(envelope-from <bruce@momjian.us>)
	id 1lwVPl-0006i5-6j; Thu, 24 Jun 2021 15:53:29 -0400
Date: Thu, 24 Jun 2021 15:53:29 -0400
From: Bruce Momjian <bruce@momjian.us>
To: Laurenz Albe <laurenz.albe@cybertec.at>
Cc: eric.mutta@gmail.com, pgsql-docs@lists.postgresql.org
Subject: Re: 24.1.5.1. Multixacts And Wraparound
Message-ID: <20210624195329.GB16214@momjian.us>
References: <162395467510.686.11947486273299446208@wrigleys.postgresql.org>
 <8b8fed1d71a692d9cd72116e42ddde776a12be7e.camel@cybertec.at>
 <20210622025023.GH22121@momjian.us>
 <ad54b2b4c7c84b35cfa20066f4c2c2f163edca2f.camel@cybertec.at>
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="2oS5YaxWCcQjTEyO"
Content-Disposition: inline
In-Reply-To: <ad54b2b4c7c84b35cfa20066f4c2c2f163edca2f.camel@cybertec.at>
User-Agent: Mutt/1.10.1 (2018-07-13)
List-Id: <pgsql-docs.lists.postgresql.org>
List-Help: <https://lists.postgresql.org/manage/>
List-Subscribe: <https://lists.postgresql.org/manage/>
List-Post: <mailto:pgsql-docs@lists.postgresql.org>
List-Owner: <mailto:pgsql-docs-owner@lists.postgresql.org>
List-Archive: <https://www.postgresql.org/list/pgsql-docs>
Archived-At: 
 <https://www.postgresql.org/message-id/20210624195329.GB16214%40momjian.us>
Precedence: bulk


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

On Tue, Jun 22, 2021 at 08:32:18AM +0200, Laurenz Albe wrote:
> > What does "the amount of used member storage space exceeds 50% of the
> > addressable storage space" mean?
> 
> You are right.  See MultiXactMemberFreezeThreshold for the whole story.
> 
> What about:
> 
>   As a safety device, an aggressive vacuum scan will occur for any table
>   whose multixact-age (see <xref linkend="vacuum-for-multixact-wraparound"/>)
>   is greater than <xref linkend="guc-autovacuum-multixact-freeze-max-age"/>.
>   Also, if the storage occupied by multixacts exceeds 2GB, aggressive vacuum
>   scans will occur more often for all tables, starting with those that have
>   the oldest multixact-age.
>   Both of these kinds ...

Yes, very good.   There were three problems with the original paragraph:

*  Had duplicate words
*  Had awkward phrasing
*  Used undefined terms

Your version fixes all three of those.  Patch attached.

> I hope I read the source right concerning the 2GB.

I can confirm the 2GB.  do_vacuum() calls
MultiXactMemberFreezeThreshold() to set its
effective_multixact_freeze_max_age.  MultiXactMemberFreezeThreshold()
compares the number of members to 2^32 (0xFFFFFFFF) / 2 or 2GB:

	#define MaxMultiXactOffset  ((MultiXactOffset) 0xFFFFFFFF)
	#define MULTIXACT_MEMBER_SAFE_THRESHOLD     (MaxMultiXactOffset / 2)
	
	/* If member space utilization is low, no special action is required. */
	if (members <= MULTIXACT_MEMBER_SAFE_THRESHOLD)
	    return autovacuum_multixact_freeze_max_age;

If that test fails, autovacuum_multixact_freeze_max_age is set much more
aggressively.

-- 
  Bruce Momjian  <bruce@momjian.us>        https://momjian.us
  EDB                                      https://enterprisedb.com

  If only the physical world exists, free will is an illusion.


--2oS5YaxWCcQjTEyO
Content-Type: text/x-diff; charset=us-ascii
Content-Disposition: attachment; filename="mxact.diff"

diff --git a/doc/src/sgml/maintenance.sgml b/doc/src/sgml/maintenance.sgml
index 4b535809b6..dd0eed193e 100644
--- a/doc/src/sgml/maintenance.sgml
+++ b/doc/src/sgml/maintenance.sgml
@@ -697,14 +697,14 @@ HINT:  Stop the postmaster and vacuum that database in single-user mode.
     </para>
 
     <para>
-     As a safety device, an aggressive vacuum scan will occur for any table
-     whose multixact-age is greater than
-     <xref linkend="guc-autovacuum-multixact-freeze-max-age"/>.  Aggressive
-     vacuum scans will also occur progressively for all tables, starting with
-     those that have the oldest multixact-age, if the amount of used member
-     storage space exceeds the amount 50% of the addressable storage space.
-     Both of these kinds of aggressive scans will occur even if autovacuum is
-     nominally disabled.
+     As a safety device, an aggressive vacuum scan will
+     occur for any table whose multixact-age (see <xref
+     linkend="vacuum-for-multixact-wraparound"/>) is greater than <xref
+     linkend="guc-autovacuum-multixact-freeze-max-age"/>.  Also, if the
+     storage occupied by multixacts exceeds 2GB, aggressive vacuum scans
+     will occur more often for all tables, starting with those that have
+     the oldest multixact-age.  Both of these kinds of aggressive scans
+     will occur even if autovacuum is nominally disabled.
     </para>
    </sect3>
   </sect2>

--2oS5YaxWCcQjTEyO--