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>
 <225d1bc1-5117-4c72-85a1-bac6355fb659@aklaver.com>
In-Reply-To: <225d1bc1-5117-4c72-85a1-bac6355fb659@aklaver.com>
From: veem v <veema0000@gmail.com>
Date: Wed, 18 Sep 2024 01:04:59 +0530
Message-ID: <CAB+=1TV-b8=RGKVFN0qj5Nxdgbj-JYCNh4ZqFjxxmQPpFD6=PQ@mail.gmail.com>
Subject: Re: IO related waits
To: Adrian Klaver <adrian.klaver@aklaver.com>, Greg Sabino Mullane <htamfids@gmail.com>
Cc: Christophe Pettus <xof@thebuild.com>, pgsql-general <pgsql-general@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000b4f572062255c9dc"
Archived-At: <https://www.postgresql.org/message-id/CAB%2B%3D1TV-b8%3DRGKVFN0qj5Nxdgbj-JYCNh4ZqFjxxmQPpFD6%3DPQ%40mail.gmail.com>
Precedence: bulk

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

On Tue, 17 Sept 2024 at 21:24, Adrian Klaver <adrian.klaver@aklaver.com>
wrote:

>
> Which means you need to on Flink end:
>
> 1) Use Flink async I/O .
>
> 2) Find a client that supports async or fake it by using multiple
> synchronous clients.
>
> On Postgres end there is this:
>
> https://www.postgresql.org/docs/current/wal-async-commit.html
>
> That will return a success signal to the client quicker if
> synchronous_commit is set to off. Though the point of the Flink async
> I/O is not to wait for the response before moving on, so I am not sure
> how much synchronous_commit = off would help.
>
>
 Got it. So it means their suggestion was to set the asynch_io at flink
level but not DB level, so that the application will not wait for the
commit response from the database. But in that case , won't it overload the
DB with more and more requests if database will keep doing the commit (
with synchronous_commit=ON)  and waiting for getting the response back from
its storage for the WAL's to be flushed to the disk, while the application
will not wait for its response back(for those inserts) and keep flooding
the database with more and more incoming Insert requests?

Additionally as I mentioned before, we see that from "pg_stat_database"
from the column "xact_commit" , it's almost matching with the sum of
"tup_inserted", "tup_updated", "tup_deleted" column. And also we verified
in pg_stats_statements the  "calls" column is same as 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 doesn't it suggest that we are
doing row by row operations/dmls.

Also after seeing above and asking application team to do the batch commit
,we are still seeing the similar figures from pg_stat_database and
pg_stat_statements, so does it mean that we are looking into wrong stats?
or the application code change has not been done accurately? and we see
even when no inserts are running from the application side, we do see
"xact_commit" keep increasing along with "tup_fetched" , why so?

Finally we see in postgres here, even if we just write a DML statement it
does commit that by default, until we explicitly put it in a "begin... end"
block. Can that be the difference between how a "commit" gets handled in
postgres vs other databases?

--000000000000b4f572062255c9dc
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 21:24, Adrian Kla=
ver &lt;<a href=3D"mailto:adrian.klaver@aklaver.com">adrian.klaver@aklaver.=
com</a>&gt; wrote:<br></div><blockquote class=3D"gmail_quote" style=3D"marg=
in:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1e=
x"><br>
Which means you need to on Flink end:<br>
<br>
1) Use Flink async I/O .<br>
<br>
2) Find a client that supports async or fake it by using multiple <br>
synchronous clients.<br>
<br>
On Postgres end there is this:<br>
<br>
<a href=3D"https://www.postgresql.org/docs/current/wal-async-commit.html" r=
el=3D"noreferrer" target=3D"_blank">https://www.postgresql.org/docs/current=
/wal-async-commit.html</a><br>
<br>
That will return a success signal to the client quicker if <br>
synchronous_commit is set to off. Though the point of the Flink async <br>
I/O is not to wait for the response before moving on, so I am not sure <br>
how much synchronous_commit =3D off would help.<br><br></blockquote><div><b=
r></div><div>=C2=A0Got it. So it means their suggestion was to set the asyn=
ch_io at flink level but not DB level, so that the application will not wai=
t for the commit response from the database. But in that case , won&#39;t i=
t overload the DB with more and more requests if database will keep doing t=
he commit ( with synchronous_commit=3DON)=C2=A0 and waiting for getting the=
 response back from its storage for the WAL&#39;s to be flushed to the disk=
, while the application will not wait for its response back(for those inser=
ts) and keep flooding the database with more and more incoming Insert reque=
sts?</div><div><br></div><div>Additionally as I mentioned before, we see th=
at from &quot;pg_stat_database&quot; from the column &quot;xact_commit&quot=
; , it&#39;s almost matching=C2=A0with the sum of &quot;tup_inserted&quot;,=
 &quot;tup_updated&quot;, &quot;tup_deleted&quot; column. And also we verif=
ied in pg_stats_statements the=C2=A0 &quot;calls&quot; column is same as th=
e &quot;rows&quot; column for the INSERT queries, so it means also we are i=
nserting exactly same number of rows as the number of DB calls, so doesn=
9;t it suggest that we are doing=C2=A0row by row operations/dmls.</div><div=
><br></div><div>Also after seeing above and asking application team to do t=
he batch commit ,we are still seeing the similar figures from pg_stat_datab=
ase and pg_stat_statements, so does it mean that we are looking into wrong =
stats? or the application code change has not been done accurately? and we =
see even when no inserts are running from the application side, we do see &=
quot;xact_commit&quot; keep increasing along with &quot;tup_fetched&quot; ,=
 why so?</div><div><br></div><div>Finally we see in postgres here, even if =
we just write a DML statement it does commit=C2=A0that by default, until we=
 explicitly put it in a &quot;begin... end&quot; block. Can that be the dif=
ference between how a &quot;commit&quot; gets handled in postgres vs other =
databases?</div><div><br></div><div><br></div></div></div>

--000000000000b4f572062255c9dc--