public inbox for [email protected]
help / color / mirror / Atom feedFrom: Adrian Klaver <[email protected]>
To: Tefft, Michael J <[email protected]>
To: [email protected] <[email protected]>
Subject: Re: Autovacuum and visibility maps
Date: Tue, 3 Dec 2024 11:23:22 -0800
Message-ID: <[email protected]> (raw)
In-Reply-To: <BN8PR04MB62890EE42B888F3A0B455F76D0362@BN8PR04MB6289.namprd04.prod.outlook.com>
References: <BN8PR04MB6289F7099F7B38E5B08D85B7D0362@BN8PR04MB6289.namprd04.prod.outlook.com>
<[email protected]>
<BN8PR04MB62890EE42B888F3A0B455F76D0362@BN8PR04MB6289.namprd04.prod.outlook.com>
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 <[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] <mailto:[email protected]>
>
--
Adrian Klaver
[email protected]
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected]
Subject: Re: Autovacuum and visibility maps
In-Reply-To: <[email protected]>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox