Обсуждение: pg_stat_activity - proc id

On Sun, Oct 6, 2013 at 7:38 PM, Rajagopalan, Jayashree
<Jayashree.Rajagopalan@emc.com> wrote:
> How to correlate the procpid in pg_stat activity table to any application
> process? I need to track down some connections to the queries/application
> threads. Please help!!

Note down the client_port from pg_stat_activity and run:

netstat -pnao | grep <client_port>

It should give you two lines one of which is the opposite side of the
connection. Look at the next to last column.

--
Kind regards,
Sergey Konoplev
PostgreSQL Consultant and DBA

http://www.linkedin.com/in/grayhemp
+1 (415) 867-9984, +7 (901) 903-0499, +7 (988) 888-1979
gray.ru@gmail.com

we set the application_name to some unique id such as client process ID or processid#threadid when client Start the new session.

jov

Hello,

It's me again..

Log_connection inspection didn't help to find the cause, the connections come from our app-servers as usual..

Btw we have the max pool size of web application 50 connections and since we have 4 instances of application running  it makes max 200.

I don't really get how the database pool gets full when application can use 200 connections max and postrgesql config has set to 250 max connections ?

Autovacuum and master-slave replication shouldn't use many connections also.

Please explain that, maybe i'm missing something.

Should we try to reduce this limit to 40 per application?

Viktor Juhanson <spoon@planet.ee> wrote:

> Btw we have the max pool size of web application 50 connections
> and since we have 4 instances of application running  it makes
> max 200.
>
> I don't really get how the database pool gets full when
> application can use 200 connections max and postrgesql config has
> set to 250 max connections ?

If the client application  (or pooler) doesn't close the database
connections gracefully, it might take some time before the server
figures out the connection is dead and frees the resources on the
server side.  Does the server log say anything about broken
connections or client not responding?

Also, the pooler might maintanin some *minimum* number of
connections but go beyond that "on demand".  Without knowing what
pooler and how it is configured, it's hard to say what might be
going on.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company