RE: any impact to DML sql statements when checkpoint starting

["James Pang (chaolpan)" <chaolpan@xxxxxxxxx>]

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

 

Thanks,

 

James

From: David G. Johnston <david.g.johnston@xxxxxxxxx>
Sent: Friday, February 4, 2022 8:35 AM
To: James Pang (chaolpan) <chaolpan@xxxxxxxxx>
Cc: pgsql-admin@xxxxxxxxxxxxxxxxxxxx
Subject: Re: any impact to DML sql statements when checkpoint starting

 

On Thu, Feb 3, 2022 at 5:29 PM James Pang (chaolpan) <chaolpan@xxxxxxxxx> wrote:

Any impact to DML(insert,update,delete,commit)  when checkpoint starting ,  not sure any lock waiting ?

 

 

Checkpoints consume disk I/O.  If the pages your DML are touching are not already in shared memory, getting them there also consumes I/O.  The WAL writing consumes I/O.

I would expect some possible (but I'd think minor) contention if the changes are touching the same buffer pages that are being flushed to disk.  But pages are fairly small individually.

 

David J.