Thread: PHPBuilder article -- Postgres vs MySQL
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
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
>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: >>> >>> 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 > >
>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 -----------------------------------------------------------------
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 >
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.
* 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
Attachment
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.
At 09:43 AM 11/13/00 -0600, davidb@vectormath.com wrote: >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. Why is all this e-mail showing up so late? (I'm curious because there have been complaints about the mail server here, and the article is old hat). - 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.
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.
>
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
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