Thread

1. Re: re : PHP and persistent connections

   Paul McGarry <paulm@opentec.com.au> — 2000-11-24T04:17:59Z

   Howdy,
   
   > It turns out that the number of max_persistent 
   > is linked to the httpd processes in some 
   > difficult-to-describe way.
   
   It's not that hard to describe. The max_persistent/max_links values are
   per Apache process.
   
   Thus if you have:
   
   pgsql.max_persistent = 2
   
   and 
   
   MaxClients 300
   
   You could potentially reach a state where you are maintaining 600
   persistant connections to the database (if your PHP scripts actually
   pg_pconnect() with two different connect strings).
   
   I think that if you are using persistant connections you may as well set
   MaxClients to the same number of database backends you are allowing (or
   possibly a bit less if you need other connections to the database).
   There's no real point in allowing more Maxclients as they'll just start
   hitting connect errors anyway.
   
   Obviously this isn't the most efficient use of backends because a fair
   amount of the time the Apache processes won't be using them at all
   (they'll be sitting there doing nothing, or serving images or other
   static content).
   
   If your application is big enough you may benefit from serving static
   content (images etc) from a different server, so the Apache processes
   with persistant connection to backends are being used more heavily for
   database work.
   
   Ie if you normally have 100 Apache processes running on your webserver
   and you are using one persistant connection per process you will need
   100 backends. However if at any one time those 60%
   of those processes are serving images then you could have an Apache
   server on one machine serving those images and only need 40 Apache
   processes and therefore 40 backends on the Apache server that serves the
   PHP script. You'll be tuning each machine for a more specific task,
   rather than having one machine doing all sorts of different stuff.
   
   > I do not know what will happen with PHP when there are more than one 
   > different (i.e. different username, database) persistent connections.
   > I suppose they would be affected by the max_persistent. (?).
   
   If you want persistant connections to two different database/username
   pairs then you need to have max_persistant=2, one for each different
   connection string.
   
   If you have one database that is used a lot and one that isn't, you may
   wish to set max_persistant to 1 and max_clients to 2. Use pg_pconnect()
   for the one accessed a lot and pg_connect() for the other. Set Apache
   MaxClients to X and the max number of PG backends to X + Y, where Y
   allows for the load required by the short lived pg_connect()s.
   
   As you've probably noticed, balancing all this is a rather manual
   process.
   Perhaps Apache 2.0 will make way for some connection pooling.
   
   I hope that wasn't too confusing.
   
   Oh, and if you are using pg_close() I don't think it works
   in any currently released PHP4 versions. See:
   http://bugs.php.net/bugs.php?id=7007
   >From the changelog:
   http://cvs.php.net/viewcvs.cgi/~checkout~/php4/ChangeLog?rev=1.541&conte
   nt-type=text/plain
   it seems a fix went in to CVS on 2000-11-03.
   
   
   --
   Paul McGarry            mailto:paulm@opentec.com.au 
   Systems Integrator      http://www.opentec.com.au 
   Opentec Pty Ltd         http://www.iebusiness.com.au
   6 Lyon Park Road        Phone: (02) 9878 1744 
   North Ryde NSW 2113     Fax:   (02) 9878 1755755
   
   

2. Re: Re: re : PHP and persistent connections

   GH <grasshacker@over-yonder.net> — 2000-11-24T04:47:40Z

   On Fri, Nov 24, 2000 at 03:17:59PM +1100, some SMTP stream spewed forth: 
   > Howdy,
   > 
   > > It turns out that the number of max_persistent 
   > > is linked to the httpd processes in some 
   > > difficult-to-describe way.
   > 
   > It's not that hard to describe. The max_persistent/max_links values are
   > per Apache process.
   
   It was difficult to describe because I was not recieving consistent
   results in experiments due to a number of factors. It makes sense now.
   
   > 
   > Thus if you have:
   > 
   > pgsql.max_persistent = 2
   > 
   > and 
   > 
   > MaxClients 300
   > 
   > You could potentially reach a state where you are maintaining 600
   > persistant connections to the database (if your PHP scripts actually
   > pg_pconnect() with two different connect strings).
   > 
   > I think that if you are using persistant connections you may as well set
   > MaxClients to the same number of database backends you are allowing (or
   > possibly a bit less if you need other connections to the database).
   > There's no real point in allowing more Maxclients as they'll just start
   > hitting connect errors anyway.
   
   Well, see, the thing is, we do webhosting for a number of different
   domains from the same server so the number of MaxClients needs to be
   high. I think that 300 is obscene, as the server is not powerful enough
   to handle 300 apache processes without dumping a large number of them
   into swap space, not to mention the processing, but no matter what, we
   would have several extra postgres backends just hanging around wasting
   ram. 
   Only a few unique persistent connections would be in use at any given 
   time as only a few domains use the database.
   
   This has made me realize just how completely braindead our server setup
   is. ;-) It seems that we would to bring up a seperate database
   server, very soon. 
   
   > 
   > Obviously this isn't the most efficient use of backends because a fair
   > amount of the time the Apache processes won't be using them at all
   > (they'll be sitting there doing nothing, or serving images or other
   > static content).
   
   Just what I was thinking. Connection pooling would avoid that, correct?
   
   > 
   > If your application is big enough you may benefit from serving static
   > content (images etc) from a different server, so the Apache processes
   > with persistant connection to backends are being used more heavily for
   > database work.
   
   True, but in this case probably moving the database to a different server
   would make more sense because most of the backends would be serving
   content that is completely unrelated to the database.
   
   > 
   > Ie if you normally have 100 Apache processes running on your webserver
   > and you are using one persistant connection per process you will need
   > 100 backends. However if at any one time those 60%
   > of those processes are serving images then you could have an Apache
   > server on one machine serving those images and only need 40 Apache
   > processes and therefore 40 backends on the Apache server that serves the
   > PHP script. You'll be tuning each machine for a more specific task,
   > rather than having one machine doing all sorts of different stuff.
   > 
   > > I do not know what will happen with PHP when there are more than one 
   > > different (i.e. different username, database) persistent connections.
   > > I suppose they would be affected by the max_persistent. (?).
   > 
   > If you want persistant connections to two different database/username
   > pairs then you need to have max_persistant=2, one for each different
   > connection string.
   > 
   > If you have one database that is used a lot and one that isn't, you may
   > wish to set max_persistant to 1 and max_clients to 2. Use pg_pconnect()
   > for the one accessed a lot and pg_connect() for the other. Set Apache
   > MaxClients to X and the max number of PG backends to X + Y, where Y
   > allows for the load required by the short lived pg_connect()s.
   > 
   > As you've probably noticed, balancing all this is a rather manual
   > process.
   > Perhaps Apache 2.0 will make way for some connection pooling.
   > 
   > I hope that wasn't too confusing.
   
   Your explanation makes perfect sense. A Zen sort of understanding has 
   come to me through experimenting with different settings. 
   
   How would persistent connections fit into a dual-server setup where one
   server is handling all of the webserving and the other simply handles the
   database data-serving? 
   The number of backends on the database server would be independent
   of the number of Apache processes on the webserver inasmuch as there
   could be 75 Apache processes but only 25 are connected to backends on the
   database server, correct?
   There would not necessarily be any Apache stuff on the database server?
   
   > 
   > Oh, and if you are using pg_close() I don't think it works
   > in any currently released PHP4 versions. See:
   
   This seems to be true. I ran into some fun link errors while
   connecting and disconnecting more than once in a script.
   
   Thanks again, and again.
   
   gh
   
   > http://bugs.php.net/bugs.php?id=7007
   > >From the changelog:
   > http://cvs.php.net/viewcvs.cgi/~checkout~/php4/ChangeLog?rev=1.541&conte
   > nt-type=text/plain
   > it seems a fix went in to CVS on 2000-11-03.
   > 
   > 
   > --
   > Paul McGarry            mailto:paulm@opentec.com.au 
   > Systems Integrator      http://www.opentec.com.au 
   > Opentec Pty Ltd         http://www.iebusiness.com.au
   > 6 Lyon Park Road        Phone: (02) 9878 1744 
   > North Ryde NSW 2113     Fax:   (02) 9878 1755755
   
   

3. re: PHP and persistent connections

   jmcazurin <mikah@info.com.ph> — 2000-11-24T06:48:18Z

   At 12:47 PM 11/24/00, GH wrote:
   >On Fri, Nov 24, 2000 at 03:17:59PM +1100, some SMTP stream spewed forth:
   > > Oh, and if you are using pg_close() I don't think it works
   > > in any currently released PHP4 versions. See:
   >
   >This seems to be true. I ran into some fun link errors while
   >connecting and disconnecting more than once in a script.
   
      This sounds disturbing!
   
      How then should I go about closing persistent connections? Can I close 
   them at all?
   
      Would pg_close() work if I used it on non-persistent connections?
   
      Thanks in advance,
   
   Mikah
   
   
   

4. Re: Re: re : PHP and persistent connections

   Ronald Chmara <ron@opus1.com> — 2000-11-24T07:52:34Z

   GH wrote:
   > On Fri, Nov 24, 2000 at 03:17:59PM +1100, some SMTP stream spewed forth:
   > > Howdy,
   > > > It turns out that the number of max_persistent
   > > > is linked to the httpd processes in some
   > > > difficult-to-describe way.
   > > It's not that hard to describe. The max_persistent/max_links values are
   > > per Apache process.
   > It was difficult to describe because I was not recieving consistent
   > results in experiments due to a number of factors. It makes sense now.
   
   I've copied this email exchange over to my PHP folder.. I see what I can do
   do to improve the online documentation. :-)
   
   > Well, see, the thing is, we do webhosting for a number of different
   > domains from the same server so the number of MaxClients needs to be
   > high. I think that 300 is obscene, as the server is not powerful enough
   > to handle 300 apache processes without dumping a large number of them
   > into swap space, not to mention the processing, but no matter what, we
   > would have several extra postgres backends just hanging around wasting
   > ram.
   > Only a few unique persistent connections would be in use at any given
   > time as only a few domains use the database.
   
   Give them their own apache? You can set up two apache instances on one box,
   set up one with lots of backends, set up the other to match the applicable
   db usage...
   You could make a postgres+apache box for these few clients...
   
   > This has made me realize just how completely braindead our server setup
   > is. ;-) It seems that we would to bring up a seperate database
   > server, very soon.
   
   Depends on the load. I'm serving 429 domains off of PHP/PostgreSQL,
   using non-persistant connections (even though almost every page has
   a select or two), and it's working just fine. My biggest selects only
   return a few hundred rows, my small inserts/updates are done in PHP,
   the big ones (4,000+ rows) are just parsed into files that a Perl/cron job
   takes care of them. It also depends, obviously, on how you write your
   code for all of this, how good the hardware is, etc.
   (PII/500, 512Mb of RAM, RH 6.2 for the above)
   
   > > Obviously this isn't the most efficient use of backends because a fair
   > > amount of the time the Apache processes won't be using them at all
   > > (they'll be sitting there doing nothing, or serving images or other
   > > static content).
   > Just what I was thinking. Connection pooling would avoid that, correct?
   > > If your application is big enough you may benefit from serving static
   > > content (images etc) from a different server, so the Apache processes
   > > with persistant connection to backends are being used more heavily for
   > > database work.
   > True, but in this case probably moving the database to a different server
   > would make more sense because most of the backends would be serving
   > content that is completely unrelated to the database.
   
   Well, here's the problem:
   
   1 apache/php/postgres thread = 1 possible persistant postgres connection
   
   if you run up 200 threads on _any_ server instance, that means you need
   200 waiting backends, if that server is also doing postgres content
   with persistant connections anywhere in that server.
   
   I think the idea being referred to works like this:
   In a big, mega-hit app, you put your simple content on a simple server,
   so the web pages reference GIF's/frames/whatever stored there, rather
   than on a heavy-use box. This means that the clients go to *another*
   web server for that non-dynamic content.
   
   > How would persistent connections fit into a dual-server setup where one
   > server is handling all of the webserving and the other simply handles the
   > database data-serving?
   
   Er... well, if you db load was really heavy, this would make sense,
   but your problem is about having all of the webserving in one place.
   
   > The number of backends on the database server would be independent
   > of the number of Apache processes on the webserver inasmuch as there
   > could be 75 Apache processes but only 25 are connected to backends on the
   > database server, correct?
   
   All 75 Apache processes might eventually try to serve up the db pages.
   
   So all 75 *could* eventually want persistant connections. You can't control
   which process gets which page.
   
   > There would not necessarily be any Apache stuff on the database server?
   
   Not if you don't want it, no. Keep in mind that using _non_ persistant
   connections on this setup will be even slower, as well.
   
   -Ron
   
   --
   Brought to you from iBop the iMac, a MacOS, Win95, Win98, LinuxPPC machine,
   which is currently in MacOS land.  Your bopping may vary.
   
   

5. Re: PHP and persistent connections

   GH <grasshacker@over-yonder.net> — 2000-11-24T09:00:20Z

   On Fri, Nov 24, 2000 at 02:48:18PM +0800, some SMTP stream spewed forth: 
   > 
   > At 12:47 PM 11/24/00, GH wrote:
   > >On Fri, Nov 24, 2000 at 03:17:59PM +1100, some SMTP stream spewed forth:
   > > > Oh, and if you are using pg_close() I don't think it works
   > > > in any currently released PHP4 versions. See:
   > >
   > >This seems to be true. I ran into some fun link errors while
   > >connecting and disconnecting more than once in a script.
   > 
   >    This sounds disturbing!
   
   Maybe it should, I thought it was. Who knows.
   > 
   
   >    How then should I go about closing persistent connections? Can I close 
   > them at all?
   
   You cannot, by design and purpose, close persistent connections.
   You could kill the postgres backend, but that is not quite the same. ;-)
   
   >    Would pg_close() work if I used it on non-persistent connections?
   
   My experience has caused me to believe that no, it will not.
   That is not final, as I do not have true proof.
   
   > 
   >    Thanks in advance,
   
   No prob, we are here to benefit each other.
   
   It seems like PHP would open other new connections using pg_connect(), 
   but would not close them. Has anyone had experiences other than this?
   
   
   gh
   
   > 
   > Mikah
   > 
   
   

6. Re: Re: re : PHP and persistent connections

   GH <grasshacker@over-yonder.net> — 2000-11-24T09:16:36Z

   On Fri, Nov 24, 2000 at 12:52:34AM -0700, some SMTP stream spewed forth: 
   > GH wrote:
   > > On Fri, Nov 24, 2000 at 03:17:59PM +1100, some SMTP stream spewed forth:
   > > > Howdy,
   > > > > It turns out that the number of max_persistent
   > > > > is linked to the httpd processes in some
   > > > > difficult-to-describe way.
   > > > It's not that hard to describe. The max_persistent/max_links values are
   > > > per Apache process.
   > > It was difficult to describe because I was not recieving consistent
   > > results in experiments due to a number of factors. It makes sense now.
   > 
   > I've copied this email exchange over to my PHP folder.. I see what I can do
   > do to improve the online documentation. :-)
   
   Great. Thanks.
   
   > 
   > > Well, see, the thing is, we do webhosting for a number of different
   > > domains from the same server so the number of MaxClients needs to be
   > > high. I think that 300 is obscene, as the server is not powerful enough
   > > to handle 300 apache processes without dumping a large number of them
   > > into swap space, not to mention the processing, but no matter what, we
   > > would have several extra postgres backends just hanging around wasting
   > > ram.
   > > Only a few unique persistent connections would be in use at any given
   > > time as only a few domains use the database.
   > 
   > Give them their own apache? You can set up two apache instances on one box,
   > set up one with lots of backends, set up the other to match the applicable
   > db usage...
   > You could make a postgres+apache box for these few clients...
   
   Er, I think I missed something.
   You mean give them their own Apache instance using a seperate ip?
   
   Is it /possible/ to have a group of httpd processes (Apache) share a 
   group of Postgres backends without having one backend to one httpd?
   That would be connection pooling, correct? Which is not yet possible?
   
   > 
   > > This has made me realize just how completely braindead our server setup
   > > is. ;-) It seems that we would to bring up a seperate database
   > > server, very soon.
   > 
   > Depends on the load. I'm serving 429 domains off of PHP/PostgreSQL,
   > using non-persistant connections (even though almost every page has
   > a select or two), and it's working just fine. My biggest selects only
   > return a few hundred rows, my small inserts/updates are done in PHP,
   > the big ones (4,000+ rows) are just parsed into files that a Perl/cron job
   > takes care of them. It also depends, obviously, on how you write your
   > code for all of this, how good the hardware is, etc.
   > (PII/500, 512Mb of RAM, RH 6.2 for the above)
   
   That makes sense. The only reason I am so zealous about persistent 
   connections is that I have seen them be 3 times as fast as regular
   connections.
   
   > 
   > > > Obviously this isn't the most efficient use of backends because a fair
   > > > amount of the time the Apache processes won't be using them at all
   
   My main question now is, how can I avoid this?
   I would have to go to non-persistent connections, correct?
   I think I further understand things now.
   
   
   So, persistent connections create a one-to-one ratio of 
   db-using Apache processes and Postgres backends, no matter what?
   The only way to avoid such a one-to-one setup would be to 
   use non-persistent connections or do connection pooling?
   
   So, even if the database were running on a seperate server, 
   each apache procees on the main server would require one backend process
   on the db server?
   
   > 
   > -Ron
   > 
   > --
   > Brought to you from iBop the iMac, a MacOS, Win95, Win98, LinuxPPC machine,
   > which is currently in MacOS land.  Your bopping may vary.
   
   

7. Re: Re: re : PHP and persistent connections

   GH <grasshacker@over-yonder.net> — 2000-11-24T09:36:37Z

   I have a couple of other questions that I believe are not ansvered in 
   the docs anywhere.
   
   Do the "persistent-connected" Postgres backends ever timeout or die?
   Is it possible to set something like a timeout for persistent connctions?
   (Er, would that be something that someone would want 
   	to do? A Bad Thing?)
   
   What happens when the httpd process that held a persistent connection
   dies? Does "its" postgres process drop the connection and wait for
   others? When the spare apache processes die, the postgres processes
   remain.
   
   Thanks.
   
   gh
   
   
   

8. Re: Re: re : PHP and persistent connections

   Ronald Chmara <ron@opus1.com> — 2000-11-24T11:52:27Z

   We're in quote hell.
   Yay. 
   
   GH wrote:
   > 
   > On Fri, Nov 24, 2000 at 12:52:34AM -0700, some SMTP stream spewed forth:
   > > GH wrote:
   > > > On Fri, Nov 24, 2000 at 03:17:59PM +1100, some SMTP stream spewed forth:
   > > > > Howdy,
   > > Give them their own apache? You can set up two apache instances on one box,
   > > set up one with lots of backends, set up the other to match the applicable
   > > db usage...
   > > You could make a postgres+apache box for these few clients...
   > Er, I think I missed something.
   > You mean give them their own Apache instance using a seperate ip?
   
   Yes.
   
   Apache one, httpd, serves 14 domains, conf files in /usr/local/apache/conf.
   pgsql.max_persistent = 1
   MaxClients 8
   
   Apache two, httpd2, serves 327 domains, conf files in /usr/local/apache2/conf.
   Max clients 150 (no postgres backends, no PHP)
   
   > Is it /possible/ to have a group of httpd processes (Apache) share a
   > group of Postgres backends without having one backend to one httpd?
   > That would be connection pooling, correct? Which is not yet possible?
   
   Apache's process management, AFAIK, makes this fairly difficult. As in:
   "I've never seen it, and I can't find docs on on, maybe v.2 will
   have better support for children sharing common resources".
   
   > > Depends on the load. I'm serving 429 domains off of PHP/PostgreSQL,
   > > using non-persistant connections (even though almost every page has
   > > a select or two), and it's working just fine. My biggest selects only
   > > return a few hundred rows, my small inserts/updates are done in PHP,
   > > the big ones (4,000+ rows) are just parsed into files that a Perl/cron job
   > > takes care of them. It also depends, obviously, on how you write your
   > > code for all of this, how good the hardware is, etc.
   > > (PII/500, 512Mb of RAM, RH 6.2 for the above)
   > That makes sense. The only reason I am so zealous about persistent
   > connections is that I have seen them be 3 times as fast as regular
   > connections.
   
   Hm.
   
   I havn't. In PHP, one connection for the duration of a single
   page (pg_connect()) takes as much time as a new persistant connection
   (pg_pconnect()). Since you're often only creating one connection per page,
   and running a single transaction on it, the main difference
   would be in your connection setup... how did you test this? (I'm just
   curious). Is it a usage test (real, live, use) or a bench test (push
   to a limit that won't be reached in actual use.) I have one horribly
   written app, that does maybe 50 _different_ selects on one page,
   and it's still under two seconds per user....
   
   > > > > Obviously this isn't the most efficient use of backends because a fair
   > > > > amount of the time the Apache processes won't be using them at all
   > My main question now is, how can I avoid this?
   
   Serve the postgres pages from a different server instance, on the same
   machine, or a different one.
   
   > I would have to go to non-persistent connections, correct?
   
   You could use persistant connections on a different server/instance,
   or use non-persistant and loose ~10ms per page, less time than your average
   10K GIF takes up on a 56K download.
   
   You see, persistant PHP connections offer *no other value*, at all. None.
   (it's a common error for new PHP folks to think that a web server
   will somehow track their connections.) All it does is reduce setup time on a
   page. No "session", no "tracking", nada. It reduces your connection
   time for the page, but not significanly enough for users to know,
   or care (IME). In web-page uses, the time is pretty much irrelevant,
   because you only need one or two connections per page to get most
   of your data out. Persistant connections are an interesting idea,
   but they don't offer much. See:
   http://www.php.net/manual/features.persistent-connections.php
   
   > So, persistent connections create a one-to-one ratio of
   > db-using Apache processes and Postgres backends, no matter what?
   
   Almost. You can have more persistant connections for each apache
   child, but each child may look for one. So it may be 5 apache
   to 5 postgres, or 5 apache to 50 postgres, if needed (of course,
   if you had that many conections, you may want to re-architect anyways)
   
   > The only way to avoid such a one-to-one setup would be to
   > use non-persistent connections or do connection pooling?
   
   I'm still not following you on the "pooling". Apache doesn't, AFAICT,
   offer this in each child. Each child is its own application, it's own
   apache+php+postgres. Postgres doesn't care. PHP doesn't care. Apache
   cares. If you give each child piece 5 postgres connections, and have
   10 children, you need up to 50 backends.
   
   > So, even if the database were running on a seperate server,
   > each apache procees on the main server would require one backend process
   > on the db server?
   
   Yup. If it was going to pull a postgres+PHP page, it would. You see,
   apache doesn't work in a space where one apache process can crash the
   whole thing. Each piece is isolated. This means that each piece needs
   it's own resources. Compare this to other engines, where a single
   crash on one serving instance takes down the _entire_ server, and
   it makes sense (if the pool is down, it all goes down, a la IIS).
   
   "It scales, but not that way". :-(
   
   -Ronabop
   
   --
   Brought to you from iBop the iMac, a MacOS, Win95, Win98, LinuxPPC machine,
   which is currently in MacOS land.  Your bopping may vary.
   
   

9. Re: Re: re : PHP and persistent connections

   GH <grasshacker@over-yonder.net> — 2000-11-24T12:18:04Z

   On Fri, Nov 24, 2000 at 04:52:27AM -0700, some SMTP stream spewed forth: 
   > We're in quote hell.
   > Yay. 
   
   Ah, but now the hell thickens. ;-)
   
   > 
   > GH wrote:
   > > 
   > > On Fri, Nov 24, 2000 at 12:52:34AM -0700, some SMTP stream spewed forth:
   > > > GH wrote:
   > > > > On Fri, Nov 24, 2000 at 03:17:59PM +1100, some SMTP stream spewed forth:
   > > > > > Howdy,
   > > > Give them their own apache? You can set up two apache instances on one box,
   > > > set up one with lots of backends, set up the other to match the applicable
   > > > db usage...
   > > > You could make a postgres+apache box for these few clients...
   > > Er, I think I missed something.
   > > You mean give them their own Apache instance using a seperate ip?
   > 
   > Yes.
   > 
   > Apache one, httpd, serves 14 domains, conf files in /usr/local/apache/conf.
   > pgsql.max_persistent = 1
   > MaxClients 8
   > 
   > Apache two, httpd2, serves 327 domains, conf files in /usr/local/apache2/conf.
   > Max clients 150 (no postgres backends, no PHP)
   
   I see. 
   
   > 
   > > Is it /possible/ to have a group of httpd processes (Apache) share a
   > > group of Postgres backends without having one backend to one httpd?
   > > That would be connection pooling, correct? Which is not yet possible?
   > 
   > Apache's process management, AFAIK, makes this fairly difficult. As in:
   > "I've never seen it, and I can't find docs on on, maybe v.2 will
   > have better support for children sharing common resources".
   > 
   
   Just checking. I had heard (and expected) that it did not -- for the same
   reason.
   
   > > > Depends on the load. I'm serving 429 domains off of PHP/PostgreSQL,
   > > > using non-persistant connections (even though almost every page has
   > > > a select or two), and it's working just fine. My biggest selects only
   > > > return a few hundred rows, my small inserts/updates are done in PHP,
   > > > the big ones (4,000+ rows) are just parsed into files that a Perl/cron job
   > > > takes care of them. It also depends, obviously, on how you write your
   > > > code for all of this, how good the hardware is, etc.
   > > > (PII/500, 512Mb of RAM, RH 6.2 for the above)
   > > That makes sense. The only reason I am so zealous about persistent
   > > connections is that I have seen them be 3 times as fast as regular
   > > connections.
   > 
   > Hm.
   > 
   > I havn't. In PHP, one connection for the duration of a single
   > page (pg_connect()) takes as much time as a new persistant connection
   > (pg_pconnect()). Since you're often only creating one connection per page,
   > and running a single transaction on it, the main difference
   > would be in your connection setup... how did you test this? (I'm just
   > curious). Is it a usage test (real, live, use) or a bench test (push
   > to a limit that won't be reached in actual use.) I have one horribly
   > written app, that does maybe 50 _different_ selects on one page,
   > and it's still under two seconds per user....
   
   "Test" is a strong word. ;-) I have a timer set on a page.
   The overall exec time is less that 1-tenth of a second using persistent 
   connections, so long as a connection exists. Using regular connections,
   the exec time soars (;-)) to a whopping 3-tenths or so.
   So, no big fat deal. The exec time is low enough that the effects of the
   connections shine, but in general are insignificant.
   If the script in discussion did anything worthwhile, I doubt that I would
   notice anything even close to 3x.
   
   > 
   > > > > > Obviously this isn't the most efficient use of backends because a fair
   > > > > > amount of the time the Apache processes won't be using them at all
   > > My main question now is, how can I avoid this?
   > 
   > Serve the postgres pages from a different server instance, on the same
   > machine, or a different one.
   > 
   > > I would have to go to non-persistent connections, correct?
   > 
   > You could use persistant connections on a different server/instance,
   > or use non-persistant and loose ~10ms per page, less time than your average
   > 10K GIF takes up on a 56K download.
   > 
   > You see, persistant PHP connections offer *no other value*, at all. None.
   > (it's a common error for new PHP folks to think that a web server
   > will somehow track their connections.) All it does is reduce setup time on a
   > page. No "session", no "tracking", nada. It reduces your connection
   > time for the page, but not significanly enough for users to know,
   > or care (IME). In web-page uses, the time is pretty much irrelevant,
   > because you only need one or two connections per page to get most
   > of your data out. Persistant connections are an interesting idea,
   > but they don't offer much. See:
   > http://www.php.net/manual/features.persistent-connections.php
   
   I have read it (note: the phrasing seems to be a bit "messy"), but 
   for some reason I must have missed what it was saying. I "get it" now.
   
   > 
   > > So, persistent connections create a one-to-one ratio of
   > > db-using Apache processes and Postgres backends, no matter what?
   > 
   > Almost. You can have more persistant connections for each apache
   > child, but each child may look for one. So it may be 5 apache
   > to 5 postgres, or 5 apache to 50 postgres, if needed (of course,
   > if you had that many conections, you may want to re-architect anyways)
   > 
   > > The only way to avoid such a one-to-one setup would be to
   > > use non-persistent connections or do connection pooling?
   > 
   > I'm still not following you on the "pooling". Apache doesn't, AFAICT,
   
   I almost knew that it did not. But I was trying to re-affirm my grasp of 
   just what "pooling" would do.
   
   > offer this in each child. Each child is its own application, it's own
   > apache+php+postgres. Postgres doesn't care. PHP doesn't care. Apache
   > cares. If you give each child piece 5 postgres connections, and have
   > 10 children, you need up to 50 backends.
   > 
   > > So, even if the database were running on a seperate server,
   > > each apache procees on the main server would require one backend process
   > > on the db server?
   > 
   > Yup. If it was going to pull a postgres+PHP page, it would. You see,
   > apache doesn't work in a space where one apache process can crash the
   > whole thing. Each piece is isolated. This means that each piece needs
   > it's own resources. Compare this to other engines, where a single
   > crash on one serving instance takes down the _entire_ server, and
   > it makes sense (if the pool is down, it all goes down, a la IIS).
   > 
   > "It scales, but not that way". :-(
   
   Got it. Maybe this thread will finally pass away now. ;-)
   
   Thanks again.
   
   gh
   
   > 
   > -Ronabop
   > 
   > --
   > Brought to you from iBop the iMac, a MacOS, Win95, Win98, LinuxPPC machine,
   > which is currently in MacOS land.  Your bopping may vary.
   
   

10. Re: Re: re : PHP and persistent connections

    Tom Lane <tgl@sss.pgh.pa.us> — 2000-11-24T17:02:33Z

    GH <grasshacker@over-yonder.net> writes:
    > Do the "persistent-connected" Postgres backends ever timeout or die?
    
    No.  A backend will sit patiently for the client to send it another
    query or close the connection.
    
    (Barely on topic: in recent releases, the backend does set TCP
    "keepalive" mode on the client socket.  On a cross-machine connection,
    this causes the kernel to ping every so often on an idle connection, to
    make sure that the peer machine is still alive and still believes the
    connection is open.  However, this does not guard against a client
    process that is holding connections open without any intention of using
    them again soon --- it only protects against half-open connections left
    over after a system crash at the client end.  In any case, I believe the
    total time delay before declaring the connection lost has to be an hour
    or more in a spec-compliant TCP implementation.)
    
    > Is it possible to set something like a timeout for persistent connctions?
    > (Er, would that be something that someone would want 
    > 	to do? A Bad Thing?)
    
    This has been suggested before, but I don't think any of the core
    developers consider it a good idea.  Having the backend arbitrarily
    disconnect on an active client would be a Bad Thing for sure.  Hence,
    any workable timeout would have to be quite large (order of an
    hour, maybe? not milliseconds anyway).  And that means that it's not
    an effective solution for the problem.  Under load, a webserver that
    wastes backend connections will run out of available backends long
    before a safe timeout would start to clean up after it.
    
    To my mind, a client app that wants to use persistent connections
    has got to implement some form of connection pooling, so that it
    recycles idle connections back to a "pool" for allocation to task
    threads that want to make a new query.  And the threads have to release
    connections back to the pool as soon as they're done with a transaction.
    Actively releasing an idle connection is essential, rather than
    depending on a timeout.
    
    I haven't studied PHP at all, but from this conversation I gather that
    it's only halfway there...
    
    			regards, tom lane
    
    

11. Re: [NOVICE] Re: re : PHP and persistent connections

    Ronald Chmara <ron@opus1.com> — 2000-11-26T00:26:42Z

    Note: CC'd to Hackers, as this has wandered into deeper feature issues.
    
    Tom Lane wrote:
    > GH <grasshacker@over-yonder.net> writes:
    > > Do the "persistent-connected" Postgres backends ever timeout or die?
    > No.  A backend will sit patiently for the client to send it another
    > query or close the connection.
    
    This does have an unfortunate denial-of-service implication, where
    an attack can effectively suck up all available backends, and there's
    no throttle, no timeout, no way of automatically dropping these....
    
    However, the more likely possibility is similar to the problem that
    we see in PHP's persistant connections.... a normally benign connection
    is inactive, and yet it isn't dropped. If you have two of these created
    every day, and you only have 16 backends, after 8 days you have a lockout.
    
    On a busy web site or another busy application, you can, of course,
    exhaust 64 backends in a matter of minutes.
    
    > > Is it possible to set something like a timeout for persistent connctions?
    > > (Er, would that be something that someone would want
    > >       to do? A Bad Thing?)
    > This has been suggested before, but I don't think any of the core
    > developers consider it a good idea.  Having the backend arbitrarily
    > disconnect on an active client would be a Bad Thing for sure.
    
    Right.... but I don't think anybody has suggested disconnecting an *active*
    client, just inactive ones.
    
    > Hence,
    > any workable timeout would have to be quite large (order of an
    > hour, maybe? not milliseconds anyway). 
    
    The mySQL disconnect starts at around 24 hours. It prevents a slow
    accumulation of unused backends, but does nothing for a rapid
    accumulation. It can be cranked down to a few minutes AFAIK.
    
    > And that means that it's not
    > an effective solution for the problem.  Under load, a webserver that
    > wastes backend connections will run out of available backends long
    > before a safe timeout would start to clean up after it.
    
    Depends on how it's set up... you see, this isn't uncharted territory,
    other web/db solutions have already fought with this issue. Much
    like the number of backends set up for pgsql must be static, a timeout
    may wind up being the same way. The critical thing to realize is
    that you are timing out _inactive_ connections, not connections
    in general. So provided that a connection provided information
    about when it was last used, or usage set a counter somewhere, it
    could easily be checked.
    
    > To my mind, a client app that wants to use persistent connections
    > has got to implement some form of connection pooling, so that it
    > recycles idle connections back to a "pool" for allocation to task
    > threads that want to make a new query.  And the threads have to release
    > connections back to the pool as soon as they're done with a transaction.
    > Actively releasing an idle connection is essential, rather than
    > depending on a timeout.
    > 
    > I haven't studied PHP at all, but from this conversation I gather that
    > it's only halfway there...
    
    Well...... This is exactly how apache and PHP serve pages. The
    problem is that apache children aren't threads, they are separate copies
    of the application itself. So a single apache thread will re-use the
    same connection, over and over again, and give that conection over to
    other connections on that apache thread.. so in your above model, it's
    not really one client application in the first place.
    
    It's a dynamic number of client applications, between one and hundreds
    or so.
    
    So to turn the feature request the other way 'round:
    "I have all sorts of client apps, connecting in different ways, to
    my server. Some of the clients are leaving their connections open,
    but unused. How can I prevent running out of backends, and boot
    the inactive users off?"
    
    -Ronabop
    
    --
    Brought to you from iBop the iMac, a MacOS, Win95, Win98, LinuxPPC machine,
    which is currently in MacOS land.  Your bopping may vary.
    
    

12. Re: Re: [NOVICE] Re: re : PHP and persistent connections

    Don Baccus <dhogaza@pacifier.com> — 2000-11-26T02:54:21Z

    At 05:26 PM 11/25/00 -0700, Ron Chmara wrote:
    >Note: CC'd to Hackers, as this has wandered into deeper feature issues.
    >
    >Tom Lane wrote:
    >> GH <grasshacker@over-yonder.net> writes:
    >> > Do the "persistent-connected" Postgres backends ever timeout or die?
    >> No.  A backend will sit patiently for the client to send it another
    >> query or close the connection.
    >
    >This does have an unfortunate denial-of-service implication, where
    >an attack can effectively suck up all available backends, and there's
    >no throttle, no timeout, no way of automatically dropping these....
    >
    >However, the more likely possibility is similar to the problem that
    >we see in PHP's persistant connections.... a normally benign connection
    >is inactive, and yet it isn't dropped. If you have two of these created
    >every day, and you only have 16 backends, after 8 days you have a lockout.
    >
    >On a busy web site or another busy application, you can, of course,
    >exhaust 64 backends in a matter of minutes.
    
    Ugh...the more I read stuff like this the more I appreciate AOlserver's
    built-in database API which protects the application from any such
    problems altogether.   The particular problem being described simply
    can't occur in this environment.
    
    
    
    
    - Don Baccus, Portland OR <dhogaza@pacifier.com>
      Nature photos, on-line guides, Pacific Northwest
      Rare Bird Alert Service and other goodies at
      http://donb.photo.net.
    
    

13. Re: Re: [NOVICE] Re: re : PHP and persistent connections

    Alain Toussaint <nailed@videotron.ca> — 2000-11-26T05:07:46Z

    > "I have all sorts of client apps, connecting in different ways, to
    > my server. Some of the clients are leaving their connections open,
    > but unused. How can I prevent running out of backends, and boot
    > the inactive users off?"
    
    how about having a middle man between apache (or aolserver or any other
    clients...) and PosgreSQL ??
    
    that middleman could be configured to have 16 persistant connections,every
    clients would deal with the middleman instead of going direct to the
    database,this would be an advantage where multiple PostgreSQL server are
    used...
    
    240 apache process are running on a box and there's 60 PostgreSQL instance
    running on the machine or another machine:
    
    240 apache process --> middleman --> 60 PostgreSQL process
    
    now if there's multiple Database server:
    
    240 apache process --> middleman --> 12 PostgreSQL for each server (5
    servers in this case)
    
    in this case,the middleman could be a shared library which the clients
    link to..
    
    what do you think about that ??
    
    Alain Toussaint
    
    
    

14. Re: Re: [NOVICE] Re: re : PHP and persistent connections

    Don Baccus <dhogaza@pacifier.com> — 2000-11-26T05:18:33Z

    At 10:00 PM 11/25/00 -0800, Mitch Vincent wrote:
    >    I've tried quite a bit to use persistent connections with PHP (for over
    >a year) and always the scripts that I try to use them with behave crazy...
    >The last time I tried there were problems all over the place with PHP,
    >variables getting overwritten, certain functions just totally breaking
    >(date() to name one) and so on.. I know I'm not being specific but my point
    >is that I think there are some other outstanding PHP issues that play into
    >this problem as the behavior that I've seen isn't directly related to
    >PostgreSQL but only happens when I use persistent connections.. 
    
    I've heard rumors that PHP isn't thoroughly threadsafe, could this be a
    source of your problems?
    
    
    
    
    - Don Baccus, Portland OR <dhogaza@pacifier.com>
      Nature photos, on-line guides, Pacific Northwest
      Rare Bird Alert Service and other goodies at
      http://donb.photo.net.
    
    

15. Re: Re: [NOVICE] Re: re : PHP and persistent connections

    Don Baccus <dhogaza@pacifier.com> — 2000-11-26T05:24:22Z

    At 12:07 AM 11/26/00 -0500, Alain Toussaint wrote:
    
    >how about having a middle man between apache (or aolserver or any other
    >clients...) and PosgreSQL ??
    >
    >that middleman could be configured to have 16 persistant connections,every
    >clients would deal with the middleman instead of going direct to the
    >database,this would be an advantage where multiple PostgreSQL server are
    >used...
    
    Well, this is sort of what AOLserver does for you without any need for
    middlemen.  
    
    Again, reading stuff like this makes me think "ugh!"
    
    This stuff is really pretty easy, it's amazing to me that the Apache/db
    world talks about such kludges when they're clearly not necessary.
    
    My first experience running a website (donb.photo.net) was with Apache
    on Linux on an old P100 system in 1996 when few folks had personal photo
    sites with >1000 photos on them getting thousands of hits a day.  I have
    fond memories of those days, and Apache served me (or more properly webserved
    my website) well.  This site is largely responsible for my reputation that
    lets me freelance nature photography to the national media market pretty
    much at will.  Thus my fondness.
    
    But ... for database stuff the release of AOLserver as first Free Beer,
    and now Free Speech software has caused me to abandon Apache and suggestions
    like the above just make me cringe.
    
    It shouldn't be that hard, folks.
    
    
    
    - Don Baccus, Portland OR <dhogaza@pacifier.com>
      Nature photos, on-line guides, Pacific Northwest
      Rare Bird Alert Service and other goodies at
      http://donb.photo.net.
    
    

16. Re: Re: [NOVICE] Re: re : PHP and persistent connections

    Mitch Vincent <mitch@venux.net> — 2000-11-26T06:00:27Z

        I've tried quite a bit to use persistent connections with PHP (for over
    a year) and always the scripts that I try to use them with behave crazy...
    The last time I tried there were problems all over the place with PHP,
    variables getting overwritten, certain functions just totally breaking
    (date() to name one) and so on.. I know I'm not being specific but my point
    is that I think there are some other outstanding PHP issues that play into
    this problem as the behavior that I've seen isn't directly related to
    PostgreSQL but only happens when I use persistent connections.. I've been
    trying to corner the problem for quite some time, it's an elusive one for
    sure.. I spoke with the PHP developers 9 or so months ago about the problems
    and they didn't seem to pay any attention to it, the thread on the mailing
    list was short with the bug report collecting dust at the bottom of the
    to-do list I'm sure (as that was back before PHP 4 was even released and
    obviously the problem remains)..
    
    Just my $0.02 worth.
    
    
    -Mitch
    
    ----- Original Message -----
    From: "Ron Chmara" <ron@Opus1.COM>
    To: "Tom Lane" <tgl@sss.pgh.pa.us>; "PostgreSQL Hackers List"
    <pgsql-hackers@postgresql.org>
    Cc: "GH" <grasshacker@over-yonder.net>; <pgsql-novice@postgresql.org>
    Sent: Saturday, November 25, 2000 4:26 PM
    Subject: [HACKERS] Re: [NOVICE] Re: re : PHP and persistent connections
    
    
    > Note: CC'd to Hackers, as this has wandered into deeper feature issues.
    >
    > Tom Lane wrote:
    > > GH <grasshacker@over-yonder.net> writes:
    > > > Do the "persistent-connected" Postgres backends ever timeout or die?
    > > No.  A backend will sit patiently for the client to send it another
    > > query or close the connection.
    >
    > This does have an unfortunate denial-of-service implication, where
    > an attack can effectively suck up all available backends, and there's
    > no throttle, no timeout, no way of automatically dropping these....
    >
    > However, the more likely possibility is similar to the problem that
    > we see in PHP's persistant connections.... a normally benign connection
    > is inactive, and yet it isn't dropped. If you have two of these created
    > every day, and you only have 16 backends, after 8 days you have a lockout.
    >
    > On a busy web site or another busy application, you can, of course,
    > exhaust 64 backends in a matter of minutes.
    >
    > > > Is it possible to set something like a timeout for persistent
    connctions?
    > > > (Er, would that be something that someone would want
    > > >       to do? A Bad Thing?)
    > > This has been suggested before, but I don't think any of the core
    > > developers consider it a good idea.  Having the backend arbitrarily
    > > disconnect on an active client would be a Bad Thing for sure.
    >
    > Right.... but I don't think anybody has suggested disconnecting an
    *active*
    > client, just inactive ones.
    >
    > > Hence,
    > > any workable timeout would have to be quite large (order of an
    > > hour, maybe? not milliseconds anyway).
    >
    > The mySQL disconnect starts at around 24 hours. It prevents a slow
    > accumulation of unused backends, but does nothing for a rapid
    > accumulation. It can be cranked down to a few minutes AFAIK.
    >
    > > And that means that it's not
    > > an effective solution for the problem.  Under load, a webserver that
    > > wastes backend connections will run out of available backends long
    > > before a safe timeout would start to clean up after it.
    >
    > Depends on how it's set up... you see, this isn't uncharted territory,
    > other web/db solutions have already fought with this issue. Much
    > like the number of backends set up for pgsql must be static, a timeout
    > may wind up being the same way. The critical thing to realize is
    > that you are timing out _inactive_ connections, not connections
    > in general. So provided that a connection provided information
    > about when it was last used, or usage set a counter somewhere, it
    > could easily be checked.
    >
    > > To my mind, a client app that wants to use persistent connections
    > > has got to implement some form of connection pooling, so that it
    > > recycles idle connections back to a "pool" for allocation to task
    > > threads that want to make a new query.  And the threads have to release
    > > connections back to the pool as soon as they're done with a transaction.
    > > Actively releasing an idle connection is essential, rather than
    > > depending on a timeout.
    > >
    > > I haven't studied PHP at all, but from this conversation I gather that
    > > it's only halfway there...
    >
    > Well...... This is exactly how apache and PHP serve pages. The
    > problem is that apache children aren't threads, they are separate copies
    > of the application itself. So a single apache thread will re-use the
    > same connection, over and over again, and give that conection over to
    > other connections on that apache thread.. so in your above model, it's
    > not really one client application in the first place.
    >
    > It's a dynamic number of client applications, between one and hundreds
    > or so.
    >
    > So to turn the feature request the other way 'round:
    > "I have all sorts of client apps, connecting in different ways, to
    > my server. Some of the clients are leaving their connections open,
    > but unused. How can I prevent running out of backends, and boot
    > the inactive users off?"
    >
    > -Ronabop
    >
    > --
    > Brought to you from iBop the iMac, a MacOS, Win95, Win98, LinuxPPC
    machine,
    > which is currently in MacOS land.  Your bopping may vary.
    >
    
    
    

17. Re: Re: [NOVICE] Re: re : PHP and persistent connections

    Alain Toussaint <nailed@videotron.ca> — 2000-11-26T07:50:36Z

    > Well, this is sort of what AOLserver does for you without any need for
    > middlemen.
    
    i agree that AolServer is good karma,i've been reading various docs on
    Aolserver since Philip Greenspun talked about it on linuxworld and i'm glad
    that there's some java support being coded for it (im my opinion,it's the only
    advantage that Apache had over AolServer for me).
    
    > Again, reading stuff like this makes me think "ugh!"
    >
    > This stuff is really pretty easy, it's amazing to me that the Apache/db
    > world talks about such kludges when they're clearly not necessary.
    
    well...i was using Apache as an example due to it DB model but the stuff i
    was talking would work quite well in the case of multiple DB server
    hosting differents table and you want to maintain location
    independance,here's an example:
    
    you have 7 Database server,5 are online and the other 2 are for
    maintenance and/or development purpose,for simplicity,we'll name the
    server database1.example.net to
    database7.example.net,database4.example.net is currently doing a dump and
    database6.example.net is loading the dump from database4,then,you
    reconfigure the middleman so it redirect all request from database4 to
    database6:
    
    vim /etc/middleman.conf
    
    and then a sighup to the middleman so it reread its config file:
    
    killall -HUP middleman
    
    this would update the middleman's shared lib with the new configuration
    info (and BTW,i just extended my idea from a single shared lib to a
    daemon/shared lib combo).
    
    now i'm off to get the dog out for a walk and then,take a nap,see ya !!
    
    Alain Toussaint
    
    
    

18. Re: Re: [NOVICE] Re: re : PHP and persistent connections

    Mitch Vincent <mitch@venux.net> — 2000-11-26T08:02:59Z

    I'm sure that this, if true, could certainly be the source of the problems
    I've seen... I can't comment on if PHP is completely threadsafe, I know that
    some of the modules (for lack of a better word) aren't, possible the ClibPDF
    library I'm using. I'll check into it.
    
    Thanks!
    
    -Mitch
    
    ----- Original Message -----
    From: "Don Baccus" <dhogaza@pacifier.com>
    To: "Mitch Vincent" <mitch@venux.net>; "PostgreSQL Hackers List"
    <pgsql-hackers@postgresql.org>
    Cc: <pgsql-novice@postgresql.org>
    Sent: Saturday, November 25, 2000 9:18 PM
    Subject: Re: [HACKERS] Re: [NOVICE] Re: re : PHP and persistent connections
    
    
    > At 10:00 PM 11/25/00 -0800, Mitch Vincent wrote:
    > >    I've tried quite a bit to use persistent connections with PHP (for
    over
    > >a year) and always the scripts that I try to use them with behave
    crazy...
    > >The last time I tried there were problems all over the place with PHP,
    > >variables getting overwritten, certain functions just totally breaking
    > >(date() to name one) and so on.. I know I'm not being specific but my
    point
    > >is that I think there are some other outstanding PHP issues that play
    into
    > >this problem as the behavior that I've seen isn't directly related to
    > >PostgreSQL but only happens when I use persistent connections..
    >
    > I've heard rumors that PHP isn't thoroughly threadsafe, could this be a
    > source of your problems?
    >
    >
    >
    >
    > - Don Baccus, Portland OR <dhogaza@pacifier.com>
    >   Nature photos, on-line guides, Pacific Northwest
    >   Rare Bird Alert Service and other goodies at
    >   http://donb.photo.net.
    >
    
    
    

19. Re: Re: [NOVICE] Re: re : PHP and persistent connections

    Tom Samplonius <tom@sdf.com> — 2000-11-26T18:58:34Z

    On Sun, 26 Nov 2000, Alain Toussaint wrote:
    
    > > "I have all sorts of client apps, connecting in different ways, to
    > > my server. Some of the clients are leaving their connections open,
    > > but unused. How can I prevent running out of backends, and boot
    > > the inactive users off?"
    > 
    > how about having a middle man between apache (or aolserver or any other
    > clients...) and PosgreSQL ??
    
      I don't see it solving anything.  You just move the connection
    management problem from the database to the middleman (in the industry
    such a thing would be called a query multiplexor).  Multiplexors have
    often been used in the past to solve this problem, because the database
    could not be extended or protected.
    
      Besides, if you are an n-tier developer, this isn't a problem as your
    middle tier not does connection management, but some logic as well.  At
    the end of the day, PHP/Apache is just not suitable for complex
    applications.  
    
    Tom
    
    
    

20. Re: Re: [NOVICE] Re: re : PHP and persistent connections

    Ronald Chmara <ron@opus1.com> — 2000-11-27T07:38:46Z

    Don Baccus wrote:
    > At 12:07 AM 11/26/00 -0500, Alain Toussaint wrote:
    > >how about having a middle man between apache (or aolserver or any other
    > >clients...) and PosgreSQL ??
    > >that middleman could be configured to have 16 persistant connections,every
    > >clients would deal with the middleman instead of going direct to the
    > >database,this would be an advantage where multiple PostgreSQL server are
    > >used...
    > Well, this is sort of what AOLserver does for you without any need for
    > middlemen.
    
    What if you have a server farm of 8 AOL servers, and 12 perl clients, and
    3 MS Access connections, leaving things open? Is AOLserver parsing the
    Perl DBD/DBI, connects, too? So you're using AOLserver as (cough) a
    middleman? <g>
    
    > Again, reading stuff like this makes me think "ugh!"
    > This stuff is really pretty easy, it's amazing to me that the Apache/db
    > world talks about such kludges when they're clearly not necessary.
    
    How does AOL server time out access clients, ODBC connections, Perl
    clients? I thought it was mainly web-server stuff.
    
    Apache/PHP isn't the only problem. The problem isn't solved by
    telling others to fix their software, either... is this something
    that can be done _within_ postmaster?
    
    -Bop
    
    --
    Brought to you from iBop the iMac, a MacOS, Win95, Win98, LinuxPPC machine,
    which is currently in MacOS land.  Your bopping may vary.
    
    

21. Re: Re: [NOVICE] Re: re : PHP and persistent connections

    Ronald Chmara <ron@opus1.com> — 2000-11-27T07:56:03Z

    Tom Samplonius wrote:
    > On Sun, 26 Nov 2000, Alain Toussaint wrote:
    > > > "I have all sorts of client apps, connecting in different ways, to
    > > > my server. Some of the clients are leaving their connections open,
    > > > but unused. How can I prevent running out of backends, and boot
    > > > the inactive users off?"
    > > how about having a middle man between apache (or aolserver or any other
    > > clients...) and PosgreSQL ??
    >   I don't see it solving anything.  You just move the connection
    > management problem from the database to the middleman (in the industry
    > such a thing would be called a query multiplexor).  Multiplexors have
    > often been used in the past to solve this problem, because the database
    > could not be extended or protected.
    
    And I'm requesting protection. Because the database isn't capable of dynamically
    detroying temporary backends. (Which would be another solution to this
    problem)
    
    >   Besides, if you are an n-tier developer, this isn't a problem as your
    > middle tier not does connection management, but some logic as well.  At
    > the end of the day, PHP/Apache is just not suitable for complex
    > applications.
    
    Is it dump on PHP day?
    
    Okay, pretend the problem is left-open Perl connections. Slam that for
    a while. Move over to left open Access connections. Bag on that for
    a few posts. Errant C code for a few days. Still have a problem. :-) 
    
    How does a db admin close connections that are idle, and unwanted, without
    shutting the postmaster down?
    
    -Bop
    
    --
    Brought to you from iBop the iMac, a MacOS, Win95, Win98, LinuxPPC machine,
    which is currently in MacOS land.  Your bopping may vary.
    
    

22. Re: Re: [NOVICE] Re: re : PHP and persistent connections

    Don Baccus <dhogaza@pacifier.com> — 2000-11-27T15:18:48Z

    At 12:38 AM 11/27/00 -0700, Ron Chmara wrote:
    >Don Baccus wrote:
    >> At 12:07 AM 11/26/00 -0500, Alain Toussaint wrote:
    >> >how about having a middle man between apache (or aolserver or any other
    >> >clients...) and PosgreSQL ??
    >> >that middleman could be configured to have 16 persistant connections,every
    >> >clients would deal with the middleman instead of going direct to the
    >> >database,this would be an advantage where multiple PostgreSQL server are
    >> >used...
    >> Well, this is sort of what AOLserver does for you without any need for
    >> middlemen.
    >
    >What if you have a server farm of 8 AOL servers, and 12 perl clients, and
    >3 MS Access connections, leaving things open? Is AOLserver parsing the
    >Perl DBD/DBI, connects, too? So you're using AOLserver as (cough) a
    >middleman? <g>
    
    Well, no - we'd use the built-in Tcl, Python or nsjava (still in infancy)
    modules which interface natively to AOLserver's built-in database API.
    
    You don't NEED the various connection implementations buried in various
    languages because they're provided directly in the server.  That's the
    point.  That's the main reason people use it.
    
    If you're going to run CGI/Perl scripts using its database connectivity
    stuff, don't use AOLserver.  They'll run since AOLserver supports CGI,
    but they'll run no better than under Apache and probably worse, since
    no one doing serious AOLserver work uses CGI and therefore the code which
    implements it has languished - there's no motivation to improve something
    that no one uses.
    
    If you're willing to use a language module which exposes the AOLserver
    API to your application, then AOLserver's a great choice.
    
    >> Again, reading stuff like this makes me think "ugh!"
    >> This stuff is really pretty easy, it's amazing to me that the Apache/db
    >> world talks about such kludges when they're clearly not necessary.
    >
    >How does AOL server time out access clients, ODBC connections, Perl
    >clients? I thought it was mainly web-server stuff.
    
    Well, for starters one normally wouldn't use ODBC since AOLserver
    includes drivers for PostgreSQL, Oracle and Sybase.  There's one for
    Solid, too, but no one seems to use Solid since they raised their
    prices drastically a couple of years ago (if you're going to spend
    lots of money on a database, Oracle and Sybase are more than willing
    to help you).  Nor does nsjava use JDBC, it encapsulates the AOLserver
    API into a database API class(es?).
    
    AOLserver manages the database pools in about the same way it manages
    threads, i.e. if a thread can't get the handles it needs (usually only
    one, sometimes two, more than that usually indicates poorly written
    code) it blocks until another thread releases a handle.  When a thread
    ends (returns a page) any allocated handles are released.  Transactions
    that haven't been properly committed are rolled back as well (lesser of
    two evils - the event's logged since it indicates a bug).  
    
    For each pool you provide the name of the driver (which of course serves
    to select which RDMBS that pool will use - you can use as many different
    RDBMSs as you have, and have drivers for), a datasource, the maximum 
    number of connections to open for that pool, minimum and maximum lifetimes
    for connections, etc.  
    
    
    
    - Don Baccus, Portland OR <dhogaza@pacifier.com>
      Nature photos, on-line guides, Pacific Northwest
      Rare Bird Alert Service and other goodies at
      http://donb.photo.net.
    
    

23. Re: Re: [NOVICE] Re: re : PHP and persistent connections

    Ross Reedstrom <reedstrm@rice.edu> — 2000-11-27T16:46:08Z

    Uh, Don?
    Not all the world's a web page, you know. Thatkind of thinking is _so_
    mid 90's ;-) Dedicated apps that talk directly the user seem to be making
    a comeback, due to a number of factors.  They can have much cleaner user
    interfaces, for example.
    
    Which brings us back around to the point of why this is on Hackers:
    PostgreSQL currently has no clean method for dropping idle connections.
    Yes, some apps handle this themselves, but not all. A number of people
    seem to feel there is a need for this feature. How hard would it be to
    implement? 
    
    Probably not too hard: we've already got an 'idle' state, suring which we 
    block on the input. Add a timeout to hat, and we're pretty much there.
    
    <goes and looks at code for a bit> 
    
    Hmm, we're down in the bowels of libpq, doing a recv() on the socket
    to the frontend, about 4 layers down from backend's blocking call to
    ReadCommand(). I seem to recall someone working on creating an async
    version of the libpq API, but Tom not being happy with the approach.
    So, it's not a simple change.
    
    Ross
    
    On Mon, Nov 27, 2000 at 07:18:48AM -0800, Don Baccus wrote:
    > At 12:38 AM 11/27/00 -0700, Ron Chmara wrote:
    > >Don Baccus wrote:
    > >> At 12:07 AM 11/26/00 -0500, Alain Toussaint wrote:
    > >> >how about having a middle man between apache (or aolserver or any other
    > >> >clients...) and PosgreSQL ??
    > >> >that middleman could be configured to have 16 persistant connections,every
    > >> >clients would deal with the middleman instead of going direct to the
    > >> >database,this would be an advantage where multiple PostgreSQL server are
    > >> >used...
    > >> Well, this is sort of what AOLserver does for you without any need for
    > >> middlemen.
    > >
    > >What if you have a server farm of 8 AOL servers, and 12 perl clients, and
    > >3 MS Access connections, leaving things open? Is AOLserver parsing the
    > >Perl DBD/DBI, connects, too? So you're using AOLserver as (cough) a
    > >middleman? <g>
    
    Note that only the AOL servers here are web client/servers, the rest are
    dedicated apps.
    
    <snip Don missing the point>
    
    -- 
    Open source code is like a natural resource, it's the result of providing
    food and sunshine to programmers, and then staying out of their way.
    [...] [It] is not going away because it has utility for both the developers 
    and users independent of economic motivations.  Jim Flynn, Sunnyvale, Calif.
    
    

24. re: PHP and persistent connections

    Philip Hallstrom <philip@adhesivemedia.com> — 2000-11-27T16:55:58Z

    You could set MaxRequestsPerChild in apache's httpd.conf.  This controls
    how many requests each apache process is allowed to serve.  After it
    serves this many the process dies which should close the postgres process
    as well (if it isn't, you have other problems). 
    
    I know that for a long time Apache recommened setting this fairly low on
    Solaris due to a memory leak in solaris...ideally you'd want to set this
    really high, but setting it low will make the processes die...
    
    -philip
    
    On Fri, 24 Nov 2000, jmcazurin wrote:
    
    > 
    > At 12:47 PM 11/24/00, GH wrote:
    > >On Fri, Nov 24, 2000 at 03:17:59PM +1100, some SMTP stream spewed forth:
    > > > Oh, and if you are using pg_close() I don't think it works
    > > > in any currently released PHP4 versions. See:
    > >
    > >This seems to be true. I ran into some fun link errors while
    > >connecting and disconnecting more than once in a script.
    > 
    >    This sounds disturbing!
    > 
    >    How then should I go about closing persistent connections? Can I close 
    > them at all?
    > 
    >    Would pg_close() work if I used it on non-persistent connections?
    > 
    >    Thanks in advance,
    > 
    > Mikah
    > 
    
    
    

25. Re: Re: re : PHP and persistent connections

    Philip Hallstrom <philip@adhesivemedia.com> — 2000-11-27T16:57:23Z

    > Is it possible to set something like a timeout for persistent connctions?
    > (Er, would that be something that someone would want 
    > 	to do? A Bad Thing?)
    
    see my other email about apache's MaxRequestsPerChild...
    
    > What happens when the httpd process that held a persistent connection
    > dies? Does "its" postgres process drop the connection and wait for
    > others? When the spare apache processes die, the postgres processes
    > remain.
    
    On my server (freebsd 4.x, php 4.0.2, postgresl 7.0.3) when I kill the
    httpd processes the postgres processes die as well...
    
    
    

26. Re: Re: [NOVICE] Re: re : PHP and persistent connections

    Tom Lane <tgl@sss.pgh.pa.us> — 2000-11-27T17:09:00Z

    "Ross J. Reedstrom" <reedstrm@rice.edu> writes:
    > Which brings us back around to the point of why this is on Hackers:
    > PostgreSQL currently has no clean method for dropping idle connections.
    > Yes, some apps handle this themselves, but not all. A number of people
    > seem to feel there is a need for this feature.
    
    I'm still not following exactly what people think would happen if we did
    have such a "feature".  OK, the backend times out after some interval
    of seeing no activity, and disconnects.  How is the client going to
    react to that, exactly, and why would it not conclude that something's
    gone fatally wrong with the database?
    
    Seems to me that you still end up having to fix the client, and that
    in the last analysis this is a client issue, not something for the
    backend to hack around.
    
    			regards, tom lane
    
    

27. Re: Re: [NOVICE] Re: re : PHP and persistent connections

    Ross Reedstrom <reedstrm@rice.edu> — 2000-11-27T18:10:39Z

    On Mon, Nov 27, 2000 at 12:09:00PM -0500, Tom Lane wrote:
    > 
    > I'm still not following exactly what people think would happen if we did
    > have such a "feature".  OK, the backend times out after some interval
    > of seeing no activity, and disconnects.  How is the client going to
    > react to that, exactly, and why would it not conclude that something's
    > gone fatally wrong with the database?
    
    Because a lot of commercial (and other) databases have this "feature",
    a lot of well behaved apps (and middleware packages) already know how
    to deal with it: i.e. try to reconnect, and continue.  If that fails,
    throw an error.
    
    > Seems to me that you still end up having to fix the client, and that
    > in the last analysis this is a client issue, not something for the
    > backend to hack around.
    
    It's already fixed, see above. In addition, your assuming the same
    administrative entity has control over the clients and the backend.
    This is not always the case.  For example, in a web hosting environment.
    Then, the DBA has the responsibiltiy to ensure minimal interference
    between different customers.
    
    As it stands, the client that causes the problem sees no problem to
    fix: other clients get 'that damn PostgreSQL backend quits accepting
    connections', and yell at the DBA. So, the DBA wants a way to propagate
    the 'problem' to the clients that cause it, by timing out the idle
    connections. Then, those clients _will_ fix their code, if it doesn't
    already do it for them, as per above.
    
    Basically, PostgreSQL is being too polite: it's in the clients interest to
    keep the connection open, since it minimizes response time, regardless
    of how this might affect other backends. It's cooperative vs. hard
    multitasking, all over again.
    
    Clients and servers optimize for different parameters: the client wants
    minimum response time for it's requests. The backend wants minimum
    _average_ response time, over all requests.
    
    Ross
    -- 
    Open source code is like a natural resource, it's the result of providing
    food and sunshine to programmers, and then staying out of their way.
    [...] [It] is not going away because it has utility for both the developers 
    and users independent of economic motivations.  Jim Flynn, Sunnyvale, Calif.
    
    

28. Re: Re: [NOVICE] Re: re : PHP and persistent connections

    Don Baccus <dhogaza@pacifier.com> — 2000-11-27T18:30:27Z

    At 10:46 AM 11/27/00 -0600, Ross J. Reedstrom wrote:
    >Uh, Don?
    >Not all the world's a web page, you know. Thatkind of thinking is _so_
    >mid 90's ;-) Dedicated apps that talk directly the user seem to be making
    >a comeback, due to a number of factors.  They can have much cleaner user
    >interfaces, for example.
    
    Of course.  But the question's been raised in the context of a web server,
    and I've answered in context.
    
    I've been trying to move the discussion offline to avoid clogging
    the hackers list with this stuff but some of the messages have escaped
    my machine with my forgetting to remove pg_hackers from the distribution
    list.  I'll try to be more diligent if the discussion continues.
    
    
    
    - Don Baccus, Portland OR <dhogaza@pacifier.com>
      Nature photos, on-line guides, Pacific Northwest
      Rare Bird Alert Service and other goodies at
      http://donb.photo.net.