MIME-Version: 1.0
References: <CAG-eXHLT2Fgw_szi3KUd8a2AE7cgzBsdi3MaUaGb=TQ+w7oV4w@mail.gmail.com>
 <202502051537.rpohgrxu4vrz@alvherre.pgsql>
In-Reply-To: <202502051537.rpohgrxu4vrz@alvherre.pgsql>
From: ravi k <ravisql09@gmail.com>
Date: Fri, 7 Feb 2025 18:30:10 +0530
Message-ID: <CAFL4M8FrJqr_VZ4pMu-xNTYA_sg8W=vvrq_d-45zd9nWH7wX1g@mail.gmail.com>
Subject: Re: Commit Latency
To: =?UTF-8?Q?=C3=81lvaro_Herrera?= <alvherre@alvh.no-ip.org>
Cc: Ramakrishna m <ram.pgdb@gmail.com>, pgsql-general <pgsql-general@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="0000000000000da5e5062d8cf129"
Archived-At: <https://www.postgresql.org/message-id/CAFL4M8FrJqr_VZ4pMu-xNTYA_sg8W%3Dvvrq_d-45zd9nWH7wX1g%40mail.gmail.com>
Precedence: bulk

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

Thanks for the suggestions!

It looks the issue is happening at the time of wal creation, does
wal_init_zero off is good option?


Best

On Wed, 5 Feb, 2025, 9:07=E2=80=AFpm =C3=81lvaro Herrera, <alvherre@alvh.no=
-ip.org>
wrote:

> Hello
>
> On 2025-Feb-05, Ramakrishna m wrote:
>
> > I have a system handling *300 TPS*, with resource usage *below 10%*.
> > However, I=E2=80=99m noticing *commit latency of around 200ms* for *1% =
of
> > transactions*, occasionally spiking to *1 second*. Since there is no
> > significant *I/O pressure*, I=E2=80=99m trying to identify what else mi=
ght be
> > causing this in *PostgreSQL 16*.
>
> max_connections=3D8000 doesn't sound great -- how many of those are
> active, typically, and how many are idle-in-transaction?  And you have
> autovacuum_naptime=3D5s ... which sounds rather dubious.  Either somebody
> with great expertise configured this very carefully, or the settings are
> somewhat randomly chosen with little or no expert oversight.  Do you
> have monitoring on the amount of bloat on these database?  Maybe you
> should consider connection pooling and limit the number that are active,
> for starters.
>
> Maybe have a look at whether pg_wait_sampling can give you more clues.
> Some basic bloat monitoring is a prerequisite to any further performance
> tweaking anyhow.
>
> --
> =C3=81lvaro Herrera        Breisgau, Deutschland  =E2=80=94
> https://www.EnterpriseDB.com/
> Maybe there's lots of data loss but the records of data loss are also los=
t.
> (Lincoln Yeoh)
>

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

<div dir=3D"auto">Thanks for the suggestions!<div dir=3D"auto"><br></div><d=
iv dir=3D"auto">It looks the issue is happening at the time of wal creation=
, does wal_init_zero off is good option?</div><div dir=3D"auto"><br></div><=
div dir=3D"auto"><br></div><div dir=3D"auto">Best</div></div><br><div class=
=3D"gmail_quote gmail_quote_container"><div dir=3D"ltr" class=3D"gmail_attr=
">On Wed, 5 Feb, 2025, 9:07=E2=80=AFpm =C3=81lvaro Herrera, &lt;<a href=3D"=
mailto:alvherre@alvh.no-ip.org">alvherre@alvh.no-ip.org</a>&gt; wrote:<br><=
/div><blockquote class=3D"gmail_quote" style=3D"margin:0 0 0 .8ex;border-le=
ft:1px #ccc solid;padding-left:1ex">Hello<br>
<br>
On 2025-Feb-05, Ramakrishna m wrote:<br>
<br>
&gt; I have a system handling *300 TPS*, with resource usage *below 10%*.<b=
r>
&gt; However, I=E2=80=99m noticing *commit latency of around 200ms* for *1%=
 of<br>
&gt; transactions*, occasionally spiking to *1 second*. Since there is no<b=
r>
&gt; significant *I/O pressure*, I=E2=80=99m trying to identify what else m=
ight be<br>
&gt; causing this in *PostgreSQL 16*.<br>
<br>
max_connections=3D8000 doesn&#39;t sound great -- how many of those are<br>
active, typically, and how many are idle-in-transaction?=C2=A0 And you have=
<br>
autovacuum_naptime=3D5s ... which sounds rather dubious.=C2=A0 Either someb=
ody<br>
with great expertise configured this very carefully, or the settings are<br=
>
somewhat randomly chosen with little or no expert oversight.=C2=A0 Do you<b=
r>
have monitoring on the amount of bloat on these database?=C2=A0 Maybe you<b=
r>
should consider connection pooling and limit the number that are active,<br=
>
for starters.<br>
<br>
Maybe have a look at whether pg_wait_sampling can give you more clues.<br>
Some basic bloat monitoring is a prerequisite to any further performance<br=
>
tweaking anyhow.<br>
<br>
-- <br>
=C3=81lvaro Herrera=C2=A0 =C2=A0 =C2=A0 =C2=A0 Breisgau, Deutschland=C2=A0 =
=E2=80=94=C2=A0 <a href=3D"https://www.EnterpriseDB.com/" rel=3D"noreferrer=
 noreferrer" target=3D"_blank">https://www.EnterpriseDB.com/</a><br>
Maybe there&#39;s lots of data loss but the records of data loss are also l=
ost.<br>
(Lincoln Yeoh)<br>
</blockquote></div>

--0000000000000da5e5062d8cf129--