Re: Monitoring multixact members growth

[Jeremy Schneider <schneider@xxxxxxxxxxxxxx>]

On 8/19/22 12:52 AM, Vido Vlahinic wrote:

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