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@xxxxxxxxxxxxx> wrote:
> On Sep 24, 2023, at 3:27 AM, Rajesh Kumar <rajeshkumar.dba09@xxxxxxxxx> 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.
