[转帖]PostgreSQL中切换WAL是否会触发checkpoint

postgresql,切换,wal,是否,触发,checkpoint · 浏览次数 : 0

小编点评

是的，PostgreSQL中普通WAL切换也会触发检查点事件。在PostgreSQL中，普通WAL切换不会触发checkpoint事件，只有当WAL数量超过`max_wal_size`即默认64 files才会触发检查点，完成后WAL文件可以被删除/重复使用。

正文

https://www.modb.pro/db/570143?utm_source=index_ori

内容概述

Oracle数据库中切换redo日志会触发检查点事件，那么在PostgreSQL中是否也会触发checkpoint事件呢？ 

Oracle中测试

[oracle@orcldb ~]$ sqlplus / as sysdba SQL> show parameter alert NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ log_checkpoints_to_alert boolean TRUE SQL> alter system switch logfile; System altered. SQL>  ### alert日志信息 Wed Nov 30 21:23:38 2022 Beginning log switch checkpoint up to RBA [0x25.2.10], SCN: 1069264 Thread 1 advanced to log sequence 37 (LGWR switch) Current log# 1 seq# 37 mem# 0: /oradata/orcl/redo01.log Wed Nov 30 21:23:38 2022 Archived Log entry 31 added for thread 1 sequence 36 ID 0x625613b9 dest 1: 

PG中测试

postgres=# show log_checkpoints ; log_checkpoints ----------------- on (1 row) postgres=# select pg_switch_wal(); pg_switch_wal --------------- 1/D5000248 (1 row) postgres=# select pg_switch_wal(); pg_switch_wal --------------- 1/D6000000 (1 row) postgres=# select pg_switch_wal(); pg_switch_wal --------------- 1/D6000000 (1 row) postgres=#  ### PG中的server log没有checkpoint日志产生  ### 手工触发检查点 postgres=# checkpoint; CHECKPOINT postgres=#  ### PG中的server log 2022-11-30 21:26:34.640 CST [56562] LOG: checkpoint starting: immediate force wait 2022-11-30 21:26:34.651 CST [56562] LOG: checkpoint complete: wrote 0 buffers (0.0%); 0 WAL file(s) added, 1 removed, 0 recycled; write=0.001 s, sync=0.001 s, total=0.012 s; sync files=0, longest=0.000 s, average=0.000 s; distance=16383 kB, estimate=16383 kB 

WAL segment超过max_wal_size测试

WAL segment超过max_wal_size(the default value is 1GB (64 files))触发检查点， ### 1. pg中插入数据 postgres=# insert into t1 select n from generate_series(1,1000000) as n;\watch 1 INSERT 0 1000000 INSERT 0 1000000 INSERT 0 1000000 INSERT 0 1000000 INSERT 0 1000000 INSERT 0 1000000 INSERT 0 1000000 INSERT 0 1000000 INSERT 0 1000000 。。。。。  ### 2. pg server log中观察日志信息 2022-11-30 21:31:54.931 CST [56562] LOG: checkpoints are occurring too frequently (20 seconds apart) 2022-11-30 21:31:54.931 CST [56562] HINT: Consider increasing the configuration parameter "max_wal_size". 2022-11-30 21:31:54.941 CST [56562] LOG: checkpoint starting: wal 2022-11-30 21:32:12.879 CST [56562] LOG: checkpoint complete: wrote 59 buffers (0.4%); 0 WAL file(s) added, 0 removed, 33 recycled; write=17.517 s, sync=0.040 s, total=17.948 s; sync files=3, longest=0.022 s, average=0.014 s; distance=531539 kB, estimate=531539 kB 2022-11-30 21:32:14.330 CST [56562] LOG: checkpoints are occurring too frequently (20 seconds apart) 2022-11-30 21:32:14.330 CST [56562] HINT: Consider increasing the configuration parameter "max_wal_size". 2022-11-30 21:32:14.330 CST [56562] LOG: checkpoint starting: wal 2022-11-30 21:32:31.044 CST [56562] LOG: checkpoint complete: wrote 3 buffers (0.0%); 0 WAL file(s) added, 0 removed, 33 recycled; write=16.415 s, sync=0.002 s, total=16.715 s; sync files=3, longest=0.002 s, average=0.001 s; distance=543622 kB, estimate=543622 kB 2022-11-30 21:32:32.398 CST [56562] LOG: checkpoints are occurring too frequently (18 seconds apart) 2022-11-30 21:32:32.398 CST [56562] HINT: Consider increasing the configuration parameter "max_wal_size". 2022-11-30 21:32:32.398 CST [56562] LOG: checkpoint starting: wal 2022-11-30 21:32:49.401 CST [56562] LOG: checkpoint complete: wrote 30 buffers (0.2%); 0 WAL file(s) added, 0 removed, 33 recycled; write=16.689 s, sync=0.047 s, total=17.003 s; sync files=5, longest=0.044 s, average=0.010 s; distance=539002 kB, estimate=543160 kB 2022-11-30 21:32:50.935 CST [56562] LOG: checkpoints are occurring too frequently (18 seconds apart) 2022-11-30 21:32:50.935 CST [56562] HINT: Consider increasing the configuration parameter "max_wal_size". 2022-11-30 21:32:50.935 CST [56562] LOG: checkpoint starting: wal 2022-11-30 21:32:53.894 CST [62320] ERROR: canceling statement due to user request 2022-11-30 21:32:53.894 CST [62320] STATEMENT: insert into t1 select n from generate_series(1,1000000) as n; 2022-11-30 21:37:20.024 CST [56562] LOG: checkpoint complete: wrote 910 buffers (5.6%); 0 WAL file(s) added, 0 removed, 33 recycled; write=269.061 s, sync=0.001 s, total=269.089 s; sync files=5, longest=0.001 s, average=0.001 s; distance=538522 kB, estimate=542696 kB 

小结

Oracle数据库中进行redo切换会触发检查点事件将"脏块"刷到物理磁盘，完成后redo log可以被重复利用。 PostgreSQL中普通WAL切换并不会触发checkpoint，只有当WAL数量超过max_wal_size即默认64 files才会触发检查点，完成后WAL文件可以被删除/重复使用。

与[转帖]PostgreSQL中切换WAL是否会触发checkpoint相似的内容：

[转帖]PostgreSQL中切换WAL是否会触发checkpoint

小编点评

正文

内容概述

Oracle中测试

WAL segment超过max_wal_size测试

小结

与[转帖]PostgreSQL中切换WAL是否会触发checkpoint相似的内容：

# 热门排行