MIME-Version: 1.0
References: <CAD=mzVX5DYUZxt7VE-eB4=6eXTpbG7r6MiR7gD-0K9cctscnYw@mail.gmail.com>
 <3133182a-35c4-4a81-be60-8ab227c0fbc0@aklaver.com> <CAD=mzVUns_nBPOOiDMxvZgnra+e9Nz7QJqr3r3X6JFMxuUGRZg@mail.gmail.com>
In-Reply-To: <CAD=mzVUns_nBPOOiDMxvZgnra+e9Nz7QJqr3r3X6JFMxuUGRZg@mail.gmail.com>
From: Greg Sabino Mullane <htamfids@gmail.com>
Date: Tue, 1 Oct 2024 08:14:36 -0400
Message-ID: <CAKAnmm+Q2tbpDT7byjorFEkvjjTUGMuvV2iFhfmBJUW476LR2Q@mail.gmail.com>
Subject: Re: Question on session timeout
To: sud <suds1434@gmail.com>
Cc: Adrian Klaver <adrian.klaver@aklaver.com>, 
	pgsql-general <pgsql-general@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000049b400623694603"
Archived-At: <https://www.postgresql.org/message-id/CAKAnmm%2BQ2tbpDT7byjorFEkvjjTUGMuvV2iFhfmBJUW476LR2Q%40mail.gmail.com>
Precedence: bulk

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

On Tue, Oct 1, 2024 at 1:57=E2=80=AFAM sud <suds1434@gmail.com> wrote:

> *Where are you getting the ~2000  count from?*
> Seeing this in the "performance insights" dashboard and also its matching
> when I query the count of sessions from pg_stat_activity.
>

So I'm guessing this is perhaps RDS or Aurora? Stating that up front can be
helpful.


> As you described, a long time open transaction with a session state as
> "idle" will be threatening as that will cause locking
>

No, idle is fine, "idle in transaction" is bad. :)

Is it correct to assume the session in pg_stat_activity with very old
> XACT_START are the one which are part of long running
>

<snip rest of question>

You need to look at the "state" column as your primary bit of information.
Second most important is how long something has been in that state, which
you can find with now() - state_change. The best way to learn all of this
is to open a few concurrent sessions in psql and experiment.


> We have max_connections set as 5000.
>

That's quite high. But if you never reach that high, it doesn't matter a
whole lot.

"Database connection" touching ~2000 then coming down till 200. And we see
> uneven spikes in those, it seems to be matching with the pattern , when w=
e
> have some errors occurring during the insert queries which are submitted =
by
> the Java application to insert the data into the tables.
>

(What sort of errors?) 2000 is high. Clearly, you are not pooling
connections, or not pooling them well. If you are using plain Postgres,
look into setting up pgbouncer. If using something managed (e.g. RDS) look
into their particular pooling solution. Or fix your application-level
pooling.

Cheers,
Greg

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

<div dir=3D"ltr"><div dir=3D"ltr">On Tue, Oct 1, 2024 at 1:57=E2=80=AFAM su=
d &lt;<a href=3D"mailto:suds1434@gmail.com">suds1434@gmail.com</a>&gt; wrot=
e:</div><div class=3D"gmail_quote"><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"ltr"><div class=3D"gmail_quote"><i>Where are you get=
ting the ~2000 =C2=A0count from?</i><br>Seeing this in the &quot;performanc=
e insights&quot; dashboard and also its matching when I query the count of =
sessions from pg_stat_activity.<br></div></div></blockquote><div><br></div>=
<div>So I&#39;m guessing this is perhaps RDS or Aurora? Stating that up fro=
nt can be helpful.</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);pa=
dding-left:1ex"><div dir=3D"ltr"><div class=3D"gmail_quote">As you describe=
d, a long time open transaction with a session state as &quot;idle&quot; wi=
ll be threatening as that will cause locking</div></div></blockquote><div><=
br></div><div>No, idle is fine, &quot;idle in transaction&quot; is bad. :)=
=C2=A0</div><div><br></div><blockquote class=3D"gmail_quote" style=3D"margi=
n:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex=
"><div dir=3D"ltr"><div class=3D"gmail_quote">Is it correct to assume the s=
ession in pg_stat_activity with very old XACT_START are the one which are p=
art of long running</div></div></blockquote><div><br></div><div>&lt;snip re=
st of question&gt;</div><div><br></div><div>You need to look at the &quot;s=
tate&quot; column as your primary bit of information. Second most important=
 is how long something has been in that state, which you can find with now(=
) - state_change. The best way to learn all of this is to open a few concur=
rent sessions in psql and experiment.</div><div>=C2=A0</div><blockquote cla=
ss=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"ltr"><div class=3D"gmail_quo=
te">We have max_connections set as 5000.<br></div></div></blockquote><div><=
br></div><div>That&#39;s quite high. But if you never reach that high, it d=
oesn&#39;t matter a whole lot.</div><div><br></div><blockquote class=3D"gma=
il_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,2=
04,204);padding-left:1ex"><div dir=3D"ltr"><div class=3D"gmail_quote">&quot=
;Database connection&quot; touching ~2000 then coming down till 200. And we=
 see uneven spikes in those, it seems to be matching with the pattern , whe=
n we have some errors occurring during the insert queries which are submitt=
ed by the Java application to insert the data into the tables.</div></div><=
/blockquote><div><br></div><div>(What sort of errors?) 2000 is high.=C2=A0C=
learly, you are not pooling connections, or not pooling them well. If you a=
re using plain Postgres, look into setting up pgbouncer. If using something=
 managed (e.g. RDS) look into their particular pooling solution. Or fix you=
r application-level pooling.</div><div><br></div><div>Cheers,</div><div>Gre=
g</div><div><br></div></div></div>

--000000000000049b400623694603--