how to evaluate checkpoints of postgreSQL

Question

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?

do you mean set share_buffer=8GB, checkpoint_timeout=30mins? — maomifadacai
– maomifadacai, Commented Aug 2, 2024 at 4:36
pg_postmaster_start_time() has no valid role to play in this query. Stats do not get reset merely upon a (non-crash-recovery) server start up. They get carried over. The correct timestamp would presumably be 'stats_reset' from the view itself. — jjanes
– jjanes, Commented Aug 2, 2024 at 18:36

Laurenz Albe · Accepted Answer · 2024-08-02 05:51:25Z

0

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.

answered Aug 2, 2024 at 5:51

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

how to evaluate checkpoints of postgreSQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related