3 ms·

This sounds promising! Transaction wraparound autovacuum is a particularly nasty pathology because it is so devastating and strikes with so little warning: a s

This sounds promising! Transaction wraparound autovacuum is a particularly nasty pathology because it is so devastating and strikes with so little warning: a system transitions from entirely fine to one that is entirely (and mysteriously!) non-functional in a single transaction. And because the system needs to be up for a pretty long time before you see this (it is tautologically rare), many only learn about this failure mode the hard way. Certainly, we at Joyent got completely nailed by this -- and if you want to get a sense of how disorienting this failure mode is when you're seeing it for the first time, read our postmortem of the service outage that transaction wraparound autovacuum induced.[1] A long way of saying: this is a nasty failure mode, and it's terrific to see the PostgreSQL team tackle it. While there are still some questions (e.g., this sounds like the autovacuum is still kicked but that is at once more aggressive and more intelligent, but does that mean that an exclusive lock on the table is still blocked for the duration of the operation?), this is clearly a big leap in the right direction, and (more) validation that we've made the right choice for our transactional data; thank you PostgreSQL team! [1] https://www.joyent.com/blog/manta-postmortem-7-27-2015 https://www.joyent.com/blog/manta-postmortem-7-27-2015

draw_down 11y ago
Thanks very much for sharing that, interesting reading!
dice 11y ago
>Since these tunables are not dynamically adjustable, we manually patched the running process to avoid sleeping in this case. Holy crap. You wrote a jump into an active process' memory space?
- bcantrill 11y ago
  While I have hot-patched program text on production machines many times in my career, in this case it was a bit simpler in that we were dynamically changing the value of a variable. The effect was instant; our chat logs from the time captured our visceral reaction to the resulting performance improvement.[1] [1] https://twitter.com/bcantrill/status/628320729898045440 https://twitter.com/bcantrill/status/628320729898045440
pilif 11y ago
> strikes with so little warning: a system transitions from entirely fine to one that is entirely (and mysteriously!) non-functional in a single transaction that's half true. After vacuum_freeze_table_age postgres will automatically start freeze-only autovacuum processes with very low i/o priority. Unless you have tweaked that value, it's set to 150M which is way below the 2^32 hard failure point. From then on, postgres will also start warning you about this in the log file. If you ignore this, once you're at 2^32-10M transactions, every single query to the database will yield a WARNING including a nice countdown that will count down how many transaction can still be processed before armageddon. Only if you also ignore these, you will eventually run into the problem. So while I agree that it's a very real problem, I really don't agree that it's a problem which strikes with "little warning". Postgres is very much yelling at you as it starts panicking.

draw_down 11y ago

Thanks very much for sharing that, interesting reading!

dice 11y ago

>Since these tunables are not dynamically adjustable, we manually patched the running process to avoid sleeping in this case. Holy crap. You wrote a jump into an active process' memory space?

bcantrill 11y ago
While I have hot-patched program text on production machines many times in my career, in this case it was a bit simpler in that we were dynamically changing the value of a variable. The effect was instant; our chat logs from the time captured our visceral reaction to the resulting performance improvement.[1] [1] https://twitter.com/bcantrill/status/628320729898045440 https://twitter.com/bcantrill/status/628320729898045440

pilif 11y ago

> strikes with so little warning: a system transitions from entirely fine to one that is entirely (and mysteriously!) non-functional in a single transaction that's half true. After vacuum_freeze_table_age postgres will automatically start freeze-only autovacuum processes with very low i/o priority. Unless you have tweaked that value, it's set to 150M which is way below the 2^32 hard failure point. From then on, postgres will also start warning you about this in the log file. If you ignore this, once you're at 2^32-10M transactions, every single query to the database will yield a WARNING including a nice countdown that will count down how many transaction can still be processed before armageddon. Only if you also ignore these, you will eventually run into the problem. So while I agree that it's a very real problem, I really don't agree that it's a problem which strikes with "little warning". Postgres is very much yelling at you as it starts panicking.