When Postgres checkpoint, it always write buffers from pg shared_buffers to OS filesystem cache, after that, it finally make a system call fsync , that will flush dirty blocks from OS filesystem cache to disk, right?
Thanks,
James
From: David G. Johnston <david.g.johnston@gmail.com>
Sent: Friday, February 4, 2022 8:50 AM
To: James Pang (chaolpan) <chaolpan@cisco.com>
Cc: pgsql-admin@lists.postgresql.org
Subject: Re: any impact to DML sql statements when checkpoint starting
So the main concern about checkpoint impact is IO, in order to reduce the IO by checkpoint, in addition to postgres checkpoint parameters. Linux(Redhat 8), some OS kernel parameters need to be set ,right?
vm.dirty_expire_centisecs=500
vm.dirty_writeback_centisecs=250
vm.dirty_ratio=30
vm.dirty_background_ratio = 10
I understand the big picture (more-or-less), not tuning specifics. But for a checkpoint we don't really give the O/S much control - it needs to do fsync when we ask for it. It's in between the explicit issuance of checkpoints that the algorithms written into PostgreSQL and the O/S come into play to hopefully spread out the load over more time and keep the active memory holding useful data.