From: Wetmore, Matthew (CTR) <Matthew.Wetmore@mdlive.com>
To: Fabrice Chapuis <fabrice636861@gmail.com>
To: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
Subject: Re: wal buffer optimisation
Date: Fri, 31 Jan 2025 15:43:26 +0000
Message-ID: <E1962633-6872-423C-A339-9A70A6BFE3EB@glbcore.com> (raw)
In-Reply-To: <CAA5-nLANs+CT9820jUm+SU9xA6rZ+6T5EFrLvTtQDH_y0=FW_g@mail.gmail.com>
References: <CAA5-nLANs+CT9820jUm+SU9xA6rZ+6T5EFrLvTtQDH_y0=FW_g@mail.gmail.com>
One of the things I do…
SELECT * from pg_stat_bgwriter;
checkpoints_timed | xxxxx
checkpoints_req | xx
checkpoints_timed will be how many scheduled WAL checkpoints you have based on settings.
Checkpoints_req is how many extra checkpoints were needed due to over capacity/misconfigurtation
Divide req/time
You want this ratio/percentage to be less than ~5%., otherwise, you should be monitoring WAL activity and adjust the other settings to avoid checkpoints_req.
--
Matt Wetmore
Data Engineer
Braze Certified Architect
415.416.9738
[signature_4160879279]
From: Fabrice Chapuis <fabrice636861@gmail.com>
Date: Friday, January 31, 2025 at 6:46 AM
To: "pgsql-admin@lists.postgresql.org" <pgsql-admin@lists.postgresql.org>
Subject: wal buffer optimisation
Hi, It's hard for me to know if my Postgres 14 WALs configuration is optimal postgres [2854804]=# select wal_buffers_full from pg_stat_wal; +------------------+ | wal_buffers_full | +------------------+ | 910589 | +-----------------
Hi,
It's hard for me to know if my Postgres 14 WALs configuration is optimal
postgres [2854804]=# select wal_buffers_full from pg_stat_wal;
+------------------+
| wal_buffers_full |
+------------------+
| 910589 |
+-----------------
(1 row)
postgres [2854804]=# show wal_writer_delay ;
+------------------+
| wal_writer_delay |
+------------------+
| 200ms |
+------------------+
(1 row)
I got some metrics from pg_stats_wal view: 15,702 buffers full in 24 hours and 1,243,890 writes in 24 hours, are these values indicative of a performance problem?
Regards,
Fabrice
Attachments:
[image/png] image001.png (8.1K, ../E1962633-6872-423C-A339-9A70A6BFE3EB@glbcore.com/3-image001.png)
download | view image
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: Matthew.Wetmore@mdlive.com, fabrice636861@gmail.com, pgsql-admin@lists.postgresql.org
Subject: Re: wal buffer optimisation
In-Reply-To: <E1962633-6872-423C-A339-9A70A6BFE3EB@glbcore.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox