Postgres Pro
Downloads
Home   >   mailing lists

Thread: Connections - Postgres 9.2

Connections - Postgres 9.2

From
Lucas Possamai
Date:
Hi guys,

Inline images 1

Those IDLE connections, might be because the user/application didn't commit the transaction?
Attachment

Re: Connections - Postgres 9.2

From
Charles Clavadetscher
Date:
Hello

> On 16.05.2016, at 04:10, Lucas Possamai <drum.lucas@gmail.com> wrote:
>
> Hi guys,
>
> <Screen Shot 2016-05-16 at 2.06.20 PM.png>
>
> Those IDLE connections, might be because the user/application didn't commit the transaction?

I think that idle means that a client is connected but is doing nothing. Possibly It includes terminated processes
withouta commit, since they do nothing, but I am not sure that you can assume that all connections are uncommitted
transactions.

Bye
Charles

Re: Connections - Postgres 9.2

From
Francisco Olarte
Date:
Hi Lucas

On Mon, May 16, 2016 at 4:10 AM, Lucas Possamai <drum.lucas@gmail.com> wrote:

Those IDLE connections, might be because the user/application didn't commit the transaction?

​IIRC Those would be 'Idle in transaction' ( which are normally bad if numerous, unless your app has a reason for them, as they are the ones which can block things ). Plain 'Idle' are normally connections between transactions, totally normal if you use poolers, or if your app keeps connection opens while it does other things ( like preparing for a transaction ).

Francisco Olarte.

 

Re: Connections - Postgres 9.2

From
Charles Clavadetscher
Date:
Hello

On 16.05.2016, at 18:32, Francisco Olarte <folarte@peoplecall.com> wrote:

Hi Lucas

On Mon, May 16, 2016 at 4:10 AM, Lucas Possamai <drum.lucas@gmail.com> wrote:

Those IDLE connections, might be because the user/application didn't commit the transaction?

​IIRC Those would be 'Idle in transaction' ( which are normally bad if numerous, unless your app has a reason for them, as they are the ones which can block things ). Plain 'Idle' are normally connections between transactions, totally normal if you use poolers, or if your app keeps connection opens while it does other things ( like preparing for a transaction ).


There really is a state 'Idle in transaction'? Good to learn.
Thank you and bye
Charles

Francisco Olarte.

 

Re: Connections - Postgres 9.2

From
Francisco Olarte
Date:
Charles:

On Mon, May 16, 2016 at 6:56 PM, Charles Clavadetscher
<clavadetscher@swisspug.org> wrote:

> There really is a state 'Idle in transaction'? Good to learn.

Again, IIRC, it was there in the graph legend, orange was Idle, yellow
was Idle in transaction ( not in the data, just in the legend ).

Francisco Olarte.

Re: Connections - Postgres 9.2

From
Adrian Klaver
Date:
On 05/16/2016 09:56 AM, Charles Clavadetscher wrote:
> Hello
>
> On 16.05.2016, at 18:32, Francisco Olarte <folarte@peoplecall.com
> <mailto:folarte@peoplecall.com>> wrote:
>
>> Hi Lucas
>>
>> On Mon, May 16, 2016 at 4:10 AM, Lucas Possamai <drum.lucas@gmail.com
>> <mailto:drum.lucas@gmail.com>> wrote:
>>
>>
>>     Those IDLE connections, might be because the user/application
>>     didn't commit the transaction?
>>
>>
>> ​IIRC Those would be 'Idle in transaction' ( which are normally bad if
>> numerous, unless your app has a reason for them, as they are the ones
>> which can block things ). Plain 'Idle' are normally connections
>> between transactions, totally normal if you use poolers, or if your
>> app keeps connection opens while it does other things ( like preparing
>> for a transaction ).
>>
>
> There really is a state 'Idle in transaction'? Good to learn.

http://www.postgresql.org/docs/9.5/static/monitoring-stats.html
"
state     text     Current overall state of this backend. Possible values are:

     active: The backend is executing a query.

     idle: The backend is waiting for a new client command.

     idle in transaction: The backend is in a transaction, but is not
currently executing a query.

     idle in transaction (aborted): This state is similar to idle in
transaction, except one of the statements in the transaction caused an
error.

     fastpath function call: The backend is executing a fast-path function.

     disabled: This state is reported if track_activities is disabled in
this backend.
"

> Thank you and bye
> Charles
>
>> Francisco Olarte.
>>
>>
>>


--
Adrian Klaver
adrian.klaver@aklaver.com

Re: Connections - Postgres 9.2

From
Lucas Possamai
Date:
hmm.. thanks for all the answers guys...


One more question: Those IDLE connections.. are using the server's resources?
To solve that problem I would need a Pool connection, right?

Would the pool connection solve that IDLE connections? But more important than that, are the IDLE connections using the machine's resources ?


cheers

Re: Connections - Postgres 9.2

From
Adrian Klaver
Date:
On 05/16/2016 01:28 PM, Lucas Possamai wrote:
> hmm.. thanks for all the answers guys...
>
>
> One more question: Those IDLE connections.. are using the server's
> resources?
> To solve that problem I would need a Pool connection, right?

Yes and no. If your application/clients are generating connections that
are not being closed then putting a pooler between the client and the
database just moves the problem to the pooler. In other words if the
client is asking for a connection from the pooler and then does not
close it then the pooler is going to have to add connections to deal
with subsequent connections. Now you can set a max number of connections
and reject new ones after that number, but that then means you may be
shutting out connections that need to get through. The first step in
dealing with this would be to determine what code is generating
connections and then not closing them. A good place to start would be:

http://www.postgresql.org/docs/9.5/static/monitoring-stats.html#PG-STAT-ACTIVITY-VIEW

Table 27-3. pg_stat_activity View

That should give you an idea of what is creating the connections.


>
> Would the pool connection solve that IDLE connections? But more
> important than that, are the IDLE connections using the machine's
> resources ?

Yes a connection consumes resources.
>
>
> cheers


--
Adrian Klaver
adrian.klaver@aklaver.com

Re: Connections - Postgres 9.2

From
Venkata Balaji N
Date:

On Tue, May 17, 2016 at 6:28 AM, Lucas Possamai <drum.lucas@gmail.com> wrote:
hmm.. thanks for all the answers guys...


One more question: Those IDLE connections.. are using the server's resources?
To solve that problem I would need a Pool connection, right?

Would the pool connection solve that IDLE connections? But more important than that, are the IDLE connections using the machine's resources ?

Yes. There is no straight way to terminate the IDLE connections from the database end. You would need a connection pooler like pgBouncer which can help you terminate IDLE connections after a certain time.

It would be much better if your Application can terminate connections to the database.

Regards,
Venkata B N

Fujitsu Australia

Re: Connections - Postgres 9.2

From
John R Pierce
Date:
On 5/16/2016 1:55 PM, Adrian Klaver wrote:
Would the pool connection solve that IDLE connections? But more
important than that, are the IDLE connections using the machine's
resources ?

Yes a connection consumes resources.

an idle connection consumes some memory, a process context, and a network socket.   its not using CPU or disk IO.


-- 
john r pierce, recycling bits in santa cruz

Re: Connections - Postgres 9.2

From
Lucas Possamai
Date:


On 17 May 2016 at 08:56, Venkata Balaji N <nag1010@gmail.com> wrote:

On Tue, May 17, 2016 at 6:28 AM, Lucas Possamai <drum.lucas@gmail.com> wrote:
hmm.. thanks for all the answers guys...


One more question: Those IDLE connections.. are using the server's resources?
To solve that problem I would need a Pool connection, right?

Would the pool connection solve that IDLE connections? But more important than that, are the IDLE connections using the machine's resources ?

Yes. There is no straight way to terminate the IDLE connections from the database end. You would need a connection pooler like pgBouncer which can help you terminate IDLE connections after a certain time.

It would be much better if your Application can terminate connections to the database.

Regards,
Venkata B N

Fujitsu Australia




Ok awesome.. thanks a lot!

Lucas

Re: Connections - Postgres 9.2

From
Adrian Klaver
Date:
On 05/16/2016 02:00 PM, John R Pierce wrote:
> On 5/16/2016 1:55 PM, Adrian Klaver wrote:
>>> Would the pool connection solve that IDLE connections? But more
>>> important than that, are the IDLE connections using the machine's
>>> resources ?
>>
>> Yes a connection consumes resources.
>
> an idle connection consumes some memory, a process context, and a
> network socket.   its not using CPU or disk IO.

True, but the existence of poolers says that can be an issue.

>
>
> --
> john r pierce, recycling bits in santa cruz
>


--
Adrian Klaver
adrian.klaver@aklaver.com

Re: Connections - Postgres 9.2

From
John R Pierce
Date:
On 5/16/2016 2:11 PM, Adrian Klaver wrote:
Yes a connection consumes resources.

an idle connection consumes some memory, a process context, and a
network socket.   its not using CPU or disk IO.

True, but the existence of poolers says that can be an issue.

I note that MRTG style graph showed max 16, average 5 idle connections.   thats a rather small number to be concerned with.  if it was 100s, then I'd be worrying about it.



-- 
john r pierce, recycling bits in santa cruz

Re: Connections - Postgres 9.2

From
Adrian Klaver
Date:
On 05/16/2016 02:25 PM, John R Pierce wrote:
> On 5/16/2016 2:11 PM, Adrian Klaver wrote:
>>>> Yes a connection consumes resources.
>>>
>>> an idle connection consumes some memory, a process context, and a
>>> network socket.   its not using CPU or disk IO.
>>
>> True, but the existence of poolers says that can be an issue.
>
> I note that MRTG style graph showed max 16, average 5 idle
> connections.   thats a rather small number to be concerned with.  if it
> was 100s, then I'd be worrying about it.

Yeah, I failed to look at the scale of the y axis and just looked at the
relative numbers.

>
>
>
> --
> john r pierce, recycling bits in santa cruz
>


--
Adrian Klaver
adrian.klaver@aklaver.com

Re: Connections - Postgres 9.2

From
Melvin Davidson
Date:


On Sun, May 15, 2016 at 10:10 PM, Lucas Possamai <drum.lucas@gmail.com> wrote:
Hi guys,

Inline images 1

Those IDLE connections, might be because the user/application didn't commit the transaction?

No, IDLE connections means the user (or application) connected and did not disconnect.
IDLE IN TRANSACTION means the user (or application) started a transaction and did not commit (yet).

--
Melvin Davidson
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.
Attachment

Re: Connections - Postgres 9.2

From
"David G. Johnston"
Date:
On Sun, May 15, 2016 at 10:10 PM, Lucas Possamai <drum.lucas@gmail.com> wrote:
Hi guys,

Inline images 1

Those IDLE connections, might be because the user/application didn't commit the transaction?

​No, that is what the yellow-ish "Idle in transaction" would indicate.

Idle is simply remote clients actively logged into the server but not doing anything.

David J.
​

Attachment

Re: Connections - Postgres 9.2

From
Sridhar N Bamandlapally
Date:
Hi

I control this way

if "state_change" is from longtime and "state" is idle then I use

function:
  pg_terminate_backend ( integer )  ==> return TRUE if killed-successful else FALSE

example:
# select pg_terminate_backend ( pid ) from pg_stat_activity where state='idle' and state_change < (current_timestamp - interval '1 hour');

may be helpful

NOTE: we come to decision to kill sessions in idle state more then "1 hour" after lot of discussion with application / implementation / stake-holders team

*removed history as thrown error due to mail length


Thanks
Sridhar 
OpenText


Re: Connections - Postgres 9.2

From
Sridhar N Bamandlapally
Date:
Hi


I control this way


if "state_change" is from longtime and "state" is idle then I use

function:
  pg_terminate_backend ( integer )  ==> return TRUE if killed-successful else FALSE

example:
# select pg_terminate_backend ( pid ) from pg_stat_activity where state='idle' and state_change < (current_timestamp - interval '1 hour');


may be helpful

NOTE: we come to decision to kill sessions in idle state more then "1 hour" after lot of discussion with application / implementation / stake-holders team


Thanks
Sridhar 
OpenText



On Mon, May 16, 2016 at 9:57 PM, David G. Johnston <david.g.johnston@gmail.com> wrote:
On Sun, May 15, 2016 at 10:10 PM, Lucas Possamai <drum.lucas@gmail.com> wrote:
Hi guys,

Inline images 1

Those IDLE connections, might be because the user/application didn't commit the transaction?

​No, that is what the yellow-ish "Idle in transaction" would indicate.

Idle is simply remote clients actively logged into the server but not doing anything.

David J.
​


Attachment

Re: Connections - Postgres 9.2

From
Lucas Possamai
Date:


On 17 May 2016 at 22:24, Sridhar N Bamandlapally <sridhar.bn1@gmail.com> wrote:
Hi

I control this way

if "state_change" is from longtime and "state" is idle then I use

function:
  pg_terminate_backend ( integer )  ==> return TRUE if killed-successful else FALSE

example:
# select pg_terminate_backend ( pid ) from pg_stat_activity where state='idle' and state_change < (current_timestamp - interval '1 hour');

may be helpful

NOTE: we come to decision to kill sessions in idle state more then "1 hour" after lot of discussion with application / implementation / stake-holders team

*removed history as thrown error due to mail length


Thanks
Sridhar 
OpenText



Hi Sridhar!

Thanks for your help...

I also control the same way.. But was wondering if a pooler would bring me performance improvements...


cheers