MIME-Version: 1.0
References: <CAB+=1TWZNvMhVthJ2iKs_Q4qBzMw-v_oaSz7HbFE_P_qC5jMFA@mail.gmail.com>
 <3dddea5e-52ab-4075-970d-a87b0c921ae7@aklaver.com> <CAB+=1TWdRd2sBw7-vXCovH_VHLANh+aSaU-WyJ2m8tL4TkF=8g@mail.gmail.com>
 <CAKAnmm+FFS1fALtWcviwvm6=9h1NwqH20gZPSGrdXey0tproJA@mail.gmail.com>
In-Reply-To: <CAKAnmm+FFS1fALtWcviwvm6=9h1NwqH20gZPSGrdXey0tproJA@mail.gmail.com>
From: veem v <veema0000@gmail.com>
Date: Tue, 17 Sep 2024 21:24:14 +0530
Message-ID: <CAB+=1TUiwTsTovZrM9VW2i7VUeUJvTiRUQwJYwpMSH5ieuYTyA@mail.gmail.com>
Subject: Re: IO related waits
To: Greg Sabino Mullane <htamfids@gmail.com>
Cc: Adrian Klaver <adrian.klaver@aklaver.com>, Christophe Pettus <xof@thebuild.com>, 
	pgsql-general <pgsql-general@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000308ffb062252b455"
Archived-At: <https://www.postgresql.org/message-id/CAB%2B%3D1TUiwTsTovZrM9VW2i7VUeUJvTiRUQwJYwpMSH5ieuYTyA%40mail.gmail.com>
Precedence: bulk

--000000000000308ffb062252b455
Content-Type: text/plain; charset="UTF-8"

On Tue, 17 Sept 2024 at 18:43, Greg Sabino Mullane <htamfids@gmail.com>
wrote:

>
> This is a better place to optimize. Batch many rows per transaction.
> Remove unused indexes.
>
> flushing of the WAL to the disk has to happen anyway(just that it will be
>> delayed now), so can this method cause contention in the database storage
>> side if the speed in which the data gets ingested from the client is not
>> getting written to the disk , and if it can someway impact the data
>> consistency for the read queries?
>>
>
> Not quite clear what you are asking here re data consistency. The data
> will always be consistent, even if synchronous_commit is disabled. The only
> danger window is on a server crash.
>
> (Keep in mind that RDS is not Postgres, so take tuning recommendations and
> advice with a grain of salt.)
>
>
>
Thank you Greg.

Yes, our Java application was doing row by row commit and we saw that from
pg_stat_database from the column "xact_commit" which was closely the same
as the sum of tup_inserted, tup_updated, tup_deleted column. And also we
verified in pg_stats_statements the number against the "calls" column were
matching to the "rows" column for the INSERT queries, so it means also we
are inserting exactly same number of rows as the number of DB calls, so it
also suggest that we are doing row by row operations/dmls.

 And we then asked the application tema to make the inserts in batches, but
still seeing those figures in these above two views are not changing much
the number "xact_commit" is staying almost same and also even the "calls"
and the "rows" column in pg_stats_statements also staying almost same. So
does it mean that the application code is somehow still doing the same row
by row processing or we are doing something wrong in the above analysis?

And another thing we noticed , even after the data load finished , even
then the "xact_commit" was keep increasing along with "tup_fetched", so
does it mean that its doing some kind of implicit commit even for the fetch
type queries which must be "select" queries i believe? Also not sure if its
expected, but here in postgres i have seen unless we put a code within
begin and end block , it's default gets committed even we just run it on
the console , it doesn't ask for a explicit commit/rollback ,so not sure if
that is someway playing a role here.

Regards
Veem


>
>

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

<div dir=3D"ltr"><div dir=3D"ltr"><br></div><div class=3D"gmail_quote"><div=
 dir=3D"ltr" class=3D"gmail_attr">On Tue, 17 Sept 2024 at 18:43, Greg Sabin=
o Mullane &lt;<a href=3D"mailto:htamfids@gmail.com">htamfids@gmail.com</a>&=
gt; wrote:<br></div><blockquote class=3D"gmail_quote" style=3D"margin:0px 0=
px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div =
dir=3D"ltr"><div dir=3D"ltr"><br></div><div class=3D"gmail_quote"><div>This=
 is a better place to optimize. Batch many rows per transaction. Remove unu=
sed indexes.</div><div><br></div><blockquote class=3D"gmail_quote" style=3D=
"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-le=
ft:1ex"><div dir=3D"ltr"><div dir=3D"ltr">flushing of the WAL to the disk h=
as to happen anyway(just that it will be delayed now), so can this method c=
ause contention in the database=C2=A0storage side if the speed in which=C2=
=A0the data gets ingested from the client is not getting written to the=C2=
=A0disk , and if it can someway impact the data consistency for the read qu=
eries?<br></div></div></blockquote><div><br></div><div>Not quite clear what=
 you are asking here re data consistency. The data will always be consisten=
t, even if synchronous_commit is disabled. The only danger window is on a s=
erver crash.</div><div><br></div><div>(Keep in mind that RDS is not Postgre=
s, so take tuning recommendations and advice with a grain of salt.)</div><d=
iv><br></div><div><br></div></div></div></blockquote><div><br></div><div>Th=
ank you Greg.</div><div><br></div><div>Yes, our Java application was doing =
row by row commit and we saw that from pg_stat_database from the column &qu=
ot;xact_commit&quot; which was closely the same as the sum of tup_inserted,=
 tup_updated, tup_deleted column. And also we verified in pg_stats_statemen=
ts the number against the &quot;calls&quot; column were matching to the &qu=
ot;rows&quot; column for the INSERT queries, so it means also we are insert=
ing exactly same number of rows as the number of DB calls, so it also sugge=
st that we are doing=C2=A0row by row operations/dmls.</div><div><br></div><=
div>=C2=A0And we then asked the=C2=A0application tema to make the inserts i=
n batches, but still seeing those figures in these above two views are not =
changing=C2=A0much the number &quot;xact_commit&quot; is staying almost sam=
e and also even the &quot;calls&quot; and the &quot;rows&quot; column in pg=
_stats_statements also staying almost same. So does it mean that the applic=
ation code is somehow still doing the same row by row processing or we are =
doing something wrong in the above analysis?=C2=A0</div><div><br></div><div=
>And another thing we noticed , even after the data load finished , even th=
en the &quot;xact_commit&quot; was keep increasing along with &quot;tup_fet=
ched&quot;, so does it mean that its doing some kind of implicit commit eve=
n for the fetch type queries which must be &quot;select&quot; queries i bel=
ieve? Also not sure if its expected, but here in postgres i have seen unles=
s we put a code within begin and end block , it&#39;s default gets committe=
d even we just run it on the console , it doesn&#39;t ask for a explicit=C2=
=A0commit/rollback ,so not sure if that is someway playing a role here.</di=
v><div><br></div><div>Regards<br></div><div>Veem</div><div><br></div><div>=
=C2=A0</div><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0=
.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div dir=3D"l=
tr"><div class=3D"gmail_quote"><div>=C2=A0<br></div></div></div>
</blockquote></div></div>

--000000000000308ffb062252b455--