


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.



Mon, 07 Feb 2000 01:41:26 -0500
Timothy Dyck <Timothy_Dyck@zd.com>
follow-up on PC Week Labs benchmark results
> 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

Mon, 07 Feb 2000 14:54:02 +0000
Thomas Lockhart <lockhart@alumni.caltech.edu>
Re: [HACKERS] follow-up on PC Week Labs benchmark results
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 Service and other goodies at
  http://donb.photo.net.

Mon, 07 Feb 2000 07:29:35 -0800
Don Baccus <dhogaza@pacifier.com>
Re: [HACKERS] follow-up on PC Week Labs benchmark results
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

Mon, 07 Feb 2000 23:21:47 +0200
Hannu Krosing <hannu@tm.ee>
Re: [HACKERS] follow-up on PC Week Labs benchmark results