Re: wal buffer optimisation

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(at)gmail(dot)com>
Date: Friday, January 31, 2025 at 6:46 AM
To: "pgsql-admin(at)lists(dot)postgresql(dot)org" <pgsql-admin(at)lists(dot)postgresql(dot)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