>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


Mon, 13 Nov 2000 11:22:00 -0500
"Robert D. Nelson" <RDNELSON@co.centre.pa.us>
RE: PHPBuilder article -- Postgres vs MySQL
> 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

Mon, 13 Nov 2000 18:16:39 +0100
fabrizio.ermini@sysdat.it
RE: PHPBuilder article -- Postgres vs MySQL
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
-----------------------------------------------------------------

Wed, 15 Nov 2000 09:14:18 -0300
"Martin A. Marques" <martin@math.unl.edu.ar>
Re: PHPBuilder article -- Postgres vs MySQL
Speaking of MySQL, has anyone looked at www.mysql.org recently?

They have a big news article:

MySQL wins Linux Journal Readers Choice Award again!
 For the third Year in a row MySQL won the Readers Choice Award in Linux
Journal. Considering that MySQL earlier this fall won the Linux Magazine
Editors Choice Award, reading magazines on the whole has been a very
rewarding experience for MySQL fans lately.

If you follow their link to www.linuxjournal.com, all I can find is an
article about how _PostgreSQL_ won the Linux Magazine Editors Choice award!
What's with that???

Chris

> -----Original Message-----
> From: pgsql-hackers-owner@postgresql.org
> [mailto:pgsql-hackers-owner@postgresql.org]On Behalf Of Robert D. Nelson
> Sent: Tuesday, November 14, 2000 12:22 AM
> To: davidb@vectormath.com; Michael Fork; Poul L.Christiansen
> Cc: pgsql-general; pgsql-hackers
> Subject: [HACKERS] RE: [GENERAL] PHPBuilder article -- Postgres vs MySQL
>
>
> >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
>


Tue, 21 Nov 2000 11:05:35 +0800
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au>
RE: [HACKERS] RE: PHPBuilder article -- Postgres vs MySQL
At 11:22 AM 11/13/00 -0500, 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?

They don't appear to do any client-side query caching, which is understandable
from one point of view (you need some sort of handle on which queries are
hit frequently enough to warrant devoting RAM to caching the result, or else
you risk caching things that don't gain you much and cut down on the amount
of the DB cached in RAM which hits you on other queries).  On the other hand,
you'd think they'd do some analysis...

Still, the table-locking of MySQL just gets in the way.  If you can cache
everything, then you can send a postal worker to the mailbox to retrieve
uncached data without significantly impacting throughput (in other words,
the MySQL argument that simple select benchmarks are all you need are
not relevant).  If you can't cache anything but have few users, then perhaps
low levels of concurrency don't hurt.  If you don't cache anything but have
lots of users, scaling well under high levels of load rule.  

My thinking is that intellegent caching coupled with a highly-scalable
database wins.  That's the world I'm used to...



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

Mon, 20 Nov 2000 20:48:35 -0800
Don Baccus <dhogaza@pacifier.com>
Re: [HACKERS] RE: PHPBuilder article -- Postgres vs
  MySQL
At 06:16 PM 11/13/00 +0100, fabrizio.ermini@sysdat.it 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?
>> 
>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!!!!

But much of this could still be cached.  I visit my homepage at sourceforge
rarely, because my project uses sourceforge for its cvs repository, only.
So all those joins are mostly a waste.  I never have new postings in my
project forums, blah blah.  Some level of caching could help (not for me
personally, I visit too rarely for a system to want to cache my query
returns involved in building my home page, but I'm sure there are many
cases where caching would help).

Again, you have to balance query cache RAM consumption against the benefits
of extra RAM availability to the RDBMS (assuming you have one, which
MySQL isn't :)   



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

Mon, 20 Nov 2000 20:51:36 -0800
Don Baccus <dhogaza@pacifier.com>
Re: [HACKERS] RE: PHPBuilder article -- Postgres vs
  MySQL
* Christopher Kings-Lynne in "RE: [HACKERS] RE: [GENERAL] PHPBuilder
* article -- Postgres vs MySQL" dated 2000/11/21 11:05 wrote:

> MySQL wins Linux Journal Readers Choice Award again! For the third
> Year in a row MySQL won the Readers Choice Award in Linux Journal.
> Considering that MySQL earlier this fall won the Linux Magazine
> Editors Choice Award, reading magazines on the whole has been a very
> rewarding experience for MySQL fans lately.
> 
> If you follow their link to www.linuxjournal.com, all I can find is
> an article about how _PostgreSQL_ won the Linux Magazine Editors
> Choice award! What's with that???

Well, it would seem that Postgres won the _Editor's_ [1] Choice Awards
while MySQL won the _Reader's_ [2] Choice Awards

-- 
hackers ally

1. http://www2.linuxjournal.com/advertising/Press/2000ec_winners.html
2. http://www2.linuxjournal.com/articles/misc/0029.html
Tue, 21 Nov 2000 01:47:25 -0600
Ashley Clark <aclark@ghoti.org>
Re: [HACKERS] RE: PHPBuilder article -- Postgres vs MySQL
    I've wondered and am still wondering what a lot of these benchmark tests
are out to prove. I'm not sure that any PostgreSQL advocate has ever said or
implied that PostgreSQL is faster than anything, much less MySQL. While I'm
sure it's faster than some, I've just never heard the argument for using
PostgreSQL as "It's faster than anything else". I chose PostgreSQL by what
it could do, not how fast it can SELECT... No benchmark between MySQL and
PostgreSQL (or any other RDBMS ) is ever going to be truly accurate since
there are so many things MySQL simply can't to that PostgreSQL (and others)
can..

    As Don often out often and accurately points out, MySQL is not an RDBMS,
I'm not sure what it really is beyond a semi-fancy SQL interface to a file
system.. Is it fast? Yes, it is pretty fast. Fast at the expense of
functionality and stability -- two things that just aren't optional when
you're talking about a good database for anything more complicated than
click-through tracking...

    I don't dislike MySQL for any other reason except that it can't do what
I need it to do, period... I'm sure it's good for some things and some
people,  I've tried MySQL, tested MySQL and then tossed MySQL into the
garbage can...

    I found some very educational conversation here :
http://openacs.org/philosophy/why-not-mysql.html courtesy of Don and others.

-Mitch

----- Original Message -----
From: "Don Baccus" <dhogaza@pacifier.com>
To: "Robert D. Nelson" <RDNELSON@co.centre.pa.us>; <davidb@vectormath.com>;
"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>
Sent: Monday, November 20, 2000 8:48 PM
Subject: Re: [HACKERS] RE: [GENERAL] PHPBuilder article -- Postgres vs MySQL


> At 11:22 AM 11/13/00 -0500, 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?
>
> They don't appear to do any client-side query caching, which is
understandable
> from one point of view (you need some sort of handle on which queries are
> hit frequently enough to warrant devoting RAM to caching the result, or
else
> you risk caching things that don't gain you much and cut down on the
amount
> of the DB cached in RAM which hits you on other queries).  On the other
hand,
> you'd think they'd do some analysis...
>
> Still, the table-locking of MySQL just gets in the way.  If you can cache
> everything, then you can send a postal worker to the mailbox to retrieve
> uncached data without significantly impacting throughput (in other words,
> the MySQL argument that simple select benchmarks are all you need are
> not relevant).  If you can't cache anything but have few users, then
perhaps
> low levels of concurrency don't hurt.  If you don't cache anything but
have
> lots of users, scaling well under high levels of load rule.
>
> My thinking is that intellegent caching coupled with a highly-scalable
> database wins.  That's the world I'm used to...
>
>
>
> - 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.
>


Mon, 20 Nov 2000 23:55:27 -0800
"Mitch Vincent" <mitch@venux.net>
Re: [HACKERS] RE: PHPBuilder article -- Postgres vs  MySQL
>     I've wondered and am still wondering what a lot of these benchmark tests
> are out to prove.

In this case, the "benchmark test" was not out to prove anything. It was
an good-faith result of a porting effort with a suprising (to the
tester) result.

> I'm not sure that any PostgreSQL advocate has ever said or
> implied that PostgreSQL is faster than anything, much less MySQL. While I'm
> sure it's faster than some, I've just never heard the argument for using
> PostgreSQL as "It's faster than anything else".

Very true. But it turns out that in at least some real-world tests, in
this case a real application *built for MySQL*, PostgreSQL was
substantially faster when the number of users climbed above a very small
number. These results are consistant with and supported by GB's initial
published benchmark results.

Two separate styles of comparisons with consistant results might help
someone choose the right solution for their application. No harm in
that, eh?

> I chose PostgreSQL by what
> it could do, not how fast it can SELECT... No benchmark between MySQL and
> PostgreSQL (or any other RDBMS ) is ever going to be truly accurate since
> there are so many things MySQL simply can't to that PostgreSQL (and others)
> can..

Well, that is another dimension to the evaluation/comparison. But the
testing results stand on their own: you *can* choose PostgreSQL for its
performance, and you *will* have made the right choice. This is
especially gratifying for all of us who have contributed to PostgreSQL
because we *didn't* benchmark it, and *assumed* that MySQL claims for
superior speed under all circumstances were accurate. Turns out it may
be true for single-user mode, but that we've built a darn fast RDBMS for
real-world applications.

One *very unfair* part of these benchmarks and comparisons is that both
MySQL and PostgreSQL can be identified by name for the comparisons, so
they tend to be talked about the most. But the GB benchmarks could lead
one to conclude that if SourceForge had been built with another database
product it would also have seen a performance improvement when tested
with PostgreSQL.

                      - Thomas

Tue, 21 Nov 2000 14:14:57 +0000
Thomas Lockhart <lockhart@alumni.caltech.edu>
Re: RE: [GENERAL] PHPBuilder article -- Postgres vs  MySQL
I'm building a new geo type and would like to index it. I have heard about
RTREE and boundary box but I'm clueless for the moment about the
implementation....

I have tried to look into PG source code to find the location where the
indexing is done of current line object is done, but couldn't pin point where
is the code and how it works.

I would greatly appreciate if someone could guide me through the methodology to
build an index for a custom type or point me to some readings where the
algorithm is explained (web, book, etc...). I plan to use 3D geographical
objects...

Take this request as a newbie request, because I have never done database
indexing, not because I haven't done programming.

Please reply directly to my e-mail address
Thanks a lot.

franck@sopac.org


Sat, 25 Nov 2000 10:43:16 +1300
Franck Martin <franck@sopac.org>
Indexing for geographic objects?
Franck Martin <franck@sopac.org> writes:
> I would greatly appreciate if someone could guide me through the
> methodology to build an index for a custom type or point me to some
> readings where the algorithm is explained (web, book, etc...).

The Programmer's Guide chapter "Interfacing Extensions To Indices"
outlines the procedure for making a new datatype indexable.  It
only discusses the case of adding btree support for a new type,
though.  For other index classes such as R-tree there are different
sets of required operators, which are not as well documented but
you can find out by looking at code for the already-supported
datatypes.

> I plan to use 3D geographical objects...

That's a bit hard since we don't have any indexes suitable for 3-D
coordinates --- the existing R-tree type is for 2-D objects.  What's
more it assumes that coordinates are Euclidean, which is probably
not the model you want for geographical coordinates.

In theory you could build a new index type suitable for indexing
3-D points, using the R-tree code as a starting point.  I wouldn't
class it as a project suitable for a newbie however :-(.

Depending on what your needs are, you might be able to get by with
projecting your objects into a flat 2-D coordinate system and using
an R-tree index in that space.  It'd just be approximate but that
might be close enough for index purposes.

			regards, tom lane

Sat, 25 Nov 2000 23:35:40 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: [HACKERS] Indexing for geographic objects? 