Thread: Benchmark

Hi guys,

i'm planning try to do a comparative between some DBMS
and postgresql (informix, oracle, m$ sql server,
firebird and even mysql) i'm coordinating with people
in the irc spanish postgresql channel.

1) maybe can anyone give me suggestions on this?
2) point me to a good benchmark test or script that
can be used?
3) any comments?

regards,
Jaime Casanova

_________________________________________________________
Do You Yahoo!?
Información de Estados Unidos y América Latina, en Yahoo! Noticias.
Visítanos en http://noticias.espanol.yahoo.com

On Feb 10, 2005, at 12:49 AM, Jaime Casanova wrote:

> Hi guys,
>
> i'm planning try to do a comparative between some DBMS
> and postgresql (informix, oracle, m$ sql server,
> firebird and even mysql) i'm coordinating with people
> in the irc spanish postgresql channel.
>
> 2) point me to a good benchmark test or script that
> can be used?

The TPC tests are fairly widely accepted.  The thing with a benchmark
is they are unlikely to simulate your real traffic.  But it is always
fun to look at numbers

> 3) any comments?
>

If you plan on making your results public be very careful with the
license agreements on the other db's.  I know Oracle forbids the
release of benchmark numbers without their approval.

--
Jeff Trout <jeff@jefftrout.com>
http://www.jefftrout.com/
http://www.stuarthamm.net/

On Thu, 10 Feb 2005 08:21:09 -0500, Jeff <threshar@torgo.978.org> wrote:
>
> If you plan on making your results public be very careful with the
> license agreements on the other db's.  I know Oracle forbids the
> release of benchmark numbers without their approval.

...as all of the other commercial databases do. This may be off-topic,
but has anyone actually suffered any consequences of a published
benchmark without permission?

For example, I am a developer of Mambo, a PHP-based CMS application,
and am porting the mysql functions to ADOdb so I can use grown-up
databases ;-)

What is keeping me from running a copy of Mambo on a donated server
for testing and performance measures (including the commercial
databases) and then publishing the results based on Mambo's
performance on each?

It would be really useful to know if anyone has ever been punished for
doing this, as IANAL but that restriction is going to be very, VERY
difficult to back up in court without precedence. Is this just a
deterrent, or is it real?

-- Mitch

Mitch Pirtle <mitch.pirtle@gmail.com> writes:
> It would be really useful to know if anyone has ever been punished for
> doing this, as IANAL but that restriction is going to be very, VERY
> difficult to back up in court without precedence. Is this just a
> deterrent, or is it real?

If Oracle doesn't eat your rear for lunch, it would only be because you
hadn't annoyed them sufficiently for them to bother.  Under the terms of
the license agreement that you presumably clicked through, you gave up
your rights to publish anything they don't like.  Do a little Google
research.  For instance
http://www.infoworld.com/articles/op/xml/01/04/16/010416opfoster.html

The impression I get is that if you are willing to spend lots of $$
you could *maybe* win the case, if you can still find a judge who thinks
that the public good outweighs private contract law (good luck, with the
Republicans in office).  Do you have a larger budget for legal issues
than Oracle does?  If so, step right up.

            regards, tom lane

On Fri, 11 Feb 2005 01:38:13 -0500, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> If Oracle doesn't eat your rear for lunch,

That would be more like an appetizer at a california cuisine place.

> it would only be because you
> hadn't annoyed them sufficiently for them to bother.  Under the terms of
> the license agreement that you presumably clicked through, you gave up
> your rights to publish anything they don't like.  Do a little Google
> research.  For instance
> http://www.infoworld.com/articles/op/xml/01/04/16/010416opfoster.html

I did do the research, but couldn't find one instance where someone
was actually taken to task over it. So far it appears to be bluster.
Horrifying to some, but still bluster.

> The impression I get is that if you are willing to spend lots of $$
> you could *maybe* win the case, if you can still find a judge who thinks
> that the public good outweighs private contract law (good luck, with the
> Republicans in office).  Do you have a larger budget for legal issues
> than Oracle does?  If so, step right up.

The reason I asked is because this has a lot more to do with than just
money. This is restriction of speech as well, and publishing
benchmarks (simply as statistical data) cannot in any way be construed
as defamation or libel. Just because it is in the click-wrap contract
doesn't mean you waive certain rights, and this has been proven (and
now has precedence). Again, I would love to know of any instances
where someone published (forbidden) benchmarks and was actually
pursued in a court of law. Well, and the result, too ;-)

I ask not to cause trouble, but to learn if this is just a deterrent
that has never been tested ("small pebble") or a well-defined threat
that will be enforced ("plasma cannon").

-- Mitch, thinking this is off topic but still fascinating

On Fri, 11 Feb 2005 01:38:13 -0500, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Mitch Pirtle <mitch.pirtle@gmail.com> writes:
> > It would be really useful to know if anyone has ever been punished for
> > doing this, as IANAL but that restriction is going to be very, VERY
> > difficult to back up in court without precedence. Is this just a
> > deterrent, or is it real?
>
> If Oracle doesn't eat your rear for lunch, it would only be because you
> hadn't annoyed them sufficiently for them to bother.  Under the terms of
> the license agreement that you presumably clicked through, you gave up
> your rights to publish anything they don't like.  Do a little Google
> research.  For instance
> http://www.infoworld.com/articles/op/xml/01/04/16/010416opfoster.html
>
What about the free speech rigths, in USA they are in the constitution
and cannot be denied or revoked, IANAL.

And like stated by Mitch just numbers are not lies that can be pursued
in a court of law.

Think anout it, In USA you can speak and publish about the President
but cannot say anything about M$ or Oracles' DBMS?

regards,
Jaime Casanova

On Fri, Feb 11, 2005 at 02:22:39 -0500,
  Jaime Casanova <systemguards@gmail.com> wrote:
> What about the free speech rigths, in USA they are in the constitution
> and cannot be denied or revoked, IANAL.

You can voluntarily give up your rights to free speech in the US.

> And like stated by Mitch just numbers are not lies that can be pursued
> in a court of law.

I think part of the reason they don't want people doing this, is because
if you don't configure their database well, you can make it look bad
when it shouldn't.

> Think anout it, In USA you can speak and publish about the President
> but cannot say anything about M$ or Oracles' DBMS?

Not if you signed a contract that says you can't.

If you didn't actually sign an agreement saying you wouldn't publish
benchmarks, then you might have a case. You might argue that a click
through eula isn't a valid contract or that you are a third party
who isn't bound by whatever agreement the person who installed Oracle
made. However it probably would cost you a bundle to have a chance
at winning.

Bruno Wolff III wrote:
> On Fri, Feb 11, 2005 at 02:22:39 -0500,
>   Jaime Casanova <systemguards@gmail.com> wrote:
>
>>Think anout it, In USA you can speak and publish about the President
>>but cannot say anything about M$ or Oracles' DBMS?
>
>
> Not if you signed a contract that says you can't.
>
> If you didn't actually sign an agreement saying you wouldn't publish
> benchmarks, then you might have a case. You might argue that a click
> through eula isn't a valid contract or that you are a third party
> who isn't bound by whatever agreement the person who installed Oracle
> made. However it probably would cost you a bundle to have a chance
> at winning.

IANAL etc, but the key fear is more likely that Oracle merely cancel
your licence(s). And deny you any more. And prevent your software from
running on top of Oracle. At which point, you have to sue Oracle and
prove restraint of trade or unfair competition or similar. Don't forget
that you have no right to purchase Oracle licences, they are free to
sell to whoever they choose and under whatever conditions.

--
   Richard Huxton
   Archonet Ltd

On Feb 11, 2005, at 2:04 AM, Mitch Pirtle wrote:
>
> I did do the research, but couldn't find one instance where someone
> was actually taken to task over it. So far it appears to be bluster.
> Horrifying to some, but still bluster.
>

They may not have done that yet, but they _COULD_.  And if they decide
to they have more money and power than you likely have and would drive
you into financial ruin for the rest of your life (Even if you are
correct).   It is a big risk.  I think that clause is in there so MS,
etc. can't say "Use FooSQL, its 428% faster than that Oracle POS Just
look!"

After using oracle in the last few months..  I can see why they'd want
to prevent those numbers.. Oracle really isn't that good.  I had been
under the impression that it was holy smokes amazingly fast.  It just
isn't.  At least, in my experience it isn't.  but that is another
story.

--
Jeff Trout <jeff@jefftrout.com>
http://www.jefftrout.com/
http://www.stuarthamm.net/

Jeff <threshar@torgo.978.org> writes:

> After using oracle in the last few months..  I can see why they'd want to
> prevent those numbers.. Oracle really isn't that good.  I had been under the
> impression that it was holy smokes amazingly fast.  It just isn't.  At least,
> in my experience it isn't.  but that is another story.

Oracle's claim to performance comes not from tight coding and low overhead.
For that you use Mysql :)

Oracle's claim to performance comes from how you can throw it at a machine
with 4-16 processors and it really does get 4-16x as fast. Features like
partitioned tables, parallel query, materialized views, etc make it possible
to drive it further up the performance curve than Sybase/MSSQL or Postgres.

In terms of performance, Oracle is to Postgres as Postgres is to Mysql: More
complexity, more overhead, more layers of abstraction, but in the long run it
pays off when you need it. (Only without the user-friendliness of either
open-source softwares.)

--
greg

I have never used Oracle myself, nor have I read its license agreement,
but what if you didn't name Oracle directly? ie:

TPS        Database
-------------------------------
112    MySQL
120    PgSQL
90    Sybase
95    "Other database that *may* start with a letter after N"
50    "Other database that *may* start with a letter after L"

As far as I know there are only a couple databases that don't allow you
to post benchmarks, but if they remain "unnamed" can legal action be
taken?

Just like all those commercials on TV where they advertise: "Cleans 10x
better then the other leading brand".


On Fri, 2005-02-11 at 00:22 -0500, Mitch Pirtle wrote:
> On Thu, 10 Feb 2005 08:21:09 -0500, Jeff <threshar@torgo.978.org> wrote:
> >
> > If you plan on making your results public be very careful with the
> > license agreements on the other db's.  I know Oracle forbids the
> > release of benchmark numbers without their approval.
>
> ...as all of the other commercial databases do. This may be off-topic,
> but has anyone actually suffered any consequences of a published
> benchmark without permission?
>
> For example, I am a developer of Mambo, a PHP-based CMS application,
> and am porting the mysql functions to ADOdb so I can use grown-up
> databases ;-)
>
> What is keeping me from running a copy of Mambo on a donated server
> for testing and performance measures (including the commercial
> databases) and then publishing the results based on Mambo's
> performance on each?
>
> It would be really useful to know if anyone has ever been punished for
> doing this, as IANAL but that restriction is going to be very, VERY
> difficult to back up in court without precedence. Is this just a
> deterrent, or is it real?
>
> -- Mitch
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
--
Mike Benoit <ipso@snappymail.ca>

> For example, I am a developer of Mambo, a PHP-based CMS application,
> and am porting the mysql functions to ADOdb so I can use grown-up
> databases ;-)

    Just yesterday I "optimized" a query for a website running MySQL. It's
the 'new products' type query :

SELECT product_id, pd.product_name, p.price, COALESCE( s.specials_price,
p.price ) as real_price
 FROM products p, products_descriptions pd LEFT join specials s ON
(p.product_id = s.product_id)
WHERE p.product_id = pd.product_id
AND pd.language_id=(constant)
AND p.product_visible=TRUE
AND s.is_active = TRUE
ORDER BY p.date_added DESC LIMIT 6

    With ~100 products everything went smooth, about 0.5 ms. I decided to
test with 20.000 because we have a client with a large catalog coming.
Wow. It took half a second, to yield six products. Note that there are
appropriate indexes all over the place (for getting the new products, I
have an index on product_visible, date_added)

    I tested with Postgres : with 100 products it takes 0.4 ms, with 20.000
it takes 0.6 ms...

    Postgres needs a bit of query massaging (putting an extra ORDER BY
product_visible to use the index). With MySQL no amount of query rewriting
would do.
    I noted sometimes MySQL would never use a multicolumn index for an ORDER
BY LIMIT unless one specifies a dummy condition on the missing parameter.

    So I had to split the query in two : fetch the six product_ids, store
them in a PHP variable, implode(',',$ids), and SELECT ... WHERE product_id
IN (x,y,z)

    UGLY ! And a lot slower.

    Note this is with MySQL 4.0.23 or something. Maybe 4.1 would be faster.

    Here's the URL to the site. There is a query log if you wanna look just
for laughs. Note that all the products boxes are active which makes a very
long page time... There are 42000 fictive products and about 60 real
products. Don't use the search form unless you have a good book to read !
You can click on "Nouveautés" to see the old "new products" query in
action, but please, only one people at a time.

    http://pinceau-d-or.com/gros/product_info.php?products_id=164
    Ah, you can buy stuff with the test version if you like, just don't use
the credit card because ... it works ;)

    This is the un-messed-up version (production) :
    http://pinceau-d-or.com/product_info.php?products_id=164

    If some day I can recode this mess to use Postgres... this would be nice,
so nice... the other day my database went apeshit and in the absence of
foreign keys... and the absence of PHP checking anything...  !


test=# CREATE TABLE suicide (id INT NOT NULL, moment TIMESTAMP NOT NULL);
CREATE TABLE
test=# INSERT INTO suicide (id,moment) VALUES (0,now());
INSERT 6145577 1
test=# INSERT INTO suicide (id,moment) VALUES (0,0);
ERREUR:  La colonne <<moment>> est de type timestamp without time zone
mais l'expression est de type integer
HINT:  Vous devez reecrire l'expression ou lui appliquer une
transformation de type.
test=# INSERT INTO suicide (id,moment) VALUES (NULL,1);
ERREUR:  La colonne <<moment>> est de type timestamp without time zone
mais l'expression est de type integer
HINT:  Vous devez reecrire l'expression ou lui appliquer une
transformation de type.
test=# INSERT INTO suicide (id,moment) VALUES (NULL,now());
ERREUR:  Une valeur NULL dans la colonne <<id>> viole la contrainte NOT
NULL
test=# SELECT * FROM suicide;
  id |           moment
----+----------------------------
   0 | 2005-02-11 19:16:21.262359

mysql> CREATE TABLE suicide (id INT NOT NULL, moment DATETIME NOT NULL);
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO suicide (id,moment) VALUES (0,now());
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO suicide (id,moment) VALUES (0,0);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO suicide (id,moment) VALUES (NULL,1);
ERROR 1048: Column 'id' cannot be null
mysql> INSERT INTO suicide (moment) VALUES (now());
Query OK, 1 row affected (0.00 sec)

hey, did I specify a default value ?

mysql> SELECT * FROM suicide;
+----+---------------------+
| id | moment              |
+----+---------------------+
|  0 | 2005-02-11 19:17:49 |
|  0 | 0000-00-00 00:00:00 |
|  0 | 2005-02-11 19:18:45 |
+----+---------------------+
3 rows in set (0.00 sec)

> In terms of performance, Oracle is to Postgres as Postgres is to Mysql:
> More
> complexity, more overhead, more layers of abstraction, but in the long
> run it
> pays off when you need it. (Only without the user-friendliness of either
> open-source softwares.)
>

    I don't find postgres complex... I find it nice, well-behaved, very easy
to use, very powerful, user-friendly... there are a lot of features but
somehow it's well integrated and makes a coherent set. It also has some
very useful secret passages (like the whole GiST family) which can save
you from some things at which SQL really sucks. It certainly is complex on
the inside but I think the devs have done a very good job at hiding that.

    It's also polite : it will say 'I have a function with the name you said
but the parameter types don't match' ; mysql will just say 'syntax error,
RTFM', or insert its favorite value of 0.

Mike Benoit <ipso@snappymail.ca> writes:
> I have never used Oracle myself, nor have I read its license agreement,
> but what if you didn't name Oracle directly? ie:

> TPS        Database
> -------------------------------
> 112    MySQL
> 120    PgSQL
> 90    Sybase
> 95    "Other database that *may* start with a letter after N"
> 50    "Other database that *may* start with a letter after L"

Great Bridge did essentially that years ago, but I think we only got
away with it because we didn't say which DBs "Commercial Database A"
and "Commercial Database B" actually were.  Even off the record, we
were only allowed to tell people that the commercial DBs were Oracle
and SQL Server ... but not which was which.

            regards, tom lane

> I have never used Oracle myself, nor have I read its license
agreement,
> but what if you didn't name Oracle directly? ie:
>
> TPS        Database
> -------------------------------
> 112    MySQL
> 120    PgSQL
> 90    Sybase
> 95    "Other database that *may* start with a letter after N"
> 50    "Other database that *may* start with a letter after L"
>
> As far as I know there are only a couple databases that don't allow
you
> to post benchmarks, but if they remain "unnamed" can legal action be
> taken?
>
> Just like all those commercials on TV where they advertise: "Cleans
10x
> better then the other leading brand".

Instead of measuring transactions/second, let's put everything in terms
of transactions/dollar.  This will make it quite easy to determine which
database is which from the results.  Since postgresql is free and would
invalidate our test on mathematical terms, we will sub in the $19.99
price of a T-Shirt (http://www.sourcewear.com/) for the price of the
database.

TP$        Database
-------------------------------
25          A
.5          B
.01         C
.001        D
.00001      E

Merlin

Oops! merlin.moncure@rcsonline.com ("Merlin Moncure") was seen spray-painting on a wall:
> Instead of measuring transactions/second, let's put everything in terms
> of transactions/dollar.  This will make it quite easy to determine which
> database is which from the results.  Since postgresql is free and would
> invalidate our test on mathematical terms, we will sub in the $19.99
> price of a T-Shirt (http://www.sourcewear.com/) for the price of the
> database.
>
> TP$        Database
> -------------------------------
> 25          A
> .5          B
> .01         C
> .001        D
> .00001      E

Ah, but that's a completely wrong evaluation.

The fact that PostgreSQL is available without licensing charges does
_not_ make a transactions/dollar ratio break down.

After all, the cost of a computer system to run the transactions is
likely to be comprised of some combination of software licenses and
hardware costs.  Even if the software is free, the hardware isn't.

If you're doing a high end evaluation, you probably have a million
dollars worth of computer hardware.

If you're running PostgreSQL, that may mean you can afford to throw
some extra RAM on the box, but you still need the million dollar
server in order to get hefty TPS counts...
--
(reverse (concatenate 'string "moc.liamg" "@" "enworbbc"))
http://cbbrowne.com/info/linuxdistributions.html
"Let's face it  -- ASCII text is  a far richer medium  than most of us
deserve."  -- Scott McNealy

Tom Lane wrote:
> Great Bridge did essentially that years ago, but I think we only got
> away with it because we didn't say which DBs "Commercial Database A"
> and "Commercial Database B" actually were.  Even off the record, we
> were only allowed to tell people that the commercial DBs were Oracle
> and SQL Server ... but not which was which.

IMHO clues like:

  "What versions of the databases did you use?
  - PostgreSQL - 7.0 release version
  - Proprietary 1 - 8.1.5
  - Proprietary 2 - 7.0
  - MySQL - 3.22.32
  - Interbase - 6.0
  "
and
  "PostgreSQL" and "Proprietary 1" was running "red hat linux 6.1" and
  "Proprietary 2" was running Windows NT server 4 - service pack 4"
in articles like this one: http://www.xperts.com/news/press1.htm

helped some people narrow it down a bit.  :)

Christopher Browne <cbbrowne@acm.org> writes:

> After all, the cost of a computer system to run the transactions is
> likely to be comprised of some combination of software licenses and
> hardware costs.  Even if the software is free, the hardware isn't.

And labour costs.

--
greg

In article <87mzu85sgh.fsf@stark.xeocode.com>,
Greg Stark <gsstark@mit.edu> writes:

> Christopher Browne <cbbrowne@acm.org> writes:
>> After all, the cost of a computer system to run the transactions is
>> likely to be comprised of some combination of software licenses and
>> hardware costs.  Even if the software is free, the hardware isn't.

> And labour costs.

Except that working with PostgreSQL is fun, not labour :-)