Обсуждение: Too much postmaster prozesses / CPU near 100%

Hi,

first excuse my bad english (my german is better :) ).

we're running a Linux RedHat7.1, Kernel 2.4.2, Postgres 7.1.3,
Apache 1.3.19, PHP 4.0.4
Machine is Dual-Prozessor 2xIntel PIII-900+, 1GB RAM
Connection-Type via php = pconnect

We have a bad performance problem and hope, anybody has a
solution.

If 2 users connects the postmaster forks 30 (!!)
processes, and they do never die.
If 50 Users connects, over 300 postmaster-processes running
and the CPU-States are nearly 100%, so we must stop and
start the postmaster or reboot the server.

IMHO i think, if we use persistant connections, only 1
process per user should be running.

What's wrong here ?

--
Computers are like airconditioners: They stop
working properly if you open windows.
----------------------------------------------------
Michael J. Roth     mailto:MichaelRoth@schmuecker.de
Tel: 069/57005-421, Fax: 069/57005-800
Schmücker&Partner GmbH      http://www.schmuecker.de
Lurgiallee 14, 60539 Frankfurt           
----------------------------------------------------

Michael,

> first excuse my bad english (my german is better :) ).

It's okay -- my German is terrible, so we're even.

> If 2 users connects the postmaster forks 30 (!!)
> processes, and they do never die.
> If 50 Users connects, over 300 postmaster-processes running
> and the CPU-States are nearly 100%, so we must stop and
> start the postmaster or reboot the server.

There are issues with PHP persistent connections and PostgreSQL which
cause exactly the problem you're having.  They are fixable, but involve
some tradeoffs.

Go to the PGSQL-PHP list archives and comb through them; much of the
discussion for the past year has been about persistent. pooled, and
other connection strategies.  See:
http://archives2.us.postgresql.org/pgsql-php/

You also may wish to join the PGSQL-PHP list, as well as the PGSQL-SQL
list.

-Josh


______AGLIO DATABASE SOLUTIONS___________________________
                                       Josh Berkus
  Complete information technology      josh@agliodbs.com
   and data management solutions       (415) 565-7293
  for law firms, small businesses        fax 621-2533
    and non-profit organizations.      San Francisco

Thanks Josh,

but I dont think, that the connection-type is
the Problem.
I dont have found any helpful articles in the
Mailing-List Archive. I am Member in the php-list too :-)
We have rewritten our open-function for postgres.
We have the same Effect, all the same whether we
connect persistant or non-persistant.

Maybe i dont have exactly described our Problem:

With our "old" Server (MMX-233, 256MB RAM, Linux 2.2.16, Postgres 7.0.3)
we dont had any Probs.
After we run the new Server, some hours all things are ok.
And then, suddenly the machine creates many postmaster-processes in
a few seconds. Other System-Functions works now likewise not correct
(top: CPU-Idle 62985,2 %; /etc/init.d/postmaster stop = failed;
but /etc/init.d/postmaster start = postmaster already running;
"/proc not mounted"; "too many open files in system"; <-- examples)
We must "crash" the postmaster (kill -15 or kill-9) and restart apache
and start postmaster again.
And then, all things works correct again for some time (minutes or hours).
(Info: we run vacuumdb 1-2 times a day).

I think, the problem is handling of shared memory either in Postgres 7.1.3
or in Kernel 2.4.x or both.

Don't know how to fix the Prob. Consultants from Hewlett-Packard AND
RedHat don't know too. Info: Our new Server HP-LC2000r is certified
with RedHat7.1 and does NOT run with kernel 2.2.x :-((

Greez,
Michael

-----Ursprüngliche Nachricht-----
Von: Josh Berkus [mailto:josh@agliodbs.com]
Gesendet: Freitag, 26. Oktober 2001 22:06
An: Roth, Michael J.; 'pgsql-novice@postgresql.org'
Betreff: Re: [NOVICE] Too much postmaster prozesses / CPU near 100%

Michael,

> first excuse my bad english (my german is better :) ).

It's okay -- my German is terrible, so we're even.

> If 2 users connects the postmaster forks 30 (!!)
> processes, and they do never die.
> If 50 Users connects, over 300 postmaster-processes running
> and the CPU-States are nearly 100%, so we must stop and
> start the postmaster or reboot the server.

There are issues with PHP persistent connections and PostgreSQL which
cause exactly the problem you're having.  They are fixable, but involve
some tradeoffs.

Go to the PGSQL-PHP list archives and comb through them; much of the
discussion for the past year has been about persistent. pooled, and
other connection strategies.  See:
http://archives2.us.postgresql.org/pgsql-php/

You also may wish to join the PGSQL-PHP list, as well as the PGSQL-SQL
list.

-Josh

______AGLIO DATABASE SOLUTIONS___________________________
                                       Josh Berkus
  Complete information technology      josh@agliodbs.com
   and data management solutions       (415) 565-7293
  for law firms, small businesses        fax 621-2533
    and non-profit organizations.      San Francisco

Michael,

> Maybe i dont have exactly described our Problem:
>
> With our "old" Server (MMX-233, 256MB RAM, Linux 2.2.16, Postgres
> 7.0.3)
> we dont had any Probs.
> After we run the new Server, some hours all things are ok.
> And then, suddenly the machine creates many postmaster-processes in
> a few seconds. Other System-Functions works now likewise not correct
> (top: CPU-Idle 62985,2 %; /etc/init.d/postmaster stop = failed;
> but /etc/init.d/postmaster start = postmaster already running;
> "/proc not mounted"; "too many open files in system"; <-- examples)
> We must "crash" the postmaster (kill -15 or kill-9) and restart
> apache
> and start postmaster again.
> And then, all things works correct again for some time (minutes or
> hours).
> (Info: we run vacuumdb 1-2 times a day).

Hmmm ... double-check your postgresql.conf parameters, and log at a high
debug level when you start up.  I once had a similar problem to this (on
7.1.0) that was a result of bad (conflicting) postgresql.conf parameters
I had set.  PostgreSQL kept starting and spawning extra processes, but
was not accessable from psql.

> I think, the problem is handling of shared memory either in Postgres
> 7.1.3
> or in Kernel 2.4.x or both.

I doubt it's that simple, although you may check your *specific* kernel
version ... the reason why 2.4.x has been upgraded so many times in the
last 7 months is to fix bugs.  I have been using 2.4.4. with no
problems.  So, steps from here:

1. Restart the postgresql server at a high debug level, checking the
logs carefully for bootup and/or broken connection errors.
2. Re-install the server, reverting to 2.2.18 to see if it's a
kernel-specific problem.  Or upgrade to 2.4.13, either way.
3. Contact SuSE professional services to see if they can help you.
They're expensive, but good and you won't have the language barrier.

> Don't know how to fix the Prob. Consultants from Hewlett-Packard AND
> RedHat don't know too. Info: Our new Server HP-LC2000r is certified
> with RedHat7.1 and does NOT run with kernel 2.2.x :-((

Hmmm.  Have you tried getting RedHat to refer you to their RHDB
division?  Those people should be intimately familiar with PostgreSQL.
I wouldn't expect HP to know anything, myself.

-Josh Berkus


______AGLIO DATABASE SOLUTIONS___________________________
                                       Josh Berkus
  Complete information technology      josh@agliodbs.com
   and data management solutions       (415) 565-7293
  for law firms, small businesses        fax 621-2533
    and non-profit organizations.      San Francisco

"Roth, Michael J." <MichaelRoth@schmuecker.de> writes:
> After we run the new Server, some hours all things are ok.
> And then, suddenly the machine creates many postmaster-processes in
> a few seconds. Other System-Functions works now likewise not correct
> (top: CPU-Idle 62985,2 %; /etc/init.d/postmaster stop =3D failed;
> but /etc/init.d/postmaster start =3D postmaster already running;=20
> "/proc not mounted"; "too many open files in system"; <-- examples)

This is just a guess, but --- "too many open files in system" is very
suspicious.  That means you ran out of kernel filetable slots.  Postgres
itself tends to hold up fairly well under conditions of no free
filetable slots, but almost everything else on a Unix system will go to
hell in a handbasket because it's not expecting that error.  I wonder
whether what happened was that you ran out of filetable slots and then
your client software failed because of that, failing in a mode that
resulted in spawning lots of additional database connections.

It'd be worth increasing the kernel filetable size to see if that
alleviates the problem.  I forget how to do that on Linux systems,
but on other Unixen the kernel parameters NFILE and NINODE need to
be increased.

It could be that the out-of-filetable-slots condition is a consequence
of having too many backends, not its cause ... but in any case it seems
clear that you don't have enough filetable slots to support the max
number of connections you've told Postgres to accept.  You should
probably figure on Postgres chewing up 100 or so slots per backend.

            regards, tom lane