public inbox for [email protected]
help / color / mirror / Atom feedFrom: Lincoln Swaine-Moore <[email protected]>
To: Jeff Janes <[email protected]>
Cc: Andrei Lepikhov <[email protected]>
Cc: [email protected]
Subject: Re: Unfortunate Nested Loop + Missing Autovacuum
Date: Mon, 24 Feb 2025 18:17:08 -0800
Message-ID: <CABcidk+WEnWyAQW0V_NOzH790KwdKom6JoEmVD=Sg0Z_MVfJ7g@mail.gmail.com> (raw)
In-Reply-To: <CAMkU=1womOrr1Z5obLdot4wQBhzWu_ZvfEiqen0jY93O1F6jPA@mail.gmail.com>
References: <CABcidkJT9FLu4hDT5VRKD+KGu0rLkJqhyqtAA42-P6QoVfh+kA@mail.gmail.com>
<[email protected]>
<CABcidkKZWCE7oxHbxLQyERLQsktLoeWO1BORYe8U4tocNZN9gA@mail.gmail.com>
<CAMkU=1womOrr1Z5obLdot4wQBhzWu_ZvfEiqen0jY93O1F6jPA@mail.gmail.com>
Thanks Jeff for the response--I did end up just analyzing the tables
manually, as a stopgap. Resource consumption was a non-issue as you
predicted (and plan was corrected, though estimates were still slightly
awkward).
With respect to the blocking of the autovacuum/analyze: no it shouldn't be
the case that those are running frequently in our case. Ditto re: database
restarts--this is out of my control because it would be DigitalOcean's
doing, but I don't see any evidence of it. Nor anything amiss in \dt+,
unfortunately.
I'll try to figure out if I can get access to the logs to search for
cancellations. Do you happen to know what that would look like if I'm
grep-ing for it? And do you have any other guesses about possible
explanations?
Thanks again for your help.
Lincoln
On Sun, Feb 23, 2025 at 6:09 PM Jeff Janes <[email protected]> wrote:
> On Sun, Feb 23, 2025 at 5:49 PM Lincoln Swaine-Moore <
> [email protected]> wrote:
>
>> Thanks for the reply! I tried the analysis on our much shorter staging
>> table and it did change the plan. I haven’t tried it on the production ones
>> because my understanding is that the autovacuum process is gentler with
>> resource consumption and I didn’t want to gum things up in the meantime.
>> But that may be false or avoidable.
>>
>
> The default setting of vacuum_cost_delay is zero, while the default
> setting of autovacuum_vacuum_cost_delay is 2ms (or 20ms, depending on the
> software version). So you can give a manual run the same resource
> consumption as an autorun just by changing vacuum_cost_delay in that
> session so that it has the same value as autovacuum_vacuum_cost_delay.
> Also, if you just do ANALYZE (rather than VACUUM ANALYZE) the resource
> usage should be rather modest anyway.
>
> Another difference is that autovac will cancel itself if it detects it is
> blocking something else, while a manual vac/analyze operation will not
> do that. Normal operations (DML) don't block against vacuum anyway, only
> things like index creation or partition maintenance do that. But if those
> types of operation are frequent, then doing a manual VACUUM or ANALYZE
> could indeed gum things up. Also, if those operations are frequent, it
> could explain the missing autovac. If every auto attempt gets cancelled
> before it completes, then it will never complete. pg_stat_user_tables
> doesn't reflect cancelled vacuum or analyze so those will go missing. (You
> should see mentions of cancelled autovac operations in the log file though.)
>
> Database restarts will also interrupt vacuums. So if your database is
> shutdown and restarted regularly (for cold back-ups, or just out of some
> misguided belief that restarting occasionally is a good practise) and the
> period between restarts is shorter than how long it would take autovac to
> run, this could also explain the lack of completed autovacs. Also, if a
> table qualifies for both auto vacuum and auto analyze, the vacuum is done
> first. So even if auto analyze would be fast by itself, it still won't
> complete if auto vacuum is slow and never gets to finish.
>
> It is possible to override your vacuum settings on a per-table basis. So
> another possible explanation for the missing autovacs is that those two
> tables have been specifically configured to disable autovacuum on them and
> only them. If you use psql, \dt+ (but not regular \dt) will show such
> customizations. I'm sure other tools also have ways to detect this, but I
> don't know what those ways are off the top of my head.
>
> Cheers,
>
> Jeff
>
--
Lincoln Swaine-Moore
view thread (6+ messages)
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], [email protected]
Subject: Re: Unfortunate Nested Loop + Missing Autovacuum
In-Reply-To: <CABcidk+WEnWyAQW0V_NOzH790KwdKom6JoEmVD=Sg0Z_MVfJ7g@mail.gmail.com>
* 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