Horton

Thread

  1. Re: [HACKERS] Re: PHPBuilder article -- Postgres vs MySQL

    carl garland <carlhgarland@hotmail.com> — 2000-11-15T08:40:28Z

    >perhaps why, even at 5 clients, the page views he shows never went 
>significantly above 10/sec?

I think alot of it has to do with the web server/db setup not pg.  They are 
using Apache/PHP and looking at their code every page has the additional 
overhead of making the db connection.  Now if they had used AOLserver with 
its persistent db connecction pooling scheme they may have faired better ;)

_________________________________________________________________________
Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com.

Share information about yourself, create your own public profile at 
http://profiles.msn.com.

  2. Re: [HACKERS] Re: PHPBuilder article -- Postgres vs MySQL

    mlw <markw@mohawksoft.com> — 2000-11-15T14:05:19Z

    carl garland wrote:
> 
> >perhaps why, even at 5 clients, the page views he shows never went
> >significantly above 10/sec?
> 
> I think alot of it has to do with the web server/db setup not pg.  They are
> using Apache/PHP and looking at their code every page has the additional
> overhead of making the db connection.  Now if they had used AOLserver with
> its persistent db connecction pooling scheme they may have faired better ;)

PHP has a persistent PostgreSQL open pg_pConnect(....) and it does make
a difference. 

I use postgres as a music database back-end for a PHP web server.
(Actually it is a web farm, with many instances of the database, one per
web server)

The one problem I have had with Postgres is its stubborn refusal to use
an index. I understand the reasons why it won't, but it is wrong, so I
sped it up by starting the backends with -fs.

That may be the issue.

On a side note:
I'm not sure of the current workings of the vacuum and statistics vs
indexing issue, I am new to this list, but I do have a 7.0.2 relevant
observation:

My music database has 50,000 arises and 210,000 albums. Many artists
have only one or 2 entries in the albums table (for the youngsters, CD
table ;-). About 34,000 have the integer key for "Various Artists" as
their artist entry, and another few thousand have things like "Movie
Soundtrack" and so on.

When the statistics are computed, these relatively few records with a
huge number of relations distort the statistics and make it impossible
to get postgres to use an index on that table without the -fs switch.

This is bad because it always forces use of an index, even when postgres
would legitimately ignore it.






-- 
http://www.mohawksoft.com

  3. Re: Re: [GENERAL] PHPBuilder article -- Postgres vs MySQL

    Tom Samplonius <tom@sdf.com> — 2000-11-15T17:27:48Z

    On Wed, 15 Nov 2000, carl garland wrote:

> >perhaps why, even at 5 clients, the page views he shows never went 
> >significantly above 10/sec?
> 
> I think alot of it has to do with the web server/db setup not pg.  They are 
> using Apache/PHP and looking at their code every page has the additional 
> overhead of making the db connection.  Now if they had used AOLserver with 
> its persistent db connecction pooling scheme they may have faired better ;)

  I doubt it.  PostgreSQL has a higher connection startup overhead than
MySQL, so if every view required a new database connection, it would been
quite a detriment to the PostgreSQL scores.

  PHP can maintain persisitant connections.  Unfortunately, this means
that you end up with a database connection per httpd process.  That really
isn't a problem for PostgreSQL though, it just requires sufficent memory.  
No doubt that is what was being done.

  AOLServer isn't the only system that can pool database connections, so
can servlets/JSP, ColdFusion, ASP, etc.  No doubt AOLServer would be more
widely accepted if it used something other than TCL.

Tom

  4. Re: [HACKERS] Re: PHPBuilder article -- Postgres vs MySQL

    joseph@randomnetworks.com — 2000-11-15T17:28:43Z

    On Wed, 15 Nov 2000, carl garland wrote:

# >perhaps why, even at 5 clients, the page views he shows never went 
# >significantly above 10/sec?
# 
# I think alot of it has to do with the web server/db setup not pg.  They are 
# using Apache/PHP and looking at their code every page has the additional 
# overhead of making the db connection.  Now if they had used AOLserver with 
# its persistent db connecction pooling scheme they may have faired better ;)

	I haven't actually looked at their code they used to test with to
see if they are actually using it, but Apache/PHP has the ability to do
persistent db connections.  I would be surprised that someone like Tim (
who seems to have done a fair bit of php with db stuff) would not make use
of such a feature.

	If you can point out an example of where they are not using this
feature I will gladly stand corrected.

--------------------------------------------------------------------
| Joseph Scott                       The Office Of Water Programs  |
| joseph@randomnetworks.com          joseph.scott@owp.csus.edu     |
--------------------------------------------------------------------

  5. Re: [HACKERS] Re: PHPBuilder article -- Postgres vs MySQL

    Adam Lang <aalang@rutgersinsurance.com> — 2000-11-15T18:05:40Z

    PHP 4 was having problems with persistent connections (not sure if with just
pgsql or all dbs).  Maybe that is why they didn't use it.

Adam Lang
Systems Engineer
Rutgers Casualty Insurance Company
----- Original Message -----
From: <joseph@randomnetworks.com>
To: "carl garland" <carlhgarland@hotmail.com>
Cc: <martin@math.unl.edu.ar>; <RDNELSON@co.centre.pa.us>;
<davidb@vectormath.com>; <mfork@toledolink.com>; <poulc@cs.auc.dk>;
<pgsql-general@postgresql.org>; <pgsql-hackers@postgresql.org>
Sent: Wednesday, November 15, 2000 12:28 PM
Subject: Re: [HACKERS] Re: [GENERAL] PHPBuilder article -- Postgres vs MySQL


>
> On Wed, 15 Nov 2000, carl garland wrote:
>
> # >perhaps why, even at 5 clients, the page views he shows never went
> # >significantly above 10/sec?
> #
> # I think alot of it has to do with the web server/db setup not pg.  They
are
> # using Apache/PHP and looking at their code every page has the additional
> # overhead of making the db connection.  Now if they had used AOLserver
with
> # its persistent db connecction pooling scheme they may have faired better
;)
>
> I haven't actually looked at their code they used to test with to
> see if they are actually using it, but Apache/PHP has the ability to do
> persistent db connections.  I would be surprised that someone like Tim (
> who seems to have done a fair bit of php with db stuff) would not make use
> of such a feature.
>
> If you can point out an example of where they are not using this
> feature I will gladly stand corrected.
>
> --------------------------------------------------------------------
> | Joseph Scott                       The Office Of Water Programs  |
> | joseph@randomnetworks.com          joseph.scott@owp.csus.edu     |
> --------------------------------------------------------------------

  6. Re: [HACKERS] Re: PHPBuilder article -- Postgres vs MySQL

    Don Baccus <dhogaza@pacifier.com> — 2000-11-15T18:25:10Z

    At 09:27 AM 11/15/00 -0800, Tom Samplonius wrote:

>  AOLServer isn't the only system that can pool database connections, so
>can servlets/JSP, ColdFusion, ASP, etc.  No doubt AOLServer would be more
>widely accepted if it used something other than TCL.

There are two separate modules that support Java in AOLserver: ns_tomcat
which provides an identical interface as Apache tomcat (and no real
advantages) and ns_java, which is coming out of the OpenACS project.  ns_java
exposes AOLserver's pooled, persistent database API to java.

There's also support available for Python, though there's still a lot of
work to be done to support the full AOLserver API (same's true of ns_java,
actually).

If you use ADP pages, your use of Tcl is typically restricted to snippets of
code anyway, so I've never really understood the complaints about Tcl...



- 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.

  7. Re: Re: [GENERAL] PHPBuilder article -- Postgres vs MySQL

    mlw <markw@mohawksoft.com> — 2000-11-15T18:53:34Z

    Andrew McMillan wrote:

> mlw wrote:
> >
> > My music database has 50,000 arises and 210,000 albums. Many artists
> > have only one or 2 entries in the albums table (for the youngsters, CD
> > table ;-). About 34,000 have the integer key for "Various Artists" as
> > their artist entry, and another few thousand have things like "Movie
> > Soundtrack" and so on.
> >
> > When the statistics are computed, these relatively few records with a
> > huge number of relations distort the statistics and make it impossible
> > to get postgres to use an index on that table without the -fs switch.
> >
> > This is bad because it always forces use of an index, even when postgres
> > would legitimately ignore it.
>
> What about doing:
>         SET enable_seqscan TO 'Off';
> Just before the query in question?
>
> That way you'd only affect the single query.  Possibly you could even
> code to spot the two aberrant situations and not do it in those ones.

I'd rather not pollute the application's SQL with postgres-isms. Not that I
don't love postgres, but there are always critics looking for a reason to use
Oracle or (gasp) MS-SQL.

As for "code to spot.." I am fairly new to hacking postgres. (Though, I have
been using it in various projects since ~1995), but I am excellent C/C++ guy,
give me a pointer to where (a) statistics are calculated, and (b) where they
are interpreted, and I would do that.

Just a question, however, what is the feeling about the way statistics are
currently being calculated? My feeling is that some sort of windowing
algorithm be used to normalize the statistics to the majority of the entries
in a table.  It could be as simple as discarding the upper and lower 10% of
the record stats, and use the remaining 80% for statistics. That would
certainly take care of my problem (and others I am sure), and I'd be glad to
write it. ;-)

>
>
> Regards,
>                                         Andrew.
> --
> _____________________________________________________________________
>             Andrew McMillan, e-mail: Andrew@cat-it.co.nz
> Catalyst IT Ltd, PO Box 10-225, Level 22, 105 The Terrace, Wellington
> Me: +64 (21) 635 694, Fax: +64 (4) 499 5596, Office: +64 (4) 499 2267

  8. Re: [HACKERS] Re: PHPBuilder article -- Postgres vs MySQL

    Don Baccus <dhogaza@pacifier.com> — 2000-11-15T19:09:06Z

    At 01:53 PM 11/15/00 -0500, markw wrote:

>I'd rather not pollute the application's SQL with postgres-isms. Not that I
>don't love postgres, but there are always critics looking for a reason to use
>Oracle or (gasp) MS-SQL.

Define some global variable with the name of the database being run (currently
only Postgres) and guard the SET statement with a conditional...

In the OpenACS project we've got little functions that return query snippets
called things like "db_nextval" that return either "sequence_name.nextval"
or "nextval('sequence_name')" depending on whether the code's running
under Oracle or Postgres.  That helps us minimize differences in the source.




- 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.

  9. Re: [HACKERS] Re: PHPBuilder article -- Postgres vs MySQL

    Tom Lane <tgl@sss.pgh.pa.us> — 2000-11-15T19:15:59Z

    markw <markw@mohawksoft.com> writes:
> Just a question, however, what is the feeling about the way statistics are
> currently being calculated?

They suck, no question about it ;-)

> My feeling is that some sort of windowing
> algorithm be used to normalize the statistics to the majority of the entries
> in a table.  It could be as simple as discarding the upper and lower 10% of
> the record stats, and use the remaining 80% for statistics.

I think what most of the discussion has focused on is building
histograms.  The current upper-and-lower-bounds-only approach just
plain isn't enough data, even if you discard outliers so that the
data isn't actively misleading.

As far as the most-common-value issue goes, if you have one value that
is vastly more common than any other, I think it would be a real mistake
to throw away that information --- that would mean the planner would do
the wrong thing for queries that do involve that value.  What we need
is to save info about several top-frequency values, maybe three or so,
not just one.  Also the finding of those values needs to be much more
robust than it is currently.

See past discussions in pghackers --- there have been plenty...

			regards, tom lane

  10. Re: [HACKERS] Re: PHPBuilder article -- Postgres vs MySQL

    Charles Tassell <ctassell@isn.net> — 2000-11-15T21:54:41Z

    Just a note, I've been using Postgres 7.02 and PHP 4.02 or 4.03 for about a 
month in a couple sites, and haven't experienced any problems with 
persistent connections.  Problem might have been fixed in one of the point 
releases, or maybe I just don't have enough different db connections to 
trigger it.


At 02:05 PM 11/15/00, Adam Lang wrote:
>PHP 4 was having problems with persistent connections (not sure if with just
>pgsql or all dbs).  Maybe that is why they didn't use it.
>
>Adam Lang
>Systems Engineer
>Rutgers Casualty Insurance Company
>----- Original Message -----
>From: <joseph@randomnetworks.com>
>To: "carl garland" <carlhgarland@hotmail.com>
>Cc: <martin@math.unl.edu.ar>; <RDNELSON@co.centre.pa.us>;
><davidb@vectormath.com>; <mfork@toledolink.com>; <poulc@cs.auc.dk>;
><pgsql-general@postgresql.org>; <pgsql-hackers@postgresql.org>
>Sent: Wednesday, November 15, 2000 12:28 PM
>Subject: Re: [HACKERS] Re: [GENERAL] PHPBuilder article -- Postgres vs MySQL
>
>
> >
> > On Wed, 15 Nov 2000, carl garland wrote:
> >
> > # >perhaps why, even at 5 clients, the page views he shows never went
> > # >significantly above 10/sec?
> > #
> > # I think alot of it has to do with the web server/db setup not pg.  They
>are
> > # using Apache/PHP and looking at their code every page has the additional
> > # overhead of making the db connection.  Now if they had used AOLserver
>with
> > # its persistent db connecction pooling scheme they may have faired better
>;)
> >
> > I haven't actually looked at their code they used to test with to
> > see if they are actually using it, but Apache/PHP has the ability to do
> > persistent db connections.  I would be surprised that someone like Tim (
> > who seems to have done a fair bit of php with db stuff) would not make use
> > of such a feature.
> >
> > If you can point out an example of where they are not using this
> > feature I will gladly stand corrected.
> >
> > --------------------------------------------------------------------
> > | Joseph Scott                       The Office Of Water Programs  |
> > | joseph@randomnetworks.com          joseph.scott@owp.csus.edu     |
> > --------------------------------------------------------------------

  11. Re: [HACKERS] Re: PHPBuilder article -- Postgres vs MySQL

    Adam Lang <aalang@rutgersinsurance.com> — 2000-11-16T13:47:42Z

    It is possible.  I think the problems were arising before the currentpatch.
Problems involved not properly releasing conenctions and they were
eventually filling up as used.

Adam Lang
Systems Engineer
Rutgers Casualty Insurance Company
----- Original Message -----
From: "Charles Tassell" <ctassell@isn.net>
To: "Adam Lang" <aalang@rutgersinsurance.com>
Cc: <pgsql-general@postgresql.org>
Sent: Wednesday, November 15, 2000 4:54 PM
Subject: Re: [HACKERS] Re: [GENERAL] PHPBuilder article -- Postgres vs MySQL


> Just a note, I've been using Postgres 7.02 and PHP 4.02 or 4.03 for about
a
> month in a couple sites, and haven't experienced any problems with
> persistent connections.  Problem might have been fixed in one of the point
> releases, or maybe I just don't have enough different db connections to
> trigger it.
>
>
> At 02:05 PM 11/15/00, Adam Lang wrote:
> >PHP 4 was having problems with persistent connections (not sure if with
just
> >pgsql or all dbs).  Maybe that is why they didn't use it.
> >
> >Adam Lang
> >Systems Engineer
> >Rutgers Casualty Insurance Company
> >----- Original Message -----
> >From: <joseph@randomnetworks.com>
> >To: "carl garland" <carlhgarland@hotmail.com>
> >Cc: <martin@math.unl.edu.ar>; <RDNELSON@co.centre.pa.us>;
> ><davidb@vectormath.com>; <mfork@toledolink.com>; <poulc@cs.auc.dk>;
> ><pgsql-general@postgresql.org>; <pgsql-hackers@postgresql.org>
> >Sent: Wednesday, November 15, 2000 12:28 PM
> >Subject: Re: [HACKERS] Re: [GENERAL] PHPBuilder article -- Postgres vs
MySQL
> >
> >
> > >
> > > On Wed, 15 Nov 2000, carl garland wrote:
> > >
> > > # >perhaps why, even at 5 clients, the page views he shows never went
> > > # >significantly above 10/sec?
> > > #
> > > # I think alot of it has to do with the web server/db setup not pg.
They
> >are
> > > # using Apache/PHP and looking at their code every page has the
additional
> > > # overhead of making the db connection.  Now if they had used
AOLserver
> >with
> > > # its persistent db connecction pooling scheme they may have faired
better
> >;)
> > >
> > > I haven't actually looked at their code they used to test with to
> > > see if they are actually using it, but Apache/PHP has the ability to
do
> > > persistent db connections.  I would be surprised that someone like Tim
(
> > > who seems to have done a fair bit of php with db stuff) would not make
use
> > > of such a feature.
> > >
> > > If you can point out an example of where they are not using this
> > > feature I will gladly stand corrected.
> > >
> > > --------------------------------------------------------------------
> > > | Joseph Scott                       The Office Of Water Programs  |
> > > | joseph@randomnetworks.com          joseph.scott@owp.csus.edu     |
> > > --------------------------------------------------------------------

  12. Re: [HACKERS] Re: PHPBuilder article -- Postgres vs MySQL

    Marc G. Fournier <scrappy@hub.org> — 2000-11-16T17:33:08Z

    On Wed, 15 Nov 2000, Charles Tassell wrote:

> Just a note, I've been using Postgres 7.02 and PHP 4.02 or 4.03 for about a 
> month in a couple sites, and haven't experienced any problems with 
> persistent connections.  Problem might have been fixed in one of the point 
> releases, or maybe I just don't have enough different db connections to 
> trigger it.

I run PHP4 and IMP (http://www.horde.org) and we've gotten then to remove
the useof pg_pconnect() since it is broken.  Broken how, you might
ask?  Well, I ran on a standalone machine, no other web users but myself,
to test, and each tim eI hit the database with IMP,. it opened a new
backend, but it never reused old, idle ones ... eventually, you run out of
the ability to connect since you've locked up all connections ...



 > > 
> At 02:05 PM 11/15/00, Adam Lang wrote:
> >PHP 4 was having problems with persistent connections (not sure if with just
> >pgsql or all dbs).  Maybe that is why they didn't use it.
> >
> >Adam Lang
> >Systems Engineer
> >Rutgers Casualty Insurance Company
> >----- Original Message -----
> >From: <joseph@randomnetworks.com>
> >To: "carl garland" <carlhgarland@hotmail.com>
> >Cc: <martin@math.unl.edu.ar>; <RDNELSON@co.centre.pa.us>;
> ><davidb@vectormath.com>; <mfork@toledolink.com>; <poulc@cs.auc.dk>;
> ><pgsql-general@postgresql.org>; <pgsql-hackers@postgresql.org>
> >Sent: Wednesday, November 15, 2000 12:28 PM
> >Subject: Re: [HACKERS] Re: [GENERAL] PHPBuilder article -- Postgres vs MySQL
> >
> >
> > >
> > > On Wed, 15 Nov 2000, carl garland wrote:
> > >
> > > # >perhaps why, even at 5 clients, the page views he shows never went
> > > # >significantly above 10/sec?
> > > #
> > > # I think alot of it has to do with the web server/db setup not pg.  They
> >are
> > > # using Apache/PHP and looking at their code every page has the additional
> > > # overhead of making the db connection.  Now if they had used AOLserver
> >with
> > > # its persistent db connecction pooling scheme they may have faired better
> >;)
> > >
> > > I haven't actually looked at their code they used to test with to
> > > see if they are actually using it, but Apache/PHP has the ability to do
> > > persistent db connections.  I would be surprised that someone like Tim (
> > > who seems to have done a fair bit of php with db stuff) would not make use
> > > of such a feature.
> > >
> > > If you can point out an example of where they are not using this
> > > feature I will gladly stand corrected.
> > >
> > > --------------------------------------------------------------------
> > > | Joseph Scott                       The Office Of Water Programs  |
> > > | joseph@randomnetworks.com          joseph.scott@owp.csus.edu     |
> > > --------------------------------------------------------------------
> 
> 

Marc G. Fournier                   ICQ#7615664               IRC Nick: Scrappy
Systems Administrator @ hub.org 
primary: scrappy@hub.org           secondary: scrappy@{freebsd|postgresql}.org

  13. Re: [HACKERS] Re: PHPBuilder article -- Postgres vs MySQL

    Frank Joerdens <frank@joerdens.de> — 2000-11-16T18:08:40Z

    The Hermit Hacker wrote:
> 
> On Wed, 15 Nov 2000, Charles Tassell wrote:
> 
> > Just a note, I've been using Postgres 7.02 and PHP 4.02 or 4.03 for about a
> > month in a couple sites, and haven't experienced any problems with
> > persistent connections.  Problem might have been fixed in one of the point
> > releases, or maybe I just don't have enough different db connections to
> > trigger it.
> 
> I run PHP4 and IMP (http://www.horde.org) and we've gotten then to remove
> the useof pg_pconnect() since it is broken.  Broken how, you might
> ask?  Well, I ran on a standalone machine, no other web users but myself,
> to test, and each tim eI hit the database with IMP,. it opened a new
> backend, but it never reused old, idle ones ... eventually, you run out of
> the ability to connect since you've locked up all connections ...

That sounds pretty evil. Have you also tested PHP3 by any chance? Has this been posted to
php-general? If this is a general issue, it would have to be considered a _very_ nasty bug
indeed. Can you describe how you made the test, i.e. how you saw that it wouldn't reuse
idle ones and keeps opening new ones? How would you monitor this?

- Frank

  14. Re: [HACKERS] Re: PHPBuilder article -- Postgres vs MySQL

    Adam Lang <aalang@rutgersinsurance.com> — 2000-11-16T18:29:39Z

    Basically, after so many people connect and disconnect, the PHP doesn't
reuse/drop old connections so you maximum amount is reached.  You'd get to
the point where no one is connected, but one another person would try to and
it would come back with an error saying no connections available.

I am unaware if anything has been posted to PHP about it.

Adam Lang
Systems Engineer
Rutgers Casualty Insurance Company
----- Original Message -----
From: "Frank Joerdens" <frank@joerdens.de>
To: "The Hermit Hacker" <scrappy@hub.org>
Cc: "Charles Tassell" <ctassell@isn.net>; "Adam Lang"
<aalang@rutgersinsurance.com>; <pgsql-general@postgresql.org>;
<scherf@ventasoft.de>
Sent: Thursday, November 16, 2000 1:08 PM
Subject: Re: [HACKERS] Re: [GENERAL] PHPBuilder article -- Postgres vs MySQL


> The Hermit Hacker wrote:
> >
> > On Wed, 15 Nov 2000, Charles Tassell wrote:
> >
> > > Just a note, I've been using Postgres 7.02 and PHP 4.02 or 4.03 for
about a
> > > month in a couple sites, and haven't experienced any problems with
> > > persistent connections.  Problem might have been fixed in one of the
point
> > > releases, or maybe I just don't have enough different db connections
to
> > > trigger it.
> >
> > I run PHP4 and IMP (http://www.horde.org) and we've gotten then to
remove
> > the useof pg_pconnect() since it is broken.  Broken how, you might
> > ask?  Well, I ran on a standalone machine, no other web users but
myself,
> > to test, and each tim eI hit the database with IMP,. it opened a new
> > backend, but it never reused old, idle ones ... eventually, you run out
of
> > the ability to connect since you've locked up all connections ...
>
> That sounds pretty evil. Have you also tested PHP3 by any chance? Has this
been posted to
> php-general? If this is a general issue, it would have to be considered a
_very_ nasty bug
> indeed. Can you describe how you made the test, i.e. how you saw that it
wouldn't reuse
> idle ones and keeps opening new ones? How would you monitor this?
>
> - Frank

  15. Re: [HACKERS] Re: PHPBuilder article -- Postgres vs MySQL

    Karl DeBisschop <kdebisschop@alert.infoplease.com> — 2000-11-16T18:45:36Z

    Frank Joerdens wrote:
> 
> The Hermit Hacker wrote:
> >
> > On Wed, 15 Nov 2000, Charles Tassell wrote:
> >
> > > Just a note, I've been using Postgres 7.02 and PHP 4.02 or 4.03 for about a
> > > month in a couple sites, and haven't experienced any problems with
> > > persistent connections.  Problem might have been fixed in one of the point
> > > releases, or maybe I just don't have enough different db connections to
> > > trigger it.
> >
> > I run PHP4 and IMP (http://www.horde.org) and we've gotten then to remove
> > the useof pg_pconnect() since it is broken.  Broken how, you might
> > ask?  Well, I ran on a standalone machine, no other web users but myself,
> > to test, and each tim eI hit the database with IMP,. it opened a new
> > backend, but it never reused old, idle ones ... eventually, you run out of
> > the ability to connect since you've locked up all connections ...
> 
> That sounds pretty evil. Have you also tested PHP3 by any chance? Has this been posted to
> php-general? If this is a general issue, it would have to be considered a _very_ nasty bug
> indeed. Can you describe how you made the test, i.e. how you saw that it wouldn't reuse
> idle ones and keeps opening new ones? How would you monitor this?

We have looked into the issue. Our conclusion was that persistent 
connections are allocated by httpd process. They are persistent, 
but not shared. Thus, if you have hundredes of httpd processes, 
you have issues (fewer if you don't tune postgres max allowable 
backends). Or if there is more than one user or DB
per httpd process.

We have seen the same behavior on PHP3  and on PHP4.

Maybe our diagnosis is wrong. I would love to be able to
use persistent connections. But I don't think it is a bug 
per say. The docs talk about persistence. They say nothing
about pooling. (maybe phplib will do this -- I have not 
looked).
use persistent

-- 
Karl DeBisschop                        kdebisschop@alert.infoplease.com
Learning Network Reference             http://www.infoplease.com
Netsaint Plugin Developer              kdebisschop@users.sourceforge.net

  16. Re: [HACKERS] Re: PHPBuilder article -- Postgres vs MySQL

    Marko Kreen <marko@l-t.ee> — 2000-11-17T01:54:12Z

    On Thu, Nov 16, 2000 at 01:33:08PM -0400, The Hermit Hacker wrote:
> On Wed, 15 Nov 2000, Charles Tassell wrote:
> 
> > Just a note, I've been using Postgres 7.02 and PHP 4.02 or 4.03 for about a 
> > month in a couple sites, and haven't experienced any problems with 
> > persistent connections.  Problem might have been fixed in one of the point 
> > releases, or maybe I just don't have enough different db connections to 
> > trigger it.
> 
> I run PHP4 and IMP (http://www.horde.org) and we've gotten then to remove
> the useof pg_pconnect() since it is broken.  Broken how, you might
> ask?  Well, I ran on a standalone machine, no other web users but myself,
> to test, and each tim eI hit the database with IMP,. it opened a new
> backend, but it never reused old, idle ones ... eventually, you run out of
> the ability to connect since you've locked up all connections ...

Maybe you have the apache maxspareservers set too high?  I run
PHP4/apache (debian-woody) and it works ok.  If this is only IMP
site maybe you are better off

  apache.MaxClients = postgres.backendcount 

Just a thought.

-- 
marko

  17. Re: [HACKERS] Re: PHPBuilder article -- Postgres vs MySQL

    Marc G. Fournier <scrappy@hub.org> — 2000-11-17T06:33:06Z

    Install PgSQL, install PHP4, install IMP ... do it on a standalone machine
that you would never get an external connection on ... open connection to
IMP several times and watch number of postgres backends start and never
die ...


 On Thu, 16 Nov 2000, Frank Joerdens wrote:

> The Hermit Hacker wrote:
> > 
> > On Wed, 15 Nov 2000, Charles Tassell wrote:
> > 
> > > Just a note, I've been using Postgres 7.02 and PHP 4.02 or 4.03 for about a
> > > month in a couple sites, and haven't experienced any problems with
> > > persistent connections.  Problem might have been fixed in one of the point
> > > releases, or maybe I just don't have enough different db connections to
> > > trigger it.
> > 
> > I run PHP4 and IMP (http://www.horde.org) and we've gotten then to remove
> > the useof pg_pconnect() since it is broken.  Broken how, you might
> > ask?  Well, I ran on a standalone machine, no other web users but myself,
> > to test, and each tim eI hit the database with IMP,. it opened a new
> > backend, but it never reused old, idle ones ... eventually, you run out of
> > the ability to connect since you've locked up all connections ...
> 
> That sounds pretty evil. Have you also tested PHP3 by any chance? Has this been posted to
> php-general? If this is a general issue, it would have to be considered a _very_ nasty bug
> indeed. Can you describe how you made the test, i.e. how you saw that it wouldn't reuse
> idle ones and keeps opening new ones? How would you monitor this?
> 
> - Frank
> 
> 

Marc G. Fournier                   ICQ#7615664               IRC Nick: Scrappy
Systems Administrator @ hub.org 
primary: scrappy@hub.org           secondary: scrappy@{freebsd|postgresql}.org

  18. Re[2]: [HACKERS] Re: PHPBuilder article -- Postgres vs MySQL

    Alexey Borzov <borz_off@rdw.ru> — 2000-11-17T08:36:01Z

    Greetings, The Hermit Hacker!

  Well, there's a problem with PHP's [mis]documentation. First of all,
it counts open DB connections not on per-webserver, but on
per-process/thread basis.
  The default PHP config file has the limits of persistent and
non-persistent connections set to -1 (no limit)... Setting it to
some (supposedly) reasonable value (like, 50) accomplishes nothing: you
should multiply 50 by the number of webserver processes/threads. There
can be lots of them... :[
  And then there comes PHP's "logic": if I can just open the new
connection, why bother reusing the old one? And thus Postgres backends
start multiplying like rabbits, eventually reaching the limit... :[
  You should set a reasonable limit on number of open persistent
connections (like 1, maybe 2 or 3), only then PHP will actually reuse
them. My webserver now works with such setup and there are no more
problems with pg_pconnect().


Hell, I never thought I'll give advice to one of PgGurus. ;]

At 16.11.2000, 11:21, you wrote:
THH> I run PHP4 and IMP (http://www.horde.org) and we've gotten then to remove
THH> the useof pg_pconnect() since it is broken.  Broken how, you might
THH> ask?  Well, I ran on a standalone machine, no other web users but myself,
THH> to test, and each tim eI hit the database with IMP,. it opened a new
THH> backend, but it never reused old, idle ones ... eventually, you run out of
THH> the ability to connect since you've locked up all connections ...
-- 
Yours, Alexey V. Borzov, Webmaster of RDW

  19. Re: [HACKERS] Re: PHPBuilder article -- Postgres vs MySQL

    Martin A. Marques <martin@math.unl.edu.ar> — 2000-11-17T19:08:26Z

    On Jue 16 Nov 2000 22:54, Marko Kreen wrote:
> On Thu, Nov 16, 2000 at 01:33:08PM -0400, The Hermit Hacker wrote:
> >
> > I run PHP4 and IMP (http://www.horde.org) and we've gotten then to remove
> > the useof pg_pconnect() since it is broken.  Broken how, you might
> > ask?  Well, I ran on a standalone machine, no other web users but myself,
> > to test, and each tim eI hit the database with IMP,. it opened a new
> > backend, but it never reused old, idle ones ... eventually, you run out
> > of the ability to connect since you've locked up all connections ...
>
> Maybe you have the apache maxspareservers set too high?  I run
> PHP4/apache (debian-woody) and it works ok.  If this is only IMP
> site maybe you are better off
>
>   apache.MaxClients = postgres.backendcount

What I can say on my behave is that (as the only user of PHP and Postgres on 
the computer) when I login to IMP webmail, quite a lot of postmasters appear, 
and don't dissapear after logging off.
but I have no problem. I can be all day with IMP fooling around and the 
backend postmaster would stay around the 20 live conections.

Any comments?

Saludos.. :-)


-- 
"And I'm happy, because you make me feel good, about me." - Melvin Udall
-----------------------------------------------------------------
Martn Marqus			email: 	martin@math.unl.edu.ar
Santa Fe - Argentina		http://math.unl.edu.ar/~martin/
Administrador de sistemas en math.unl.edu.ar
-----------------------------------------------------------------

  20. Re: [HACKERS] Re: PHPBuilder article -- Postgres vs MySQL

    Philip Hallstrom <philip@adhesivemedia.com> — 2000-11-17T21:15:20Z

    > On Jue 16 Nov 2000 22:54, Marko Kreen wrote:
> > On Thu, Nov 16, 2000 at 01:33:08PM -0400, The Hermit Hacker wrote:
> > >
> > > I run PHP4 and IMP (http://www.horde.org) and we've gotten then to remove
> > > the useof pg_pconnect() since it is broken.  Broken how, you might
> > > ask?  Well, I ran on a standalone machine, no other web users but myself,
> > > to test, and each tim eI hit the database with IMP,. it opened a new
> > > backend, but it never reused old, idle ones ... eventually, you run out
> > > of the ability to connect since you've locked up all connections ...
> >
> > Maybe you have the apache maxspareservers set too high?  I run
> > PHP4/apache (debian-woody) and it works ok.  If this is only IMP
> > site maybe you are better off
> >
> >   apache.MaxClients = postgres.backendcount
> 
> What I can say on my behave is that (as the only user of PHP and Postgres on 
> the computer) when I login to IMP webmail, quite a lot of postmasters appear, 
> and don't dissapear after logging off.
> but I have no problem. I can be all day with IMP fooling around and the 
> backend postmaster would stay around the 20 live conections.
> 
> Any comments?

What is the value for MinSpareServers in your apache's conf?  If that's
set to 20, then even if you only hit your site 20 times (one for each),
that many apache processes will continue to live and therefore keep a db
connection open..??

What would be interesting to know is after your down how many postmaster
processes are running and how many httpd processes are running...

-philip

  21. Re: [HACKERS] Re: PHPBuilder article -- Postgres vs MySQL

    Martin A. Marques <martin@math.unl.edu.ar> — 2000-11-17T21:48:17Z

    On Fri, 17 Nov 2000, Philip Hallstrom wrote:

> What is the value for MinSpareServers in your apache's conf?  If that's
> set to 20, then even if you only hit your site 20 times (one for each),
> that many apache processes will continue to live and therefore keep a db
> connection open..??
> 
> What would be interesting to know is after your down how many postmaster
> processes are running and how many httpd processes are running...

postamaster - 19
httpd - 11

Saludos... ;-)


"And I'm happy, because you make me feel good, about me." - Melvin Udall
-----------------------------------------------------------------
Martín Marqués			email: 	martin@math.unl.edu.ar
Santa Fe - Argentina		http://math.unl.edu.ar/~martin/
Administrador de sistemas en math.unl.edu.ar
-----------------------------------------------------------------

  22. Re: [HACKERS] Re: PHPBuilder article -- Postgres vs MySQL

    Martin A. Marques <martin@math.unl.edu.ar> — 2000-11-20T18:43:27Z

    On Friday 17 November 2000 18:15, Philip Hallstrom wrote:
>
> What is the value for MinSpareServers in your apache's conf?  If that's
> set to 20, then even if you only hit your site 20 times (one for each),
> that many apache processes will continue to live and therefore keep a db
> connection open..??

I have 10 as MaxSpareServers and 5 as MinSpareServers.

> What would be interesting to know is after your down how many postmaster
> processes are running and how many httpd processes are running...

My question would be, when do those connections die?

Saludos... :-)


-- 
"And I'm happy, because you make me feel good, about me." - Melvin Udall
-----------------------------------------------------------------
Martn Marqus			email: 	martin@math.unl.edu.ar
Santa Fe - Argentina		http://math.unl.edu.ar/~martin/
Administrador de sistemas en math.unl.edu.ar
-----------------------------------------------------------------

  23. Re: [HACKERS] Re: PHPBuilder article -- Postgres vs MySQL

    Philip Hallstrom <philip@adhesivemedia.com> — 2000-11-21T16:41:04Z

    My understanding is that with your configs below you will always have 10
httpd proccesses running as long as your machine is up... ie... they will
never die.

On Mon, 20 Nov 2000, Martin A. Marques wrote:

> On Friday 17 November 2000 18:15, Philip Hallstrom wrote:
> >
> > What is the value for MinSpareServers in your apache's conf?  If that's
> > set to 20, then even if you only hit your site 20 times (one for each),
> > that many apache processes will continue to live and therefore keep a db
> > connection open..??
> 
> I have 10 as MaxSpareServers and 5 as MinSpareServers.
> 
> > What would be interesting to know is after your down how many postmaster
> > processes are running and how many httpd processes are running...
> 
> My question would be, when do those connections die?
> 
> Saludos... :-)
> 
> 
> -- 
> "And I'm happy, because you make me feel good, about me." - Melvin Udall
> -----------------------------------------------------------------
> Martn Marqus			email: 	martin@math.unl.edu.ar
> Santa Fe - Argentina		http://math.unl.edu.ar/~martin/
> Administrador de sistemas en math.unl.edu.ar
> -----------------------------------------------------------------
>

  24. Re: [HACKERS] Re: PHPBuilder article -- Postgres vs MySQL

    Tim Uckun <tim@diligence.com> — 2000-11-21T16:44:47Z

    >
> >  AOLServer isn't the only system that can pool database connections, so
> >can servlets/JSP, ColdFusion, ASP, etc.  No doubt AOLServer would be more
> >widely accepted if it used something other than TCL.
>
>There's also support available for Python, though there's still a lot of
>work to be done to support the full AOLserver API (same's true of ns_java,
>actually).
>
>If you use ADP pages, your use of Tcl is typically restricted to snippets of
>code anyway, so I've never really understood the complaints about Tcl...

What about the php module? Does it take advantage of API?
It seems to me your choice of web/application server is kind of dependent 
on the language you like. If you like perl/php use apache if you like tcl 
use aolserver, if you like java use tomcat,enhydra,orion (or whatever), if 
you like python use zope. I guess for the few people who like VB there is 
IIS/ASP.

:wq
Tim Uckun
Due Diligence Inc. http://www.diligence.com/   Americas Background 
Investigation Expert.
If your company isn't doing background checks, maybe you haven't considered 
the risks of a bad hire.

  25. Re: [HACKERS] Re: PHPBuilder article -- Postgres vs MySQL

    Don Baccus <dhogaza@pacifier.com> — 2000-11-23T15:58:29Z

    At 09:44 AM 11/21/00 -0700, Tim Uckun wrote:

>What about the php module? Does it take advantage of API?

I don't know.  If not, though, there wouldn't be much point in using
AOLserver, since the simple and efficient database API is the main
attraction.  So I think there's a pretty good chance it does.



- 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.

  26. Re: [HACKERS] Re: PHPBuilder article -- Postgres vs MySQL

    GH <grasshacker@over-yonder.net> — 2000-12-01T01:50:26Z

    On Thu, Nov 23, 2000 at 07:58:29AM -0800, some SMTP stream spewed forth: 
> At 09:44 AM 11/21/00 -0700, Tim Uckun wrote:
> 
> >What about the php module? Does it take advantage of API?
> 
> I don't know.  If not, though, there wouldn't be much point in using
> AOLserver, since the simple and efficient database API is the main
> attraction.  So I think there's a pretty good chance it does.
> 

Through the course of another thread on the lists we have concluded that
PHP does not support the AOLServer (or any other similar) database API.
The "blockage" is that PHP includes its own database functions, albeit
they are based on the Postgres, MySQL, etc. APIs individually. 

I am considering looking into urging an integration of PHP and
AOLServer's connection pooling (for lack of a better word) stuff.

*shrug*

gh

> 
> 
> - 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.

  27. Re: [HACKERS] Re: PHPBuilder article -- Postgres vs MySQL

    Don Baccus <dhogaza@pacifier.com> — 2000-12-01T01:57:44Z

    At 07:50 PM 11/30/00 -0600, GH wrote:
>On Thu, Nov 23, 2000 at 07:58:29AM -0800, some SMTP stream spewed forth: 
>> At 09:44 AM 11/21/00 -0700, Tim Uckun wrote:
>> 
>> >What about the php module? Does it take advantage of API?
>> 
>> I don't know.  If not, though, there wouldn't be much point in using
>> AOLserver, since the simple and efficient database API is the main
>> attraction.  So I think there's a pretty good chance it does.
>> 
>
>Through the course of another thread on the lists we have concluded that
>PHP does not support the AOLServer (or any other similar) database API.
>The "blockage" is that PHP includes its own database functions, albeit
>they are based on the Postgres, MySQL, etc. APIs individually. 
>
>I am considering looking into urging an integration of PHP and
>AOLServer's connection pooling (for lack of a better word) stuff.

Well, meanwhile I've gotten confirmation from folks in the PHP world 
(via an openacs forum) that it still isn't threadsafe, though there's
an effort underway to track down the problems.  I don't know how close
to solving this they are.



- 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.

  28. Re: [HACKERS] Re: PHPBuilder article -- Postgres vsMySQL

    Frank Joerdens <frank@joerdens.de> — 2000-12-01T11:39:50Z

    Don Baccus wrote:
> 
[ . . . ]
> Well, meanwhile I've gotten confirmation from folks in the PHP world
> (via an openacs forum) 

Which forum is that? I'm asking because I am currently trying to migrate
from PHP to Openacs, or integrate the two somehow (lurking on this list
has convinced me that openacs deserves a hard look indeed), and thus I'd
like to find a forum where relevant stuff is being discussed.

Ta,
Frank

  29. Re: [HACKERS] Re: PHPBuilder article -- Postgres vsMySQL

    Daniel Wickstrom <danw@rtp.ericsson.se> — 2000-12-01T14:53:01Z

    >>>>> "Frank" == Frank Joerdens <frank@joerdens.de> writes:

    Frank> Which forum is that? I'm asking because I am currently
    Frank> trying to migrate from PHP to Openacs, or integrate the two
    Frank> somehow (lurking on this list has convinced me that openacs
    Frank> deserves a hard look indeed), and thus I'd like to find a
    Frank> forum where relevant stuff is being discussed.


Check out the bboard forums at http://openacs.org/

-Dan

  30. Re: [HACKERS] Re: PHPBuilder article -- Postgres vsMySQL

    Don Baccus <dhogaza@pacifier.com> — 2000-12-01T14:57:45Z

    At 12:39 PM 12/1/00 +0100, Frank Joerdens wrote:
>Don Baccus wrote:
>> 
>[ . . . ]
>> Well, meanwhile I've gotten confirmation from folks in the PHP world
>> (via an openacs forum) 
>
>Which forum is that? I'm asking because I am currently trying to migrate
>from PHP to Openacs, or integrate the two somehow (lurking on this list
>has convinced me that openacs deserves a hard look indeed), and thus I'd
>like to find a forum where relevant stuff is being discussed.

Here's the URL to the thread discussion PHP and AOLserver (and other
stuff, as naturally happens to threads!)

http://openacs.org/bboard/q-and-a-fetch-msg.tcl?msg_id=0000lY&topic_id=11&topic=OpenACS



- 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.