Обсуждение: PGSQL-IDLE connection problem

Hi,

    we had problem with <IDLE> connections.when we start the database there huge number of connections goes to <IDLE> which leads to database access slow.
   How can we prevent the <IDLE> connections and also where we can see the <IDLE> connection query so that we can identify.

Em 28-01-2013 07:12, Sathish Reddy Yelala escreveu:
> Hi,
>
>      we had problem with <IDLE> connections.when we start the database
> there huge number of connections goes to <IDLE> which leads to database
> access slow.

These connections are opened by your client application, not by PostgreSQL.

>     How can we prevent the <IDLE> connections and also where we can see
> the <IDLE> connection query so that we can identify.

You should consider looking at your application and how it handles the
connections it opens. What kind of application you have, distributed
clients or application server?

There's nothing to see about connections in <IDLE> state. They are doing
nothing, there's no query or transaction. They are just open connections.

Consider looking at the view pg_stat_activity. There you can see
username, IP and other things about these connections.

Regards,

__________________________________
Flavio Henrique A. Gurgel
Líder de Projetos Especiais
Consultoria, Projetos & Treinamentos 4LINUX
Tel1: +55-11.2125-4747 ou 2125-4748
www.4linux.com.br
email: flavio@4linux.com.br
______________________________
FREE SOFTWARE SOLUTIONS

hi,

    Thanks for the response.Here I have few more queries regarding Connections.

 1.When the connection goes to <IDLE> state
 2. when the <IDLE> connection becomes active 
 3.Is there any chance to database get slow because of <IDLE> connections

    Any help would be appreciated..........

Sathish Reddy Yelala wrote:
>     Thanks for the response.Here I have few more queries regarding Connections.
>
>  1.When the connection goes to <IDLE> state

When the server has completed a request and waits for
the next one.

>  2. when the <IDLE> connection becomes active

When the server receives the next request from the client.

>  3.Is there any chance to database get slow because of <IDLE> connections

It's unlikely, but not impossible, if the idle connections
hog enough system resources to affect the server.
Also a very large value for max_connections might affect
performance of some internal data structures
(see http://wiki.postgresql.org/wiki/Number_Of_Database_Connections).

However, idle connections are not the first place where I would
look for the cause of a performance problem.
Connections "idle in transaction" are more likely to cause trouble.

How big is max_connections?

Yours,
Laurenz Albe