Re: Handling idle connections

> On Sep 24, 2023, at 3:27 AM, Rajesh Kumar <rajeshkumar.dba09@gmail.com> wrote:
>
> Hi
>
> What is the best way to handle idle connections other than manually killing idle connections if I do not have
connectionpooler. 

Are they actually a problem? Depending on your application, it may be doing connection pooling (most do) and/or killing
thesessions 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
bugin the application or a very bad practice.  Applications shouldn’t be leaving open transaction idle for long periods
oftime — at most a few minutes.  I seen environments where the thought was hour long idle in sessions transactions was
considerednormal; it’s not.  We had to create tickets to manually kill them after an hour or when they started to
createissues. The development team was non responsive in fixing these issues and the DBA team didn’t have any authority
tomandate it. 

2. Setup TCP/IP timeouts to flush out abandoned idle sessions (i.e. server crashes, people unplugging the network cable
withoutlogging 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
thatwould detect multiple bad connections and then do a hard reboot.  Killing idle sessions would cause the entire
applicationto go down and reset itself resulting in the site not being available for couple of minutes.  Point is, know
yourapplication and why it currently has idle sessions before killing them indiscriminately.