From: Jerry Sievers <gsievers19@comcast.net>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-admin@lists.postgresql.org
Subject: Re: Does raising maintenance_work_mem get seen by an already
 running aucovac?
References: <m0y1pqcxl9.fsf@comcast.net> <2845694.1674682563@sss.pgh.pa.us>
Date: Wed, 25 Jan 2023 17:28:00 -0600
In-Reply-To: <2845694.1674682563@sss.pgh.pa.us> (Tom Lane's message of "Wed,
	25 Jan 2023 16:36:03 -0500")
Message-ID: <m0r0vichz3.fsf@comcast.net>
User-Agent: Gnus/5.13 (Gnus v5.13) Emacs/28.1 (darwin)
MIME-Version: 1.0
Content-Type: text/plain
Archived-At: 
 <https://www.postgresql.org/message-id/m0r0vichz3.fsf%40comcast.net>
Precedence: bulk

Tom Lane <tgl@sss.pgh.pa.us> writes:

> Jerry Sievers <gsievers19@comcast.net> writes:
>
>> There's a long running anti-wrap autovac that we'd rather let finish but
>> on the server instance, I found maintenance_work_mem rather low and thus
>> raised it in postgresql.comf, reloaded and confirmed that my interactive
>> session saw the change.
>
>> Can a running autovac make use of this or should I either wait it out or
>> cancel and let it restart when ready?
>
> Looks like an autovac worker will notice postgresql.conf changes once
> per table:
>
>     /*
>      * Perform operations on collected tables.
>      */
>     foreach(cell, table_oids)
>     {
>         ...
>
>         /*
>          * Check for config changes before processing each collected table.
>          */
>         if (ConfigReloadPending)
>             ProcessConfigFile(PGC_SIGHUP);
>
> If it is stuck on a particularly enormous table, you might be best off
> to kill it, but otherwise I'd let it go.
>
> 			regards, tom lane

Thanks Tom!

That's the situation, the >1TB table is just chugging along.

So, a live individual vacuum of a particulart table will not start using
more mwm but later tasks will.

In fact, the existing setting which I believed too small probably does
exceed the 1G limit of some kind that's explained in the docs and ps
shows that process having about 1G of memory allocated, so perhaps the
frobbling wasn't too useful a fix.

Ack'd

I'll keep an eye on things.