Обсуждение: any impact to DML sql statements when checkpoint starting

Поиск
Список
Период
Сортировка

any impact to DML sql statements when checkpoint starting

От
"James Pang (chaolpan)"
Дата:

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

 

Thanks,

 

James

Re: any impact to DML sql statements when checkpoint starting

От
"David G. Johnston"
Дата:
On Thu, Feb 3, 2022 at 5:29 PM James Pang (chaolpan) <chaolpan@cisco.com> 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.

RE: any impact to DML sql statements when checkpoint starting

От
"James Pang (chaolpan)"
Дата:

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@gmail.com>
Sent: Friday, February 4, 2022 8:35 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

 

On Thu, Feb 3, 2022 at 5:29 PM James Pang (chaolpan) <chaolpan@cisco.com> 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.

 

Re: any impact to DML sql statements when checkpoint starting

От
"David G. Johnston"
Дата:
On Thu, Feb 3, 2022 at 5:45 PM James Pang (chaolpan) <chaolpan@cisco.com> wrote:

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.

David J.

RE: any impact to DML sql statements when checkpoint starting

От
"James Pang (chaolpan)"
Дата:

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

 

On Thu, Feb 3, 2022 at 5:45 PM James Pang (chaolpan) <chaolpan@cisco.com> wrote:

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.

 

David J.

Re: any impact to DML sql statements when checkpoint starting

От
"David G. Johnston"
Дата:

On Thursday, February 3, 2022, James Pang (chaolpan) <chaolpan@cisco.com> wrote:

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?


Please don’t top-post.

From the docs:

On Linux and POSIX platforms checkpoint_flush_after allows to force the OS that pages written by the checkpoint should be flushed to disk after a configurable number of bytes. Otherwise, these pages may be kept in the OS's page cache, inducing a stall when fsync is issued at the end of a checkpoint. This setting will often help to reduce transaction latency, but it also can have an adverse effect on performance; particularly for workloads that are bigger than shared_buffers, but smaller than the OS's page cache.

David J.