Обсуждение: follow-up on PC Week Labs benchmark results
Hi all, here are my responses to various questions: First, there was one more syntax request I forgot to include before: create view doesn't allow column aliases in the view definition: e.g. create view myview (a,b,c) as select x,y,z ... doesn't work, but create view myview select x as a, y as b, z as c ... works. from Tom Lane: > Thanks for the report! I don't suppose you'd be interested in rerunning your tests on current (pre-beta-7.0) sources? (and other questions on what version to test) We get a lot of that anytime we run tests. A simple answer is that PC Week never benchmarks non-production, non-released code. The next question is why not wait until version x when we are that much better? We have to put the stake in the ground sometime, and this story was driven by the news of InterBase going open source. We cover the news week by week, and all stories have to have a current news hook of some kind. That being said, when there is a significant change in the competitive landscape, I'd like to run the tests again (esp. now that I have gotten all the scripts done). In particular, I'd like to compare IB 6 with PG 7.x and MySQL later this year (I didn't benchmark MySQL this time because I ran out of time, but would very much like to.) > 65536 buffer limit? I was using a 4KB page size, so this was 256MB of cache. In this case, I was using a database that was about 86 MB of data and indices, so I didn't need this much cache. However, I could easily see a production database server for a mid-size company equipped with 1 or 2 GB of RAM and I would assign 80% of that RAM to the db cache. In future tests, I will be testing with a 4 GB database and so will need to be using as big a cache as PostgreSQL can support. > outer joins As Don Baccus points out, simulating outer joins with a union and a not exists gets hairy when you have more than two tables involved. Interestingly, Sybase only supported outer joins as of two months ago (12.0), though the others have supported them for some time. (from Ed Loehr) > I was disappointed this benchmark did not include database recovery and reliability measurements. Benchmarks ought to include the most important characteristics of an RDBMS, and recovery/reliability is certainly one of them I quite agree, though, of course, there always a balancing of time to run the tests vs. value of results gained. The benchmark I chose for PC Week's use (AS3AP by Turbyfill, Orji and Bitton) is actually much more rounded than most benchmarks (Wisconsin, TPC-A/B/C, etc.) because it includes a) load time, b) index time, c) update stats time, d) DSS queries such as aggregates and counts, e) is both single user and multiuser, and f) uses a wide variety of data types, not just int and char. In addition, I have a) extended the query set quite a bit to cover much more of the SQL92 entry/intermediate level spec, and b) added query log tables and consistency check queries to do some testing of proper ACID properties. Now on the specific issue of recovery, I decided a few years ago not to measure that metric solely because the TPC-C test does such a good job of checking for ACIDity. In fact, continual TPC-C testing is a big reason why today's databases are so reliable. The problem here is that no open source database has ever been tested by the TPC because none of the development groups are TPC members. I'd certainly suggest that Red Hat or VA Linux do this to get some database numbers on the board. It's a key credibility test because just passing is a very good assurance of really debugged transaction logging code.
> create view doesn't allow column aliases in the view definition: e.g.
> create view myview (a,b,c) as select x,y,z ... doesn't work, but create
> view myview select x as a, y as b, z as c ... works.
Thanks for the heads up. We've never run into it before (and have not
had any requests for it), but will look at implementing it.
> ... PC Week never benchmarks non-production, non-released code...
> ... this story was driven by the news of
> InterBase going open source.
Hmm. InterBase going open source seems to be a pre-alpha vaporware
feature so far ;)
> In particular, I'd like to compare IB 6 with PG 7.x and MySQL later this
> year (I didn't benchmark MySQL this time because I ran out of time, but
> would very much like to.)
Great. We'll look forward to it. Also, it will be interesting to see
the relative performance and feature improvements over time; Postgres
has been living on "Internet time" for the last three or four years,
and I'll be suprised if other "Open Source" DBs can keep up.
> In addition, I have a) extended the query set quite a bit to cover much
> more of the SQL92 entry/intermediate level spec, and b) added query log
> tables and consistency check queries to do some testing of proper ACID
> properties.
You had inquired earlier about "when we would support complete SQL92"
(give or take a few words). What areas of entry level SQL92 are we
missing in your opinion (or should we wait for the article)?
btw, I've been amused and gratified by PC Week's obvious shift from
Open Source FUD generator to covering Open Source with a more even
hand. It's been months since the last time John Dodge referred to
"linux fanatics" with obvious scorn, and it is nice to see that y'all
are starting to get the point.
Regards.
- Thomas
--
Thomas Lockhart lockhart@alumni.caltech.edu
South Pasadena, California
At 02:54 PM 2/7/00 +0000, Thomas Lockhart wrote: >> ... PC Week never benchmarks non-production, non-released code... >> ... this story was driven by the news of >> InterBase going open source. > >Hmm. InterBase going open source seems to be a pre-alpha vaporware >feature so far ;) This is a good point as InterBase announced that their upcoming Beta was going open source. Did you test an early copy of their upcoming beta, or did you test their current, non-Open Source product? If you tested their upcoming beta, then it would seem fair to test our upcoming beta, too :) If you tested their non-Open Source current production version, then you're not testing two Open Source databases...hmmm... Of course, InterBase may've expanded on their earlier announcement of what's going Open Source, I've not been tracking it. >> In particular, I'd like to compare IB 6 with PG 7.x and MySQL later this >> year (I didn't benchmark MySQL this time because I ran out of time, but >> would very much like to.) Actually, this slipped by me the first time. Why benchmark MySQL? It's not a real RDBMS, it doesn't even pretend to support ACID semantics. Clearly it is going to be faster than databases that do because supporting ACID semantics is expensive. This would be comparing apples with oranges, meaningless. Now, don't get me wrong, for many application spaces mySQL is fine. If you're running a bboard system for overclockers, for instance, you probably would sigh in relief if disaster struck and you lost all your data. On the other hand, if you're running an e-commerce site losing data is not cool and mySQL is not appropriate. Rather than benchmark, it would seem more useful to educate your readers about the meaning of ACID, and how to decide when you need it and when you don't. That would seem far more important, because in my experience many people don't understand that there is a real difference between a program that executes a subset of SQL in a simple manner, and an RDBMS that passes the ACID test and happens to be driven by SQL queries. If you were to benchmark in the context of such an article, it would make some sense, because you could do so in order to answer the question, "How much does ACID hurt performance?" This would give your readers real information to help drive their choice of software. - Don Baccus, Portland OR <dhogaza@pacifier.com> Nature photos, on-line guides, Pacific Northwest Rare Bird Alert Serviceand other goodies at http://donb.photo.net.
Don Baccus wrote: > > >> In particular, I'd like to compare IB 6 with PG 7.x and MySQL later this > >> year (I didn't benchmark MySQL this time because I ran out of time, but > >> would very much like to.) > > Actually, this slipped by me the first time. > > Why benchmark MySQL? It's not a real RDBMS, it doesn't even pretend > to support ACID semantics. Clearly it is going to be faster than > databases that do because supporting ACID semantics is expensive. I remember some reports of it still being slower on more complex queries. > This would be comparing apples with oranges, meaningless. > > Now, don't get me wrong, for many application spaces mySQL is fine. If > you're running a bboard system for overclockers, for instance, you probably > would sigh in relief if disaster struck and you lost all your data. > > On the other hand, if you're running an e-commerce site losing data is > not cool and mySQL is not appropriate. > > Rather than benchmark, it would seem more useful to educate your readers > about the meaning of ACID, and how to decide when you need it and when you > don't. That would seem far more important, because in my experience many > people don't understand that there is a real difference between a program > that executes a subset of SQL in a simple manner, and an RDBMS that > passes the ACID test and happens to be driven by SQL queries. You probably can get ACID behaviour from MySQL by serializing at transaction level ;) ------------ Hannu