public inbox for [email protected]
help / color / mirror / Atom feedAggressive vacuum
3+ messages / 3 participants
[nested] [flat]
* Aggressive vacuum
@ 2024-12-19 13:37 Graham Hay <[email protected]>
0 siblings, 2 replies; 3+ messages in thread
From: Graham Hay @ 2024-12-19 13:37 UTC (permalink / raw)
To: [email protected]
Can anyone enlighten me on exactly what triggers an "aggressive"
vacuum? I have read the docs multiple times (and watched several
videos!), but the changes I make do not have the expected outcomes; so
I'm clearly missing something.
https://www.postgresql.org/docs/15/routine-vacuuming.html#VACUUM-FOR-WRAPAROUND
I have a fairly large table, that has outgrown the vacuum defaults. We
started by setting:
- autovacuum_vacuum_scale_factor=0
- autovacuum_vacuum_threshold=100000 # 100K
(10% was a lot of tuples). This was definitely an improvement, but
when the big vacuum (freeze) kicked off there was a noticeable
io/latency spike. We then tried setting:
- autovacuum_freeze_table_age=100000000 # 100M
which seemed to make it happen more frequently (and one would hope,
with less work to do). We reduced this to 50M, and again it was more
frequent (but not the 2x I expected). And when I dropped it to 10M,
nothing changed.
After re-reading the docs, I got the impression I also needed to set:
- autovacuum_freeze_min_age=10000000 # 10M
As the system default was 50M. And again, this had an effect, but not
the 5x I was expecting. The docs say:
> all-visible but not all-frozen pages are scanned if the number of transactions that have passed since the last such scan is greater than vacuum_freeze_table_age minus vacuum_freeze_min_age
but wouldn't that have been 10M - 50M? i.e. -40M. Is there some other
setting I'm missing?
All suggestions welcome!
Thanks,
Graham
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Aggressive vacuum
@ 2025-01-06 23:18 Slava Mudry <[email protected]>
parent: Graham Hay <[email protected]>
1 sibling, 0 replies; 3+ messages in thread
From: Slava Mudry @ 2025-01-06 23:18 UTC (permalink / raw)
To: Graham Hay <[email protected]>; +Cc: [email protected]
Hello Graham,
I had to deal with "vacuum to prevent wraparound" a few times and it's not
fun :(
Postgres doc explains it very well.. if you see that nasty thing running on
your db, your best options are:
1. manually vacuum freeze the table
2. examine vacuum costs and lower them to make sure the autovacuum can do
it's job. Allow more vacuum processes by tuning *autovacuum_max_workers*
3. tune and set autovacuum settings for your table to make sure autovacuum
picks it up sooner than defaults allow it so.
Depending on your usage, there are some other good optimizations you can
try.. The best in my opinion is to partition the large table and isolate
old rows from frequently updated rows and manually vacuum freeze rows that
you know wan't change. Also newer postgres versions have improved the
autovacuuming, if you're on ver 15 (based on your link to docs), you should
consider upgrading to more recent version.
Good luck,
-Slava
On Thu, Dec 19, 2024 at 5:37 AM Graham Hay <[email protected]> wrote:
> Can anyone enlighten me on exactly what triggers an "aggressive"
> vacuum? I have read the docs multiple times (and watched several
> videos!), but the changes I make do not have the expected outcomes; so
> I'm clearly missing something.
>
>
> https://www.postgresql.org/docs/15/routine-vacuuming.html#VACUUM-FOR-WRAPAROUND
>
> I have a fairly large table, that has outgrown the vacuum defaults. We
> started by setting:
>
> - autovacuum_vacuum_scale_factor=0
> - autovacuum_vacuum_threshold=100000 # 100K
>
> (10% was a lot of tuples). This was definitely an improvement, but
> when the big vacuum (freeze) kicked off there was a noticeable
> io/latency spike. We then tried setting:
>
> - autovacuum_freeze_table_age=100000000 # 100M
>
> which seemed to make it happen more frequently (and one would hope,
> with less work to do). We reduced this to 50M, and again it was more
> frequent (but not the 2x I expected). And when I dropped it to 10M,
> nothing changed.
>
> After re-reading the docs, I got the impression I also needed to set:
>
> - autovacuum_freeze_min_age=10000000 # 10M
>
> As the system default was 50M. And again, this had an effect, but not
> the 5x I was expecting. The docs say:
>
> > all-visible but not all-frozen pages are scanned if the number of
> transactions that have passed since the last such scan is greater than
> vacuum_freeze_table_age minus vacuum_freeze_min_age
>
> but wouldn't that have been 10M - 50M? i.e. -40M. Is there some other
> setting I'm missing?
>
> All suggestions welcome!
>
> Thanks,
>
> Graham
>
>
>
--
-slava
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Aggressive vacuum
@ 2025-01-07 19:30 Jeff Janes <[email protected]>
parent: Graham Hay <[email protected]>
1 sibling, 0 replies; 3+ messages in thread
From: Jeff Janes @ 2025-01-07 19:30 UTC (permalink / raw)
To: Graham Hay <[email protected]>; +Cc: [email protected]
On Thu, Dec 19, 2024 at 8:37 AM Graham Hay <[email protected]> wrote:
>
>
>
> https://www.postgresql.org/docs/15/routine-vacuuming.html#VACUUM-FOR-WRAPAROUND
>
>
This link is to v15 docs. Is that the version you are actually using? The
behavior of this feature has changed (repeatedly) over the years, so this
is important to know.
> This was definitely an improvement, but
> when the big vacuum (freeze) kicked off there was a noticeable
> io/latency spike.
Was the change noticeable because it caused degradation in the user
experience (latency of things users actually care about) or because you
have monitoring tools which detected a change even though no one was
complaining? Can you tell if the cause is driven by sequential reads,
random reads, or writes?
The way to suppress IO spikes is generally to tweak the vacuum_cost_*
and/or auto_vacuum_vacuum_cost_* settings. This will make the vacuums take
longer but be less intensive.
> We then tried setting:
>
> - autovacuum_freeze_table_age=100000000 # 100M
>
> which seemed to make it happen more frequently (and one would hope,
> with less work to do). We reduced this to 50M, and again it was more
> frequent (but not the 2x I expected). And when I dropped it to 10M,
> nothing changed.
>
Please be more quantitative. It wasn't 2x, but then what was it? And how
long did each one take?
Freezing automatically more often is likely not the answer to IO spikes.
Going from gumming up your system once a week for one hour to gumming it up
3 times a week for 20 minutes each is likely not a real solution. And it
might not even do that--you could gum it up 3 times a week for 50 minutes
each! Unless you have large swaths of table which become effectively
read-only over time, each aggressive vacuum might need to do the same
amount of IO so doing it more often just makes things worse.
The solution would be to either use the *vacuum_cost_* parameters to
throttle it down to the point where it doesn't cause problems, or
intentionally schedule vacuum freeze during quiet periods (over night, over
weekends) preempting the need for them to happen automatically.
Cheers,
Jeff
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2025-01-07 19:30 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-12-19 13:37 Aggressive vacuum Graham Hay <[email protected]>
2025-01-06 23:18 ` Slava Mudry <[email protected]>
2025-01-07 19:30 ` Jeff Janes <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox