pg12基本参数调整

1.日志

巡检时发现日志报如下，结合数据批量导入有关

postgres@dbabackup:/data/pg12/pg_root/log$ tail -f postgresql-2022-05-26_120232.log
2022-05-26 13:16:13.031 CST [575399] LOG: checkpoints are occurring too frequently (8 seconds apart)
2022-05-26 13:16:13.031 CST [575399] HINT: Consider increasing the configuration parameter "max_wal_size".
2022-05-26 13:16:29.200 CST [575399] LOG: checkpoints are occurring too frequently (16 seconds apart)
2022-05-26 13:16:29.200 CST [575399] HINT: Consider increasing the configuration parameter "max_wal_size".
2022-05-26 13:16:44.132 CST [575399] LOG: checkpoints are occurring too frequently (15 seconds apart)
2022-05-26 13:16:44.132 CST [575399] HINT: Consider increasing the configuration parameter "max_wal_size".
2022-05-26 13:17:00.159 CST [575399] LOG: checkpoints are occurring too frequently (16 seconds apart)
2022-05-26 13:17:00.159 CST [575399] HINT: Consider increasing the configuration parameter "max_wal_size".
2022-05-26 13:17:15.011 CST [575399] LOG: checkpoints are occurring too frequently (15 seconds apart)
2022-05-26 13:17:15.011 CST [575399] HINT: Consider increasing the configuration parameter "max_wal_size".

 

2.version

postgres=# select version();
version
--------------------------------------------------------------------------------------------------------
PostgreSQL 12.10 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 9.4.0-1ubuntu1~20.04.1) 9.4.0, 64-bit
(1 row)

 

3.现有参数

postgres=# select name, setting from pg_settings where name like '%wal_size%' or name like '%checkpoint%' order by name;
name | setting
------------------------------+---------
checkpoint_completion_target | 0.5
checkpoint_flush_after | 32
checkpoint_timeout | 300
checkpoint_warning | 30
log_checkpoints | off
max_wal_size | 1024
min_wal_size | 80
(7 rows)

 

max_wal_size设置自动检查点之间WAL日志可以增长的最大值。这是一个软限制。在特殊情况下，例如超负载、归档命令失败、或者wal_keep_segments设置太高，WAL大小可能会超过max_wal_size设定的值。

如果增加此参数可能会增加崩溃恢复所需的时间。默认值为1GB（1024 MB）。

 

4. 为什么max_wal_size低值可能与问题原因有关。

当max_wal_size较低且数据库具有大量更新或快速插入时，生成WAL的速度往往会比其归档快，并且超出标准检查点进程的速度。

在Postgres实例上监视磁盘使用情况，pg_wal目录的大小急剧增加，因为保留了这些WAL文件。

 

max_wal_size还有一个对应参数，与之相反：min_wal_size。min_wal_size定义了WAL的最小值。只要在归档过程中WAL磁盘使用率保持在此设置以下，旧的WAL文件将始终被检查点回收以备将来使用，而不是删除。 这对于确保保留足够的WAL空间来处理WAL使用率的峰值很有用，例如在运行大的批量批处理作业时。缺省值为80 MB

postgres=# show max_wal_size;
max_wal_size
--------------
1GB
(1 row)

postgres=# show min_wal_size;
min_wal_size
--------------
80MB
(1 row)