pg.ddx.io pgsql-php@postgresql.org mailing list archive
help / color / mirror / Atom feedToo many clients
15+ messages / 8 participants
[nested] [flat]
* Too many clients
@ 2004-02-27 21:11 Andy Dunlop <andy@infocus.co.za>
0 siblings, 2 replies; 15+ messages in thread
From: Andy Dunlop @ 2004-02-27 21:11 UTC (permalink / raw)
To: pgsql-php
I hope this goes to the right place!
I occasionally get the error "too many clients" when connecting to the
database. I try restarting the Postgres server and also Apache, but the
only thing that clears the problem is restarting Linux. I have maxusers
set to 35, and I am the only user, so I suspect I am not doing something
in my scripts that I should be, or doing something that I shouldn't be!
I have the pg_connect at the top of the script so each time it loads the
page it does a new connect - maybe this is a problem?
Probably a simple solution, anyone know?
Thanks
Andy
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Too many clients
@ 2004-02-27 21:16 Devrim GUNDUZ <devrim@gunduz.org>
parent: Andy Dunlop <andy@infocus.co.za>
1 sibling, 0 replies; 15+ messages in thread
From: Devrim GUNDUZ @ 2004-02-27 21:16 UTC (permalink / raw)
To: Andy Dunlop <andy@infocus.co.za>; +Cc: pgsql-php
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
Hi,
On 27 Feb 2004, Andy Dunlop wrote:
> I occasionally get the error "too many clients" when connecting to the
> database. I try restarting the Postgres server and also Apache, but the
> only thing that clears the problem is restarting Linux. I have maxusers
> set to 35, and I am the only user, so I suspect I am not doing something
> in my scripts that I should be, or doing something that I shouldn't be!
> I have the pg_connect at the top of the script so each time it loads the
> page it does a new connect - maybe this is a problem?
The max_connections parameter of PostgreSQL is the max number of
connections 'at the same time'.
So, if your web site has more than 33 visitors (let's say 2 connections
were "superuser_reserved_connections") at the same time, then you'll get
the error "too many connections". BTW, PHP calls pg_close() at the end of
script execution, if you are using pg_connect ('not pg_pconnect') at the
beginning of the script.
To get rid of that error,
* Make sure that you aren't using pg_pconnect
* Increase max_connections parameter, as well as the parameters related to
it.
Regards,
- --
Devrim GUNDUZ
devrim@gunduz.org devrim.gunduz@linux.org.tr
http://www.TDMSoft.com
http://www.gunduz.org
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.1 (GNU/Linux)
iD8DBQFAP7O2tl86P3SPfQ4RAvnbAKCTRWe1MT/L3ps6TVCIvTduyGozTACffmde
Yp3RnOxG90Q7LawzzhTLyC8=
=+cPt
-----END PGP SIGNATURE-----
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Too many clients
@ 2004-02-27 21:46 scott.marlowe <scott.marlowe@ihs.com>
parent: Andy Dunlop <andy@infocus.co.za>
1 sibling, 0 replies; 15+ messages in thread
From: scott.marlowe @ 2004-02-27 21:46 UTC (permalink / raw)
To: Andy Dunlop <andy@infocus.co.za>; +Cc: pgsql-php
On 27 Feb 2004, Andy Dunlop wrote:
> I hope this goes to the right place!
>
> I occasionally get the error "too many clients" when connecting to the
> database. I try restarting the Postgres server and also Apache, but the
> only thing that clears the problem is restarting Linux. I have maxusers
> set to 35, and I am the only user, so I suspect I am not doing something
> in my scripts that I should be, or doing something that I shouldn't be!
> I have the pg_connect at the top of the script so each time it loads the
> page it does a new connect - maybe this is a problem?
1: Make sure you're not using pg_pconnect, but this doesn't sound like
that's the problem. Just making sure.
2: It sounds like php may be crashing at some point it its execution. Do
you have a log of its output when it's running? I've seen issues where if
you compile apache against one set of libs and php against another, then
apache/php starts having children crash and doesn't close the connections.
It takes a certain amount of time for the timeout to hit on the TCP/IP
layer and for the pg backend to close.
Are you sure you're actually closing down postgresql all the way when you
say you tried restarting the postgresql server? It could be that it's not
shutting all the way down. What does 'ps ax|grep post' say?
^ permalink raw reply [nested|flat] 15+ messages in thread
* too many clients
@ 2004-12-29 15:05 Matthew Terenzio <webmaster@localnotion.com>
0 siblings, 1 reply; 15+ messages in thread
From: Matthew Terenzio @ 2004-12-29 15:05 UTC (permalink / raw)
To: pgsql-php
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
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: too many clients
@ 2004-12-29 16:38 Mitch Pirtle <mitch.pirtle@gmail.com>
parent: Matthew Terenzio <webmaster@localnotion.com>
0 siblings, 2 replies; 15+ messages in thread
From: Mitch Pirtle @ 2004-12-29 16:38 UTC (permalink / raw)
To: Matthew Terenzio <webmaster@localnotion.com>; +Cc: pgsql-php
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
>
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: too many clients
@ 2004-12-29 17:12 Matthew Terenzio <webmaster@localnotion.com>
parent: Mitch Pirtle <mitch.pirtle@gmail.com>
1 sibling, 1 reply; 15+ messages in thread
From: Matthew Terenzio @ 2004-12-29 17:12 UTC (permalink / raw)
To: pgsql-php
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
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: too many clients
@ 2004-12-29 17:21 Mitch Pirtle <mitch.pirtle@gmail.com>
parent: Matthew Terenzio <webmaster@localnotion.com>
0 siblings, 0 replies; 15+ messages in thread
From: Mitch Pirtle @ 2004-12-29 17:21 UTC (permalink / raw)
To: Matthew Terenzio <webmaster@localnotion.com>; +Cc: pgsql-php
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
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: too many clients
@ 2004-12-29 18:13 Frank Bax <fbax@sympatico.ca>
parent: Mitch Pirtle <mitch.pirtle@gmail.com>
1 sibling, 1 reply; 15+ messages in thread
From: Frank Bax @ 2004-12-29 18:13 UTC (permalink / raw)
To: pgsql-php
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
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: too many clients
@ 2004-12-29 23:32 Gavin M. Roy <gmr@ehpg.net>
parent: Frank Bax <fbax@sympatico.ca>
0 siblings, 1 reply; 15+ messages in thread
From: Gavin M. Roy @ 2004-12-29 23:32 UTC (permalink / raw)
To: Frank Bax <fbax@sympatico.ca>; +Cc: pgsql-php
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
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: too many clients
@ 2004-12-30 05:20 Greg Stark <gsstark@mit.edu>
parent: Gavin M. Roy <gmr@ehpg.net>
0 siblings, 1 reply; 15+ messages in thread
From: Greg Stark @ 2004-12-30 05:20 UTC (permalink / raw)
To: Gavin M. Roy <gmr@ehpg.net>; +Cc: Frank Bax <fbax@sympatico.ca>; pgsql-php
"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
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: too many clients
@ 2004-12-30 05:33 Gavin M. Roy <gmr@ehpg.net>
parent: Greg Stark <gsstark@mit.edu>
0 siblings, 2 replies; 15+ messages in thread
From: Gavin M. Roy @ 2004-12-30 05:33 UTC (permalink / raw)
To: Greg Stark <gsstark@mit.edu>; +Cc: Frank Bax <fbax@sympatico.ca>; pgsql-php
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.
>
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: too many clients
@ 2004-12-30 14:03 Matthew Terenzio <webmaster@localnotion.com>
parent: Gavin M. Roy <gmr@ehpg.net>
1 sibling, 2 replies; 15+ messages in thread
From: Matthew Terenzio @ 2004-12-30 14:03 UTC (permalink / raw)
To: Gavin M. Roy <gmr@ehpg.net>; +Cc: pgsql-php
>
>
> 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
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: too many clients
@ 2004-12-30 14:35 Frank Bax <fbax@sympatico.ca>
parent: Gavin M. Roy <gmr@ehpg.net>
1 sibling, 0 replies; 15+ messages in thread
From: Frank Bax @ 2004-12-30 14:35 UTC (permalink / raw)
To: pgsql-php
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!
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: too many clients
@ 2004-12-30 18:45 Gavin M. Roy <gmr@ehpg.net>
parent: Matthew Terenzio <webmaster@localnotion.com>
1 sibling, 0 replies; 15+ messages in thread
From: Gavin M. Roy @ 2004-12-30 18:45 UTC (permalink / raw)
To: Matthew Terenzio <webmaster@localnotion.com>; +Cc: pgsql-php
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
>
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: too many clients
@ 2004-12-30 20:34 Frank Bax <fbax@sympatico.ca>
parent: Matthew Terenzio <webmaster@localnotion.com>
1 sibling, 0 replies; 15+ messages in thread
From: Frank Bax @ 2004-12-30 20:34 UTC (permalink / raw)
To: pgsql-php
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
^ permalink raw reply [nested|flat] 15+ messages in thread
end of thread, other threads:[~2004-12-30 20:34 UTC | newest]
Thread overview: 15+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-02-27 21:11 Too many clients Andy Dunlop <andy@infocus.co.za>
2004-02-27 21:16 ` Devrim GUNDUZ <devrim@gunduz.org>
2004-02-27 21:46 ` scott.marlowe <scott.marlowe@ihs.com>
2004-12-29 15:05 too many clients Matthew Terenzio <webmaster@localnotion.com>
2004-12-29 16:38 ` Re: too many clients Mitch Pirtle <mitch.pirtle@gmail.com>
2004-12-29 17:12 ` Re: too many clients Matthew Terenzio <webmaster@localnotion.com>
2004-12-29 17:21 ` Re: too many clients Mitch Pirtle <mitch.pirtle@gmail.com>
2004-12-29 18:13 ` Re: too many clients Frank Bax <fbax@sympatico.ca>
2004-12-29 23:32 ` Re: too many clients Gavin M. Roy <gmr@ehpg.net>
2004-12-30 05:20 ` Re: too many clients Greg Stark <gsstark@mit.edu>
2004-12-30 05:33 ` Re: too many clients Gavin M. Roy <gmr@ehpg.net>
2004-12-30 14:03 ` Re: too many clients Matthew Terenzio <webmaster@localnotion.com>
2004-12-30 18:45 ` Re: too many clients Gavin M. Roy <gmr@ehpg.net>
2004-12-30 20:34 ` Re: too many clients Frank Bax <fbax@sympatico.ca>
2004-12-30 14:35 ` Re: too many clients Frank Bax <fbax@sympatico.ca>
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