文章目录
- 问题描述
- 处理思路
- 处理过程
问题描述
PG数据库实例磁盘空间使用率高达97%，其中wal空间使用量占用32G，相对比较异常。
处理思路
wal日志是PG数据库的事务预写日志，对于数据库的一个变更操作，数据库流程大概如下：
● 客户端执行DML操作进行数据变更
● 将相关的变更数据页写入wal buffer，对应的数据更新写到内存中的数据页（脏页）
● 事务commit时，wal buffer进行刷盘，写入到wal log，该步骤已经保证了数据不会丢失
● 周期性checkpoint出发将内存中的脏页进行刷盘
对于wal日志，PG数据库也提供了一些参数以及机制保证wal日志可周期性的进行归档，并控制相关大小，通过checkpoint来将历史无效的wal历史进行清理，从而避免wal日志占用过大的空间。所以针对wal日志两空间使用较大的情况，我们可以分两步去排查：
1）检查wal相关参数
● max_wal_size( integer) ： 两次checkpoint之间允许的最大wal日志大小，这是一个软限制，当重负载、失败archive_command等情况下可能超出该参数设置大小。增加此参数会增加崩溃恢复所需的时
● min_wal_size( integer) ：只要 WAL 磁盘使用率保持在此设置以下，旧的 WAL 文件总是会在检查点被回收以备将来使用，而不是被删除。这可用于确保保留足够的 WAL 空间来处理 WAL 使用量的峰值，例如在运行大型批处理作业时。
● wal_keep_segments ： 主节点数据库为standby节点保留的最大wal log数量
● wal_level ： wal日志记录模式
2）在参数设置正常的情况下，定位一些可能会导致wal日志无法正常清理的情况
● standby数据库实例复制存在一定的异常，导致主节点保存大量的wal日志
● 逻辑复制订阅端数据消费异常，导致发布节点堆积大量的wal日志
处理过程
1、检查wal相关参数
可以看到wal相关的参数设置基本都是正常的，wal_level设置为logical，一定程度上会增大wal日志空间使用量，但是也不至于会导致wal日志量使用高达32G
psql=#SELECT * FROM pg_settings where name like '%wal%'
+------------------------------+-------------------+----------------+-------------------------------+---------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------+-------------------+-------------------+--------------------+-------------------+-------------------+-------------------------------------------+--------------------+---------------------+----------------------+----------------------+---------------------------+
| NAME | SETTING | UNIT | CATEGORY | SHORT_DESC | EXTRA_DESC | CONTEXT | VARTYPE | SOURCE | MIN_VAL | MAX_VAL | ENUMVALS | BOOT_VAL | RESET_VAL | SOURCEFILE | SOURCELINE | PENDING_RESTART |
+------------------------------+-------------------+----------------+-------------------------------+---------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------+-------------------+-------------------+--------------------+-------------------+-------------------+-------------------------------------------+--------------------+---------------------+----------------------+----------------------+---------------------------+
| max_wal_senders | 20 | | Replication / Sending Servers | Sets the maximum number of simultaneously running WAL sender processes. | | postmaster | integer | configuration file | 0 | 262143 | | 10 | 20 | | | |
| max_wal_size | 32768 | MB | Write-Ahead Log / Checkpoints | Sets the WAL size that triggers a checkpoint. | | sighup | integer | configuration file | 2 | 2147483647 | | 1024 | 32768 | | | |
| min_wal_size | 8192 | MB | Write-Ahead Log / Checkpoints | Sets the minimum size to shrink the WAL to. | | sighup | integer | configuration file | 2 | 2147483647 | | 80 | 8192 | | | |
| rds_max_non_super_wal_snd | 10 | | Ungrouped | Sets the maximum number of simultaneously running WAL sender processes requested by non-super users. | | sighup | integer | configuration file | -1 | 262143 | | -1 | 10 | | | |
| wal_block_size | 8192 | | Preset Options | Shows the block size in the write ahead log. | | internal | integer | default | 8192 | 8192 | | 8192 | 8192 | | | |
| wal_buffers | 2048 | 8kB | Write-Ahead Log / Settings | Sets the number of disk-page buffers in shared memory for WAL. | | postmaster | integer | configuration file | -1 | 262143 | | -1 | 2048 | | | |
| wal_compression | off | | Write-Ahead Log / Settings | Compresses full-page writes written in WAL file. | | superuser | bool | default | | | | off | off | | | |
| wal_consistency_checking | | | Developer Options | Sets the WAL resource managers for which WAL consistency checks are done. | Full-page images will be logged for all data blocks and cross-checked against the results of WAL replay. | superuser | string | default | | | | | | | | |
| wal_keep_segments | 64 | | Replication / Sending Servers | Sets the number of WAL files held for standby servers. | | sighup | integer | configuration file | 0 | 2147483647 | | 0 | 64 | | | |
| wal_level | logical | | Write-Ahead Log / Settings | Set the level of information written to the WAL. | | postmaster | enum | configuration file | | | {minimal,replica,logical} | replica | replica | | | |
| wal_log_hints | off | | Write-Ahead Log / Settings | Writes full pages to WAL when first modified after a checkpoint, even for a non-critical modifications. | | postmaster | bool | default | | | | off | off | | | |
| wal_receiver_status_interval | 1 | s | Replication / Standby Servers | Sets the maximum interval between WAL receiver status reports to the primary. | | sighup | integer | configuration file | 0 | 2147483 | | 10 | 1 | | | |
| wal_receiver_timeout | 60000 | ms | Replication / Standby Servers | Sets the maximum wait time to receive data from the primary. | | sighup | integer | default | 0 | 2147483647 | | 60000 | 60000 | | | |
| wal_retrieve_retry_interval | 1000 | ms | Replication / Standby Servers | Sets the time to wait before retrying to retrieve WAL after a failed attempt. | | sighup | integer | configuration file | 1 | 2147483647 | | 5000 | 1000 | | | |
| wal_segment_size | 2048 | 8kB | Preset Options | Shows the number of pages per write ahead log segment. | | internal | integer | default | 2048 | 2048 | | 2048 | 2048 | | | |
| wal_sender_timeout | 60000 | ms | Replication / Sending Servers | Sets the maximum time to wait for WAL replication. | | sighup | integer | configuration file | 0 | 2147483647 | | 60000 | 60000 | | | |
| wal_sync_method | fdatasync | | Write-Ahead Log / Settings | Selects the method used for forcing WAL updates to disk. | | sighup | enum | default | | | {fsync,fdatasync,open_sync,open_datasync} | fdatasync | fdatasync | | | |
| wal_writer_delay | 10 | ms | Write-Ahead Log / Settings | Time between WAL flushes performed in the WAL writer. | | sighup | integer | configuration file | 1 | 10000 | | 200 | 10 | | | |
| wal_writer_flush_after | 64 | 8kB | Write-Ahead Log / Settings | Amount of WAL written out by WAL writer that triggers a flush. | | sighup | integer | configuration file | 0 | 2147483647 | | 128 | 64 | | | |
+------------------------------+-------------------+----------------+-------------------------------+---------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------+-------------------+-------------------+--------------------+-------------------+-------------------+-------------------------------------------+--------------------+---------------------+----------------------+----------------------+---------------------------+
返回 19 行记录,花费 5.00 ms.
psql=#SELECT * FROM pg_settings where name like '%checkpoint_timeout%'
+--------------------+-------------------+----------------+-------------------------------+----------------------------------------------------------+----------------------+-------------------+-------------------+--------------------+-------------------+-------------------+--------------------+--------------------+---------------------+----------------------+----------------------+---------------------------+
| NAME | SETTING | UNIT | CATEGORY | SHORT_DESC | EXTRA_DESC | CONTEXT | VARTYPE | SOURCE | MIN_VAL | MAX_VAL | ENUMVALS | BOOT_VAL | RESET_VAL | SOURCEFILE | SOURCELINE | PENDING_RESTART |
+--------------------+-------------------+----------------+-------------------------------+----------------------------------------------------------+----------------------+-------------------+-------------------+--------------------+-------------------+-------------------+--------------------+--------------------+---------------------+----------------------+----------------------+---------------------------+
| checkpoint_timeout | 1200 | s | Write-Ahead Log / Checkpoints | Sets the maximum time between automatic WAL checkpoints. | | sighup | integer | configuration file | 30 | 86400 | | 300 | 1200 | | | |
+--------------------+-------------------+----------------+-------------------------------+----------------------------------------------------------+----------------------+-------------------+-------------------+--------------------+-------------------+-------------------+--------------------+--------------------+---------------------+----------------------+----------------------+---------------------------+
返回 1 行记录,花费 4.00 ms.
2、检查可能导致wal日志无法正常清理的情况
从数据库中可以看到该数据库实例是存在一个逻辑复制的发布任务，但是对应的复制槽已经不再使用！这极有可能是导致wal日志大量堆积的原因，因此我们将对应的信息与业务方进行确认，需要与业务方确定该逻辑复制订阅端是否仍正常运行。
业务方反馈该逻辑复制的订阅端已经关闭，根本原因基本得到印证。逻辑复制的订阅端无法正常的消费数据更新，导致主数据库节点无法正常获取更新相关的lsn消费位点，为了保证复制链路的数据正常复制，这部分wal日志是无法被清理的。也正是这个原因，就会导致源端wal日志产生大量的日志堆积。
3、wal日志清理
对于因为逻辑复制异常造成的wal日志无法正常被清理的情况，我们可以将对应的异常复制槽进行删除，等待数据库进行一次checkpoint后会自动对无效的wal日志进行清理。
select pg_drop_replication_slot(’${unhealth_logical_slot_name}’);
可以看到删除异常复制槽，然后手动checkpoint后，wal日志的空间占用下降很明显