Feedback-ID: i76984098:Fastmail
Message-ID: <b612761b-4e27-4ef0-a2f6-e57c066ab5c2@aklaver.com>
Date: Fri, 9 Aug 2024 08:36:39 -0700
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: Trouble understanding how to avoid/manage ERROR: multixact
 "members" limit exceeded
To: Jim Vanns <jvanns@ilm.com>, pgsql-general@lists.postgresql.org
References: <CAH7vdhNKOxe80kxS2TUQsYDZnx1+=gqSbkiPzqEDKO08uk7mow@mail.gmail.com>
Content-Language: en-US
From: Adrian Klaver <adrian.klaver@aklaver.com>
In-Reply-To: <CAH7vdhNKOxe80kxS2TUQsYDZnx1+=gqSbkiPzqEDKO08uk7mow@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: <https://www.postgresql.org/message-id/b612761b-4e27-4ef0-a2f6-e57c066ab5c2%40aklaver.com>
Precedence: bulk

On 8/9/24 03:26, Jim Vanns wrote:
> Hi pggen community!
> 
> I am struggling with this error almost daily now and despite various 
> efforts, not succeeding in avoiding or dealing with it;

> My questions are;
> 
> 1) How should I be managing this? Although not new to PG, I am new to 
> this particular problem.
> 2) How can I confirm what processes/tables are contributing to this 
> multixact "members" limit?
> 3) What are the units for 
> vacuum_multixact_freeze_min_age/vacuum_multixact_freeze_table_age and 
> how should I be
>      setting them appropriately for my rates etc.? I can't really find 
> anything that explains this clearly.
> 4) How can I check that autovacuum workers are specifically able to 
> freeze multixact XIDs and thus avoid this?

pg_stat_activity

https://www.postgresql.org/docs/current/monitoring-stats.html#MONITORING-PG-STAT-ACTIVITY-VIEW

wait_event_type

wait_event

Table 28.12. Wait Events of Type LWLock

This might tell you if something is hanging up on a table or tables.

> 5) Can I check if autovacuum is actually succeeding in its work


pg_stat_all_tables

https://www.postgresql.org/docs/current/monitoring-stats.html#MONITORING-PG-STAT-ALL-TABLES-VIEW

Will show you the vacuum/analyze that has been done on a table.


> Jim Vanns
> Principal Production Engineer
> Industrial Light & Magic, London

-- 
Adrian Klaver
adrian.klaver@aklaver.com