Re: BUG #17103: WAL segments are not removed after exceeding max_slot_wal_keep_size - Mailing list pgsql-bugs
From | Kyotaro Horiguchi |
---|---|
Subject | Re: BUG #17103: WAL segments are not removed after exceeding max_slot_wal_keep_size |
Date | |
Msg-id | 20210714.111214.590009579344145004.horikyota.ntt@gmail.com Whole thread Raw |
In response to | BUG #17103: WAL segments are not removed after exceeding max_slot_wal_keep_size (PG Bug reporting form <noreply@postgresql.org>) |
Responses |
Re: BUG #17103: WAL segments are not removed after exceeding max_slot_wal_keep_size
|
List | pgsql-bugs |
At Tue, 13 Jul 2021 09:15:17 +0000, PG Bug reporting form <noreply@postgresql.org> wrote in > We are using max_slot_wal_keep_size from Postgresql 13 to prevent master > from being killed by a lagging replication. It seems, that in our case, WAL > storage wasn't freed up after exceeding this parameter which resulted in a > replication failure. WAL which, as I believe, should have been freed up did > not seem to be needed by any other transaction at a time. Yeah, the max_slot_wal_keep_size is the maximum WAL size that replication slots are guaranteed to be able to keep files up to. It is not the size that replication slot are guaranteed not to keep WAL files beyond it. Addition to that, WAL removal happens only at the ending of a checkpoint so WAL files can grow up to max_slot_wal_keep_size plus checkpoint distance assuming an even load. > -- Configuration -- > master & one replica - streaming replication using a slot > ~700GB available for pg_wal > max_slot_wal_keep_size = 600GB > min_wal_size = 20GB > max_wal_size = 40GB > default checkpoint_timeout = 5 minutes (no problem with checkpoints) > archiving is on and is catching up well Assuming an even load (or WAL speed) and 0.5 for checkpoint_completion_target, 40GB of max_wal_size causes checkpoints every 27GB (1706 segments) (*1) at longest (in the case where xlog checkpoint fires before timeout checkpoint). Thus with 600GB of max_slot_wal_keep_size, the maximum size of WAL files can reach 627GB, which size can even be exceeded if a sudden high-load is given. [1] checkpoint distance = max_wal_size / (1.0 + checkpoint_completion_target) > -- What happened -- > Under heavy load (large COPY/INSERT transactions, loading hundreds of GB of > data), the replication started falling behind. Available space on pg_wal was > being reduced in the same rate as safe_slot > pg_replication_slot.safe_wal_size - as expected. At some point safe_wal_size > went negative and streaming stopped working. It wasn't a problem, because > replica started recovery from WAL archive. I expected that once the slot is > lost, WALs will be removed up to max_wal_size. This did not happen though. > It seems that Postgres tried to maintain something close to > max_slot_wal_keep_size (600GB) available, in case replica starts catching up > again. Over the time, there was no single transaction which would require > this much WAL to be kept. archiving wasn't behind either. Useless WAL files will be removd after a checkpoint runs. > Amount of free space on pg_wal was more or less 70GB for most of the time, > however at some point, during heavy autovacuuming, it dipped to 0 :( This is > when PG crashed and (auto-recovered soon after). After getting back up, > there was 11GB left on pg_wal and no transaction running, no loading. This > lasted for hours. During this time replica finally caught up from the > archive and restored the replication with no delay. None of the WALs were > removed. I manually run checkpoint but it did not clear any WALs. I finally > restarted Postgresql and during the restarting pg_wal were finally > cleared. > > Again - why PG did not clear WAL? WALs, even more clearly, were not needed > by any process. Maybe manual CHECKPINT work for you , however, you should reconsider the setting assuming the above behavior to prevent a crash due to WAL storage exhaustion. regards. -- Kyotaro Horiguchi NTT Open Source Software Center
pgsql-bugs by date: