MIME-Version: 1.0
From: yudhi s <learnerdatabase99@gmail.com>
Date: Thu, 26 Sep 2024 16:33:28 +0530
Message-ID: <CAEzWdqfUQuKtpqGAwf86dwkjPq2Kkeyj6Pw31GXr92YC8M2Y5g@mail.gmail.com>
Subject: Suggestion for memory parameters
To: pgsql-general <pgsql-general@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000eb7dd4062303b0c9"
Archived-At: <https://www.postgresql.org/message-id/CAEzWdqfUQuKtpqGAwf86dwkjPq2Kkeyj6Pw31GXr92YC8M2Y5g%40mail.gmail.com>
Precedence: bulk

--000000000000eb7dd4062303b0c9
Content-Type: text/plain; charset="UTF-8"

Hello All,

In a RDS postgres we are seeing some select queries when running and doing
sorting on 50 million rows(as its having order by clause in it) , the
significant portion of wait event is showing as "IO:BufFileWrite" and it
runs for ~20minutes+.

Going through the document in the link below, it states we should monitor
the "FreeLocalStorage" metric and when monitoring that, I see it showing up
to ~535GB as the max limit and when these queries run this goes down till
100GB. Note-  (it's a R7g8xl instance)

https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/apg-waits.iobuffile.html

We were thinking of bumping up the work_mem to a higher value in database
level , which is currently having size 4MB default. But we will also have
~100 sessions running at same time and majority were from other
applications which execute other single row "insert" queries and I hope
that will not need high "work_mem" . And setting it at database level will
consume 100 times that set work_mem value. So how to handle this situation?
 Or
 Is it fine to let it use "FreeLocalStorage" unless it goes till zero?

Also I am confused between the local storage (which is showing as 535GB) vs
the memory/RAM which is 256GB for this instance class with ~128TB max
storage space restriction, how are these storage different, (mainly the
535GB space which it's showing vs the 128TB storage space restriction)?
Appreciate your guidance.

select query looks something as below with no Joins but just single table
fetch:-

Select....
from <table_name>
where
order by column1, column2 LIMIT $b1 OFFSET $B2 ;

Regards
Yudhi

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

<div dir=3D"ltr">Hello All,<br><br>In a RDS postgres we are seeing some sel=
ect queries when running and doing sorting on 50 million rows(as its having=
 order by clause in it) , the significant portion of wait event is showing =
as &quot;IO:BufFileWrite&quot; and it runs for ~20minutes+. =C2=A0<br><br>G=
oing through the document in the link below, it states we should monitor th=
e &quot;FreeLocalStorage&quot; metric and when monitoring that, I see it sh=
owing up to ~535GB as the max limit and when these queries run this goes do=
wn till 100GB. Note-=C2=A0

(it&#39;s a R7g8xl instance)<br><br><a href=3D"https://docs.aws.amazon.com/=
AmazonRDS/latest/AuroraUserGuide/apg-waits.iobuffile.html">https://docs.aws=
.amazon.com/AmazonRDS/latest/AuroraUserGuide/apg-waits.iobuffile.html</a><b=
r><br>We were thinking of bumping up the work_mem to a higher value in data=
base level , which is currently having size 4MB default. But we will also h=
ave ~100 sessions running at same time and majority were from other applica=
tions which execute other single row &quot;insert&quot; queries and I hope =
that will not need high &quot;work_mem&quot; . And setting it at database l=
evel will consume 100 times that set work_mem value. So how to handle this =
situation?<br>=C2=A0Or<br>=C2=A0Is it fine to let it use &quot;FreeLocalSto=
rage&quot; unless it goes till zero?<br><br>Also I am confused between the =
local storage (which is showing as 535GB) vs the memory/RAM which is 256GB =
for this instance class with ~128TB max storage space restriction, how are =
these storage different, (mainly the 535GB space which it&#39;s showing vs =
the 128TB storage space restriction)?=C2=A0 Appreciate=C2=A0your guidance.<=
br><br>select query looks something as below with no Joins but just single =
table fetch:-<div><br>Select....<br>from &lt;table_name&gt;<br>where <br>or=
der by column1, column2 LIMIT $b1 OFFSET $B2 ;<br><div><br></div><div>Regar=
ds</div><div>Yudhi</div></div></div>

--000000000000eb7dd4062303b0c9--