[prev in list] [next in list] [prev in thread] [next in thread]
List: postgresql-general
Subject: Re: Monitoring multixact members growth
From: Jeremy Schneider <schneider () ardentperf ! com>
Date: 2022-08-19 23:31:54
Message-ID: 247e3ce4-ae81-d6ad-f54d-7d3e0409a950 () ardentperf ! com
[Download RAW message or body]
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
[Attachment #3 (text/html)]
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
</head>
<body>
<div class="moz-cite-prefix">On 8/19/22 12:52 AM, Vido Vlahinic
wrote:<br>
</div>
<blockquote type="cite"
cite="mid:SYYP282MB1294AC64BADA3A4A3B8636C7806C9@SYYP282MB1294.AUSP282.PROD.OUTLOOK.COM">
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<meta name="Generator" content="Microsoft Word 15 (filtered
medium)">
<style>@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;}</style><!--[if gte mso 9]><xml>
<o:shapedefaults v:ext="edit" spidmax="1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext="edit">
<o:idmap v:ext="edit" data="1" />
</o:shapelayout></xml><![endif]-->
<div class="WordSection1"><span style="font-size:12.0pt">My goal
here is to predict where multixact members are growing the
fastest so I can perform manual VACUUM FREEZE only on those
tables<o:p></o:p></span>
<p class="MsoNormal"><span style="font-size:12.0pt">(typically
with multi-billion row count) when system is relatively idle
as opposed to just sit and wait for wraparound protection to
take over<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:12.0pt">when
autovacuum_multixact_freeze_max_age threshold is reached
(slowing the whole system down).<o:p></o:p></span><span
style="font-size:12.0pt"><o:p> </o:p></span></p>
</div>
</blockquote>
<br>
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.)<br>
<br>
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.<br>
<br>
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!)<br>
<br>
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!<br>
<br>
-Jeremy<br>
<br>
=====<br>
<br>
<font face="monospace">pg-14.4 rw root@db1=# create extension
pageinspect;<br>
CREATE EXTENSION<br>
Time: 7.561 ms<br>
<br>
pg-14.4 rw root@db1=# with tab_name as (select 'public.my_test' t)<br>
select min(now()) current_timestamp, count(*) number_mxid_on_page,
min(xmax) min_mxid, max(xmax) max_mxid,<br>
trunc((xmax)/(8192/4)) page_no<br>
from (<br>
select ((attrs).t_infomask::bit(16) &
x'1000'::bit(16))::int::boolean is_multixact,<br>
(attrs).t_infomask::bit(16) infomask,<br>
(attrs).t_xmax::text::integer xmax<br>
from (<br>
select page,heap_page_item_attrs(get_raw_page((select t from
tab_name),page),<br>
(select t from
tab_name)::regclass) attrs<br>
from generate_series(0,(select relpages from pg_class where
oid=(select t from tab_name)::regclass)-1) page<br>
) subq where (attrs).t_infomask is not null<br>
) subq3 where is_multixact<br>
group by trunc((xmax)/(8192/4));<br>
current_timestamp | number_mxid_on_page | min_mxid | max_mxid |
page_no<br>
-------------------+---------------------+----------+----------+---------<br>
(0 rows)<br>
<br>
Time: 2223.640 ms (00:02.224)<br>
<br>
pg-14.4 rw root@db1=# begin;<br>
BEGIN<br>
Time: 0.466 ms<br>
<br>
pg-14.4 rw root@db1=# select * from my_test where i<5 for
update;<br>
i | data<br>
---+----------------------<br>
1 | XXXXXXXXXXXXXXXXXXXX<br>
2 | XXXXXXXXXXXXXXXXXXXX<br>
3 | XXXXXXXXXXXXXXXXXXXX<br>
4 | XXXXXXXXXXXXXXXXXXXX<br>
(4 rows)<br>
<br>
Time: 50.074 ms<br>
<br>
pg-14.4 rw root@db1=# savepoint a;<br>
SAVEPOINT<br>
Time: 0.605 ms<br>
<br>
pg-14.4 rw root@db1=# update my_test set i=i-10 where i<5;<br>
UPDATE 4<br>
Time: 49.481 ms<br>
<br>
pg-14.4 rw root@db1=# with tab_name as (select 'public.my_test' t)<br>
select min(now()) current_timestamp, count(*) number_mxid_on_page,
min(xmax) min_mxid, max(xmax) max_mxid,<br>
trunc((xmax)/(8192/4)) page_no<br>
from (<br>
select ((attrs).t_infomask::bit(16) &
x'1000'::bit(16))::int::boolean is_multixact,<br>
(attrs).t_infomask::bit(16) infomask,<br>
(attrs).t_xmax::text::integer xmax<br>
from (<br>
select page,heap_page_item_attrs(get_raw_page((select t from
tab_name),page),<br>
(select t from
tab_name)::regclass) attrs<br>
from generate_series(0,(select relpages from pg_class where
oid=(select t from tab_name)::regclass)-1) page<br>
) subq where (attrs).t_infomask is not null<br>
) subq3 where is_multixact<br>
group by trunc((xmax)/(8192/4));<br>
current_timestamp | number_mxid_on_page | min_mxid |
max_mxid | page_no<br>
-------------------------------+---------------------+----------+----------+---------<br>
2022-08-19 23:05:43.349723+00 | 4 | 1
| 1 | 0<br>
(1 row)<br>
<br>
Time: 2117.555 ms (00:02.118)<br>
<br>
pg-14.4 rw root@db1=#</font><br>
<br>
<br>
<pre class="moz-signature" cols="72">--
<a class="moz-txt-link-freetext" \
href="http://about.me/jeremy_schneider">http://about.me/jeremy_schneider</a></pre> \
</body> </html>
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic