Re: wal buffer optimisation

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(dot)Wetmore(at)mdlive(dot)com> wrote:

> *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
>
>
>
> [image: 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
>

--
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!