Обсуждение: Does raising maintenance_work_mem get seen by an already running aucovac?

Groovy admins,

This is on Pg11 (shameful, I know !).

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?

The vacuum progress view shows that it's finished the heap and I guess
toast, but is now working through the indexes.

Thx

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

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.