前言：
WAL膨胀是PostgreSQL运维过程中比较常见的问题，PostgreSQL 13允许对EXPLAN命令、auto_explain模块，AUTOVACUUM进程和pg_stat_statements模块跟踪WAL用量。
1、对EXPLAIN命令跟踪WAL用量
说明： EXPLAIN命令可以通过WAL选项（同时依赖ANALYZE选项）跟踪WAL用量，WAL的信息包含以下3个部分：
- 1、recprds：WAL记录数。
- 2、Fpi：产生全页写的数量。
- 3、Bytes：WAL字节量。
- 下面通过示例演示WAL用量跟踪的过程。
创建表：
postgres=# postgres=# CREATE TABLE tab1(id int,info text,create_time timestamp(0) without time zone); CREATE TABLE postgres=#
插入50万条数据
postgres=# postgres=# EXPLAIN(ANALYZE ON,WAL ON)INSERT INTo tab1(id,info,create_time) postgres-# SELECT n,'test_'|| n,clock_timestamp()FRoM generate_series(1,500000)n; QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------- ----------- Insert on tab1 (cost=0.00..12500.00 rows=0 width=0) (actual time=978.940..978.942 rows=0 loops=1) WAL: records=500000 bytes=39500000 -> Subquery Scan on "*SELECT*" (cost=0.00..12500.00 rows=500000 width=44) (actual time=62.020..412.255 rows=500000 loops=1) -> Function Scan on generate_series n (cost=0.00..10000.00 rows=500000 width=44) (actual time=62.011..252.100 rows=50000 0 loops=1) Planning Time: 1.684 ms Execution Time: 981.259 ms (6 rows) postgres=#
在上述代码中，records=500000，表示插入的记录数对应；bytes=39500000，标识37MB左右的WAL，与pg_wal目录产生2个16MB的WAL对应。
修改表产生的WAL的信息如下：
postgres=# EXPLAIN(ANALYZE ON,WAL ON)update tab1 SET info = info||'test'; QUERY PLAN -------------------------------------------------------------------------------------------------------------------- Update on tab1 (cost=0.00..9435.00 rows=0 width=0) (actual time=2674.537..2674.538 rows=0 loops=1) WAL: records=1000000 fpi=3185 bytes=106091595 -> Seq Scan on tab1 (cost=0.00..9435.00 rows=500000 width=38) (actual time=0.014..123.538 rows=500000 loops=1) Planning Time: 0.181 ms Execution Time: 2684.944 ms (5 rows) postgres=#
在上述代码中，records=1000000，由于使用MVCC机制，因此增加了一备，bytes=106091595，表示100MB左右的WAL，同样也是增加了大约1倍多。
- 删除表产生的WAL信息如下：
postgres=# EXPLAIN(ANALYZE ON,WAL ON)delete from tab1; QUERY PLAN ------------------------------------------------------------------------------------------------------------------- Delete on tab1 (cost=0.00..11862.00 rows=0 width=0) (actual time=1262.408..1262.410 rows=0 loops=1) WAL: records=500000 fpi=3678 bytes=57106318 -> Seq Scan on tab1 (cost=0.00..11862.00 rows=500000 width=6) (actual time=4.133..79.831 rows=500000 loops=1) Planning Time: 0.140 ms Execution Time: 1262.527 ms (5 rows) postgres=#
在上述代码中，records=500000，表示和插入的记录数对应；bytes=57106318，表示54MB左右的WAL。
2、对auto_explain模块跟踪WAL用量
通过设置参数auto_explain.log_wal可以根据WAL用量，从数据库日志文件中可以观察到以下信息。
postgres=# 2024-07-18 22:51:31.054 CST [1727] LOG: checkpoint complete: wrote 4631 buffers (28.3%); 0 WAL file(s) added, 0 removed, 10 recycled; write=269.353 s, sync=0.002 s, total=269.377 s; sync files=8, longest=0.001 s, average=0.001 s; distance=157106 kB, estimate=157106 kB; lsn=0/30000098, redo lsn=0/2B000060 postgres=#
3、对AUTOVACUUM进程跟踪WAL用量
通过设置参数log_autovacuum_min_duration，同样可以从数据库日志文件中观察到WAL用量。
2024-07-18 23:23:29.206 CST [2042] LOG: checkpoint complete: wrote 6396 buffers (39.0%); 0 WAL file(s) added, 5 removed, 0 recycled; write=269.817 s, sync=0.003 s, total=270.019 s; sync files=25, longest=0.003 s, average=0.001 s; distance=78470 kB, estimate=78470 kB; lsn=0/49000060, redo lsn=0/38CA1850 2024-07-18 23:24:30.682 CST [2111] LOG: connection received: host=[local] 2024-07-18 23:24:30.685 CST [2111] LOG: connection authenticated: user="postgres" method=trust (/postgres/data/pg_hba.conf:117) 2024-07-18 23:24:30.685 CST [2111] LOG: connection authorized: user=postgres database=postgres application_name=psql
4、对pg_stat_statements模块跟踪WAL用量
Pg_stat_statements模块的pg_stat_statements视图中的3 个字段，即：wal_records、wal_fpi、wal_bytes，用于跟踪WAL用量。
postgres=# select wal_records,wal_fpi,wal_bytes from pg_stat_statements; wal_records | wal_fpi | wal_bytes -------------+---------+----------- 0 | 0 | 0 97 | 21 | 116099 7 | 0 | 428 1000000 | 0 | 79000008 0 | 0 | 0 0 | 0 | 0 968 | 39 | 305958 0 | 0 | 0 2000000 | 6370 | 212183968 0 | 0 | 0 (10 rows)
5、使用视图pg_stat_wal查看WAL文件大小。
使用 pg_stat_wal 系统视图查看 PostgreSQL 的 WAL 日志生成情况
- pg_stat_wal 是 PostgreSQL 中的一个系统视图，它提供了关于 Write-Ahead Logging (WAL) 文件的统计信息。通过查询这个视图，您可以了解 WAL 文件的生成情况，包括 WAL 记录的数量、完整页面映像的数量以及 WAL 字节的大小等。
要查看 WAL 日志的生成情况，您可以使用以下 SQL 查询示例：
postgres=# postgres=# SELECT * FROM pg_stat_wal; wal_records | wal_fpi | wal_bytes | wal_buffers_full | wal_write | wal_sync | wal_write_time | wal_sync_time | stats_rese t -------------+---------+-----------+------------------+-----------+----------+----------------+---------------+-------------------- ----------- 5048325 | 13327 | 501217939 | 58674 | 58819 | 156 | 0 | 0 | 2024-07-18 22:32:01 .101402+08 (1 row) postgres=#
这个查询将返回当前数据库中所有 WAL 文件的统计信息。pg_stat_wal 视图中的每一行代表一个 WAL 文件，并且包含了以下列：
- pid：生成 WAL 文件的进程 ID。
- pseq：WAL 文件的序列号。
- plsn：WAL 文件的逻辑序列号。
- psize：WAL 文件的大小（以字节为单位）。
- pwrite_time：自 PostgreSQL 启动以来，WAL 文件被写入的总时间（以微秒为单位）。
- psync_time：自 PostgreSQL 启动以来，WAL 文件被同步到磁盘的总时间（以微秒为单位）。
- pwrite_count：自 PostgreSQL 启动以来，WAL 文件被写入的次数。
- psync_count：自 PostgreSQL 启动以来，WAL 文件被同步到磁盘的次数。
- p这些信息可以帮助您监控 WAL 文件的生成速度和数据库的 I/O 负载，从而优化数据库的性能和维护策略。
6、PostgreSQL WAL用量跟踪总结
PostgreSQL中的WAL文件增长是否会影响数据库性能？
PostgreSQL中的WAL（Write-Ahead Logging）文件记录了所有对数据库进行的修改，确保了事务的持久性和数据库的一致性。WAL文件的增长本身不会直接影响数据库的性能，因为WAL的写入是顺序进行的，通常对磁盘I/O的影响较小。然而，如果WAL文件增长过快，可能会间接影响性能，尤其是在以下情况下：
磁盘空间压力：
- 如果WAL文件增长到占据大量磁盘空间，可能会导致磁盘空间不足，进而影响数据库的正常运行和其他应用程序的性能。
归档和维护开销：
- 在启用WAL归档的环境中，大量的WAL文件需要被传输和存储，这会增加网络带宽和存储系统的负担。
检查点频率：
- WAL文件的累积可能会迫使数据库更频繁地执行检查点操作，以释放WAL空间，这可能会暂时增加I/O负载和CPU使用率。
长事务和复制槽：
- 长事务或未正确管理的复制槽可能导致WAL文件累积，因为数据库需要保留这些事务或复制槽所需的WAL文件，以防止数据丢失。
为了避免WAL文件增长对性能的负面影响，可以采取以下措施：
- 优化数据库配置，如调整max_wal_size和wal_keep_segments参数，以平衡WAL管理和性能。
- 监控WAL文件的增长，及时处理长事务和管理复制槽。
- 在必要时增加磁盘空间，以容纳WAL文件，避免磁盘空间瓶颈。
- 考虑使用压缩技术来减少WAL文件的大小，从而减轻网络和存储的压力。
- 通过这些方法，可以有效管理WAL文件的增长，维持数据库的高性能和稳定性。
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
