Subject: Re: Should vacuum process config file reload more often
Content-Type: multipart/alternative;
	boundary="------------9BLD7ekdFtG7j36AoJ7DPzPZ"
Message-ID: <7121ac43-d6ae-33ca-8ddc-538b0c6ab492@amazon.com>
Date: Wed, 8 Mar 2023 11:42:31 -0600
MIME-Version: 1.0
User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.15; rv:102.0)
 Gecko/20100101 Thunderbird/102.8.0
Content-Language: en-US
To: Masahiko Sawada <sawada.mshk@gmail.com>, Melanie Plageman
	<melanieplageman@gmail.com>
CC: Pg Hackers <pgsql-hackers@postgresql.org>, Andres Freund
	<andres@anarazel.de>
References: <CAAKRu_ZngzqnEODc7LmS1NH04Kt6Y9huSjz5pp7+DXhrjDA0gw@mail.gmail.com>
 <CAD21AoC=b09pzWNyPYuti0M2DQwy57CGF9UWWb_+v=gdWeNG=A@mail.gmail.com>
 <CAAKRu_bR2VrTUskR24GkXV15SxPaCiHYD=soOuWZcT-rGDcA8Q@mail.gmail.com>
 <CAD21AoB=EC6wzjjqDXKmNEhu_BhGg0g_Gc50yh_zvXqiDC_OSg@mail.gmail.com>
From: Jim Nasby <nasbyj@amazon.com>
In-Reply-To: <CAD21AoB=EC6wzjjqDXKmNEhu_BhGg0g_Gc50yh_zvXqiDC_OSg@mail.gmail.com>
Archived-At: <https://www.postgresql.org/message-id/7121ac43-d6ae-33ca-8ddc-538b0c6ab492%40amazon.com>
Precedence: bulk

--------------9BLD7ekdFtG7j36AoJ7DPzPZ
Content-Type: text/plain; charset="UTF-8"; format=flowed
Content-Transfer-Encoding: 7bit

On 3/2/23 1:36 AM, Masahiko Sawada wrote:

>>>> For example, I guess we will need to take care of changes of
>>>> maintenance_work_mem. Currently we initialize the dead tuple space at
>>>> the beginning of lazy vacuum, but perhaps we would need to
>>>> enlarge/shrink it based on the new value?
Doesn't the dead tuple space grow as needed? Last I looked we don't 
allocate up to 1GB right off the bat.
>>> I don't think we need to do anything about that initially, just because the
>>> config can be changed in a more granular way, doesn't mean we have to react to
>>> every change for the current operation.
>> Perhaps we can mention in the docs that a change to maintenance_work_mem
>> will not take effect in the middle of vacuuming a table. But, Ithink it probably
>> isn't needed.
> Agreed.

I disagree that there's no need for this. Sure, if 
maintenance_work_memory is 10MB then it's no big deal to just abandon 
your current vacuum and start a new one, but the index vacuuming phase 
with maintenance_work_mem set to say 500MB can take quite a while. 
Forcing a user to either suck it up or throw everything in the phase 
away isn't terribly good.

Of course, if the patch that eliminates the 1GB vacuum limit gets 
committed the situation will be even worse.

While it'd be nice to also honor maintenance_work_mem getting set lower, 
I don't see any need to go through heroics to accomplish that. Simply 
recording the change and honoring it for future attempts to grow the 
memory and on future passes through the heap would be plenty.

All that said, don't let these suggestions get in the way of committing 
this. Just having the ability to tweak cost parameters would be a win.

--------------9BLD7ekdFtG7j36AoJ7DPzPZ
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: 7bit

<html>
  <head>
    <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
  </head>
  <body>
    <p>On 3/2/23 1:36 AM, Masahiko Sawada wrote:<br>
    </p>
    <blockquote type="cite"
cite="mid:CAD21AoB=EC6wzjjqDXKmNEhu_BhGg0g_Gc50yh_zvXqiDC_OSg@mail.gmail.com">
      <blockquote type="cite" style="color: #007cff;">
        <blockquote type="cite" style="color: #007cff;">
          <blockquote type="cite" style="color: #007cff;">
            <pre class="moz-quote-pre" wrap="">For example, I guess we will need to take care of changes of
maintenance_work_mem. Currently we initialize the dead tuple space at
the beginning of lazy vacuum, but perhaps we would need to
enlarge/shrink it based on the new value?</pre>
          </blockquote>
        </blockquote>
      </blockquote>
    </blockquote>
    Doesn't the dead tuple space grow as needed? Last I looked we don't
    allocate up to 1GB right off the bat.
    <blockquote type="cite"
cite="mid:CAD21AoB=EC6wzjjqDXKmNEhu_BhGg0g_Gc50yh_zvXqiDC_OSg@mail.gmail.com">
      <blockquote type="cite" style="color: #007cff;">
        <blockquote type="cite" style="color: #007cff;">
          <pre class="moz-quote-pre" wrap="">I don't think we need to do anything about that initially, just because the
config can be changed in a more granular way, doesn't mean we have to react to
every change for the current operation.
</pre>
        </blockquote>
        <pre class="moz-quote-pre" wrap="">Perhaps we can mention in the docs that a change to maintenance_work_mem
will not take effect in the middle of vacuuming a table. But, Ithink it probably
isn't needed.
</pre>
      </blockquote>
      <pre class="moz-quote-pre" wrap="">Agreed.
</pre>
    </blockquote>
    <p>I disagree that there's no need for this. Sure, if
      maintenance_work_memory is 10MB then it's no big deal to just
      abandon your current vacuum and start a new one, but the index
      vacuuming phase with maintenance_work_mem set to say 500MB can
      take quite a while. Forcing a user to either suck it up or throw
      everything in the phase away isn't terribly good.</p>
    <p>Of course, if the patch that eliminates the 1GB vacuum limit gets
      committed the situation will be even worse.</p>
    <p>While it'd be nice to also honor maintenance_work_mem getting set
      lower, I don't see any need to go through heroics to accomplish
      that. Simply recording the change and honoring it for future
      attempts to grow the memory and on future passes through the heap
      would be plenty.</p>
    <p>All that said, don't let these suggestions get in the way of
      committing this. Just having the ability to tweak cost parameters
      would be a win.<br>
    </p>
  </body>
</html>

--------------9BLD7ekdFtG7j36AoJ7DPzPZ--