Connections - Postgres 9.2

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 without a commit, since they do nothing, but I am not sure that you can assume that all connections are uncommitted transactions.

Bye
Charles

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

Hi guys,

[image: 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.
​

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

Hi guys,

[image: 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.

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.

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

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.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

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

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

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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:

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

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