Re: FW: URGENT: pgsql on the web server - memory problems....

"Nikolay Mijaylov" <nmmm(at)nmmm(dot)nu> writes:

> Thank for responce.
>
> the php is installed as CGI-BIN executable. I think "keep the connections
> open" option is not applicable here. I do this because every user works
> under its own uid and gid, instead all under "nobody".
>
> i dont have any idea what lsof is...
>
> I will try with netstat, but I was used "ps -axef" and "top".
>

This is what I do on Redhat Linux 6.2, if you are on another platform you
might not have the lsof utility by default.

Use the command :

# netstat -a | grep postgres
tcp 0 0 localhost:postgres localhost:1052 ESTABLISHED
tcp 0 0 localhost:1052 localhost:postgres ESTABLISHED
tcp 0 0 localhost:postgres localhost:1044 ESTABLISHED
tcp 0 0 localhost:1044 localhost:postgres ESTABLISHED
tcp 0 0 localhost:postgres localhost:1035 ESTABLISHED
tcp 0 0 localhost:1035 localhost:postgres ESTABLISHED
tcp 0 0 *:postgres *:* LISTEN

To list all connections to the backend. If you don't have postgres in
/etc/services replace postgres with the portnumber the backend is listening
to. You should see one connection here for every backend running.
>From the output of this command I can see that one of the postgres
clients have connection open at port 1052, so I can use lsof to find out
which process this is :

# /usr/sbin/lsof -i @localhost:1052
COMMAND PID USER FD TYPE DEVICE SIZE NODE NAME
java 855 nobody 33u IPv4 1134 TCP localhost:1052->localhost:po
stgres (ESTABLISHED)
java 856 nobody 33u IPv4 1134 TCP localhost:1052->localhost:po
stgres (ESTABLISHED)
java 857 nobody 33u IPv4 1134 TCP localhost:1052->localhost:po
stgres (ESTABLISHED)
java 858 nobody 33u IPv4 1134 TCP localhost:1052->localhost:po
stgres (ESTABLISHED)
java 859 nobody 33u IPv4 1134 TCP localhost:1052->localhost:po
stgres (ESTABLISHED)
java 860 nobody 33u IPv4 1134 TCP localhost:1052->localhost:po
stgres (ESTABLISHED)
postmaste 871 postgres 5u IPv4 1135 TCP localhost:postgres->localhos
t:1052 (ESTABLISHED)

Here I see that it is my Java servlet process that has the open
connection.[The reason to all the "processes" on this output is because I
use native threads on Linux and this are LWPs].

Similarly you should be able to find the processes that is keeping open
connections to the postgres backend.

Regards,

Gunnar