pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedHandling idle connections
15+ messages / 7 participants
[nested] [flat]
* Handling idle connections
@ 2023-09-24 07:27 Rajesh Kumar <rajeshkumar.dba09@gmail.com>
0 siblings, 4 replies; 15+ messages in thread
From: Rajesh Kumar @ 2023-09-24 07:27 UTC (permalink / raw)
To: Pgsql-admin <pgsql-admin@lists.postgresql.org>
Hi
What is the best way to handle idle connections other than manually killing
idle connections if I do not have connection pooler.
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Handling idle connections
@ 2023-09-24 12:54 Scott Ribe <scott_ribe@elevated-dev.com>
parent: Rajesh Kumar <rajeshkumar.dba09@gmail.com>
3 siblings, 1 reply; 15+ messages in thread
From: Scott Ribe @ 2023-09-24 12:54 UTC (permalink / raw)
To: Rajesh Kumar <rajeshkumar.dba09@gmail.com>; +Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
> 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
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Handling idle connections
@ 2023-09-24 13:03 Rajesh Kumar <rajeshkumar.dba09@gmail.com>
parent: Scott Ribe <scott_ribe@elevated-dev.com>
0 siblings, 1 reply; 15+ messages in thread
From: Rajesh Kumar @ 2023-09-24 13:03 UTC (permalink / raw)
To: Scott Ribe <scott_ribe@elevated-dev.com>; +Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Handling idle connections
@ 2023-09-24 13:19 Scott Ribe <scott_ribe@elevated-dev.com>
parent: Rajesh Kumar <rajeshkumar.dba09@gmail.com>
0 siblings, 0 replies; 15+ messages in thread
From: Scott Ribe @ 2023-09-24 13:19 UTC (permalink / raw)
To: Rajesh Kumar <rajeshkumar.dba09@gmail.com>; +Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
> 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 shows last/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.
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Handling idle connections
@ 2023-09-24 13:29 Erik Wienhold <ewie@ewie.name>
parent: Rajesh Kumar <rajeshkumar.dba09@gmail.com>
3 siblings, 0 replies; 15+ messages in thread
From: Erik Wienhold @ 2023-09-24 13:29 UTC (permalink / raw)
To: Rajesh Kumar <rajeshkumar.dba09@gmail.com>; +Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
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-transact...
[2] https://www.postgresql.org/docs/current/runtime-config-client.html#GUC-IDLE-SESSION-TIMEOUT
--
Erik
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Handling idle connections
@ 2023-09-24 15:05 David G. Johnston <david.g.johnston@gmail.com>
parent: Rajesh Kumar <rajeshkumar.dba09@gmail.com>
3 siblings, 0 replies; 15+ messages in thread
From: David G. Johnston @ 2023-09-24 15:05 UTC (permalink / raw)
To: Rajesh Kumar <rajeshkumar.dba09@gmail.com>; +Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
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.
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Handling idle connections
@ 2023-09-24 18:15 Rui DeSousa <rui@crazybean.net>
parent: Rajesh Kumar <rajeshkumar.dba09@gmail.com>
3 siblings, 1 reply; 15+ messages in thread
From: Rui DeSousa @ 2023-09-24 18:15 UTC (permalink / raw)
To: Rajesh Kumar <rajeshkumar.dba09@gmail.com>; +Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
> 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.
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Handling idle connections
@ 2023-09-24 22:07 Rajesh Kumar <rajeshkumar.dba09@gmail.com>
parent: Rui DeSousa <rui@crazybean.net>
0 siblings, 3 replies; 15+ messages in thread
From: Rajesh Kumar @ 2023-09-24 22:07 UTC (permalink / raw)
To: Rui DeSousa <rui@crazybean.net>; +Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
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.
>
>
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Handling idle connections
@ 2023-09-25 03:10 Rui DeSousa <rui@crazybean.net>
parent: Rajesh Kumar <rajeshkumar.dba09@gmail.com>
2 siblings, 0 replies; 15+ messages in thread
From: Rui DeSousa @ 2023-09-25 03:10 UTC (permalink / raw)
To: Rajesh Kumar <rajeshkumar.dba09@gmail.com>; +Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
> 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:
https://www.cybertec-postgresql.com/en/tcp-keepalive-for-a-better-postgresql-experience/ <https://www.cybertec-postgresql.com/en/tcp-keepalive-for-a-better-postgresql-experience/;
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Handling idle connections
@ 2023-09-25 03:34 Tom Lane <tgl@sss.pgh.pa.us>
parent: Rajesh Kumar <rajeshkumar.dba09@gmail.com>
2 siblings, 0 replies; 15+ messages in thread
From: Tom Lane @ 2023-09-25 03:34 UTC (permalink / raw)
To: Rajesh Kumar <rajeshkumar.dba09@gmail.com>; +Cc: Rui DeSousa <rui@crazybean.net>; Pgsql-admin <pgsql-admin@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Handling idle connections
@ 2023-09-25 17:24 Jeff Janes <jeff.janes@gmail.com>
parent: Rajesh Kumar <rajeshkumar.dba09@gmail.com>
2 siblings, 1 reply; 15+ messages in thread
From: Jeff Janes @ 2023-09-25 17:24 UTC (permalink / raw)
To: Rajesh Kumar <rajeshkumar.dba09@gmail.com>; +Cc: Rui DeSousa <rui@crazybean.net>; Pgsql-admin <pgsql-admin@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Handling idle connections
@ 2023-09-26 14:04 Rajesh Kumar <rajeshkumar.dba09@gmail.com>
parent: Jeff Janes <jeff.janes@gmail.com>
0 siblings, 1 reply; 15+ messages in thread
From: Rajesh Kumar @ 2023-09-26 14:04 UTC (permalink / raw)
To: Jeff Janes <jeff.janes@gmail.com>; +Cc: Rui DeSousa <rui@crazybean.net>; Pgsql-admin <pgsql-admin@lists.postgresql.org>
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
>
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Handling idle connections
@ 2023-09-26 14:55 Jeff Janes <jeff.janes@gmail.com>
parent: Rajesh Kumar <rajeshkumar.dba09@gmail.com>
0 siblings, 1 reply; 15+ messages in thread
From: Jeff Janes @ 2023-09-26 14:55 UTC (permalink / raw)
To: Rajesh Kumar <rajeshkumar.dba09@gmail.com>; +Cc: Rui DeSousa <rui@crazybean.net>; Pgsql-admin <pgsql-admin@lists.postgresql.org>
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
>
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Handling idle connections
@ 2023-09-26 15:08 Scott Ribe <scott_ribe@elevated-dev.com>
parent: Jeff Janes <jeff.janes@gmail.com>
0 siblings, 1 reply; 15+ messages in thread
From: Scott Ribe @ 2023-09-26 15:08 UTC (permalink / raw)
To: Rajesh Kumar <rajeshkumar.dba09@gmail.com>; +Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
> 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.
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Handling idle connections
@ 2023-09-27 10:32 Rajesh Kumar <rajeshkumar.dba09@gmail.com>
parent: Scott Ribe <scott_ribe@elevated-dev.com>
0 siblings, 0 replies; 15+ messages in thread
From: Rajesh Kumar @ 2023-09-27 10:32 UTC (permalink / raw)
To: Scott Ribe <scott_ribe@elevated-dev.com>; +Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
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.
^ permalink raw reply [nested|flat] 15+ messages in thread
end of thread, other threads:[~2023-09-27 10:32 UTC | newest]
Thread overview: 15+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-09-24 07:27 Handling idle connections Rajesh Kumar <rajeshkumar.dba09@gmail.com>
2023-09-24 12:54 ` Scott Ribe <scott_ribe@elevated-dev.com>
2023-09-24 13:03 ` Rajesh Kumar <rajeshkumar.dba09@gmail.com>
2023-09-24 13:19 ` Scott Ribe <scott_ribe@elevated-dev.com>
2023-09-24 13:29 ` Erik Wienhold <ewie@ewie.name>
2023-09-24 15:05 ` David G. Johnston <david.g.johnston@gmail.com>
2023-09-24 18:15 ` Rui DeSousa <rui@crazybean.net>
2023-09-24 22:07 ` Rajesh Kumar <rajeshkumar.dba09@gmail.com>
2023-09-25 03:10 ` Rui DeSousa <rui@crazybean.net>
2023-09-25 03:34 ` Tom Lane <tgl@sss.pgh.pa.us>
2023-09-25 17:24 ` Jeff Janes <jeff.janes@gmail.com>
2023-09-26 14:04 ` Rajesh Kumar <rajeshkumar.dba09@gmail.com>
2023-09-26 14:55 ` Jeff Janes <jeff.janes@gmail.com>
2023-09-26 15:08 ` Scott Ribe <scott_ribe@elevated-dev.com>
2023-09-27 10:32 ` Rajesh Kumar <rajeshkumar.dba09@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox