Postgres Pro
Downloads
Home   >   mailing lists

Thread: Autovacuum and visibility maps

Autovacuum and visibility maps

From
"Tefft, Michael J"
Date:

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?

 

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

Re: Autovacuum and visibility maps

From
Adrian Klaver
Date:
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://www.postgresql.org/docs/current/routine-vacuuming.html#VACUUM-FOR-SPACE-RECOVERY

"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://www.postgresql.org/docs/current/runtime-config-autovacuum.html#GUC-AUTOVACUUM-VACUUM-INSERT-THRESHOLD

"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

Re: Autovacuum and visibility maps

From
Ron Johnson
Date:
On Tue, Dec 3, 2024 at 11:57 AM Adrian Klaver <adrian.klaver@aklaver.com> wrote:
[snip] 

I have to believe it is due to this:

https://www.postgresql.org/docs/current/routine-vacuuming.html#VACUUM-FOR-SPACE-RECOVERY

"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://www.postgresql.org/docs/current/runtime-config-autovacuum.html#GUC-AUTOVACUUM-VACUUM-INSERT-THRESHOLD

"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.

Shouldn't autovacuum_vacuum_insert_threshold kick off an autovacuum if you're doing a lot of inserts?

--
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!

RE: Autovacuum and visibility maps

From
"Tefft, Michael J"
Date:

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 <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$
 
"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$
 
"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
 

Re: Autovacuum and visibility maps

From
Ron Johnson
Date:
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 <Michael.J.Tefft@snapon.com> 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 <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$
 
"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$
 
"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
 


--
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!

Re: Autovacuum and visibility maps

From
Adrian Klaver
Date:

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