MIME-Version: 1.0
References: <CAEzWdqfUQuKtpqGAwf86dwkjPq2Kkeyj6Pw31GXr92YC8M2Y5g@mail.gmail.com>
 <A57336D3-37A0-413F-8D74-B41607359DC6@americanefficient.com>
In-Reply-To: <A57336D3-37A0-413F-8D74-B41607359DC6@americanefficient.com>
From: yudhi s <learnerdatabase99@gmail.com>
Date: Tue, 1 Oct 2024 12:21:37 +0530
Message-ID: <CAEzWdqc1eTH43ok0xuv-kTrWeEjVxXc2rcEEAcz5FeG2HBoFWw@mail.gmail.com>
Subject: Re: Suggestion for memory parameters
To: Philip Semanchuk <philip@americanefficient.com>
Cc: pgsql-general <pgsql-general@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="0000000000006e7c04062364c102"
Archived-At: <https://www.postgresql.org/message-id/CAEzWdqc1eTH43ok0xuv-kTrWeEjVxXc2rcEEAcz5FeG2HBoFWw%40mail.gmail.com>
Precedence: bulk

--0000000000006e7c04062364c102
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

On Mon, Sep 30, 2024 at 8:46=E2=80=AFPM Philip Semanchuk <
philip@americanefficient.com> wrote:

>
>
> > On Sep 26, 2024, at 7:03=E2=80=AFAM, yudhi s <learnerdatabase99@gmail.c=
om>
> wrote:
> >
> > In a RDS postgres ...
>
> >  Is it fine to let it use "FreeLocalStorage" unless it goes till zero?
>
> Hi Yudhi,
> FreeLocalStorage and some of the other things you ask about are specific
> to AWS RDS, so you might have better luck getting answers on an
> RDS-specific mailing list. We also use RDS-hosted Postgres and so I
> completely understand how Postgres and RDS are intertwined.
>
> We have had runaway queries exhaust FreeLocalStorage. It has been quite a
> while since that happened, so my memories are hazy, but I=E2=80=99m prett=
y sure
> that when we used all of FreeLocalStorage, the result was that Postgres
> restarted. It might be equivalent to using all memory and disk space on a
> standalone system. Once there=E2=80=99s no storage left, behavior is unpr=
edictable
> but we can=E2=80=99t be surprised if things crash. Usually our runaway qu=
eries got
> killed before FreeLocalStorage filled up, but not always.
>
> I second Veem=E2=80=99s suggestion to set work_mem on a per-session basis=
. Also
> note that the doc for work_mem says, =E2=80=9Cthe total memory used could=
 be many
> times the value of work_mem; it is necessary to keep this fact in mind wh=
en
> choosing the value."
>
>
> https://www.postgresql.org/docs/current/runtime-config-resource.html#GUC-=
WORK-MEM
>
>
Thank you.

When I execute the query with explain (analyze, buffers),I see the section
below in the plan having "sort method" information in three places
each showing ~75MB size, which if combined is coming <250MB. So , does that
mean it's enough to set the work_mem as ~250MB for these queries before
they start?

 But yes somehow this query is finished in a few seconds when i execute
using explain(analyze,buffers) while if i run it without using explain it
runs for ~10minutes+. My expectation was that doing (explain analyze)
should actually execute the query fully. Is my understanding correct here
and if the disk spilling stats which I am seeing is accurate enough to go
with?


Limit  (cost=3D557514.75..592517.20 rows=3D300000 width=3D1430) (actual
time=3D2269.939..2541.527 rows=3D300000 loops=3D1)
  Buffers: shared hit=3D886206, temp read=3D38263 written=3D56947
  I/O Timings: temp read=3D70.040 write=3D660.073
  ->  Gather Merge  (cost=3D557514.75..643393.02 rows=3D736048 width=3D1430=
)
(actual time=3D2269.938..2513.748 rows=3D300000 loops=3D1)
        Workers Planned: 2
        Workers Launched: 2
        Buffers: shared hit=3D886206, temp read=3D38263 written=3D56947
        I/O Timings: temp read=3D70.040 write=3D660.073
        ->  Sort  (cost=3D556514.73..557434.79 rows=3D368024 width=3D1430)
(actual time=3D2227.392..2279.389 rows=3D100135 loops=3D3)
              Sort Key: column1, column2
              Sort Method: external merge  Disk: *77352kB*
              Buffers: shared hit=3D886206, temp read=3D38263 written=3D569=
47
              I/O Timings: temp read=3D70.040 write=3D660.073
              Worker 0:  Sort Method: external merge  Disk: *75592kB*
              Worker 1:  Sort Method: external merge  Disk: *74440kB*
              ->  Parallel Append  (cost=3D0.00..64614.94 rows=3D368024
width=3D1430) (actual time=3D0.406..570.105 rows=3D299204 loops=3D3)

--0000000000006e7c04062364c102
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div dir=3D"ltr"><br></div><br><div class=3D"gmail_quote">=
<div dir=3D"ltr" class=3D"gmail_attr">On Mon, Sep 30, 2024 at 8:46=E2=80=AF=
PM Philip Semanchuk &lt;<a href=3D"mailto:philip@americanefficient.com">phi=
lip@americanefficient.com</a>&gt; wrote:<br></div><blockquote class=3D"gmai=
l_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,20=
4,204);padding-left:1ex"><br>
<br>
&gt; On Sep 26, 2024, at 7:03=E2=80=AFAM, yudhi s &lt;<a href=3D"mailto:lea=
rnerdatabase99@gmail.com" target=3D"_blank">learnerdatabase99@gmail.com</a>=
&gt; wrote:<br>
&gt; <br>
&gt; In a RDS postgres ...<br>
<br>
&gt;=C2=A0 Is it fine to let it use &quot;FreeLocalStorage&quot; unless it =
goes till zero?<br>
<br>
Hi Yudhi,<br>
FreeLocalStorage and some of the other things you ask about are specific to=
 AWS RDS, so you might have better luck getting answers on an RDS-specific =
mailing list. We also use RDS-hosted Postgres and so I completely understan=
d how Postgres and RDS are intertwined.<br>
<br>
We have had runaway queries exhaust FreeLocalStorage. It has been quite a w=
hile since that happened, so my memories are hazy, but I=E2=80=99m pretty s=
ure that when we used all of FreeLocalStorage, the result was that Postgres=
 restarted. It might be equivalent to using all memory and disk space on a =
standalone system. Once there=E2=80=99s no storage left, behavior is unpred=
ictable but we can=E2=80=99t be surprised if things crash. Usually our runa=
way queries got killed before FreeLocalStorage filled up, but not always.<b=
r>
<br>
I second Veem=E2=80=99s suggestion to set work_mem on a per-session basis. =
Also note that the doc for work_mem says, =E2=80=9Cthe total memory used co=
uld be many times the value of work_mem; it is necessary to keep this fact =
in mind when choosing the value.&quot;<br>
<br>
<a href=3D"https://www.postgresql.org/docs/current/runtime-config-resource.=
html#GUC-WORK-MEM" rel=3D"noreferrer" target=3D"_blank">https://www.postgre=
sql.org/docs/current/runtime-config-resource.html#GUC-WORK-MEM</a><br><br><=
/blockquote><div><br></div><div>Thank you.</div><div><br></div><div>When I =
execute the query with explain (analyze, buffers),I see the section below i=
n the plan having &quot;sort method&quot; information in three places each=
=C2=A0showing ~75MB size, which if combined is coming &lt;250MB. So , does =
that mean it&#39;s enough to set the work_mem as ~250MB for these queries b=
efore they start?</div><div><br></div><div>=C2=A0But yes somehow this query=
 is finished in a few seconds when i execute using explain(analyze,buffers)=
 while if i run it without using explain it runs for ~10minutes+. My expect=
ation was that doing (explain analyze) should actually execute the query fu=
lly. Is my understanding correct here and if the=C2=A0disk spilling stats w=
hich I am seeing is accurate=C2=A0enough to go with?</div><div><br></div><b=
r><font face=3D"monospace">Limit =C2=A0(cost=3D557514.75..592517.20 rows=3D=
300000 width=3D1430) (actual time=3D2269.939..2541.527 rows=3D300000 loops=
=3D1)<br>=C2=A0 Buffers: shared hit=3D886206, temp read=3D38263 written=3D5=
6947<br>=C2=A0 I/O Timings: temp read=3D70.040 write=3D660.073<br>=C2=A0 -&=
gt; =C2=A0Gather Merge =C2=A0(cost=3D557514.75..643393.02 rows=3D736048 wid=
th=3D1430) (actual time=3D2269.938..2513.748 rows=3D300000 loops=3D1)<br>=
=C2=A0 =C2=A0 =C2=A0 =C2=A0 Workers Planned: 2<br>=C2=A0 =C2=A0 =C2=A0 =C2=
=A0 Workers Launched: 2<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 Buffers: shared hit=
=3D886206, temp read=3D38263 written=3D56947<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0=
 I/O Timings: temp read=3D70.040 write=3D660.073<br>=C2=A0 =C2=A0 =C2=A0 =
=C2=A0 -&gt; =C2=A0Sort =C2=A0(cost=3D556514.73..557434.79 rows=3D368024 wi=
dth=3D1430) (actual time=3D2227.392..2279.389 rows=3D100135 loops=3D3)<br>=
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 Sort Key: column1, column2=
<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 Sort Method: external =
merge =C2=A0Disk: <b>77352kB</b><br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 Buffers: shared hit=3D886206, temp read=3D38263 written=3D56947<=
br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 I/O Timings: temp read=
=3D70.040 write=3D660.073<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 Worker 0: =C2=A0Sort Method: external merge =C2=A0Disk: <b>75592kB</b><=
br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 Worker 1: =C2=A0Sort Me=
thod: external merge =C2=A0Disk: <b>74440kB</b><br>=C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 =C2=A0 =C2=A0 -&gt; =C2=A0Parallel Append =C2=A0(cost=3D0.00..64=
614.94 rows=3D368024 width=3D1430) (actual time=3D0.406..570.105 rows=3D299=
204 loops=3D3)<br></font><br><div>=C2=A0</div></div></div>

--0000000000006e7c04062364c102--