Re: Handling idle connections

Yes, i came here only after doing a lot of research on internet. I am doing
most of these things already.

I need help on two things now.

1. I see lot of idle connections where application_name is blank and also
query is blank, i am identifying only with user. In this case how do I
identify which application is using idle connection?
2. How to set up TCP/IP timeouts? I saw some parameters like
tcp_user_timeout, tcp_keepalives_idle etc or how?

On Sun, 24 Sep, 2023, 11:45 PM Rui DeSousa, <rui(at)crazybean(dot)net> wrote:

>
>
> > On Sep 24, 2023, at 3:27 AM, Rajesh Kumar <rajeshkumar(dot)dba09(at)gmail(dot)com>
> wrote:
> >
> > Hi
> >
> > What is the best way to handle idle connections other than manually
> killing idle connections if I do not have connection pooler.
>
> Are they actually a problem? Depending on your application, it may be
> doing connection pooling (most do) and/or killing the sessions could create
> problems for the application.
>
> Here’s what I normally do.
>
> 1. Setup idle_in_transaction_session_timeout — these are idle sessions
> with open transactions. This is most likely a bug in the application or a
> very bad practice. Applications shouldn’t be leaving open transaction idle
> for long periods of time — at most a few minutes. I seen environments
> where the thought was hour long idle in sessions transactions was
> considered normal; it’s not. We had to create tickets to manually kill
> them after an hour or when they started to create issues. The development
> team was non responsive in fixing these issues and the DBA team didn’t have
> any authority to mandate it.
>
> 2. Setup TCP/IP timeouts to flush out abandoned idle sessions (i.e. server
> crashes, people unplugging the network cable without logging out, firewall
> dropping the connection without sending reset packets, etc).
>
> 3. Setup up a cron job to kill long idle sessions from non applications
> accounts/networks.
>
> Killing idle sessions just because they are idle could cause problems for
> the application. I seen one application a that would detect multiple bad
> connections and then do a hard reboot. Killing idle sessions would cause
> the entire application to go down and reset itself resulting in the site
> not being available for couple of minutes. Point is, know your application
> and why it currently has idle sessions before killing them indiscriminately.
>
>