PHPBuilder article -- Postgres vs MySQL

At 11:38 PM 11/12/00 -0500, Michael Fork wrote:

Thought this may be of interest to some...

http://www.phpbuilder.com/columns/tim20001112.php3

I just submitted it to slashdot, what the heck :)

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

And now it's on www.slashdot.org ...

http://slashdot.org/articles/00/11/13/1342208.shtml

Poul L. Christiansen

Michael Fork wrote:

Thought this may be of interest to some...

http://www.phpbuilder.com/columns/tim20001112.php3

Michael Fork - CCNA - MCP - A+
Network Support - Toledo Internet Access - Toledo Ohio

It would be nice if the fourth page were there. Am I the only one getting
"Not Found"?

Rob Nelson
rdnelson@co.centre.pa.us

[ Charset ISO-8859-1 unsupported, converting... ]

And now it's on www.slashdot.org ...

http://slashdot.org/articles/00/11/13/1342208.shtml

Poul L. Christiansen

Michael Fork wrote:

Thought this may be of interest to some...

http://www.phpbuilder.com/columns/tim20001112.php3

Michael Fork - CCNA - MCP - A+
Network Support - Toledo Internet Access - Toledo Ohio

It would be nice if the fourth page were there. Am I the only one getting
"Not Found"?

They are getting lots of hits. Hit reload and eventually it will
appear. I am on page six now.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

I made it all the way through the article. I'll summarize it for you:
Postgres - hooray!
MySQL - boo!
Since this is an open source database article linked off of slashdot, I
imagine they're getting pounded.

David Boerwinkle

-----Original Message-----
From: Robert D. Nelson <RDNELSON@co.centre.pa.us>
To: Michael Fork <mfork@toledolink.com>; Poul L.Christiansen
<poulc@cs.auc.dk>
Cc: pgsql-general <pgsql-general@postgresql.org>; pgsql-hackers
<pgsql-hackers@postgresql.org>
Date: Monday, November 13, 2000 8:31 AM
Subject: RE: [GENERAL] PHPBuilder article -- Postgres vs MySQL

And now it's on www.slashdot.org ...

http://slashdot.org/articles/00/11/13/1342208.shtml

Poul L. Christiansen

Michael Fork wrote:

I made it all the way through the article. I'll summarize it for you:
Postgres - hooray!
MySQL - boo!

Yeah, and that's about it. No analysis or anything. Disappointing, after
waiting so long for the pages to load.

Since this is an open source database article linked off of slashdot, I
imagine they're getting pounded.

Still...Regardless of what database they're running, either their
abstraction layer is shit or their queries really need optimized. Is that
perhaps why, even at 5 clients, the page views he shows never went
significantly above 10/sec?

Rob Nelson
rdnelson@co.centre.pa.us

Still...Regardless of what database they're running, either their
abstraction layer is shit or their queries really need optimized. Is that
perhaps why, even at 5 clients, the page views he shows never went
significantly above 10/sec?

I think this could be because they used real killer pages in the test,
and maybe this also the reason PgSQL fared this good (I've always
been and I'm still a postgres fan, but looking at that results I've
been quite astonished!!). Have you looked the spec? If I remember
well, Tim was talking about executing cuncurrently a page that
joined a dozen tables and another that was doing
update/select/insert on the same tables. Under these condition, 10
pages/sec it seems lighting to me!!!!

bye!

/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/

Fabrizio Ermini Alternate E-mail:
C.so Umberto, 7 faermini@tin.it
loc. Meleto Valdarno Mail on GSM: (keep it short!)
52020 Cavriglia (AR) faermini@sms.tin.it

On Lun 13 Nov 2000 13:22, Robert D. Nelson wrote:

Still...Regardless of what database they're running, either their
abstraction layer is shit or their queries really need optimized. Is that
perhaps why, even at 5 clients, the page views he shows never went
significantly above 10/sec?

In the article it was said that the querys were unoptimized to get the best
out of the database. Tim said that with some changes to the querys they could
have gotten much better results.

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

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.

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

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

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

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

;)

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.

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

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.

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

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:

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