public inbox for [email protected]
help / color / mirror / Atom feedRE: Autovacuum and visibility maps
2+ messages / 2 participants
[nested] [flat]
* RE: Autovacuum and visibility maps
@ 2024-12-03 18:11 Tefft, Michael J <[email protected]>
0 siblings, 1 reply; 2+ messages in thread
From: Tefft, Michael J @ 2024-12-03 18:11 UTC (permalink / raw)
To: Adrian Klaver <[email protected]>; [email protected] <[email protected]>
Thanks for the point about truncates versus deletes.
But most of these partitions have over 100k rows, all inserted at once. We have the default setting:
#autovacuum_vacuum_insert_threshold = 1000 # min number of row inserts
So I thought we should be triggering by inserts.
Mike
From: Adrian Klaver <[email protected]>
Sent: Tuesday, December 3, 2024 11:57 AM
To: Tefft, Michael J <[email protected]>; [email protected]
Subject: Re: Autovacuum and visibility maps
On 12/3/24 08: 32, Tefft, Michael J wrote: > We have some batch queries that had occasionally having degraded > runtimes: from 2 hours degrading to 16 hours, etc. > > Comparing plans from good and bad runs, we saw that the good plans
On 12/3/24 08:32, Tefft, Michael J wrote:
> We have some batch queries that had occasionally having degraded
> runtimes: from 2 hours degrading to 16 hours, etc.
>
> Comparing plans from good and bad runs, we saw that the good plans used
> index-only scans on table “x”, while the bad plans used index scans.
>
> Using the pg_visibility utility, we found that all of the 83 partitions
> of table “x” were showing zero blocks where all tuples were visible. We
> ran a VACUUM on the table; the visibility maps are now clean and the
> good plans came back.
>
> Our question is: why did autovacuum not spare us from this?
>
> We are using default autovacuum parameters for all except
> log_autovacuum_min_duration=5000. These partitions are populated by
> processes that do a truncate + a single insert-select.
>
> We see autovacuum failure (failed to get lock) messages, followed by a
> success message, in the log for one of these partitions (the biggest
> one) but even that partition showed zero blocks with all tuples visible.
>
> Are we wrong to expect autovacuum to clean up the visibility map?
I have to believe it is due to this:
https://urldefense.com/v3/__https://www.postgresql.org/docs/current/routine-vacuuming.html*VACUUM-FO...;
"If you have a table whose entire contents are deleted on a periodic
basis, consider doing it with TRUNCATE rather than using DELETE followed
by VACUUM. TRUNCATE removes the entire content of the table immediately,
without requiring a subsequent VACUUM or VACUUM FULL to reclaim the
now-unused disk space. The disadvantage is that strict MVCC semantics
are violated."
Combined with this:
https://urldefense.com/v3/__https://www.postgresql.org/docs/current/runtime-config-autovacuum.html*G...;
"autovacuum_vacuum_threshold
Specifies the minimum number of updated or deleted tuples needed to
trigger a VACUUM in any one table. ...
"
I'm going to say the TRUNCATE itself does not trigger an autovacuum. I
would suggest throwing a manual VACUUM in the table population script.
>
> postgres=# select version();
>
> version
>
> ----------------------------------------------------------------------------------------------------------
>
> PostgreSQL 14.13 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0
> 20210514 (Red Hat 8.5.0-22), 64-bit
>
> Thank you,
>
> Mike Tefft
>
--
Adrian Klaver
[email protected]<mailto:[email protected]>
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Autovacuum and visibility maps
@ 2024-12-03 18:18 Ron Johnson <[email protected]>
parent: Tefft, Michael J <[email protected]>
0 siblings, 0 replies; 2+ messages in thread
From: Ron Johnson @ 2024-12-03 18:18 UTC (permalink / raw)
To: [email protected] <[email protected]>
When in doubt, "manually" vacuum and/or analyze.
Maybe even disable autovacuum on that table before the TRUNCATE + INSERT,
do the "manual" vacuum-analyze and then re-enable autovacuum. Bonus points
for programmatically determining which partitions you're going to insert
into, so that you only manually maintain those partitions.
On Tue, Dec 3, 2024 at 1:11 PM Tefft, Michael J <[email protected]>
wrote:
> Thanks for the point about truncates versus deletes.
>
> But most of these partitions have over 100k rows, all inserted at once. We
> have the default setting:
>
> #autovacuum_vacuum_insert_threshold = 1000 # min number of row inserts
>
>
>
> So I thought we should be triggering by inserts.
>
>
>
> Mike
>
>
>
> *From:* Adrian Klaver <[email protected]>
> *Sent:* Tuesday, December 3, 2024 11:57 AM
> *To:* Tefft, Michael J <[email protected]>;
> [email protected]
> *Subject:* Re: Autovacuum and visibility maps
>
>
>
> On 12/3/24 08: 32, Tefft, Michael J wrote: > We have some batch queries
> that had occasionally having degraded > runtimes: from 2 hours degrading to
> 16 hours, etc. > > Comparing plans from good and bad runs, we saw that the
> good plans
>
> On 12/3/24 08:32, Tefft, Michael J wrote:
>
> > We have some batch queries that had occasionally having degraded
>
> > runtimes: from 2 hours degrading to 16 hours, etc.
>
> >
>
> > Comparing plans from good and bad runs, we saw that the good plans used
>
> > index-only scans on table “x”, while the bad plans used index scans.
>
> >
>
> > Using the pg_visibility utility, we found that all of the 83 partitions
>
> > of table “x” were showing zero blocks where all tuples were visible. We
>
> > ran a VACUUM on the table; the visibility maps are now clean and the
>
> > good plans came back.
>
> >
>
> > Our question is: why did autovacuum not spare us from this?
>
> >
>
> > We are using default autovacuum parameters for all except
>
> > log_autovacuum_min_duration=5000. These partitions are populated by
>
> > processes that do a truncate + a single insert-select.
>
> >
>
> > We see autovacuum failure (failed to get lock) messages, followed by a
>
> > success message, in the log for one of these partitions (the biggest
>
> > one) but even that partition showed zero blocks with all tuples visible.
>
> >
>
> > Are we wrong to expect autovacuum to clean up the visibility map?
>
>
>
> I have to believe it is due to this:
>
>
>
> https://urldefense.com/v3/__https://www.postgresql.org/docs/current/routine-vacuuming.html*VACUUM-FO... <https://urldefense.com/v3/__https:/www.postgresql.org/docs/current/routine-vacuuming.html*VACUUM-FOR...;
>
>
>
> "If you have a table whose entire contents are deleted on a periodic
>
> basis, consider doing it with TRUNCATE rather than using DELETE followed
>
> by VACUUM. TRUNCATE removes the entire content of the table immediately,
>
> without requiring a subsequent VACUUM or VACUUM FULL to reclaim the
>
> now-unused disk space. The disadvantage is that strict MVCC semantics
>
> are violated."
>
>
>
> Combined with this:
>
>
>
> https://urldefense.com/v3/__https://www.postgresql.org/docs/current/runtime-config-autovacuum.html*G... <https://urldefense.com/v3/__https:/www.postgresql.org/docs/current/runtime-config-autovacuum.html*GU...;
>
>
>
> "autovacuum_vacuum_threshold
>
>
>
> Specifies the minimum number of updated or deleted tuples needed to
>
> trigger a VACUUM in any one table. ...
>
>
>
> "
>
>
>
> I'm going to say the TRUNCATE itself does not trigger an autovacuum. I
>
> would suggest throwing a manual VACUUM in the table population script.
>
>
>
> >
>
> > postgres=# select version();
>
> >
>
> > version
>
> >
>
> > ----------------------------------------------------------------------------------------------------------
>
> >
>
> > PostgreSQL 14.13 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0
>
> > 20210514 (Red Hat 8.5.0-22), 64-bit
>
> >
>
> > Thank you,
>
> >
>
> > Mike Tefft
>
> >
>
>
>
> --
>
> Adrian Klaver
>
> [email protected]
>
>
>
>
--
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2024-12-03 18:18 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-12-03 18:11 RE: Autovacuum and visibility maps Tefft, Michael J <[email protected]>
2024-12-03 18:18 ` Ron Johnson <[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