Re: Monitoring multixact members growth

[Jeremy Schneider]

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 | 
 2 | 
 3 | 
 4 | 
(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

Re: Monitoring multixact members growth

[Peter Geoghegan]

On Fri, Aug 19, 2022 at 8:40 AM Vido Vlahinic
 wrote:
> However it is not quite clear to me how I can interpret results from above 
> and e.g. conclude: my_table accumulates x more multixact 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 on those 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

Re: High Availability PostgresDB on K8s

[Jan Kohnert]

Hi,

Am Freitag, 19. August 2022, 17:09:35 CEST schrieb Marco Schmucki:
> I want to run an HA-Postgres-DB on a Kubernetes cluster for testing purposes
> (Proof of Concept).
> 
> Do you have some links or documentation?

We're usually using Bitnami's Helm charts [1], [2] to deploy PostgreSQL into 
our K8S clusters. They also have -ha variants that might fit your needs, 
though I haven't tested them myself.

Be aware, that you'll get a single-master-multiple-replica, so you still only 
have one Postgres-instance actually *writing* data. As long as most of the 
traffic is reading traffic, that might just work well; if you have a lot of 
writing traffic, you will be limited by the master pod, the node it is running 
on, and of course the underlying PVC.

If you need multi-master, you might have a look at CockroachDB [3] which 
mostly Postgres-compatible.

[1] https://bitnami.com/stack/postgresql/helm
[2] https://github.com/bitnami/charts/tree/master/bitnami/postgresql/
#installing-the-chart
[3] https://www.cockroachlabs.com/product/kubernetes/

-- 
MfG Jan

Re: recovery_command has precedence over phisical slots?

[Laurenz Albe]

On Fri, 2022-08-19 at 16:54 +0200, Giovanni Biscontini wrote:
> Hello everyone, 
> I'm experiencing a behaviour I don't really understand if is a 
> misconfiguration or a wanted behaviour:
> 1) I set up a primary server (a.k.a. db1) with and archive_command to a 
> storage
> 2) I set up a replica (a.k.a. db2) that created a slot named as slot_2 and 
> that has the recovery_command set to read archived wal on the storage.
> If I shutdown replica db2 during a pgbench I see the safe_wal_size queried 
> from pg_replication_slots on the primary decrease to a certain amount but 
> still in the max_slot_wal_kepp_size window: even
> if I restart the replica db2 before the slot_state changes to unreserved or 
> lost I see that the replica gets needed wals from the storage using 
> recovery_command but doesn't use slot on primary.
> Only if I comment the recovery command on the .conf of the replica then it 
> uses slot.
> If this is a wanted behaviour I can't understand the need of slots on primary.

This is normal behavior and is no problem.

After the standby has caught up using "restore_command", it will connection to
the primary as defined in "primary_conninfo" and stream WAL from there.

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com

Re: High Availability PostgresDB on K8s

[Sumit Sengupta]

This may help - https://phoenixnap.com/kb/postgresql-kubernetes


[https://phoenixnap.com/kb/wp-content/uploads/2021/08/how-to-deploy-postgresql-on-kubernetes.png]
How to Deploy PostgreSQL on Kubernetes {Helm Chart or Manual Config} - 
Knowledge Base by phoenixNAP
Introduction. PostgreSQL is a reliable and robust relational database system 
featuring ACID-compliant transactions.It is designed to handle workloads of all 
sizes, making it a good fit for personal use and large-scale deployments such 
as data warehouses, big data servers, or web services.. Deploying PostgreSQL on 
Kubernetes creates a scalable and portable PostgreSQL instance, leveraging the 
...
phoenixnap.com


From: Marco Schmucki 
Sent: Friday, August 19, 2022 11:09 AM
To: pgsql-general@lists.postgresql.org 
Subject: High Availability PostgresDB on K8s


Hi all



I want to run an HA-Postgres-DB on a Kubernetes cluster for testing purposes 
(Proof of Concept).



Do you have some links or documentation?



Many thanks for your feedback!



Best regards,

Marco

High Availability PostgresDB on K8s

[Marco Schmucki]

Hi all

I want to run an HA-Postgres-DB on a Kubernetes cluster for testing purposes 
(Proof of Concept).

Do you have some links or documentation?

Many thanks for your feedback!

Best regards,
Marco

RE: Monitoring multixact members growth

[Vido Vlahinic]

Hello everyone,

Is there a way to monitor multixact members growth per table ?

There is this query:

SELECT relname, relminmxid, mxid_age(relminmxid) AS age
FROM pg_class
WHERE relkind = 'r'
AND relnamespace::regnamespace::text = 'public'
ORDER BY 3 DESC

However it is not quite clear to me how I can interpret results from the above 
and e.g. conclude: my_table accumulates x more multixact members since I 
measured it last.

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).

Please advise on how to achieve this.

Best regards,
Vido

Monitoring multixact members growth

[Vido Vlahinic]

Hello everyone,


Is there a way to monitor multixact members growth per table ?

There is this query:

SELECT relname, relminmxid, mxid_age(relminmxid) AS age
FROM pg_class
WHERE relkind = 'r'
AND relnamespace::regnamespace::text = 'public'
ORDER BY 3 DESC

However it is not quite clear to me how I can interpret results from above and 
e.g. conclude: my_table accumulates x more multixact members since I measured 
last.

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).

Please advise on how to achieve this.

Best regards,
Vido

recovery_command has precedence over phisical slots?

[Giovanni Biscontini]

Hello everyone,
I'm experiencing a behaviour I don't really understand if is a
misconfiguration or a wanted behaviour:
1) I set up a primary server (a.k.a. db1) with and archive_command to a
storage
2) I set up a replica (a.k.a. db2) that created a slot named as slot_2 and
that has the recovery_command set to read archived wal on the storage.
If I shutdown replica db2 during a pgbench I see the safe_wal_size queried
from pg_replication_slots on the primary decrease to a certain amount but
still in the max_slot_wal_kepp_size window: even if I restart the replica
db2 before the slot_state changes to unreserved or lost I see that the
replica gets needed wals from the storage using recovery_command but
doesn't use slot on primary.
Only if I comment the recovery command on the .conf of the replica then it
uses slot.
If this is a wanted behaviour I can't understand the need of slots on
primary.
Hopin' could someone explain me, thanks in advance, Giovanni

Re:Re:Re: Different execution plan between PostgreSQL 8.2 and 12.5

[gzh]

Dear Adrian,
Sorry, there is an error in the email just replied, the version of PostgreSQL 
is wrong.
PostgreSQL 8.4 → PostgreSQL 8.2
















At 2022-08-19 12:42:54, "gzh"  wrote:

Dear Adrian,

I appreciate your reply. Your reply gave me a new idea, 

it should not be the problem that the lower() function causes the unique index 
to fail. 

I checked the postgresql.conf file and found that shared_buffers, work_mem and 
maintenance_work_mem are default value, 

but in the postgresql.conf file of PostgreSQL 8.4, the value of these 
parameters are very large. 

When I changed the value of these parameters to a larger value, the problem 
solved.