Обсуждение: Freeing Connections
Hi, Sorry if this has been covered before, but I haven't been able to find any information about it. I'm running PostgreSQL 7.0.3 with the following command: postmaster -i -D/var/pgsql/data -N 32 -B 64 Normally, 32 connections is heaps for what I need. However, I often get connections that seem to be doing nothing. For example: [bash] \_ postmaster -i -D/var/pgsql/data -N 32 -B 64 \_ [postmaster] \_ /var/pgsql/bin/postgres 202.174.32.67 postgres anb idle \_ /var/pgsql/bin/postgres 202.174.32.68 postgres anb idle \_ [postmaster] \_ /var/pgsql/bin/postgres 203.34.190.137 postgres bmf idle \_ [postmaster] \_ [postmaster] \_ [postmaster] \_ [postmaster] \_ /var/pgsql/bin/postgres 202.174.32.8 radius bmf idle \_ /var/pgsql/bin/postgres 203.34.190.137 postgres bmf idle \_ /var/pgsql/bin/postgres 202.174.32.66 postgres bmf idle \_ /var/pgsql/bin/postgres 203.34.190.137 postgres bmf idle \_ /var/pgsql/bin/postgres 203.34.190.137 postgres bmf idle \_ /var/pgsql/bin/postgres localhost postgres bmf idle \_ /var/pgsql/bin/postgres 202.174.32.66 postgres bmf idle \_ logger -p local5.notice The "idle" connections are from boxes that are using PHP's pg_pconnect to connect to the database, so that's no problem. However, it the [postmaster] entries that worry me - over time, these just increase in number until I start getting connections refused. Can anyone please help me with what these are, and how to stop them accumulating? TIA! -- Andrew Hill "RAID - Don't believe the hype." -- 2001-09-22
Andrew Hill <list@fornax.net> writes:
> Normally, 32 connections is heaps for what I need. However, I often get
> connections that seem to be doing nothing. For example:
> [bash]
> \_ postmaster -i -D/var/pgsql/data -N 32 -B 64
> \_ [postmaster]
> \_ /var/pgsql/bin/postgres 202.174.32.67 postgres anb idle
> \_ /var/pgsql/bin/postgres 202.174.32.68 postgres anb idle
> \_ [postmaster]
> \_ /var/pgsql/bin/postgres 203.34.190.137 postgres bmf idle
> \_ [postmaster]
> \_ [postmaster]
> \_ [postmaster]
> \_ [postmaster]
> \_ /var/pgsql/bin/postgres 202.174.32.8 radius bmf idle
Curious. Can you attach to some of the unidentified processes with
a debugger, and get a stack traceback from 'em? Offhand I can't
think of a reason for 7.0 to have any subprocesses that haven't
changed their PS display. It would help to know what they are doing.
regards, tom lane
On Thu, Oct 18, 2001 at 01:55:33PM -0400, Tom Lane wrote: > Andrew Hill <list@fornax.net> writes: > > Normally, 32 connections is heaps for what I need. However, I often get > > connections that seem to be doing nothing. For example: > > > [bash] > > \_ postmaster -i -D/var/pgsql/data -N 32 -B 64 > > \_ [postmaster] > > \_ /var/pgsql/bin/postgres 202.174.32.67 postgres anb idle > > \_ /var/pgsql/bin/postgres 202.174.32.68 postgres anb idle > > \_ [postmaster] > > \_ /var/pgsql/bin/postgres 203.34.190.137 postgres bmf idle > > \_ [postmaster] > > \_ [postmaster] > > \_ [postmaster] > > \_ [postmaster] > > \_ /var/pgsql/bin/postgres 202.174.32.8 radius bmf idle > > Curious. Can you attach to some of the unidentified processes with > a debugger, and get a stack traceback from 'em? Offhand I can't > think of a reason for 7.0 to have any subprocesses that haven't > changed their PS display. It would help to know what they are doing. Andrew doesn't mention his platform, but if it's linux, those could just be swapped out processes: since the execution state gets swapped, the kernel only has minimal info about the process, including the original name. I'm guessing that his PHP setup is configured for persistent connections with MAX_CON >32 and isn't reusing connections properly. ISTR some vesion of PHP with exactly that bug. Should be in the mailing list archives. Ross
Ross J. Reedstrom wrote: > Andrew doesn't mention his platform, but if it's linux, It is... > those could just > be swapped out processes: since the execution state gets swapped, the > kernel only has minimal info about the process, including the original > name. I'm guessing that his PHP setup is configured for persistent > connections with MAX_CON >32 and isn't reusing connections properly. > ISTR some vesion of PHP with exactly that bug. Should be in the mailing > list archives. Ah! Interesting. I'll look into this before I come back with the info that Tom asked about. Thanks! Cheers, -- Andrew Hill "RAID - Don't believe the hype." -- 2001-09-22
On Thu, Oct 18, 2001 at 01:55:33PM -0400, Tom Lane wrote: > Andrew Hill <list@fornax.net> writes: > > Normally, 32 connections is heaps for what I need. However, I often get > > connections that seem to be doing nothing. For example: > > > [bash] > > \_ postmaster -i -D/var/pgsql/data -N 32 -B 64 > > \_ [postmaster] > > \_ /var/pgsql/bin/postgres 202.174.32.67 postgres anb idle > > \_ /var/pgsql/bin/postgres 202.174.32.68 postgres anb idle > > \_ [postmaster] > > \_ /var/pgsql/bin/postgres 203.34.190.137 postgres bmf idle > > \_ [postmaster] > > \_ [postmaster] > > \_ [postmaster] > > \_ [postmaster] > > \_ /var/pgsql/bin/postgres 202.174.32.8 radius bmf idle > > Curious. Can you attach to some of the unidentified processes with > a debugger, and get a stack traceback from 'em? Offhand I can't > think of a reason for 7.0 to have any subprocesses that haven't > changed their PS display. It would help to know what they are doing. I believe the processes displayed as [foo] are paged out which is why you don't see their full commandlines. Regards, Lenny -- Lenny Mastrototaro VP System Operations HorizonLive.com lenny@horizonlive.com collaborate . interact . learn (212)813-3867
Ross J. Reedstrom wrote: > ISTR some vesion of PHP with exactly that bug. Should be in the mailing > list archives. Well, I wasn't able to find anything that matched the problem I seem to be having exactly, but restarting the Apache/PHP servers that are connecting to the database does make these processes dissapear. I have turned off persistent connections in the php.ini file, and the number of instances of this problem has gone down - I suspect that there is one machine that is causing the problem, and that a PHP update will solve this issue - I'll let you know when I've had the time to do the update if it fixes it! Thanks, -- Andrew Hill "RAID - Don't believe the hype." -- 2001-09-22
Apologies for replying to my own post, and resurrecting something from ages ago, but I though that people may be interested in what had happened. You may recall that I was seeing a number of processes like this on my linux PostgreSQL server: [bash] \_ postmaster -i -D/var/pgsql/data -N 32 -B 64 \_ [postmaster] etc... It was suggested that PHP persistent connections were the cause of this, but tracking down all of the PHP boxes that used the database server and updating to the latest version of PHP, as well as configuring PHP to not use persistent connections, while helping (i.e. reducing the number of un-named processes), didn't actually solve the problem. However, Ross Reedstrom hit the nail on the head when he said: "...if it's linux, those could just be swapped out processes: since the execution state gets swapped, the kernel only has minimal info about the process, including the original name." I finally had the opportunity to take the PostgreSQL server down over the weekend and dump a considerable amount of extra RAM in the machine, and the problem has dissapeared entirely. Let it never be said that a database server can have too much RAM :-) Thanks for all the help everyone! Cheers, -- Andrew Hill "RAID - Don't believe the hype." -- 2001-09-22