Count mxids and number of mxid slru pages on a table

gistfile1.txt

#### tested on r6i.16xlarge with RDS Postgres

pgbench -i -s 10 --foreign-keys
pgbench -N -M prepared -T 360 -P 5 -c 512 -j 8

psql -c "create extension pageinspect"

psql <<EOF

  -- this query will return one row for each SLRU page. 
  -- the output tells you how many mxids are on that page, along with min/max xid value for the page and page_number
  -- on a healthy system you should see a cluster of page_numbers that are recent and close together
  -- if you see a few really old page_numbers hanging around, then try a vacuum freeze to see if it cleans it up
  -- old page_numbers hanging around can increase pressure on the mxid SLRU caches

  with params as (
    select oid, relnamespace::regnamespace::text||'.'||relname fullname, relpages 
    from pg_class 
    where oid=       'pgbench_branches' -- put your table name here!!
    ::regclass
  )
  select min(now()), count(*) count, min(xmax), max(xmax),
    trunc((xmax)/(8192/4)) page_number
  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 fullname from params),page),
                                        (select oid from params)) attrs
       from generate_series(00000,(select relpages from params)-1) page
     ) subq where (attrs).t_infomask is not null
   ) subq3 where is_multixact
  group by trunc((xmax)/(8192/4));

EOF

              min              | count |   min    |   max    | page_number
-------------------------------+-------+----------+----------+-------------
 2024-03-04 19:08:55.290096+00 |    10 | 14390544 | 14390568 |        7026


# the output above shows that all currently existing mxids fit on a single SLRU page
#
# now lets run pgbench again and then run the query again


pgbench -N -M prepared -T 30 -P 5 -c 512 -j 8

              min              | count |   min    |   max    | page_number
-------------------------------+-------+----------+----------+-------------
 2024-03-04 19:10:07.952587+00 |    10 | 15568321 | 15568380 |        7601


# notice that there are still 10 mxids and they all still fit on a single page
# but now the page_number has increased, as have the min/max xids. this
# means that autovacuum is successfully cleaning up mxid's as we go and there
# are not any old pages hanging around, and we don't have any pressure on the
# mxid SLRUs