Обсуждение: Discard All in pgbouncer

Поиск
Список
Период
Сортировка

Discard All in pgbouncer

От
Rajesh Kumar
Дата:
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

Re: Discard All in pgbouncer

От
Scott Ribe
Дата:
> 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
connectionusing 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.




Re: Discard All in pgbouncer

От
Samed YILDIRIM
Дата:
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

Re: Discard All in pgbouncer

От
Ron
Дата:
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.

Re: Discard All in pgbouncer

От
Rajesh Kumar
Дата:
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

Re: Discard All in pgbouncer

От
Scott Ribe
Дата:
> 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,
howto 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
executedon that connection, and will remain the last query executed on that connection until another query is executed
onthat connection. 





Re: Discard All in pgbouncer

От
Rajesh Kumar
Дата:
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.

Re: Discard All in pgbouncer

От
Michael Banck
Дата:
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



Re: Discard All in pgbouncer

От
"David G. Johnston"
Дата:
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.

Re: Discard All in pgbouncer

От
Rajesh Kumar
Дата:
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.

Re: Discard All in pgbouncer

От
"David G. Johnston"
Дата:
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.

Re: Discard All in pgbouncer

От
Ron
Дата:
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.