Thread: too many clients
After years of running apache-php-postgres with no issues, I'm suddenly receiving the cannot connect to postgres - too many clients already error on my script pages. 1. Does everyone ALWAYS have Apache max connections lower than postgres max clients? I tried this but the problem still returned. 2.I'm using pg_pconnect(). 3. Traffic is slightly higher lately but not that high. 4. I'm thinking about apache changing apache maxrequestsperchild from 0 to maybe 10 or something to periodically kill apache children, but I can't see why the maxclients in apache would overload postgres if they are both the same number. It is hard fro me to believe there are actually that many simultaneous users of this system, so for some reason the connections are remaining open and unused. Any wisdom out there? Matt
Mey Matt, You got a couple solutions, one of which is getting a connection pooler setup between apache(php) and postgres. SQL Relay (sqlrelay.sf.net) could be a common candidate, as I have used it in the past with great results. Another option could be to implement caching on the webserver of common, static data, by using either a database abstraction library such as ADOdb (adodb.sf.net) that can cache queries, or to implement a RAM-based caching solution such as memcached (www.danga.com/memcached). Some people tell me that persistent connections are actually bad, and to always use pg_connect. I cannot vouch for this approach, but you might want to try it to see if it helps you in your particular situation. I'm rolling out a site that gets >5M page views daily in a couple weeks, so this should be a good opportunity to get some detailed real-world performance metrics. - Mitch On Wed, 29 Dec 2004 10:05:18 -0500, Matthew Terenzio <webmaster@localnotion.com> wrote: > After years of running apache-php-postgres with no issues, I'm suddenly > receiving the cannot connect to postgres - too many clients already > error on my script pages. > > 1. Does everyone ALWAYS have Apache max connections lower than postgres > max clients? I tried this but the problem still returned. > > 2.I'm using pg_pconnect(). > > 3. Traffic is slightly higher lately but not that high. > > 4. I'm thinking about apache changing apache maxrequestsperchild from 0 > to maybe 10 or something to periodically kill apache children, but I > can't see why the maxclients in apache would overload postgres if they > are both the same number. It is hard fro me to believe there are > actually that many simultaneous users of this system, so for some > reason the connections are remaining open and unused. > > Any wisdom out there? > > Matt > > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org >
On Dec 29, 2004, at 11:38 AM, Mitch Pirtle wrote: > You got a couple solutions, one of which is getting a connection > pooler setup between apache(php) and postgres. SQL Relay > (sqlrelay.sf.net) could be a common candidate, as I have used it in > the past with great results. Yes, I have taken a look at that before. It might be a good time to try it. I don't exactly understand the technical difference between pooled connections and persistent ones. pg_pconnect() is grabbing an existing open connection if one is available. How is a connection pool more efficient in doing something along these lines? Thanks, Matt
On Wed, 29 Dec 2004 12:12:13 -0500, Matthew Terenzio
<webmaster@localnotion.com> wrote:
>
> On Dec 29, 2004, at 11:38 AM, Mitch Pirtle wrote:
>
> I don't exactly understand the technical difference between pooled
> connections and persistent ones.
>
> pg_pconnect() is grabbing an existing open connection if one is
> available.
It is, but I am told that it is not really very efficient, and so
pg_connect will actually give you better performance on heavy-load
sites. Again, I've heard this but cannot substantiate.
> How is a connection pool more efficient in doing something along these
> lines?
The pool is specifically designed to do just that - manage connections
and keep existing ones maintained - so it is lightweight and fast.
You can get more info here:
http://sqlrelay.sourceforge.net/sqlrelay/faq.html
I've used it for Oracle-powered sites, as well as putting something
sane between zope/plone and both Oracle and MS SQL.
-- Mitch
At 12:12 PM 12/29/04, Matthew Terenzio wrote:
>On Dec 29, 2004, at 11:38 AM, Mitch Pirtle wrote:
>
>>You got a couple solutions, one of which is getting a connection
>>pooler setup between apache(php) and postgres. SQL Relay
>>(sqlrelay.sf.net) could be a common candidate, as I have used it in
>>the past with great results.
>
>Yes, I have taken a look at that before. It might be a good time to try it.
>
>I don't exactly understand the technical difference between pooled
>connections and persistent ones.
>
>pg_pconnect() is grabbing an existing open connection if one is available.
>
>How is a connection pool more efficient in doing something along these lines?
Have you read the php docs on persistent connections?
http://www.php.net/manual/en/features.persistent-connections.php
pg_pconnect is sometimes used in situations where it does not make sense.
As you mentioned, apache uses numerous child process. A persistent
connection can only be reused if the following things are the same:
- same apache child process requests the connection
- same connect string (ie host, database, user are *all* the same)
If you have a site where there are many databases and/or many users, it is
very easy for apache to retain more persistent connections than your
postgres limit. There are many variables when trying to figure out which
function works better, so simply try it on your own system. If pg_connect
does not add significant overhead, and manages to avoid the problems you
mentioned, then use it instead of pg_pconnect.
I haven't heard of pooled connections, but can only assume they somehow
overcome the problems with pg_pconnect gets out of control - perhaps the
connections are shared across apache child processes.
Frank
Per your direction, pg_pConnect is great when you have one site dedicated on one box talking to another dedicated PgSQL box. It does not make sense in mass vhosting environments. Using pg_pConnect greatly reduces execution time in an environment where you're not fighting for resources. I use it on a few very high traffic sites without issue, but it is tuned so that there is only 1 persistant connection per apache backend and postgresql will allow the max apache backends, which by default is generally 256. I highly recommend it in such a situation, while I generally do not recommend it in any other. Gavin On Wed, 2004-12-29 at 13:13 -0500, Frank Bax wrote: > At 12:12 PM 12/29/04, Matthew Terenzio wrote: > > > >On Dec 29, 2004, at 11:38 AM, Mitch Pirtle wrote: > > > >>You got a couple solutions, one of which is getting a connection > >>pooler setup between apache(php) and postgres. SQL Relay > >>(sqlrelay.sf.net) could be a common candidate, as I have used it in > >>the past with great results. > > > >Yes, I have taken a look at that before. It might be a good time to try it. > > > >I don't exactly understand the technical difference between pooled > >connections and persistent ones. > > > >pg_pconnect() is grabbing an existing open connection if one is available. > > > >How is a connection pool more efficient in doing something along these lines? > > > Have you read the php docs on persistent connections? > http://www.php.net/manual/en/features.persistent-connections.php > pg_pconnect is sometimes used in situations where it does not make sense. > > As you mentioned, apache uses numerous child process. A persistent > connection can only be reused if the following things are the same: > - same apache child process requests the connection > - same connect string (ie host, database, user are *all* the same) > > If you have a site where there are many databases and/or many users, it is > very easy for apache to retain more persistent connections than your > postgres limit. There are many variables when trying to figure out which > function works better, so simply try it on your own system. If pg_connect > does not add significant overhead, and manages to avoid the problems you > mentioned, then use it instead of pg_pconnect. > > I haven't heard of pooled connections, but can only assume they somehow > overcome the problems with pg_pconnect gets out of control - perhaps the > connections are shared across apache child processes. > > Frank > > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org
"Gavin M. Roy" <gmr@ehpg.net> writes: > I use it on a few very high traffic sites without issue, but it is tuned so > that there is only 1 persistant connection per apache backend and postgresql > will allow the max apache backends, which by default is generally 256. I > highly recommend it in such a situation, while I generally do not recommend > it in any other. That doesn't sound reasonable. Does your machine really have so many processors or i/o bandwidth that 256 postgres processes can really all make progress? Or do you have images and static html on the same web server? If so I suggest moving them to another web server. No need to have a postgres instance (and a php instance) sitting idle consuming memory waiting until someone happens to hit a dynamic page. -- greg
My primary setup that I use this in is an back-end for an application that uses XML over HTTP to talk to a PHP application using PgSQL. It's run in production for a long time, on modest hardware (dual xeon, 4 gigs ram, raid 5 ultra 160 drives - pgsql that is). On Thu, 2004-12-30 at 00:20 -0500, Greg Stark wrote: > "Gavin M. Roy" <gmr@ehpg.net> writes: > > > I use it on a few very high traffic sites without issue, but it is tuned so > > that there is only 1 persistant connection per apache backend and postgresql > > will allow the max apache backends, which by default is generally 256. I > > highly recommend it in such a situation, while I generally do not recommend > > it in any other. > > That doesn't sound reasonable. Does your machine really have so many > processors or i/o bandwidth that 256 postgres processes can really all make > progress? > > Or do you have images and static html on the same web server? If so I suggest > moving them to another web server. No need to have a postgres instance (and a > php instance) sitting idle consuming memory waiting until someone happens to > hit a dynamic page. >
> > > On Thu, 2004-12-30 at 00:20 -0500, Greg Stark wrote: >> "Gavin M. Roy" <gmr@ehpg.net> writes: >> >>> I use it on a few very high traffic sites without issue, but it is >>> tuned so >>> that there is only 1 persistant connection per apache backend and >>> postgresql >>> will allow the max apache backends, which by default is generally >>> 256. I >>> highly recommend it in such a situation, while I generally do not >>> recommend >>> it in any other. So if I have one Postgres installation but two databases, is it possible that PHP would create twice as many persistant connections as apache childs? This may be where my understanding went awry. > > ---------------------------(end of > broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster
At 09:03 AM 12/30/04, Matthew Terenzio wrote: >>On Thu, 2004-12-30 at 00:20 -0500, Greg Stark wrote: >>>"Gavin M. Roy" <gmr@ehpg.net> writes: >>> >>>>I use it on a few very high traffic sites without issue, but it is tuned so >>>>that there is only 1 persistant connection per apache backend and >>>>postgresql >>>>will allow the max apache backends, which by default is generally 256. I >>>>highly recommend it in such a situation, while I generally do not recommend >>>>it in any other. > >So if I have one Postgres installation but two databases, is it possible >that PHP would create twice as many persistant connections as apache >childs? This may be where my understanding went awry. Exactly right. But consider that if you used one username in connect string for read only access and a different username for admin updates, the number of persistent connections would double again!
Yes, php opens 1 persistant connection per apache backend per database. On Thu, 2004-12-30 at 09:03 -0500, Matthew Terenzio wrote: > > > > > > On Thu, 2004-12-30 at 00:20 -0500, Greg Stark wrote: > >> "Gavin M. Roy" <gmr@ehpg.net> writes: > >> > >>> I use it on a few very high traffic sites without issue, but it is > >>> tuned so > >>> that there is only 1 persistant connection per apache backend and > >>> postgresql > >>> will allow the max apache backends, which by default is generally > >>> 256. I > >>> highly recommend it in such a situation, while I generally do not > >>> recommend > >>> it in any other. > So if I have one Postgres installation but two databases, is it > possible that PHP would create twice as many persistant connections as > apache childs? This may be where my understanding went awry. > > > > ---------------------------(end of > > broadcast)--------------------------- > > TIP 4: Don't 'kill -9' the postmaster >
Wrong - if username changes, another connection to same database is used within the same apache child process. Everything in the connect string must be the same before a persistent connection is reused. At 01:45 PM 12/30/04, Gavin M. Roy wrote: >Yes, php opens 1 persistant connection per apache backend per database. > >On Thu, 2004-12-30 at 09:03 -0500, Matthew Terenzio wrote: > > > > > > > > > On Thu, 2004-12-30 at 00:20 -0500, Greg Stark wrote: > > >> "Gavin M. Roy" <gmr@ehpg.net> writes: > > >> > > >>> I use it on a few very high traffic sites without issue, but it is > > >>> tuned so > > >>> that there is only 1 persistant connection per apache backend and > > >>> postgresql > > >>> will allow the max apache backends, which by default is generally > > >>> 256. I > > >>> highly recommend it in such a situation, while I generally do not > > >>> recommend > > >>> it in any other. > > So if I have one Postgres installation but two databases, is it > > possible that PHP would create twice as many persistant connections as > > apache childs? This may be where my understanding went awry. > > > > > > ---------------------------(end of > > > broadcast)--------------------------- > > > TIP 4: Don't 'kill -9' the postmaster > > > > >---------------------------(end of broadcast)--------------------------- >TIP 6: Have you searched our list archives? > > http://archives.postgresql.org