Re: Autovacuum and visibility maps - Mailing list pgsql-general
From | Adrian Klaver |
---|---|
Subject | Re: Autovacuum and visibility maps |
Date | |
Msg-id | 80f60c4c-aeec-46f0-9b8e-81a5aace1ffa@aklaver.com Whole thread Raw |
In response to | RE: Autovacuum and visibility maps ("Tefft, Michael J" <Michael.J.Tefft@snapon.com>) |
List | pgsql-general |
On 12/3/24 10:11 AM, Tefft, Michael J 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. From your OP I took the following literally: "... a single insert-select". Take a look at the stat table below: https://www.postgresql.org/docs/current/monitoring-stats.html#MONITORING-PG-STAT-ALL-TABLES-VIEW pg_stat_all_tables For given table and see what the *autovacuum* fields return. You can use the function below to see if there are per table settings that are overriding the postgresql.conf settings. https://www.postgresql.org/docs/current/functions-info.html pg_options_to_table() Something like: select pg_options_to_table(reloptions) from pg_class where relname = 'some_table'; > > Mike > > *From:*Adrian Klaver <adrian.klaver@aklaver.com> > *Sent:* Tuesday, December 3, 2024 11:57 AM > *To:* Tefft, Michael J <Michael.J.Tefft@snapon.com>; > pgsql-general@lists.postgresql.org > *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-FOR-SPACE-RECOVERY__;Iw!!Lf_9VycLqA!mGufXaOdGX6PdXSpHcIUnIF1pe8evFpE7r-l4vJVUcoY--jp8LtF-jWv8YicvFWegi1-_jyxJnNx3YBvbxQOracZSxzvbw$ <https://urldefense.com/v3/__https:/www.postgresql.org/docs/current/routine-vacuuming.html*VACUUM-FOR-SPACE-RECOVERY__;Iw!!Lf_9VycLqA!mGufXaOdGX6PdXSpHcIUnIF1pe8evFpE7r-l4vJVUcoY--jp8LtF-jWv8YicvFWegi1-_jyxJnNx3YBvbxQOracZSxzvbw$> > > "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*GUC-AUTOVACUUM-VACUUM-INSERT-THRESHOLD__;Iw!!Lf_9VycLqA!mGufXaOdGX6PdXSpHcIUnIF1pe8evFpE7r-l4vJVUcoY--jp8LtF-jWv8YicvFWegi1-_jyxJnNx3YBvbxQOraeerEd0yw$ <https://urldefense.com/v3/__https:/www.postgresql.org/docs/current/runtime-config-autovacuum.html*GUC-AUTOVACUUM-VACUUM-INSERT-THRESHOLD__;Iw!!Lf_9VycLqA!mGufXaOdGX6PdXSpHcIUnIF1pe8evFpE7r-l4vJVUcoY--jp8LtF-jWv8YicvFWegi1-_jyxJnNx3YBvbxQOraeerEd0yw$> > > "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 > > adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com> > -- Adrian Klaver adrian.klaver@aklaver.com
pgsql-general by date: