PostgreSQL 监控的一个重要部分是查看生成了多少事务日志或 WAL（预写日志）文件。WAL 是 PostgreSQL 使用的 MVCC（多版本并发控制）的一部分。WAL 确保在数据库崩溃时保留对数据库对象（例如，表和索引）所做的更改。因此，在对数据库文件进行更改之前，对数据库对象的任何更改都会记录在 WAL 中。
如果归档守护进程已关闭，则会生成这些 WAL，但会依次覆盖最旧的事务日志。因此，无需监视空间。但是，关闭归档后，如果发生媒体崩溃，则无法恢复数据库（因为没有备份），因此生产系统不应在关闭归档的情况下运行。为了保持连续归档，归档守护进程应该运行并配置适当的参数或流复制。
注意：要打开归档守护进程，需要将“archive_mode”参数设置为“on”并重新启动数据库集群。
这些 WAL 文件对数据库操作至关重要，因此我们需要知道正在使用多少空间。
注意：术语“事务日志”和 WAL 可以互换使用。
通常不用担心 WAL 文件的生成，因为 WAL 目录（PG 10 以下版本为 pg_xlog，PG 11 及以上版本为 pg_wal）是 PGDATA 主目录下的子目录。那么，如果 PGDATA 目录未满，我们为什么要担心 WAL 目录呢？
WAL 空间之所以重要有几个原因：
- 在基于云的数据库即服务 (DaaS) 上，为 WAL 使用更多的磁盘空间可能会很昂贵。了解需求对于估计数据库占用空间很重要。
- WAL 在备份时间中起着重要作用，因此了解预期的 WAL 数量将有助于确定需要多少空间进行备份以及恢复可能需要多长时间。
- 如果您有复制（逻辑或物理），您应该知道在订阅者或备用服务器通过正常的 WAL 文件传输变得无法恢复之前系统可以保留多少天的 WAL。这可能是逻辑复制的问题，因为长事务可能会导致延迟，进而导致主服务器上的 WAL 文件保留问题，因为在事务完成之前不会删除文件。
我们如何监控 PostgreSQL 中的事务日志使用情况？
通常，PG 的 WAL 目录（默认情况下）位于PGDATA主目录下。因此，除非“pg_wal”目录有单独的挂载点（通过创建从PGDATA主目录到新挂载点的链接），否则 WAL 只是更大的PGDATA主目录下的另一组文件。
将 WAL 文件放在单独的挂载点上是个好主意有几个原因。
生成 WAL 文件通常是 IO 瓶颈。对于在线事务处理( OLTP) 系统等高写入系统而言，能够非常快速地写入文件系统至关重要。因此，我们可以将 WAL 文件挂载点放在昂贵的固态磁盘 (SSD) 上，而普通磁盘用于 PGDATA 主目录。这为我们节省了资金并提高了性能。
我们可以直接监控 WAL 磁盘空间的使用情况，而不是监控 PGDATA 目录的大小，然后减去 pg_wal 目录的大小，来了解 WAL 文件占用了多少整体空间。这使我们更容易看到我们何时遇到 WAL 文件保留问题。
要监控 WAL 磁盘空间使用情况，我们可以使用诸如du或df 之类的 OS 程序。在数据库内部，我们可以使用一些使用名为“pg_ls_dir”的函数的 SQL 命令。 以下是一些可用于基本监控工具的示例：
Linux 操作系统检查：
作为组合文件系统的一部分：
$ du $PGDATA/pg_wal -m
1 /var/lib/postgresql/14/main/pg_wal/archive_status
881 /var/lib/postgresql/14/main/pg_wal
在单独的挂载点上：
$ df -hP /pg_wal
Filesystem Size Used Avail Use% Mounted on
/dev/mapper/vgbackup_wal 2G 881M 1167M 44% /pg_wal
使用 SQL：
$: psql -d db01
$: psql -d db01
SELECT ( a.wals::int * b.wal_seg_size::int / 1024 / 1024 ) AS “WAL_Space_Used_MB”
FROM (
SELECT count(*) wals
FROM pg_ls_dir(‘pg_wal’)
WHERE pg_ls_dir ~ ‘^[0-9A-F]{24}’
) a,
(
SELECT setting wal_seg_size
FROM pg_settings
WHERE name = ‘wal_segment_size’
) b;
WAL_Space_Used_MB: 896
注意：在基于云的数据库中，“pg_ls_dir”功能不可用，因为无法访问 DaaS 上的超级用户权限。
/* wal_records – Number of records inserted into the transaction log
wal_fpi – Number of Full Page Images written to the transaction log
wal_bytes – The amount of traffic generated towards the WAL logs.
wal_buffers_full – Number of times WAL data was written to disk because WAL buffers became full
wal_write – Number of times WAL buffers were written out to disk via XLogWrite request.
wal_write_time – Total amount of time (ms) spent writing WAL buffers to disk via XLogWrite
wal_sync_time – Total amount of time (sm) spent syncing WAL files to disk via issue_xlog_fsync
stats_reset – Time at which these statistics were last reset
*/
SELECT now() wal_capture_time,
wal_records,
wal_fpi,
wal_bytes,
wal_buffers_full,
wal_write,
wal_write_time,
wal_sync_time,
stats_reset
FROM pg_stat_wal;-[ RECORD 1 ]----±-----------------------------
wal_capture_time | 2022-05-18 18:21:11.999589+00
wal_records | 0
wal_fpi | 0
wal_bytes | 0
wal_buffers_full | 0
wal_write | 1
wal_write_time | 0
wal_sync_time | 0
stats_reset | 2022-05-18 18:06:20.203876+00最重要的数据项是带有时间戳的“wal_records”、“wal_bytes”（使用 SQL 或来自另一个程序的日期）。现在，可以随时间跟踪 WAL 字节的总量。
这种方法的真正优势在于它可以与基于云的数据库结合使用。这已使用 Amazon 的关系数据库系统 (RDS) 进行了测试并成功完成。
以下是在 Amazon 的 RDS 中使用 PostgreSQL14 实例的示例：
$ psql -h database-1.***But is .amazonaws.com -p 5432 --dbname=db01 --user=postgres
db01=> SELECT wal_records, wal_fpi, wal_bytes, wal_buffers_full FROM pg_stat_wal ;wal_records | wal_fpi | wal_bytes | wal_buffers_full
887 | 165 | 401865 | 43323db01=> CREATE TABLE t1 AS SELECT md5(random()::text) id FROM generate_series(1, 100000);
db01=> SELECT wal_records, wal_fpi, wal_bytes, wal_buffers_full FROM pg_stat_wal ;wal_records | wal_fpi | wal_bytes | wal_buffers_full
406040 | 218 | 34888334 | 51148
哪些参数会影响事务日志？
某些 PostgreSQL 参数会影响 WAL 文件的生成和性能。以下是主要的：
范围
值和单位
描述
归档模式
开|关
使用 archive 命令设置 WAL 文件的归档。此外，启用归档程序守护程序。
归档命令
例子是：。cp, pgbackrest, scp
如果命令留空（并且“archive_mode”打开），WAL 段将累积。
设置“/bin/true”会起作用，但不会累积 WAL 文件。
与“archive_mode”参数一起使用。归档完成的 WAL 文件段时执行的 OS 命令。
checkpoint_timeout
以秒计
设置自动 WAL 检查点之间的最长时间
checkpoint_completion_target
检查点之间总时间的百分比
默认为：0.9
在检查点期间刷新脏缓冲区所花费的时间，作为检查点间隔的一部分。
min_wal_size
以字节为单位，但可以是 KB、MB 或 GB。
设置 WAL 目录可以缩小到的最小大小。
max_wal_size
以字节为单位，但可以是 KB、MB 或 GB。
设置触发检查点的 WAL 大小。
wal_keep_segments
以 MB 为单位
设置为备用服务器保留的 WAL 文件数。
PG v12 后弃用。
wal_keep_size
以字节为单位
指定要保存在 pg_wal 目录中的过去事务日志的最小大小，以用于复制目的。新参数 – PG v13 +
wal_level
最小，副本，逻辑
设置写入 WAL 的信息级别。
注意：术语“WAL 段”和“WAL 文件”可以互换使用。
对于参数设置和 WAL 文件性能，还有一些额外的注意事项。
检查点会对 WAL 文件的生产产生影响。为了节省磁盘空间，WAL 文件需要偶尔回收。这就是检查点过程发挥作用的地方。
当数据写入数据库时，想法是先将数据写入 WAL 文件，然后将数据写入位于共享缓冲池内的脏缓冲区。脏缓冲区必须在某个时候由后台写入器或在检查点期间写入磁盘。当所有脏缓冲区都已写入磁盘时（直到那个时间点），可以删除/回收 WAL 文件。因此，检查点可以直接影响磁盘上保留了多少 WAL 文件。
例如，检查点之间的时间差越大，生成的 WAL 就越少。这是因为当第一次触摸数据块时，在检查点完成后，必须将整个块发送到 WAL 文件。如果该块在下一个检查点之前被多次更改，那么只有更改被写入 WAL 文件而不是整个块。检查点之间的时间越长，写入事务日志的块数就越少。因此，分散检查点会减少写入的 WAL 数量。
这些参数会影响使用了多少空间，并且通常会在“min_wal_size”和“max_wal_size”中的值之间变化。“min_wal_size”是一个参数，用于确定在任何时候在 WAL 目录中保存的 WAL 文件的最小数量。“max_wal_size”参数描述了在运行自动检查点之前可以生成的最大 WAL 文件数量。增加限制可以减少检查点的数量，但确实会增加崩溃恢复所需的时间。
“wal_keep_size”值用于确定需要保留的 WAL 文件的数量，以防备用数据库需要检索它们以进行流复制。如果“wal_keep_size”大于“max_wal_size”，则“wal_keep_size”取代“max_wal_size”值。
最后，“wal_level”值确实会改变写入 WAL 文件的数据量。每个值都会有更多的数据写入日志，所以“minimal”写入的数据最少，“replica”（WAL归档所需的级别）是默认值，“logical”用于逻辑复制，写入的数据最多. 如果需要备份或备用数据库，则应使用“副本”。只有在使用逻辑复制时，才需要“逻辑”。
其他可能影响 WAL 文件生成和性能的因素
一些数据库操作会对数据库 WAL 生成产生很大影响。这里有几个案例：
- 大量事务：如果系统正在执行大量数据操作语言（DML）调用，那么这将对数据库页面产生大量更改，并且这些更改必须在写入磁盘之前写入 WAL 文件。
- 吸尘设置：大量的吸尘也会产生大量的 WAL 段。具有不正确的真空或自动真空参数可能会导致 WAL 段不必要地增加。
- 归档失败：如果设置了“archive_command”参数并失败，则不会从 WAL 目录中删除 WAL 文件，因为它正在等待 WAL 文件可以成功归档。
- 复制：在将 WAL 文件应用于订阅者（逻辑）或备用（物理）实例之前，不会从主数据库的 WAL 目录中删除它们。因此，如果有任何复制滞后，那么保存在主服务器上的 WAL 文件的数量就会增加。应特别注意复制滞后。
- WAL 段大小： 默认缓冲区大小为 16MB，这对于重写入系统可能不够。从 PG 11 开始，有一种机制可以使用“pg_resetwal”命令调整日志缓冲区的大小。以下是将 wal_segment 大小从 16MB 更改为 64MB 的命令：
pg_resetwal -D /var/lib/postgresql/ 14 /main --wal-segsize= 64
拥有更大的缓冲区大小意味着较小文件的开销更少。它还可以提高写入性能。
我们如何减少事务日志使用的空间？
以下是减少生成的 WAL 文件数量的几种方法：
1.打开“wal_compression”。如果“full_page_writes”参数设置为“on”（默认值），此参数会压缩 WAL 文件。这将减少空间使用，但代价是使用额外的 CPU 进行写入和恢复操作。
2.减小“wal_keep_size”参数。只在“pg_wal”目录中保留我们需要的东西。确保有足够的空间容纳一天的 WAL 文件加上 20% 是一个很好的做法。你可能不需要更多。但是，这意味着任何逻辑复制事务都应保持在几分钟而不是几小时，并且必须进行监控以防延迟增加。如果出现网络故障或长时间运行的事务，还应该制定计划。例如，将最旧的 WAL 移动到另一个位置并创建指向它们的软链接或扩展“pg_wal”空间。
在 DaaS 中，空间可能会自动扩展，但这会导致另一个问题，因为除非创建新的数据库集群并移植数据，否则无法回收该空间。
3.减少大数据负载期间的 WAL 生产。在加载大量数据期间，一些点可能（如果可能）有助于减少 WAL 文件的生成。他们包括：
- 在一个命令中使用 COPY，而不是使用多个 INSERT。这比多次插入更快并且开销更少。
- 删除索引：在具有大量索引的表上，删除这些索引并在之后重建它们将节省大量时间和 WAL 空间。
- 增加“max_wal_size”。如果临时增加此参数，则解决数据负载所需的检查点数量将减少。
结论
观察“pg_wal”目录对于管理磁盘空间使用和监控 PostgreSQL 实例的整体状况非常重要。WAL 文件生成中的意外峰值可能会导致复制期间出现问题，如果启用归档，并且“ pg_wal”目录已填满，它可能会阻止集群运行。正确设置参数对于 WAL 文件性能至关重要。找到正确的参数设置是关键，但这并不容易实现。为了获得最佳性能，应将 WAL 文件放在具有更快存储速度的单独挂载点上，这应该被视为最佳实践。最后，建议增加 WAL 文件缓冲区的大小（默认为 16MB），因为更大的缓冲区（文件）往往具有更少的开销并且可以更快地归档。
原文标题：Monitoring Transaction Logs in PostgreSQL
原文作者：马特·皮尔森
原文地址：https://blog.pythian.com/monitoring-transaction-logs-in-postgresql/
