Thread: 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
> 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
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 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'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'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
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