1. 背景很多初学者会对WAL日志占用多少空间比较疑惑听网上的一些文章说是由max_wal_size来控制的但发现很多时候WAL日志空间会超过这个设置的值不知道为什么? 同时有时会发现WAL日志不清理了占用空间在不停的增长然后不知道为什么看一些网上的文章发现情况不是网上说的那种情况。中启乘数科技工程师在服务客户的工程师遇到了导致WAL日志空间膨胀不清理的各种用分期并进行了深入全面的分析基本囊括了所有的导致WAL日志膨胀的各种原因。所以对于初学者来说不需要再看网上那些不全面的文章了只看这篇文章就够了。2. 决定WAL日志占用空间大小因素控制WAL日志的数量由以下这三个参数控制max_wal_sizemin_wal_sizewal_keep_segments或wal_keep_size注意PostgreSQL13版本后wal_keep_segments参数以及废弃了由wal_keep_size替代此参数很多人认为WAL占用的空间是由max_wal_size来控制的这种认识是不全面的下面我们详细讲解这几个参数的意思。假设pg_wal下的文件为000000A7000000040000005A 000000A7000000040000005B 000000A7000000040000005C 000000A7000000040000005D 000000A7000000040000005E 000000A7000000040000005F 000000A70000000400000060 000000A70000000400000061 000000A70000000400000062 000000A70000000400000063 000000A70000000400000064假设当前正在写的WAL文件为000000A70000000400000060则wal_keep_segments控制000000A7000000040000005A到000000A70000000400000060的个数而min_wal_size控制000000A70000000400000060到000000A70000000400000064即这一段至少要保留min_wal_size的WAL日志。如果min_wal_size wal_keep_segments 大于了max_wal_size那么WAL日志空间至少也会占用min_wal_size wal_keep_segments。所以从这里可以看出WAL占用的空间大小并不是完全由max_wal_size控制的只有在min_wal_size wal_keep_segments的值小于max_wal_size时PostgreSQL才尽量保值WAL的空间不超过这个值。注意这里说的是尽量原因是PostgreSQL是在做checkpoint时把不需要的WAL日志给清理掉但是如果数据库由很大的写导致还没有来得及做checkpoint时这时WAL日志占用的空间会超过max_wal_size设置的值。如果min_wal_size wal_keep_segments小于max_wal_size那么WAL日志空间尽量保持不超过max_wal_size参数设置的值当然每次checkpoint清理时会保持WAL的日志空间不会低于min_wal_size wal_keep_segments的值。所以从这个原理来说min_wal_size不需要设置太大生产库只需要为1G左右大小时就够用了不需要太大。而为了防止备库同步失败应该设置一个较大的wal_keep_segmentsWAL文件为16M大小可把wal_keep_segments设置为500或更大。max_wal_size比 min_wal_size wal_keep_segments略大一点就可以了。实际上参数max_wal_size主要时为了控制checkpoint发生的频繁程度target (double) ConvertToXSegs(max_wal_size_mb) / (2.0 CheckPointCompletionTarget);如果checkpoint_completion_target设置为0.5时则每写了 max_wal_size/2.5 的WAL日志时就会发送一次checkpoint。checkpoint_completion_target的范围为0~1那么结果就是写的WAL的日志量超过: max_wal_size的1/31/2时就会发生一次checkpoint。3. 导致WAL日志空间膨胀的原因3.1 长事务数据库中如果有长事务PostgreSQL数据库对于这个长事务开始后产生的所有WAL日志都不会清理。select pid,usename, xact_start from pg_stat_activity where now() - xact_start interval ‘8 hours’;下面时监控超过8个小时的长事务的SQL:select pid,usename, xact_start from pg_stat_activity where now() - xact_start interval 8 hours;更甚的情况是用户有“Idle in transaction”的连接即一个连接开启了事务然后什么事情也不干一直空闲着用下面的SQL查询“Idle in transaction”的连接select pid,client_addr,usename,datname, xact_start,state from pg_stat_activity where state not in (active,idle) order by xact_start;如果有长时间的“idle in transaction”的连接需要kill掉kill的方法是select pg_terminate_backend(3415)其中3415是这个连接的pid。当然kill掉之前需要调查这中长时间的“idle in transaction”的连接是如何产生的。对于一些应用产生的“idle in transaction”随便kill掉可能会导致应用出现问题需要注意。3.2 废弃的复制槽(replication slots)复制槽是用来保证逻辑复制或物理复制需要的WAL日志不会被清理掉。如果使用了逻辑复制或物理复制使用的复制槽而这些逻辑复制或物理因为某些原因停掉了那么会导致这些复制槽会把WAL的日志保留着。如果是逻辑复制或物理复制停掉了则需要尽快把这些逻辑复制或物理复制启动起来否则很容易把主库的空间撑满。用下面的SQL查询复制槽SELECT slot_name, slot_type, database, xmin,active,active_pid FROM pg_replication_slots ORDER BY age(xmin) DESC;如果上面结果某一行中active为空说明复制停掉了需要检查。如果逻辑复制或物理复制停掉了但一时半会还启动不起来而主库的空间又要慢了这时可以强制把复制槽给删除掉注意删除掉逻辑复制的复制槽后逻辑复制的同步就废弃了,后续的恢复需要做全量的数据恢复。所以这是逻辑复制的一个大缺点。逻辑复制还有一个大缺点是主备库切换后逻辑复制槽也废掉了。如果想避免这个问题可以使用中启乘数科技的产品CMiner具体请见CMiner介绍页面。3.3 废弃的未提交两阶段事务(prepared transactions)未提交的两阶段事务(prepared transactions)会让数据库保留从这个事务开始时WAL日志导致WAL日志空间膨胀。如果应用使用了两阶段事务理论上两阶段事务的提交和回滚时需要由这个应用来提交或回滚的而如果这个应用出现的问题一直没有对其创建的两阶段事务进行提交或回滚则会产生此问题。查询两阶段事务的语句SELECT gid, prepared, owner, database, transaction AS xmin FROM pg_prepared_xacts ORDER BY age(transaction) DESC;如果发现某个两阶段事务长期存在如数个小时则可能出现了这个问题如下所示postgres# SELECT gid, prepared, owner, database, transaction AS xmin FROM pg_prepared_xacts ORDER BY age(transaction) DESC; gid | prepared | owner | database | xmin ---------------------------------------------------------------------- osdba_pxid | 2019-01-10 10:27:15.44151308 | codetest | postgres | 13843 (1 row)如果发现prepared列的时间是一个之前很久的时间基本可以断定这是一个废弃的两阶端事务。这时我们可以手工提交或回滚这个事务提交的方法commit prepared osdba_pxid;回滚的方法roback prepared osdba_pxid;注意需要调查两阶端事务产生的原因以及确定应该是提交还是回滚否则可能造出数据的丢失。3.4 主库的WAL日志的归档未成功主库不会清理未归档的WAL日志从而导致了主库的WAL日志膨胀。主库开启了归档但是归档命令一直没有执行成功或归档命令hang住也可能是归档命令执行的太慢来不及归档。检查主库的日志看看释放又归档失败的日志。也可以到pg_wal/archive_status目录下看看是否大量的WAL日志未归档成功。3.5 备库开启的HOT_STANDBY_FEEDBACK如果只读备库开启了HOT_STANDBY_FEEDBACK备库上如果有个长时间运行的查询正在执行备库会通知主库这个备库上长时间查询开始启动后的WAL日志都不能被清理掉从而导致主库的WAL日志膨胀。这种情况导致主库WAL日志膨胀出现的概率很低。有人问为什么要有HOT_STANDBY_FEEDBACK这种机制呢原因是如果没有这种机制主库执行UPDATE并VACUUM了由于主库上已经不存在使用被更新元组的事务VACUUM 会将这些元组清理掉当 备库回放到 VACUUM 对应的日志时检测到当前 VACUUM 清理的元组仍然被这个长时间的查询使用则会阻塞备库的WAL日志应用导致备库有很大的延迟。为了避免备库的延迟PostgreSQL又提供了参数max_standby_streaming_delay(默认30s)让应用WAL的进程在等待此参数指定的时间后后若长时间SQL还没有执行完则直接取消长时间SQL的运行并在日志种打印如下异常信息FATAL: terminating connection due to conflict with recovery DETAIL: User query might have needed to see row versions that must be removed. HINT: In a moment you should be able to reconnect to the database and repeat your command. server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. The connection to the server was lost. Attempting reset: Succeeded.那么这样就导致了备库上无法运行长时间的SQL。为了解决此问题备库把参数HOT_STANDBY_FEEDBACK设置为on后就将 备库种长时间运行的SQL的最小活跃事务ID定期告知主库使得主库在执行 VACUUM时对这些事务还需要的数据手下留情不进行清理。