Postgres, Kafka, and a mysterious 100 GB
mattritter.me
Postgres, Kafka, and a mysterious 100 GB
1–10 of 24 posts
Re: Postgres, Kafka, and a mysterious 100 GB
#2Had this exact thing happen in production when we turned off an audit DB replication slot. We got lucky and caught it before our entire app went down. It’s one of the many foot-guns we have found with Postgres.
Re: Postgres, Kafka, and a mysterious 100 GB
#3I still don't get what caused 100GB of logs. Idle events every 5 minutes should not generate that much data, so what am I missing?
Re: Postgres, Kafka, and a mysterious 100 GB
#4I still don't get what caused 100GB of logs. Idle events every 5 minutes should not generate that much data, so what am I missing?
when turning on logical replication postgres keeps all wal files until they are also applied to the downstream consumer.
if you don't use logical replication they get cleaned up by setting wal_keep_segments and max_wal_size (which have a sane value so there is nothing to bother)
btw. a good idea is to keep these wal files or at least archive them with a basebackup, because it enables PITR recovery (https://www.postgresql.org/docs/9.3/continuous-archiving.htm...) via wal-g or wal-e.
btw. the bigger mistake here is that a lot of people do not monitor their database size, which is a huge problem.
edit: ah and wal files are always 16mb (unless compiled differently)
> There will always be at least one WAL segment file, and will normally not be more than (2 + checkpoint_completion_target) * checkpoint_segments + 1 or checkpoint_segments + wal_keep_segments + 1 files. Each segment file is normally 16 MB (though this size can be altered when building the server). You can use this to estimate space requirements for WAL. Ordinarily, when old log segment files are no longer needed, they are recycled (renamed to become the next segments in the numbered sequence). If, due to a short-term peak of log output rate, there are more than 3 * checkpoint_segments + 1 segment files, the unneeded segment files will be deleted instead of recycled until the system gets back under this limit.
Re: Postgres, Kafka, and a mysterious 100 GB
#5The write activity every 5 minutes is standard Postgres checkpointing, the default value for checkpoint_timeout is 5 minutes. This is not limited to RDS.
Background processes like vacuum and analyze also write to WAL.
Re: Postgres, Kafka, and a mysterious 100 GB
#6Debezium is a useful tool, but requires a lot of babysitting. If the DB connection blips or DNS changes (say, if you just rebuilt your prod db), or in some other cases, it'll die and present this exact problem. Fortunately, it's easy to enable a "heartbeat" topic to alert on to make sure it can be restarted before the db disk fills (of course, db size growth alerts are critical too).
We've found that it's worth it for most use cases to switch to a vanilla JDBC Kafka Connector with frequent polling. This also allows for cases such as emitting joined data.
Other than Debezium, Postgres + Kafka + Kafka Connect builds a pretty stable system for sending data around all our different dbs, apps, and data lakes.
Re: Postgres, Kafka, and a mysterious 100 GB
#7I thought it was going to be a feedback loop, where the write at the end would trigger another Kafka message. I was pleasantly surprised it wasn't.
Re: Postgres, Kafka, and a mysterious 100 GB
#8I still don't get what caused 100GB of logs. Idle events every 5 minutes should not generate that much data, so what am I missing?
when turning on logical replication postgres keeps all wal files until they are also applied to the downstream consumer. if you don't use logical replication they get cleaned up by setting wal_keep_segments and max_wal_size (which have a sane value so there is nothing to bother) btw. a good idea is to keep these wal files or at least archive them with a basebackup, because it enables PITR recovery ( https://www.postg…
So this was 21 days of idle chatter? 1 WAL per 5 minute event, 16MB per WAL, 100GB/16MB = 6250 * 5m = 31,250m/1440m/day = 21ish days.
Is that what happened?
Re: Postgres, Kafka, and a mysterious 100 GB
#9Earlier quoted context omitted.
when turning on logical replication postgres keeps all wal files until they are also applied to the downstream consumer. if you don't use logical replication they get cleaned up by setting wal_keep_segments and max_wal_size (which have a sane value so there is nothing to bother) btw. a good idea is to keep these wal files or at least archive them with a basebackup, because it enables PITR recovery ( https://www.postg…
So this was 21 days of idle chatter? 1 WAL per 5 minute event, 16MB per WAL, 100GB/16MB = 6250 * 5m = 31,250m/1440m/day = 21ish days. Is that what happened?
probably. well in his timeline it was only two weeks less, but without more information (which is probably lost) it would be hard if the problem was made worse and other stuff accumulated.
but yeah wal file are probably the culprint here.
btw. most of the time it's not just aws rds, as somebody else stated a few things in postgres do happen to write data into wal files.
there is a lot more to it and depending on the config and replication it can behave a little bit differntly i.e. under normal use these files can get reused depending on min_wal_size/max_wal_size,etc... it's quite a complex thing and normally do not need to be fine tuned.
I'm also pretty sure the behavior changed in more recent versions (which probably made it worse in this scenario, but more default safety for everbody.)
Re: Postgres, Kafka, and a mysterious 100 GB
#10I still don't get what caused 100GB of logs. Idle events every 5 minutes should not generate that much data, so what am I missing?
when turning on logical replication postgres keeps all wal files until they are also applied to the downstream consumer. if you don't use logical replication they get cleaned up by setting wal_keep_segments and max_wal_size (which have a sane value so there is nothing to bother) btw. a good idea is to keep these wal files or at least archive them with a basebackup, because it enables PITR recovery ( https://www.postg…
Yes, but this system seems to have generated WAL at a very high rate for something that is a test system with no load.
It went from effectively 0 to 97 GB in less than 6 days; which is 673 MB/hr; 11MB /min, or 58 MB /checkpoint timer.
If we consider that most, if not all, of this traffic would be based on checkpoint-related full-page writes, that's still some 7000 distinct pages being modified every 5 minutes. Even when I consider pg_stat_statements recording and writing out all activities, modifying ~7000 blocks is a lot for what I can expect to be repeating and templated maintenance tasks.