Thread: Monitoring multixact members growth

On Fri, Aug 19, 2022 at 8:40 AM Vido Vlahinic
<Vido.Vlahinic@milestonegroup.com> wrote:
> However it is not quite clear to me how I can interpret results from above and e.g. conclude: my_table accumulates x
moremultixact members since I measured last.
 

You can't. And not just because nobody got around to implementing it
yet -- it's quite a fundamental restriction. VACUUM must always make
sure of that, and must always scan all unfrozen pages to safely
determine that much (it may or may not have to freeze *any* MultiXacts
as part of that process, but it must always be sure that no Multis <
its final relminmxid remain).

> My goal here is to predict where multixact members are growing the fastest so I can perform manual VACUUM FREEZE only
onthose tables
 

The problem with that strategy is that you still have to do
anti-wraparound autovacuums when the mxid_age(relminmxid) of a table
crosses the usual threshold, even when in reality there are *zero*
MultiXacts in the table (often the case with the largest tables).
That's just how it works, unfortunately.

There is one piece of good news, though: work in Postgres 15 taught
VACUUM to track the oldest extant XID and MXID in the table, and set
relfrozenxid and remind to those oldest values (rather than using the
cutoffs for freezing, which in general might be much older than the
oldest remaining unfrozen XID/MXID).

I expect that this will make it much less likely that anti-wraparound
autovacuums affecting many tables will all stampede, hurting
performance. This will be possible because VACUUM will now be able to
set relminmxid to a value that actually tells us something about
what's really going on in each table, MultiXact-wise (not just what
you set vacuum_multixact_freeze_min_age and
autovacuum_multixact_freeze_max_age to in postgresql.conf, which is
pretty far removed from what matters most of the time). Simply by
noticing that there are no remaining MultiXacts (and probably never
were any in the first place) with the larger tables.

The timeline for anti-wraparound autovacuums will tend to make a lot
more sense for *your* workload, where huge differences in the rate of
MultiXact consumption among tables is likely the norm. This still
isn't perfect (far from it), but it has the potential to make things
far better here.

-- 
Peter Geoghegan

@font-face {font-family:"Cambria Math"; panose-1:2 4 5 3 5 4 6 3 2 4;}@font-face {font-family:Calibri; panose-1:2 15 5 2 2 2 4 3 2 4;}p.MsoNormal, li.MsoNormal, div.MsoNormal {margin:0cm; font-size:11.0pt; font-family:"Calibri",sans-serif;}span.EmailStyle18 {mso-style-type:personal-reply; font-family:"Calibri",sans-serif; color:windowtext;}.MsoChpDefault {mso-style-type:export-only; font-size:10.0pt;}div.WordSection1 {page:WordSection1;}

My goal here is to predict where multixact members are growing the fastest so I can perform manual VACUUM FREEZE only on those tables

(typically with multi-billion row count) when system is relatively idle as opposed to just sit and wait for wraparound protection to take over

when autovacuum_multixact_freeze_max_age threshold is reached (slowing the whole system down). 

I think that you're probably approaching this wrong. Vacuum is something that you generally want to run more aggressively, not less. But to be fair, it's a very common misunderstanding that waiting to do vacuum processing until later can be a good idea... even though in fact it works in the opposite way - on systems with significant load (where it matters) - sometimes a long-running report or query that needs old row versions for its own processing might cause a lot of table and index bloat and negatively impact real-time transactional performance. (For really long-running stuff, it's sometimes better to use a snapshot of the DB or maybe a standby system that's disconnected from the primary for reporting and periodically replays logs to catch up. But obviously you start simple and don't add this complexity to the architecture until it's truly needed.)

Funny thing is that I've had to do exactly what you're asking about, as part of troubleshooting problems - but the goal wasn't to run vacuum later but to run a vacuum freeze IMMEDIATELY.  🙂  As one example, pile-ups on LWLock multixact_offset.

Here's one pageinspect query that did the trick for me. In the first line (WITH...) you change public.my_test to the table you want to inspect. This only looks at a single table and it was for troubleshooting the aforementioned wait event, so it's actually breaking down mxid's by SLRU page numbers. If you're seeing a large number of SLRU pages (lots of rows coming back) then that means you might want to proactively run a manual vacuum freeze. (And then see if you can update the app code to reduce mxid usage!)

I'm not answering your question, but thought it was a nice excuse to share a related query and pontificate a bit... hopefully useful to someone!

-Jeremy

=====

pg-14.4 rw root@db1=# create extension pageinspect;
CREATE EXTENSION
Time: 7.561 ms

pg-14.4 rw root@db1=# with tab_name as (select 'public.my_test' t)
select min(now()) current_timestamp, count(*) number_mxid_on_page, min(xmax) min_mxid, max(xmax) max_mxid,
  trunc((xmax)/(8192/4)) page_no
from (
  select ((attrs).t_infomask::bit(16) & x'1000'::bit(16))::int::boolean is_multixact,
     (attrs).t_infomask::bit(16) infomask,
     (attrs).t_xmax::text::integer xmax
   from (
     select page,heap_page_item_attrs(get_raw_page((select t from tab_name),page),
                                      (select t from tab_name)::regclass) attrs
     from generate_series(0,(select relpages from pg_class where oid=(select t from tab_name)::regclass)-1) page
   ) subq where (attrs).t_infomask is not null
 ) subq3 where is_multixact
group by trunc((xmax)/(8192/4));
 current_timestamp | number_mxid_on_page | min_mxid | max_mxid | page_no
-------------------+---------------------+----------+----------+---------
(0 rows)

Time: 2223.640 ms (00:02.224)

pg-14.4 rw root@db1=# begin;
BEGIN
Time: 0.466 ms

pg-14.4 rw root@db1=# select * from my_test where i<5 for update;
 i |         data
---+----------------------
 1 | XXXXXXXXXXXXXXXXXXXX
 2 | XXXXXXXXXXXXXXXXXXXX
 3 | XXXXXXXXXXXXXXXXXXXX
 4 | XXXXXXXXXXXXXXXXXXXX
(4 rows)

Time: 50.074 ms

pg-14.4 rw root@db1=# savepoint a;
SAVEPOINT
Time: 0.605 ms

pg-14.4 rw root@db1=# update my_test set i=i-10 where i<5;
UPDATE 4
Time: 49.481 ms

pg-14.4 rw root@db1=# with tab_name as (select 'public.my_test' t)
select min(now()) current_timestamp, count(*) number_mxid_on_page, min(xmax) min_mxid, max(xmax) max_mxid,
  trunc((xmax)/(8192/4)) page_no
from (
  select ((attrs).t_infomask::bit(16) & x'1000'::bit(16))::int::boolean is_multixact,
     (attrs).t_infomask::bit(16) infomask,
     (attrs).t_xmax::text::integer xmax
   from (
     select page,heap_page_item_attrs(get_raw_page((select t from tab_name),page),
                                      (select t from tab_name)::regclass) attrs
     from generate_series(0,(select relpages from pg_class where oid=(select t from tab_name)::regclass)-1) page
   ) subq where (attrs).t_infomask is not null
 ) subq3 where is_multixact
group by trunc((xmax)/(8192/4));
       current_timestamp       | number_mxid_on_page | min_mxid | max_mxid | page_no
-------------------------------+---------------------+----------+----------+---------
 2022-08-19 23:05:43.349723+00 |                   4 |        1 |        1 |       0
(1 row)

Time: 2117.555 ms (00:02.118)

pg-14.4 rw root@db1=#

-- 
http://about.me/jeremy_schneider