postgresqlpostgresql-15

how to evaluate checkpoints of postgreSQL

I'm newer on postGreSQL. I'm using PG15 on linux server, the server memory is 32G. the checkpoint_timeout,sharebuffer, wal etc are using default setting on pg_settings. that means checkpoint_timeout is 5 mins. shared_buffers is 128M.

i used below sql to check

SELECT
total_checkpoints,
seconds_since_start / total_checkpoints / 60 AS minutes_between_checkpoints
FROM
(SELECT
EXTRACT(EPOCH FROM (now() - pg_postmaster_start_time())) AS seconds_since_start,
(checkpoints_timed+checkpoints_req) AS total_checkpoints
FROM pg_stat_bgwriter
) AS sub;

It return total_checkpoints : 147575 minutes_between_checkpoints : 0.08 mins

is that mean checkpoint happens every 0.08 mins? is it too fast ? how to performance it?

Solution

  • You must add checkpoints_timed and checkpoints_req to get the total number of checkpoints.

    checkpoints_timed should be about a hundred times more than checkpoints_req. If there are too many of the latter, you need to increase max_wal_size (you might find warnings to that effect in the PostgreSQL log).

    You should also increase shared_buffers for good performance.