Hi!
I have following problem:

I use php with postgres as backend. Every time, I run some queries,
postgres creates a lot of processes - this causes extremely high processor
usage. 
I execute some queries in quasi-parallel way, cause I need its results.
But other are executing and every time I free result - all of those
queries are executing on the same connection. So is it normal, that I get
so much processes? And if there is some way to limit it? Or may be change
process live time?
	TIA
	Rem


-------------------------------------------------------------------*------------
Remigiusz Sokolowski      e-mail: rems@gdansk.sprint.pl           * *		
-----------------------------------------------------------------*****----------


Fri, 11 Jun 1999 10:28:57 +0200 (MET DST)
Remigiusz Sokolowski <rems@gdansk.sprint.pl>
postgres processes
Remigiusz Sokolowski wrote:

>
> Hi!
> I have following problem:
>
> I use php with postgres as backend. Every time, I run some queries,
> postgres creates a lot of processes - this causes extremely high processor
> usage.
> I execute some queries in quasi-parallel way, cause I need its results.
> But other are executing and every time I free result - all of those
> queries are executing on the same connection. So is it normal, that I get
> so much processes? And if there is some way to limit it? Or may be change
> process live time?

    That's  a  general  problem  when  using  PostgreSQL  in  the
    background for Apache CGI or php scripts.

    The defaults in the Apache configuration are

      StartServers          5
      MaxClients            256
      MinSpareServers       5
      MaxSpareServers       10

    This means, that at  startup  Apache  will  create  5  server
    processes  that  can handle requests simultaneously. When the
    site gets busy  and  some  of  them  take  longer  to  handle
    requests  (especially  scripting requests), it will start new
    servers (max one per second) until the limit of 256  parallel
    server  processes  is  reached. If they finish their requests
    and become idle again, some of them get killed if  there  are
    more than 10 idle Apache processes.

    This  is  normally  a good policy. It ensures that small file
    requests can still get served while some long  running  CGI's
    block their server process.

    In  the case of having PostgreSQL in the background, any such
    CGI request causes another backend to  get  started  too.  So
    when there's a peak of such requests, PostgreSQL will have to
    serve more parallel queries, Apache will start  more  servers
    to  handle  the  incoming  requests,  causing more PostgreSQL
    connections and more parallel queries...

    What you can try is to take down the MaxClients directive  in
    the  Apache  configuration. But that would mean, that a plain
    html file request, that could be served in milliseconds, will
    have to wait if all servers are blocked waiting for CGI's.


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#========================================= wieck@debis.com (Jan Wieck) #



Fri, 11 Jun 1999 12:44:05 +0200 (MET DST)
wieck@debis.com (Jan Wieck)
Re: [HACKERS] postgres processes
>     The defaults in the Apache configuration are
> 
>       StartServers          5
>       MaxClients            256
>       MinSpareServers       5
>       MaxSpareServers       10
> 
>     This means, that at  startup  Apache  will  create  5  server
>     processes  that  can handle requests simultaneously. When the
>     site gets busy  and  some  of  them  take  longer  to  handle
>     requests  (especially  scripting requests), it will start new
>     servers (max one per second) until the limit of 256  parallel
>     server  processes  is  reached. If they finish their requests
>     and become idle again, some of them get killed if  there  are
>     more than 10 idle Apache processes.
> 
>     This  is  normally  a good policy. It ensures that small file
>     requests can still get served while some long  running  CGI's
>     block their server process.


My problem is, that server is used not only as database server, but also
(and in general) as mail server - I think that tehre are some other
services too.
I've used persistent connections to database (and I think I now understand
why so big processor usage), so postgres processes haven't die after
serve requests but wait for another. 
Hmm... I have one question more - every postgres process takes about 5% of
processor time ( I've used to measure top command ) - it is normal or may
be processor is too slow?
	Rem


Fri, 11 Jun 1999 13:45:57 +0200 (MET DST)
Remigiusz Sokolowski <rems@gdansk.sprint.pl>
Re: [HACKERS] postgres processes

   From: Remigiusz Sokolowski <rems@gdansk.sprint.pl>
   cc: pgsql-general@postgreSQL.org, pgsql-hackers@postgreSQL.org
   Content-Type: TEXT/PLAIN; charset=US-ASCII
   Sender: owner-pgsql-general@postgreSQL.org
   Precedence: bulk

   >     The defaults in the Apache configuration are
   > 
   >       StartServers          5
   >       MaxClients            256
   >       MinSpareServers       5
   >       MaxSpareServers       10
   > 
   >     This means, that at  startup  Apache  will  create  5  server
   >     processes  that  can handle requests simultaneously. When the
   >     site gets busy  and  some  of  them  take  longer  to  handle
   >     requests  (especially  scripting requests), it will start new
   >     servers (max one per second) until the limit of 256  parallel
   >     server  processes  is  reached. If they finish their requests
   >     and become idle again, some of them get killed if  there  are
   >     more than 10 idle Apache processes.
   > 
   >     This  is  normally  a good policy. It ensures that small file
   >     requests can still get served while some long  running  CGI's
   >     block their server process.


   My problem is, that server is used not only as database server, but also
   (and in general) as mail server - I think that tehre are some other
   services too.
   I've used persistent connections to database (and I think I now understand
   why so big processor usage), so postgres processes haven't die after
   serve requests but wait for another. 
   Hmm... I have one question more - every postgres process takes about 5% of
   processor time ( I've used to measure top command ) - it is normal or may
   be processor is too slow?
	   Rem



We use a similar configuration, and initially had similar problems.
We just don't use persistent connections in php anymore, and things
work fine - In our case, the number of reconnects saved by pconnect
would be small anyway.

Another strategy would be to start a second apache server on a
different port or different machine, use it only for redirects to the
pages that call postgres (assuming this is not your whole site).  Then
throttle the second server back as described above (we haven't
actually done this - but it seems it should work).

-- 
Karl DeBisschop <kdebisschop@spaceheater.infoplease.com>
617.832.0332 (Fax: 617.956.2696)

Information Please - your source for FREE online reference
http://www.infoplease.com  - Your Ultimate Fact Finder
http://kids.infoplease.com - The Great Homework Helper

Fri, 11 Jun 1999 09:02:41 -0400
Karl DeBisschop <kdebisschop@spaceheater.infoplease.com>
Re: [GENERAL] Re: [HACKERS] postgres processes
Karl DeBisschop wrote:

>    From: Remigiusz Sokolowski <rems@gdansk.sprint.pl>
>
>    My problem is, that server is used not only as database server, but also
>    (and in general) as mail server - I think that tehre are some other
>    services too.
>    I've used persistent connections to database (and I think I now understand
>    why so big processor usage), so postgres processes haven't die after
>    serve requests but wait for another.
>    Hmm... I have one question more - every postgres process takes about 5% of
>    processor time ( I've used to measure top command ) - it is normal or may
>    be processor is too slow?
>
> We use a similar configuration, and initially had similar problems.
> We just don't use persistent connections in php anymore, and things
> work fine - In our case, the number of reconnects saved by pconnect
> would be small anyway.
>
> Another strategy would be to start a second apache server on a
> different port or different machine, use it only for redirects to the
> pages that call postgres (assuming this is not your whole site).  Then
> throttle the second server back as described above (we haven't
> actually done this - but it seems it should work).

    I  don't know anything about the internals of php, but I have
    similar problems with an Apache module I'm (still) writing.

    Using Apache 1.3.3 I've tried to use  persistent  connections
    to  a  content  server.  The  methods  available for a module
    include a call AtExit, which is called just before the actual
    Apache  server  process  will  be  terminated  (due to Apache
    shutdown or because too many spare servers).

    But there's nothing that tells about a timeout. In  the  case
    of  a  communication  or  scripting  timeout,  Apache  does a
    longjmp() back into the mainloop and clears the pool.  That's
    the  reason  why  sockets, files and memory allocations under
    Apache should be done via the Apache pool utilities, and  not
    using   the  low  level  functions  (as  in  PostgreSQL  with
    palloc()).

    It MIGHT be the case, that php has that  problem  when  using
    persistent  database connections and that after a timeout, an
    unclosed connection is hanging around causing another hanging
    PostgreSQL backend.


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#========================================= wieck@debis.com (Jan Wieck) #



Mon, 14 Jun 1999 12:10:01 +0200 (MET DST)
wieck@debis.com (Jan Wieck)
Re: [GENERAL] Re: [HACKERS] postgres processes
On Mon, Jun 14, 1999 at 12:10:01PM +0200, Jan Wieck wrote:
> Karl DeBisschop wrote:
> 
> >    From: Remigiusz Sokolowski <rems@gdansk.sprint.pl>
> >
> >    My problem is, that server is used not only as database server, but also
> >    (and in general) as mail server - I think that tehre are some other
> >    services too.
> >    I've used persistent connections to database (and I think I now understand
> >    why so big processor usage), so postgres processes haven't die after
> >    serve requests but wait for another.
> >    Hmm... I have one question more - every postgres process takes about 5% of
> >    processor time ( I've used to measure top command ) - it is normal or may
> >    be processor is too slow?
> >
> > We use a similar configuration, and initially had similar problems.
> > We just don't use persistent connections in php anymore, and things
> > work fine - In our case, the number of reconnects saved by pconnect
> > would be small anyway.
> >
> > Another strategy would be to start a second apache server on a
> > different port or different machine, use it only for redirects to the
> > pages that call postgres (assuming this is not your whole site).  Then
> > throttle the second server back as described above (we haven't
> > actually done this - but it seems it should work).
> 
>     I  don't know anything about the internals of php, but I have
>     similar problems with an Apache module I'm (still) writing.
> 
>     Using Apache 1.3.3 I've tried to use  persistent  connections
>     to  a  content  server.  The  methods  available for a module
>     include a call AtExit, which is called just before the actual
>     Apache  server  process  will  be  terminated  (due to Apache
>     shutdown or because too many spare servers).

PHP3's module version uses the standard ap_register_cleanup()
call to register its shutdown function. This function calls then
all PHP module specific shutdown functions, so that PostgreSQL
persistent connection should be closed properly.

I don't know of any situation where Apache would not call these
registered handlers (I'm not familiar with the Apache
internals...). Additionally, I've been running a site for a
client for about two years now which uses PHP and persistent
connections to PostgreSQL heavily - I don't think we have ever
seen such problems as described previously.

> 
>     But there's nothing that tells about a timeout. In  the  case
>     of  a  communication  or  scripting  timeout,  Apache  does a
>     longjmp() back into the mainloop and clears the pool.  That's
>     the  reason  why  sockets, files and memory allocations under
>     Apache should be done via the Apache pool utilities, and  not
>     using   the  low  level  functions  (as  in  PostgreSQL  with
>     palloc()).
> 
>     It MIGHT be the case, that php has that  problem  when  using
>     persistent  database connections and that after a timeout, an
>     unclosed connection is hanging around causing another hanging
>     PostgreSQL backend.
> 
> 
> Jan
> 
> --
> 
> #======================================================================#
> # It's easier to get forgiveness for being wrong than for being right. #
> # Let's break this rule - forgive me.                                  #
> #========================================= wieck@debis.com (Jan Wieck) #
> 
> 
> 

-- 

          Regards,

                            Sascha Schumann
                                 Consultant

Mon, 14 Jun 1999 13:05:31 +0200
Sascha Schumann <sascha@schumann.2ns.de>
Re: [GENERAL] Re: [HACKERS] postgres processes