pg.ddx.io  pgsql-php@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Unwanted persisting connections
3+ messages / 3 participants
[nested] [flat]

* Unwanted persisting connections
@ 2005-11-21 04:06  Gary Hoffman <ghoffman@ucsd.edu>
  0 siblings, 1 reply; 3+ messages in thread

From: Gary Hoffman @ 2005-11-21 04:06 UTC (permalink / raw)
  To: pgsql-php

I've run PHP and PostgreSQL for quite a while and never seen this problem
before. I think it's new, but maybe I never paid attention to it before.

I am frequently running out of connections to the database. Here's a
snippet of my ps -ax

 1882  ??  S      0:00.07 postgres: postgres cgssd_members [local] idle   
    
 1883  ??  S      0:00.09 postgres: postgres cgssd_members [local] idle   
    
 1884  ??  S      0:00.07 postgres: postgres cgssd_members [local] idle   
    
 1886  ??  S      0:00.14 postgres: postgres cgssd_members [local] idle   
    
 1887  ??  S      0:00.06 postgres: postgres cgssd_members [local] idle   
    
  500 std- S      0:01.14 /usr/local/bin/postmaster -D
/usr/local/pgsql/data
  501 std- S      0:00.02 postgres: stats buffer process                  
    
  502 std- S      0:00.21 postgres: stats collector process               
    

As you can see, there are some idle connections that have come in from PHP
on my web pages. I've never seen these before and eventually they build up
and I get an error connecting from the PHP page to the database.

I'm running Apache 1.3.33 on Darwin Kernal 7.9.0 (MacOS X 10.3.9) with PHP
4.3.6 and PostgreSQL 7.4.2.

Ideas and suggestions are welcome.

Thanks,
Gary

**************************************************************************
* Gary B. Hoffman, Computing Services Manager  e-mail: ghoffman@ucsd.edu *
* Graduate School of International Relations and Pacific Studies (IR/PS) *
* University of California, San Diego (UCSD)       voice: (858) 534-1989 *
* 9500 Gilman Dr.  MC 0519                           fax: (858) 534-3939 *
* La Jolla, CA 92093-0519 USA                 web: http://irps.ucsd.edu/ *
**************************************************************************







^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: Unwanted persisting connections
@ 2005-11-21 08:58  Andrew McMillan <andrew@catalyst.net.nz>
  parent: Gary Hoffman <ghoffman@ucsd.edu>
  0 siblings, 1 reply; 3+ messages in thread

From: Andrew McMillan @ 2005-11-21 08:58 UTC (permalink / raw)
  To: Gary Hoffman <ghoffman@ucsd.edu>; +Cc: pgsql-php

On Sun, 2005-11-20 at 20:06 -0800, Gary Hoffman wrote:
> I've run PHP and PostgreSQL for quite a while and never seen this problem
> before. I think it's new, but maybe I never paid attention to it before.
> 
> I am frequently running out of connections to the database. Here's a
> snippet of my ps -ax
> 
>  1882  ??  S      0:00.07 postgres: postgres cgssd_members [local] idle   
>     
>  1883  ??  S      0:00.09 postgres: postgres cgssd_members [local] idle   
>     
>  1884  ??  S      0:00.07 postgres: postgres cgssd_members [local] idle   
>     
>  1886  ??  S      0:00.14 postgres: postgres cgssd_members [local] idle   
>     
>  1887  ??  S      0:00.06 postgres: postgres cgssd_members [local] idle   
>     
>   500 std- S      0:01.14 /usr/local/bin/postmaster -D
> /usr/local/pgsql/data
>   501 std- S      0:00.02 postgres: stats buffer process                  
>     
>   502 std- S      0:00.21 postgres: stats collector process               
>     
> 
> As you can see, there are some idle connections that have come in from PHP
> on my web pages. I've never seen these before and eventually they build up
> and I get an error connecting from the PHP page to the database.
> 
> I'm running Apache 1.3.33 on Darwin Kernal 7.9.0 (MacOS X 10.3.9) with PHP
> 4.3.6 and PostgreSQL 7.4.2.

You will have as many of these as you have apache processes.  That's
what persistent connections means!

If you have MaxClients set to (e.g.) 150 in your Apache configuration,
and you actually get to something like that, then the default PostgreSQL
configuration of 100 clients simply won't be enough.  You should
increase it to at leas 20-50 more than the MaxClients setting that
Apache has.

You'll be pleased to know that each extra PostgreSQL connection
typically uses less than each additional Apache connection (especially
when you're running PHP et al.) so if you find yourself running out of
RAM you will need to lower the MaxClients setting first.

Regards,
					Andrew McMillan.

-------------------------------------------------------------------------
Andrew @ Catalyst .Net .NZ  Ltd,  PO Box 11-053, Manners St,  Wellington
WEB: http://catalyst.net.nz/            PHYS: Level 2, 150-154 Willis St
DDI: +64(4)803-2201      MOB: +64(272)DEBIAN      OFFICE: +64(4)499-2267
     You will be a winner today.  Pick a fight with a four-year-old.
-------------------------------------------------------------------------

Attachments:

  signature.asc (application/pgp-signature, 188B) download

^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: Unwanted persisting connections
@ 2005-11-21 21:13  Philip Hallstrom <postgresql@philip.pjkh.com>
  parent: Andrew McMillan <andrew@catalyst.net.nz>
  0 siblings, 0 replies; 3+ messages in thread

From: Philip Hallstrom @ 2005-11-21 21:13 UTC (permalink / raw)
  To: Andrew McMillan <andrew@catalyst.net.nz>; +Cc: Gary Hoffman <ghoffman@ucsd.edu>; pgsql-php

On Mon, 21 Nov 2005, Andrew McMillan wrote:

> On Sun, 2005-11-20 at 20:06 -0800, Gary Hoffman wrote:
>> I've run PHP and PostgreSQL for quite a while and never seen this problem
>> before. I think it's new, but maybe I never paid attention to it before.
>>
>> I am frequently running out of connections to the database. Here's a
>> snippet of my ps -ax
>>
>>  1882  ??  S      0:00.07 postgres: postgres cgssd_members [local] idle
>>
>>  1883  ??  S      0:00.09 postgres: postgres cgssd_members [local] idle
>>
>>  1884  ??  S      0:00.07 postgres: postgres cgssd_members [local] idle
>>
>>  1886  ??  S      0:00.14 postgres: postgres cgssd_members [local] idle
>>
>>  1887  ??  S      0:00.06 postgres: postgres cgssd_members [local] idle
>>
>>   500 std- S      0:01.14 /usr/local/bin/postmaster -D
>> /usr/local/pgsql/data
>>   501 std- S      0:00.02 postgres: stats buffer process
>>
>>   502 std- S      0:00.21 postgres: stats collector process
>>
>>
>> As you can see, there are some idle connections that have come in from PHP
>> on my web pages. I've never seen these before and eventually they build up
>> and I get an error connecting from the PHP page to the database.
>>
>> I'm running Apache 1.3.33 on Darwin Kernal 7.9.0 (MacOS X 10.3.9) with PHP
>> 4.3.6 and PostgreSQL 7.4.2.
>
> You will have as many of these as you have apache processes.  That's
> what persistent connections means!

You can end up with a *lot* more than apache's MaxClients if you open 
persistent connections with different connection paramaters... so keep 
that in mind if you're connecting to more than one database.

You might look at pgpool. Never used it, but it comes up frequently as a 
solution to this...

>
> If you have MaxClients set to (e.g.) 150 in your Apache configuration,
> and you actually get to something like that, then the default PostgreSQL
> configuration of 100 clients simply won't be enough.  You should
> increase it to at leas 20-50 more than the MaxClients setting that
> Apache has.
>
> You'll be pleased to know that each extra PostgreSQL connection
> typically uses less than each additional Apache connection (especially
> when you're running PHP et al.) so if you find yourself running out of
> RAM you will need to lower the MaxClients setting first.
>
> Regards,
> 					Andrew McMillan.
>
> -------------------------------------------------------------------------
> Andrew @ Catalyst .Net .NZ  Ltd,  PO Box 11-053, Manners St,  Wellington
> WEB: http://catalyst.net.nz/            PHYS: Level 2, 150-154 Willis St
> DDI: +64(4)803-2201      MOB: +64(272)DEBIAN      OFFICE: +64(4)499-2267
>     You will be a winner today.  Pick a fight with a four-year-old.
> -------------------------------------------------------------------------
>
>



^ permalink  raw  reply  [nested|flat] 3+ messages in thread


end of thread, other threads:[~2005-11-21 21:13 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-11-21 04:06 Unwanted persisting connections Gary Hoffman <ghoffman@ucsd.edu>
2005-11-21 08:58 ` Andrew McMillan <andrew@catalyst.net.nz>
2005-11-21 21:13   ` Philip Hallstrom <postgresql@philip.pjkh.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox