pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedDiscard All in pgbouncer
12+ messages / 6 participants
[nested] [flat]
* Discard All in pgbouncer
@ 2023-10-09 14:07 Rajesh Kumar <rajeshkumar.dba09@gmail.com>
0 siblings, 3 replies; 12+ messages in thread
From: Rajesh Kumar @ 2023-10-09 14:07 UTC (permalink / raw)
To: Pgsql-admin <pgsql-admin@lists.postgresql.org>
Hi,
I could see queries like DISCARD ALL is idle for more than 5minutes (we are
using pgbouncer).
Why is it idle for more than 5mns? I read about pgbouncer and could not
understand this thing. If resetting a connection using query becomes one
idle connection, I don't understand the pooling system here
Plz help
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Discard All in pgbouncer
@ 2023-10-09 14:11 Scott Ribe <scott_ribe@elevated-dev.com>
parent: Rajesh Kumar <rajeshkumar.dba09@gmail.com>
2 siblings, 0 replies; 12+ messages in thread
From: Scott Ribe @ 2023-10-09 14:11 UTC (permalink / raw)
To: Rajesh Kumar <rajeshkumar.dba09@gmail.com>; +Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
> On Oct 9, 2023, at 8:07 AM, Rajesh Kumar <rajeshkumar.dba09@gmail.com> wrote:
>
> Hi,
>
> I could see queries like DISCARD ALL is idle for more than 5minutes (we are using pgbouncer).
>
> Why is it idle for more than 5mns? I read about pgbouncer and could not understand this thing. If resetting a connection using query becomes one idle connection, I don't understand the pooling system here
>
> Plz help
Idle means it's not doing anything--pg_stat_activity is showing you the last query executed.
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Discard All in pgbouncer
@ 2023-10-09 14:12 Samed YILDIRIM <samed@reddoc.net>
parent: Rajesh Kumar <rajeshkumar.dba09@gmail.com>
2 siblings, 1 reply; 12+ messages in thread
From: Samed YILDIRIM @ 2023-10-09 14:12 UTC (permalink / raw)
To: Rajesh Kumar <rajeshkumar.dba09@gmail.com>; +Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
Hi Rajesh,
DISCARD ALL is the query that is executed by PgBouncer in order to sanitise
a database session before assigning it to another client. If you see this
query in pg_stat_activity and status of the session is idle, most probably
it is ok. Probably what you see is the last executed query for the session.
And the session is idle and waiting to be assigned to a client. :)
Best regards.
Samed YILDIRIM
On Mon, 9 Oct 2023, 17:07 Rajesh Kumar, <rajeshkumar.dba09@gmail.com> wrote:
> Hi,
>
> I could see queries like DISCARD ALL is idle for more than 5minutes (we
> are using pgbouncer).
>
> Why is it idle for more than 5mns? I read about pgbouncer and could not
> understand this thing. If resetting a connection using query becomes one
> idle connection, I don't understand the pooling system here
>
> Plz help
>
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Discard All in pgbouncer
@ 2023-10-09 14:13 Ron <ronljohnsonjr@gmail.com>
parent: Rajesh Kumar <rajeshkumar.dba09@gmail.com>
2 siblings, 1 reply; 12+ messages in thread
From: Ron @ 2023-10-09 14:13 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
On 10/9/23 09:07, Rajesh Kumar wrote:
> Hi,
>
> I could see queries like DISCARD ALL is idle for more than 5minutes (we
> are using pgbouncer).
>
> Why is it idle for more than 5mns? I read about pgbouncer and could not
> understand this thing. If resetting a connection using query becomes one
> idle connection, I don't understand the pooling system here
In your case, "Idle" just means that DISCARD ALL was the last statement that
the pid executed. Now, it's just doing /nothing/, because there's nothing
to do. IOW, this is just fine. No need to worry.
--
Born in Arizona, moved to Babylonia.
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Discard All in pgbouncer
@ 2023-10-09 14:18 Rajesh Kumar <rajeshkumar.dba09@gmail.com>
parent: Samed YILDIRIM <samed@reddoc.net>
0 siblings, 3 replies; 12+ messages in thread
From: Rajesh Kumar @ 2023-10-09 14:18 UTC (permalink / raw)
To: Samed YILDIRIM <samed@reddoc.net>; +Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
Purpose of bouncer is handle idle connections right? Is there a time period
for the DISCARD ALL to go away? If so, how to configure?
The part I don't understand is if bunch of connections in the name of
Discard all is idle , what difference it make?
On Mon, 9 Oct 2023, 19:42 Samed YILDIRIM, <samed@reddoc.net> wrote:
> Hi Rajesh,
>
> DISCARD ALL is the query that is executed by PgBouncer in order to
> sanitise a database session before assigning it to another client. If you
> see this query in pg_stat_activity and status of the session is idle, most
> probably it is ok. Probably what you see is the last executed query for the
> session. And the session is idle and waiting to be assigned to a client. :)
>
> Best regards.
> Samed YILDIRIM
>
> On Mon, 9 Oct 2023, 17:07 Rajesh Kumar, <rajeshkumar.dba09@gmail.com>
> wrote:
>
>> Hi,
>>
>> I could see queries like DISCARD ALL is idle for more than 5minutes (we
>> are using pgbouncer).
>>
>> Why is it idle for more than 5mns? I read about pgbouncer and could not
>> understand this thing. If resetting a connection using query becomes one
>> idle connection, I don't understand the pooling system here
>>
>> Plz help
>>
>
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Discard All in pgbouncer
@ 2023-10-09 14:22 Scott Ribe <scott_ribe@elevated-dev.com>
parent: Rajesh Kumar <rajeshkumar.dba09@gmail.com>
2 siblings, 0 replies; 12+ messages in thread
From: Scott Ribe @ 2023-10-09 14:22 UTC (permalink / raw)
To: Rajesh Kumar <rajeshkumar.dba09@gmail.com>; +Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
> On Oct 9, 2023, at 8:18 AM, Rajesh Kumar <rajeshkumar.dba09@gmail.com> wrote:
>
> Purpose of bouncer is handle idle connections right? Is there a time period for the DISCARD ALL to go away? If so, how to configure?
>
> The part I don't understand is if bunch of connections in the name of Discard all is idle , what difference it make?
DISCARD ALL is simply the last query executed. There is no time period for it to "go away". It is the last query executed on that connection, and will remain the last query executed on that connection until another query is executed on that connection.
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Discard All in pgbouncer
@ 2023-10-09 14:22 Rajesh Kumar <rajeshkumar.dba09@gmail.com>
parent: Ron <ronljohnsonjr@gmail.com>
0 siblings, 2 replies; 12+ messages in thread
From: Rajesh Kumar @ 2023-10-09 14:22 UTC (permalink / raw)
To: Ron <ronljohnsonjr@gmail.com>; +Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
Purpose of bouncer is handle idle connections right? Is there a time period
for the DISCARD ALL to go away? If so, how to configure?
The part I don't understand is if bunch of connections in the name of
Discard all is idle , what difference it make?
If I have set Max conn in db as 250....do I have to set the same in
pgbouncer?
On Mon, 9 Oct 2023, 19:46 Ron, <ronljohnsonjr@gmail.com> wrote:
> On 10/9/23 09:07, Rajesh Kumar wrote:
>
> Hi,
>
> I could see queries like DISCARD ALL is idle for more than 5minutes (we
> are using pgbouncer).
>
> Why is it idle for more than 5mns? I read about pgbouncer and could not
> understand this thing. If resetting a connection using query becomes one
> idle connection, I don't understand the pooling system here
>
>
> In your case, "Idle" just means that DISCARD ALL was the last statement
> that the pid executed. Now, it's just doing *nothing*, because there's
> nothing to do. IOW, this is just fine. No need to worry.
>
> --
> Born in Arizona, moved to Babylonia.
>
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Discard All in pgbouncer
@ 2023-10-09 14:34 Michael Banck <mbanck@gmx.net>
parent: Rajesh Kumar <rajeshkumar.dba09@gmail.com>
1 sibling, 0 replies; 12+ messages in thread
From: Michael Banck @ 2023-10-09 14:34 UTC (permalink / raw)
To: Rajesh Kumar <rajeshkumar.dba09@gmail.com>; +Cc: Ron <ronljohnsonjr@gmail.com>; Pgsql-admin <pgsql-admin@lists.postgresql.org>
Hi,
On Mon, Oct 09, 2023 at 07:52:55PM +0530, Rajesh Kumar wrote:
> Purpose of bouncer is handle idle connections right? Is there a time period
> for the DISCARD ALL to go away? If so, how to configure?
>
> The part I don't understand is if bunch of connections in the name of
> Discard all is idle , what difference it make?
>
> If I have set Max conn in db as 250....do I have to set the same in
> pgbouncer?
You wrote the exact same text in another reply. Please don't
double-post.
Michael
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Discard All in pgbouncer
@ 2023-10-09 14:44 David G. Johnston <david.g.johnston@gmail.com>
parent: Rajesh Kumar <rajeshkumar.dba09@gmail.com>
2 siblings, 1 reply; 12+ messages in thread
From: David G. Johnston @ 2023-10-09 14:44 UTC (permalink / raw)
To: Rajesh Kumar <rajeshkumar.dba09@gmail.com>; +Cc: Samed YILDIRIM <samed@reddoc.net>; Pgsql-admin <pgsql-admin@lists.postgresql.org>
On Monday, October 9, 2023, Rajesh Kumar <rajeshkumar.dba09@gmail.com>
wrote:
> Purpose of bouncer is handle idle connections right? Is there a time
> period for the DISCARD ALL to go away? If so, how to configure?
>
>
You can configure pgbouncer to close unused connections periodically - and
indeed the defaults do this. server_idle_timeout of 10 minutes.
David J.
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Discard All in pgbouncer
@ 2023-10-09 14:48 Rajesh Kumar <rajeshkumar.dba09@gmail.com>
parent: David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 0 replies; 12+ messages in thread
From: Rajesh Kumar @ 2023-10-09 14:48 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: Samed YILDIRIM <samed@reddoc.net>; Pgsql-admin <pgsql-admin@lists.postgresql.org>
Thanks for the clarification.
On Mon, 9 Oct, 2023, 8:14 PM David G. Johnston, <david.g.johnston@gmail.com>
wrote:
> On Monday, October 9, 2023, Rajesh Kumar <rajeshkumar.dba09@gmail.com>
> wrote:
>
>> Purpose of bouncer is handle idle connections right? Is there a time
>> period for the DISCARD ALL to go away? If so, how to configure?
>>
>>
>
> You can configure pgbouncer to close unused connections periodically - and
> indeed the defaults do this. server_idle_timeout of 10 minutes.
>
> David J.
>
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Discard All in pgbouncer
@ 2023-10-09 14:52 David G. Johnston <david.g.johnston@gmail.com>
parent: Rajesh Kumar <rajeshkumar.dba09@gmail.com>
1 sibling, 0 replies; 12+ messages in thread
From: David G. Johnston @ 2023-10-09 14:52 UTC (permalink / raw)
To: Rajesh Kumar <rajeshkumar.dba09@gmail.com>; +Cc: Ron <ronljohnsonjr@gmail.com>; Pgsql-admin <pgsql-admin@lists.postgresql.org>
On Monday, October 9, 2023, Rajesh Kumar <rajeshkumar.dba09@gmail.com>
wrote:
>
> If I have set Max conn in db as 250....do I have to set the same in
> pgbouncer?
>
You need to read the pgbouncer configuration settings again and ask
specific questions about specific settings that you don’t understand.
But, no, your configuration of pgbouncer and PostgreSQL are related but
independent and you need to make choice for all three interfaces that exist
in such a setup - application-pgbouncer, pgbouncer-internal,
world-to-PostgreSQL (pgbouncer being part of the “world” from PostgreSQL’s
perspective)
David J.
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Discard All in pgbouncer
@ 2023-10-09 15:39 Ron <ronljohnsonjr@gmail.com>
parent: Rajesh Kumar <rajeshkumar.dba09@gmail.com>
2 siblings, 0 replies; 12+ messages in thread
From: Ron @ 2023-10-09 15:39 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
You're */still/* missing the point. If pgbouncer doesn't need to use a
connection, then... it's going to sit there "idle". That's what "idle" means!
What we haven't explicitly stated, but implied, is that pg_stat_activity
*does not* clear the query field when the status = 'idle'. Thus, as we've
said before, DISCARD ALL /*was*//**/ the last statement to be executed.
On 10/9/23 09:18, Rajesh Kumar wrote:
> Purpose of bouncer is handle idle connections right? Is there a time
> period for the DISCARD ALL to go away? If so, how to configure?
>
> The part I don't understand is if bunch of connections in the name of
> Discard all is idle , what difference it make?
>
> On Mon, 9 Oct 2023, 19:42 Samed YILDIRIM, <samed@reddoc.net> wrote:
>
> Hi Rajesh,
>
> DISCARD ALL is the query that is executed by PgBouncer in order to
> sanitise a database session before assigning it to another client. If
> you see this query in pg_stat_activity and status of the session is
> idle, most probably it is ok. Probably what you see is the last
> executed query for the session. And the session is idle and waiting to
> be assigned to a client. :)
>
> Best regards.
> Samed YILDIRIM
>
> On Mon, 9 Oct 2023, 17:07 Rajesh Kumar, <rajeshkumar.dba09@gmail.com>
> wrote:
>
> Hi,
>
> I could see queries like DISCARD ALL is idle for more than
> 5minutes (we are using pgbouncer).
>
> Why is it idle for more than 5mns? I read about pgbouncer and
> could not understand this thing. If resetting a connection using
> query becomes one idle connection, I don't understand the pooling
> system here
>
> Plz help
>
--
Born in Arizona, moved to Babylonia.
^ permalink raw reply [nested|flat] 12+ messages in thread
end of thread, other threads:[~2023-10-09 15:39 UTC | newest]
Thread overview: 12+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-10-09 14:07 Discard All in pgbouncer Rajesh Kumar <rajeshkumar.dba09@gmail.com>
2023-10-09 14:11 ` Scott Ribe <scott_ribe@elevated-dev.com>
2023-10-09 14:12 ` Samed YILDIRIM <samed@reddoc.net>
2023-10-09 14:18 ` Rajesh Kumar <rajeshkumar.dba09@gmail.com>
2023-10-09 14:22 ` Scott Ribe <scott_ribe@elevated-dev.com>
2023-10-09 14:44 ` David G. Johnston <david.g.johnston@gmail.com>
2023-10-09 14:48 ` Rajesh Kumar <rajeshkumar.dba09@gmail.com>
2023-10-09 15:39 ` Ron <ronljohnsonjr@gmail.com>
2023-10-09 14:13 ` Ron <ronljohnsonjr@gmail.com>
2023-10-09 14:22 ` Rajesh Kumar <rajeshkumar.dba09@gmail.com>
2023-10-09 14:34 ` Michael Banck <mbanck@gmx.net>
2023-10-09 14:52 ` David G. Johnston <david.g.johnston@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