Thread: autovacuum_freeze_max_age
Hi, I was wondering why autovacuum_freeze_max_age actually can't be set at runtime... guc.c points to varsup.c which says: * Note: autovacuum_freeze_max_age is a PGC_POSTMASTER parameter so that * we don't have to worry about dealing with on-the-fly changes in its * value. It doesn'tlook practical to update shared state from a GUC * assign hook (too many processes would try to execute thehook, * resulting in race conditions as well as crashes of those not * connected to shared memory). Perhapsthis can be improved someday. I'm not sure I understand what would need to be updated other than the variable itself? Obviously it would mean that it would only take effect the next time SetTransactionIdLimit is called, i.e. the next time VACUUM is run, but I guess this is always better than having to restart the database? Any more explicit hints? Thanks, Jacques.
Jacques Caron <jc@oxado.com> writes: > I was wondering why autovacuum_freeze_max_age actually can't be set > at runtime... guc.c points to varsup.c which says: Hmm. The original worry was that different uses might be out of sync, but right offhand it doesn't look like anything catastrophic would result from that. At least not with the current code; it's unclear whether it'd stay safe in the face of changes. > I'm not sure I understand what would need to be updated other than > the variable itself? Obviously it would mean that it would only take > effect the next time SetTransactionIdLimit is called, i.e. the next > time VACUUM is run, but I guess this is always better than having to > restart the database? Uh, no, it's called only when we've advanced datfrozenxid, which is a pretty uncommon event. So I think the real hole in the proposal is that there would be a long and not-very-predictable delay between changing the parameter and having it really take effect. If there were a big use-case for changing this setting on the fly then I'd be more worried about it, but I don't see one really ... regards, tom lane
Hi Tom, At 02:51 10/11/2007, Tom Lane wrote: >Uh, no, it's called only when we've advanced datfrozenxid, >which is a pretty uncommon event. So I think the real hole in the >proposal is that there would be a long and not-very-predictable delay >between changing the parameter and having it really take effect. Well, I believe in a situation where you need to change its value, it would happen quite "soon". >If there were a big use-case for changing this setting on the fly then >I'd be more worried about it, but I don't see one really ... Just found out that the default value had one less "0" than I thought and that it was the reason autovacuum was vacuuming 20 GB+ insert-only tables every 3 days or so on a box of ours... And that it needs an outage (or a switchover to the slave, actually) to change the value, when it does not seem obvious why. It's really just a matter of being able to change any setting that can be at runtime rather than having to restart. A few of them can be pretty annoying (e.g. max_connections and such), but are understandable given what's required to make it possible to change them, but if there's no real reason... :-) Jacques.