Обсуждение: Handling idle connections
Hi
What is the best way to handle idle connections other than manually killing idle connections if I do not have connection pooler.
> On Sep 24, 2023, at 1: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. 1) configure idle timeouts 2) figure out where they're coming from and fix the client
Could u give me a tip on how to figure out where they are coming and what do u mean exactly by saying "fixing" them
On Sun, 24 Sept 2023, 18:25 Scott Ribe, <scott_ribe@elevated-dev.com> wrote:
> On Sep 24, 2023, at 1: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 connection pooler.
1) configure idle timeouts
2) figure out where they're coming from and fix the client
> On Sep 24, 2023, at 7:03 AM, Rajesh Kumar <rajeshkumar.dba09@gmail.com> wrote: > > Could u give me a tip on how to figure out where they are coming and what do u mean exactly by saying "fixing" them Look at pg_stat_activity. There's the application_name column--if your apps are not setting that, the query column showslast/current query and that is sometimes a clue. (Also, if your clients are not setting application name, fix them.) If you don't want client applications leaving connections idle, then clients should release connections when not using them.
On 2023-09-24 12:57 +0530, Rajesh Kumar wrote: > What is the best way to handle idle connections other than manually killing > idle connections if I do not have connection pooler. Couple of options depending on your Postgres version and whether you only care about idle connections with open transactions: * set idle_in_transaction_session_timeout [1] for idle connections with an open transaction * set idle_session_timeout [2] (requires pg14+) for idle connections without an open transaction * use a cron job that checks connections in view pg_stat_activity and terminates them as necessary [1] https://www.cybertec-postgresql.com/en/idle_in_transaction_session_timeout-terminating-idle-transactions-in-postgresql/ [2] https://www.postgresql.org/docs/current/runtime-config-client.html#GUC-IDLE-SESSION-TIMEOUT -- Erik
On Sunday, September 24, 2023, Rajesh Kumar <rajeshkumar.dba09@gmail.com> wrote:
What is the best way to handle idle connections other than manually killing idle connections if I do not have connection pooler.
There are corn jobs and there is a timeout setting as well. Or don’t worry about them.
Best requires more information about the architecture and usage of the database than you’ve provided.
David J.
> 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.
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@crazybean.net> wrote:
> 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 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.
On Sep 24, 2023, at 6:07 PM, Rajesh Kumar <rajeshkumar.dba09@gmail.com> wrote: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?
Use client_addr and client_port from pg_stat_activity to identify the application. The client_addr will give the host it’s connecting from and the port will allow you to determine the process on the client host.
2. How to set up TCP/IP timeouts? I saw some parameters like tcp_user_timeout, tcp_keepalives_idle etc or how?
I meant to say TCP/IP keep alive. There are different ways and options to set keep alive and some are OS dependent.
Here’ s an article that should help you get started:
Rajesh Kumar <rajeshkumar.dba09@gmail.com> writes:
> 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?
If pg_stat_activity.query is empty, then that is a connection that has
*never* issued a query (because once it has, the query column reflects
the last query even when the session is idle). So apparently you have
a rather broken application that connects and then does nothing.
I'd suggest fixing the client side so that it sets application_name to
something that would help identify the situation. If the client is
libpq-based, you might be able to do this by setting the PGAPPNAME
environment variable in an invoking script, rather than modifying
any low-level code.
regards, tom lane
On Mon, Sep 25, 2023 at 12:27 AM Rajesh Kumar <rajeshkumar.dba09@gmail.com> wrote:
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?
Are you querying pg_stat_activity as a superuser, or at least a user who is a member of pg_monitor? If not, many fields will be uninformative due to security reasons. Although "query" shouldn't be literally blank, it should be the string '<insufficient privilege>', but maybe you misinterpreted that as being blank and being filled-in client-side.
If you are querying it as the superuser and still don't know who is connecting to your database, maybe it is time to change the password and be more diligent about who you hand it out to.
Cheers,
Jeff
With regards to handling idle connections, what advice can I give to application team ?
I am assuming like tell them "check sessions are closed properly" like dat?
On Mon, 25 Sep, 2023, 10:54 PM Jeff Janes, <jeff.janes@gmail.com> wrote:
On Mon, Sep 25, 2023 at 12:27 AM Rajesh Kumar <rajeshkumar.dba09@gmail.com> wrote: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?Are you querying pg_stat_activity as a superuser, or at least a user who is a member of pg_monitor? If not, many fields will be uninformative due to security reasons. Although "query" shouldn't be literally blank, it should be the string '<insufficient privilege>', but maybe you misinterpreted that as being blank and being filled-in client-side.If you are querying it as the superuser and still don't know who is connecting to your database, maybe it is time to change the password and be more diligent about who you hand it out to.Cheers,Jeff
On Tue, Sep 26, 2023 at 10:04 AM Rajesh Kumar <rajeshkumar.dba09@gmail.com> wrote:
With regards to handling idle connections, what advice can I give to application team ?I am assuming like tell them "check sessions are closed properly" like dat?
You haven't answered my question. If "query" is truly blank and it is not just a permission problem, then the issue is not that they fail to close connections after use, but rather that they open connections and then never use them.
Cheers,
Jeff
> I am assuming like tell them "check sessions are closed properly" like dat? Don't open until needed, close when done. Assuming they're actually a problem--for smaller scale environments, it can beperfectly OK to have clients holding connections open for the entire client lifetime. Set application name on connection, so that *if* there is a problem it's easier to track down. > On Sep 26, 2023, at 8:55 AM, Jeff Janes <jeff.janes@gmail.com> wrote: > > You haven't answered my question. If "query" is truly blank and it is not just a permission problem, then the issue isnot that they fail to close connections after use, but rather that they open connections and then never use them. Which could easily be a matter of some pg library being used. As far as I see, there was also never an answer to whetherthis is actually a problem and if so, how? And one more: if there is a problem, why not use pgbouncer? We've given all the information we can about how to identify such connections. Any further help would depend on more infofrom Rajesh.
Thank you.
I am planning to use pgbouncer. Help me with the configuration of pgbouncer. Do I have to increase max_connections in postgres.conf when we r configuring pg_bouncer? Help me with pgbouncer configuration or links that is very clear.
On Tue, 26 Sep, 2023, 8:38 PM Scott Ribe, <scott_ribe@elevated-dev.com> wrote:
> I am assuming like tell them "check sessions are closed properly" like dat?
Don't open until needed, close when done. Assuming they're actually a problem--for smaller scale environments, it can be perfectly OK to have clients holding connections open for the entire client lifetime.
Set application name on connection, so that *if* there is a problem it's easier to track down.
> On Sep 26, 2023, at 8:55 AM, Jeff Janes <jeff.janes@gmail.com> wrote:
>
> You haven't answered my question. If "query" is truly blank and it is not just a permission problem, then the issue is not that they fail to close connections after use, but rather that they open connections and then never use them.
Which could easily be a matter of some pg library being used. As far as I see, there was also never an answer to whether this is actually a problem and if so, how?
And one more: if there is a problem, why not use pgbouncer?
We've given all the information we can about how to identify such connections. Any further help would depend on more info from Rajesh.