Thread: FW: URGENT: pgsql on the web server - memory problems....
-------------------------------------------------------------- The reboots are for hardware upgrades! "http://www.nmmm.nu"; < nmmm@nmmm.nu > -----Original Message----- From: Nikolay Mijaylov [mailto:nmmm@nmmm.nu] Sent: Thursday, October 05, 2000 1:28 PM To: pgsql-general@postgresql.org Subject: URGENT: pgsql on the web server - memory problems.... Hi all I;m not sure how to explain this we have a web server with several clients which uses PostgreSQL. Currently v ersion 7.0.2 the server is linux machine with 64MB RAM and 200 MB swap on the disk. One H DD partition with free disk space about 1.5 GB. i started the server with: postmaster -N 64 -B 256 Unfortunately one of our clients runs wery big database and very big (fuckin ' strange complex) queries using PHP. The problem is that PHP is auto killing after a while (1-2 min), but the pos tgres backend process stay (and works about 2 or many more hours). Soon all Linux is filled with 64 postgres processes, free memory is 0 and Linux, free ze :) These queries freeze our computer and it stops responding. The only way we c an do is to press "reset" button. My question is : is there a way to limit the memory (and CPU time) used by p ostgres processes by our clients. Can we make auto kill like in PHP? I play with ulimit and think it is not a such a good idea.... For a moment I stop this client and do postmaster -N 24 -B 64 ... Any Suggestions? Thank you -------------------------------------------------------------- The reboots are for hardware upgrades! "http://www.nmmm.nu"; < nmmm@nmmm.nu >
"Nikolay Mijaylov" <pg@nmmm.nu> writes:
> The problem is that PHP is auto killing after a while (1-2 min), but the pos
> tgres backend process stay (and works about 2 or many more hours). Soon all
> Linux is filled with 64 postgres processes, free memory is 0 and Linux, free
> ze :)
>
The postgres backend processes should die when the connections are
closed. Are you sure that the process that runs PHP doesn't keep the
connections open ? You should check open ports using netstat -a and maybe
go on using lsof to check the client processes.
regards,
Gunnar
"Nikolay Mijaylov" <nmmm@nmmm.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
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". --------------- p.p. can you told me also how I can use ulimit to limit all postmaster children's memory ? I mean can i "say" lets all pg uses 40 MB RAM (postmaster and children) Nikolay Mijaylov -------------------------------------------------------------- The reboots are for hardware upgrades! "http://www.nmmm.nu"; < nmmm@nmmm.nu > -----Original Message----- From: pgsql-general-owner@hub.org [mailto:pgsql-general-owner@hub.org]On Behalf Of Gunnar R|nning Sent: Thursday, October 05, 2000 4:35 PM To: Nikolay Mijaylov Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] FW: URGENT: pgsql on the web server - memory problems.... "Nikolay Mijaylov" <pg@nmmm.nu> writes: > The problem is that PHP is auto killing after a while (1-2 min), but the pos > tgres backend process stay (and works about 2 or many more hours). Soon all > Linux is filled with 64 postgres processes, free memory is 0 and Linux, free > ze :) > The postgres backend processes should die when the connections are closed. Are you sure that the process that runs PHP doesn't keep the connections open ? You should check open ports using netstat -a and maybe go on using lsof to check the client processes. regards, Gunnar