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
From: Fabrice Chapuis <fabrice636861@xxxxxxxxx>
Date: Friday, January 31, 2025 at 6:46 AM
To: "pgsql-admin@xxxxxxxxxxxxxxxxxxxx" <pgsql-admin@xxxxxxxxxxxxxxxxxxxx>
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
checkpoints_timed, or checkpoint_write_time?
Because 100.*checkpoints_req/checkpoints_timed gives me a number substantively above 100, while 100.*checkpoints_req/checkpoint_write_time is less than 1.
On Fri, Jan 31, 2025 at 10:44 AM Wetmore, Matthew (CTR) <Matthew.Wetmore@xxxxxxxxxx> wrote:
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!
