postgres WAL checkpoints - external storage 1

WAL格納ディレクトリが pg_xlog から pg_wal にリネームされました。
また、トランザクション状態格納ディレクトリも pg_clog から pg_xact にリネームされました。(Michael Paquier) (10)
PostgreSQL 10 に関する技術情報 2017

postgres checkpoints チェックポイント

log_checkpoints = on にして調査

チェックポイントが発生するタイミングは以下の通りです。

前回チェックポイントから checkpoint_timeout に指定した時間が経過した時（デフォルトは5分）

【9.4まで】checkpoint_segmentsの数 × WALファイル(16MB) 分の変更履歴がWALファイルに書かれた時（デフォルトは48MB）

【9.5の場合】max_wal_sizeに指定したサイズ分の変更履歴がWALファイルに書かれた時（デフォルトは1GB）

手動でCHECKPOINTを実行した時

データベースクラスタをsmartモードかfastモードで停止した時

pg_start_backup関数を実行した時

https://www.ashisuto.co.jp/db_blog/article/20160603_max_wal_size.html

↑は↓の翻訳
https://blog.2ndquadrant.com/basics-of-tuning-checkpoints/

チェックポイントはとにかく、時間で発火させるべき。
PostgreSQLのチェックポイント処理のチューニング - Qiita

[local]:5433 user@exampledb=# select name, setting from pg_settings where name like '%wal_size%' or name like '%checkpoint%' order by name;
            name             |  setting  
------------------------------+-----------
 checkpoint_completion_target | 0.9
 checkpoint_flush_after       | 32
 checkpoint_timeout           | 300
 checkpoint_warning           | 30
 log_checkpoints              | off
 max_wal_size                 | 1024
 min_wal_size                 | 80
(7 rows)

https://blog.crunchydata.com/blog/tuning-your-postgres-database-for-high-write-loads

その他参考

https://qiita.com/nattof/items/85d5d304de11e3d396eb

https://www.ashisuto.co.jp/db_blog/article/20161004_checkpoint_tuning.html

WAL量の見積もり

Let’s use the first approach, for example. On my test machine running pgbench, I do see this:
postgres=# SELECT pg_current_xlog_insert_location();
 pg_current_xlog_insert_location 
---------------------------------
 3D/B4020A58
(1 row)

... after 5 minutes ...

postgres=# SELECT pg_current_xlog_insert_location();
 pg_current_xlog_insert_location 
---------------------------------
 3E/2203E0F8
(1 row)

postgres=# SELECT pg_xlog_location_diff('3E/2203E0F8', '3D/B4020A58');
 pg_xlog_location_diff 
-----------------------
            1845614240
(1 row)
This shows that over the 5 minutes, the database generated ~1.8GB of WAL, so for checkpoint_timeout = 30min that would be about 10GB of WAL. However as mentioned before, max_wal_size is a quota for 2 – 3 checkpoints combined, so max_wal_size = 30GB (3 x 10GB) seems about right.
https://blog.2ndquadrant.com/basics-of-tuning-checkpoints/

10以降

pg_current_xlog_insert_location → pg_current_wal_insert_lsn
pg_xlog_location_diff → pg_wal_lsn_diff

postgres=# SELECT current_timestamp, pg_current_wal_insert_lsn();
       current_timestamp       | pg_current_wal_insert_lsn 
-------------------------------+---------------------------
 2021-04-28 14:29:37.206456+09 | 13C/C45DED70
(1 row)

postgres=# SELECT current_timestamp, pg_current_wal_insert_lsn();
       current_timestamp       | pg_current_wal_insert_lsn 
-------------------------------+---------------------------
 2021-04-28 14:35:21.722344+09 | 13C/C50D7428
(1 row)

postgres=# SELECT pg_wal_lsn_diff('13C/C50D7428', '13C/C45DED70');
-----------------
        11503288
(1 row)

チェックポイントのログからも分かる？要確認

LOG:  checkpoint complete: wrote 3807 buffers (0.4%); 0 transaction log file(s) added, 0 removed, 1 recycled; write=150.013 s, sync=23.818 s, total=173.866 s; sync files=244, longest=3.502 s, average=0.097 s; distance=26699 kB, estimate=26699 kB

→ 26.7MB

WAL圧縮

wal_compression = on # enable compression of full-page writes

EDB Postgres活用の勘所と最新9.5の注目機能 | アシスト

WALファイルの保存

WALが損失するという課題の解決
https://www.fujitsu.com/jp/products/software/resources/feature-stories/201509wal/