文章目錄
- 問題描述
- 處理思路
- 處理過程
問題描述
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日志的空間占用下降很明顯