Content-Type: multipart/alternative;
	boundary="------------H5EwmvAhDuPq6cxwlkgXQJin"
Message-ID: <9a3ac0ad-0f35-d0ee-652d-09bf5af1c3ba@amazon.com>
Date: Mon, 12 Sep 2022 09:59:15 +0200
MIME-Version: 1.0
User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.15; rv:102.0)
 Gecko/20100101 Thunderbird/102.2.2
Subject: Re: Add tracking of backend memory allocated to pg_stat_activity
Content-Language: en-US
To: Justin Pryzby <pryzby@telsasoft.com>, Stephen Frost <sfrost@snowman.net>
CC: Kyotaro Horiguchi <horikyota.ntt@gmail.com>,
	<reid.thompson@crunchydata.com>, <pgsql-hackers@lists.postgresql.org>
References: <67bb5c15c0489cb499723b0340f16e10c22485ec.camel@crunchydata.com>
 <20220831170555.GW31833@telsasoft.com>
 <20220901.102820.253548442929111657.horikyota.ntt@gmail.com>
 <4c6b042a-f79c-29c0-8ce6-f3b13613dcab@amazon.com>
 <20220909163415.GH26002@tamriel.snowman.net>
 <20220909170809.GO31833@telsasoft.com>
From: "Drouvot, Bertrand" <bdrouvot@amazon.com>
In-Reply-To: <20220909170809.GO31833@telsasoft.com>
Archived-At: 
 <https://www.postgresql.org/message-id/9a3ac0ad-0f35-d0ee-652d-09bf5af1c3ba%40amazon.com>
Precedence: bulk

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

Hi,

On 9/9/22 7:08 PM, Justin Pryzby wrote:
> On Fri, Sep 09, 2022 at 12:34:15PM -0400, Stephen Frost wrote:
>>> While we are at it, what do you think about also recording the max memory
>>> allocated by a backend? (could be useful and would avoid sampling for which
>>> there is no guarantee to sample the max anyway).
>> What would you do with that information..?  By itself, it doesn't strike
>> me as useful.  Perhaps it'd be interesting to grab the max required for
>> a particular query in pg_stat_statements or such but again, that's a
>> very different thing.
>
> Storing the maxrss per backend somewhere would be useful (and avoid the
> issue of "sampling" with top), after I agree that it ought to be exposed
> to a view.  For example, it might help to determine whether (and which!)
> backends are using large multiple of work_mem, and then whether that can
> be increased.  If/when we had a "memory budget allocator", this would
> help to determine how to set its GUCs, maybe to see "which backends are
> using the work_mem that are precluding this other backend from using
> efficient query plan".

+1.

Regards,

-- 
Bertrand Drouvot
PostgreSQL Contributors Team
RDS Open Source Databases
Amazon Web Services:https://aws.amazon.com

--------------H5EwmvAhDuPq6cxwlkgXQJin
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 text="#415ce2" bgcolor="#FFFFFF">
    <p>Hi,<br>
    </p>
    <div class="moz-cite-prefix">On 9/9/22 7:08 PM, Justin Pryzby wrote:<br>
    </div>
    <blockquote type="cite"
      cite="mid:20220909170809.GO31833@telsasoft.com">
      <pre class="moz-quote-pre" wrap="">
On Fri, Sep 09, 2022 at 12:34:15PM -0400, Stephen Frost wrote:
</pre>
      <blockquote type="cite">
        <blockquote type="cite">
          <pre class="moz-quote-pre" wrap="">While we are at it, what do you think about also recording the max memory
allocated by a backend? (could be useful and would avoid sampling for which
there is no guarantee to sample the max anyway).
</pre>
        </blockquote>
      </blockquote>
      <pre class="moz-quote-pre" wrap="">
</pre>
      <blockquote type="cite">
        <pre class="moz-quote-pre" wrap="">What would you do with that information..?  By itself, it doesn't strike
me as useful.  Perhaps it'd be interesting to grab the max required for
a particular query in pg_stat_statements or such but again, that's a
very different thing.
</pre>
      </blockquote>
      <pre class="moz-quote-pre" wrap="">

Storing the maxrss per backend somewhere would be useful (and avoid the
issue of "sampling" with top), after I agree that it ought to be exposed
to a view.  For example, it might help to determine whether (and which!)
backends are using large multiple of work_mem, and then whether that can
be increased.  If/when we had a "memory budget allocator", this would
help to determine how to set its GUCs, maybe to see "which backends are
using the work_mem that are precluding this other backend from using
efficient query plan".
</pre>
    </blockquote>
    <p>+1.</p>
    <p>Regards,<br>
    </p>
    <pre class="moz-signature" cols="72">-- 
Bertrand Drouvot
PostgreSQL Contributors Team
RDS Open Source Databases
Amazon Web Services: <a class="moz-txt-link-freetext" href="https://aws.amazon.com">https://aws.amazon.com</a></pre>
  </body>
</html>

--------------H5EwmvAhDuPq6cxwlkgXQJin--