Обсуждение: autovacuum_freeze_max_age on append-only tables
Hi All, I'm attempting to mimic a new feature in version 13 where INSERTS will trigger vacuum for an append-only table. I'm using v11 and configuring autovacuum_freeze_max_age to a value representing some number of minutes worth of inserts ona table containing the current day events. I'm looking to understand the details of how the vacuum operates and what toexpect and plan for. I first ran into an issue when a script attempted to alter the table to change the value of autovacuum_freeze_max_agewhile a vacuum was running. I know there is a lock conflict while the vacuum is running but I wasunder the impression that autovacuum_vacuum_cost_limit would limit the time blocked. The ALTER hung much longer than Iexpected. I'm apparently needing an education on how this "to avoid wraparound" vacuum differs from any other. I've seen it referencedas "more aggressive" but I'd like details. An upgrade to 13 is "right around the corner". Pointers to documentation I might have missed is be appreciated. -Senor
On Wed, Apr 20, 2022 at 4:06 PM senor <frio_cervesa@hotmail.com> wrote: > I'm attempting to mimic a new feature in version 13 where INSERTS will trigger vacuum for an append-only table. The problem with that idea is that you need to express the idea that the table needs to be vacuumed now in terms of its "age", denominated in XIDs -- but XIDs consumed by the entire system, not just those XIDs that happen to modify your append-only table. It will likely be very hard for you to figure out a way to relate these logical units (XIDs) to some kind of physical cost that captures how far behind you are on freezing (like blocks, or even tuples). Maybe you'll find something that works through trial and error, but I wouldn't count on it. > I'm apparently needing an education on how this "to avoid wraparound" vacuum differs from any other. I've seen it referencedas "more aggressive" but I'd like details. An upgrade to 13 is "right around the corner". It's complicated -- more complicated than it really should be. Technically an anti-wraparound autovacuum and an aggressive vacuum are two different things. In practice anti-wraparound autovacuums are virtually guaranteed to be aggressive, though an aggressive autovacuum may not be an antiwraparound VACUUM (sometimes we do aggressive vacuuming because autovacuum launched a worker before age(relfrozenxid) reached autovacuum_freeze_max_age, but after age(relfrozenxid) reached vacuum_freeze_table_age). See my recent response to a similar question here: https://postgr.es/m/CAH2-WzkFQ-okvVXizpy4dCEVq75N-Qykh=crhZaO-eaJfLVOPQ@mail.gmail.com -- Peter Geoghegan
On Wed, 2022-04-20 at 23:06 +0000, senor wrote: > I'm apparently needing an education on how this "to avoid wraparound" vacuum differs from > any other. I've seen it referenced as "more aggressive" but I'd like details. The difference is twofold, as far as I know: - it will not skip any pages just because it happens not to get a lock on them - it will refuse to die if the lock it holds on the table conflicts with a user lock Unless you are in the habit of taking strong locks on the table, you shouldn't notice a difference. Anti-wraparound VACUUM is a routine activity and does not interfere with DML, just like a normal VACUUM. Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
Thank you both Laurenz and Peter. Laurenz - It was an article you posted a couple years ago introducing the V13 feature that got me thinking about the insert-only situation I had. Peter - I had been improperly holding anti-wraparound and aggressive in my mind as related in a way they are not. You cleared that up. 2 last questions (maybe): Are the autovacuum_vacuum_cost_* settings handled any differently for 'to avoid wraparound' vacuums? I understand that it won't give up a lock but I was expecting it to still back off due to cost and allow the query with conflicting lock to proceed. Is there any benefit to manually running a vacuum every so many inserts as opposed to using autovacuum_freeze_max_age. And in this case should it be a vacuum freeze. Rows are never updated or deleted except for the occasional roll back due to dropped network connections. Thanks again -Senor On 4/21/2022 6:35, Laurenz Albe wrote: > On Wed, 2022-04-20 at 23:06 +0000, senor wrote: >> I'm apparently needing an education on how this "to avoid wraparound" vacuum differs from >> any other. I've seen it referenced as "more aggressive" but I'd like details. > The difference is twofold, as far as I know: > > - it will not skip any pages just because it happens not to get a lock on them > - it will refuse to die if the lock it holds on the table conflicts with a user lock > > Unless you are in the habit of taking strong locks on the table, you shouldn't > notice a difference. Anti-wraparound VACUUM is a routine activity and does not > interfere with DML, just like a normal VACUUM. > > Yours, > Laurenz Albe
On Thu, Apr 21, 2022 at 8:14 PM Senor <frio_cervesa@hotmail.com> wrote: > Are the autovacuum_vacuum_cost_* settings handled any differently for > 'to avoid wraparound' vacuums? I understand that it won't give up a lock > but I was expecting it to still back off due to cost and allow the query > with conflicting lock to proceed. In general, no. For the most part an antiwraparound autovacuum does exactly the same work as any other autovacuum. Or any other aggressive VACUUM, at least. But even the extra work that it does over what non-aggressive VACUUM is still work that any VACUUM might do, if the circumstances were right. We still freeze in regular VACUUMs, provided we scan pages with XIDs that are sufficiently old. The most important difference between it and aggressive VACUUM is that the former can skip all-visible pages that have unfrozen XIDs, putting that work off. This can sometimes lead to a big balloon payment later on, when you finally have an aggressive VACUUM. I think that that's a design flaw that ought to be fixed. Currently non-aggressive VACUUMs always skip all-visible pages. They should probably freeze some older all-visible pages eagerly, rather than skipping them, so that the system never gets too far behind on freezing. > Is there any benefit to manually running a vacuum every so many inserts > as opposed to using autovacuum_freeze_max_age. And in this case should > it be a vacuum freeze. Given your restrictions, this is probably the best option available. But maybe you should just set vacuum_freeze_min_age to 0 at the table level, instead of using vacuum freeze (so you freeze more without doing aggressive vacuuming all the time, which FREEZE also forces). Users understandably think that there are several different flavors of vacuum, but that's not really true (apart from VACUUM FULL, which really is quite different). The difference between aggressive and non-aggressive can be big in practice due to an accumulation of unfrozen pages over multiple non-aggressive vacuums. -- Peter Geoghegan
On Thu, Apr 21, 2022 at 8:15 PM Senor <frio_cervesa@hotmail.com> wrote:
Are the autovacuum_vacuum_cost_* settings handled any differently for
'to avoid wraparound' vacuums?
I understand that it won't give up a lock
but I was expecting it to still back off due to cost and allow the query
with conflicting lock to proceed.
IIUC "conflicting lock to proceed" is just a different way to say "give up a lock".
In any case the cost-based stuff throttles I/O only (per the docs at least) but even while sleeping it still holds its lock. And it won't be kicked off of the lock by other processes. I don't see where it is documented that the autovacuum cost settings are altered during the anti-wraparound vacuum so I presume it will still sleep by default.
Is there any benefit to manually running a vacuum every so many inserts
as opposed to using autovacuum_freeze_max_age. And in this case should
it be a vacuum freeze. Rows are never updated or deleted except for the
occasional roll back due to dropped network connections.
You might consider creating a security definer function (that performs vacuum freeze on the table) owned by the table owner and grant your inserting process the ability to execute it.
David J.
Thanks David
In any case the cost-based stuff throttles I/O only (per the docs at least) but even while sleeping it still holds its lock. And it won't be kicked off of the lock by other processes. I don't see where it is documented that the autovacuum cost settings are altered during the anti-wraparound vacuum so I presume it will still sleep by default.
I knew I was misunderstanding something. I had picked up the impression that the vacuum process cost_delay released the lock for the period. Not just do nothing. Seems like that would be worth mentioning in the Docs. I'm learning this from the inside out in the tradition of "well someone has to do it". I'm sure I'm not alone.
-Senor