DB2 v MySql

[Mairhtin O'Feannag]

Hello,

I have a client (customer) who asked the question : "Why would I buy and
use UDB, when MySql is free?"

I had to say I was stunned. I have no experience with MySql, so I was
left sort of stammering and sputtering, and managed to pull out something
I heard a couple of years back - that there was no real transaction
safety in MySql. In flight transactions could be lost.

I felt like SUCH a dork.

I'm bracing for a deluge of flames and/or admonitions, but I'm still
going to ask:

What are the deficiencies of MySql that I and my clients should be aware
of? There are loads of people pushing MySql, and I don't want to get
into a religious war with ANYONE. I just need a decent answer to the
question I was asked. :)

TIA

Mairhtin

[Serge Rielau]

I agree. The email exchange with DB2 Dev. was informal as well.

Cheers
Serge

[Heikki Tuuri]

kenfar,

"kenfar" <ke******@yahoo .com> kirjoitti
viestissä:11*** *************** ***@c13g2000cwb .googlegroups.c om...

Heikki Tuuri wrote:

The only independent benchmark that compares DB2 and MySQL/InnoDB is this:

http://www.eweek.com/slideshow_viewe...a=23120&po=1,0...

MySQL/InnoDB beat DB2 by a factor of 3 when there were a lot of

concurrent

users. Thus, the real question may be when DB2 will attain the

MySQL/InnoDB

performance.

I think the real question is why would anyone care about a benchmark
performed by eweek. You might at least find a publication that focuses

because the TPC benchmarks and the eWeek benchmark are the only published
ones from the past 10 years. Usually, database vendors do not allow people
to publish their measurements. eWeek was able to get that permission. Of
course, users should run their own benchmarks with their own applications.
The problem is that we cannot discuss the results publicly in the Internet
because many DBMS vendors prohibit publication of benchmarks.

....

You can get HA with a single master / single slave setup. If the

master

crashes, switch to using the slave. Then set up a new slave for that

slave.

Just out of curiosity, what's the median failover time in seconds on
this?

Probably your script will want to wait a few seconds to see if the problem
with the master was only temporary. Redirecting queries to the slave by your
application only requires a new connection to be established, and can be
done in a fraction of a second.
kenfar

Regards,

Heikki

[Heikki Tuuri]

Mark,

"Mark Townsend" <ma***********@ comcast.net> kirjoitti
viestissä:41*** *********@comca st.net...

I think the real question is why would anyone care about a benchmark
performed by eweek. You might at least find a publication that focuses
on databases. And perhaps try a few different workloads - adhoc query,
transactional, content management (99% read-only driven by indexes),
etc. No documentation here on the sql statements, data models, data,
or server configurations.

Not a lot of help.

You can see for yourself a description of this test (it is not a
benchmark) - http://www.eweek.com/article2/0,1759,1646917,00.asp

Note the following

"MySQL's great performance was due mostly to our use of an in-memory query
results cache that is new in MySQL 4.0.1. When we tested without this
cache, MySQL's performance fell by two-thirds.

All the bookstore order tables (which needed to support transactions as
per our requirements specification) were configured to use MySQL's InnoDB
database engine (which supports transactions, row-level locking and a
multiversioning concurrency design also used by Oracle9i). The catalog and
user tables did not require transaction support, so MySQL staff configured
these tables to use MySQL's lighter-weight, nontransactiona l MyISAM
engine.

MySQL 4.0.1's new, extremely fast query cache is also quite notable, as no
other database we tested had this feature. If the text of an incoming
query has a byte-for-byte match with a cached query, MySQL can retrieve
the results directly from the cache without compiling the query, getting
locks or doing index accesses. This query caching will be effective only
for tables with few updates because any table updates that clear the cache
to guarantee correct results are always returned."

It's interesting that Heikki calls this exercise a "benchmark that
compares MySQL/InnoDB with DB2" when in fact not a single part of this
statement is actually true.

please clarify which parts of the statement are not true, and why they are
not true?

....

Regards,

Heikki

[DA Morgan]

Heikki Tuuri wrote:

Mark,

"Mark Townsend" <ma***********@ comcast.net> kirjoitti
viestissä:41*** *********@comca st.net...

I think the real question is why would anyone care about a benchmark
performed by eweek. You might at least find a publication that focuses
on databases. And perhaps try a few different workloads - adhoc query,
transactiona l, content management (99% read-only driven by indexes),
etc. No documentation here on the sql statements, data models, data,
or server configurations.

Not a lot of help.

You can see for yourself a description of this test (it is not a
benchmark) - http://www.eweek.com/article2/0,1759,1646917,00.asp

Note the following

"MySQL's great performance was due mostly to our use of an in-memory query
results cache that is new in MySQL 4.0.1. When we tested without this
cache, MySQL's performance fell by two-thirds.

All the bookstore order tables (which needed to support transactions as
per our requirements specification) were configured to use MySQL's InnoDB
database engine (which supports transactions, row-level locking and a
multiversioni ng concurrency design also used by Oracle9i). The catalog and
user tables did not require transaction support, so MySQL staff configured
these tables to use MySQL's lighter-weight, nontransactiona l MyISAM
engine.

MySQL 4.0.1's new, extremely fast query cache is also quite notable, as no
other database we tested had this feature. If the text of an incoming
query has a byte-for-byte match with a cached query, MySQL can retrieve
the results directly from the cache without compiling the query, getting
locks or doing index accesses. This query caching will be effective only
for tables with few updates because any table updates that clear the cache
to guarantee correct results are always returned."

It's interesting that Heikki calls this exercise a "benchmark that
compares MySQL/InnoDB with DB2" when in fact not a single part of this
statement is actually true.

please clarify which parts of the statement are not true, and why they are
not true?

...

Regards,

Heikki

Lets start slowly. It isn't a benchmark.
--
Daniel A. Morgan
University of Washington
da******@x.wash ington.edu
(replace 'x' with 'u' to respond)
----== Posted via Newsfeeds.Com - Unlimited-Uncensored-Secure Usenet News==----
http://www.newsfeeds.com The #1 Newsgroup Service in the World! >100,000 Newsgroups
---= East/West-Coast Server Farms - Total Privacy via Encryption =---

[Heikki Tuuri]

Daniel,

"DA Morgan" <da******@x.was hington.edu> kirjoitti
viestissä:41*** *******@127.0.0 .1...

Heikki Tuuri wrote:

Mark,

"Mark Townsend" <ma***********@ comcast.net> kirjoitti
viestissä:41*** *********@comca st.net...
....

It's interesting that Heikki calls this exercise a "benchmark that
compares MySQL/InnoDB with DB2" when in fact not a single part of this
statement is actually true.

please clarify which parts of the statement are not true, and why they
are not true?

...

Regards,

Heikki

Lets start slowly. It isn't a benchmark.

http://www.eweek.com/article2/0,4149,293,00.asp
"
Online exclusive (July 2003 version 1.1 update): Dig deeper into the eWEEK
Labs/PC Labs database benchmark by downloading our version 1.1 database
configuration and tuning scripts, JSP code, ASP.Net code and spreadsheets
containing expanded benchmark results (582 KB .zip file). This file was
updated in July 2003 to add a more detailed readme.txt, include PC
Magazine's ASP.Net code, add some optimizations to the code and include the
originally missing MySQL configuration file. We are also making available
the raw import data (981 MB .zip file) we used to construct our databases.
"

http://www.webster.com/cgi-bin/dictionary?va=benchmark
"
2 a : a point of reference from which measurements may be made b : something
that serves as a standard by which others may be measured or judged c : a
standardized problem or test that serves as a basis for evaluation or
comparison (as of computer system performance)
"

in what way is eWeek misusing the term 'benchmark'?

To make the discussion more on-topic, does anyone have measurements about
the relative space usage of DB2 tables versus Oracle tables?
--
Daniel A. Morgan
University of Washington
da******@x.wash ington.edu
(replace 'x' with 'u' to respond)

Regards,

Heikki

[DA Morgan]

Heikki Tuuri wrote:

in what way is eWeek misusing the term 'benchmark'?

In what way was it a benchmark other than the use of the word?
I think Mark Townsend's message was crystal clear.
--
Daniel A. Morgan
University of Washington
da******@x.wash ington.edu
(replace 'x' with 'u' to respond)
----== Posted via Newsfeeds.Com - Unlimited-Uncensored-Secure Usenet News==----
http://www.newsfeeds.com The #1 Newsgroup Service in the World! >100,000 Newsgroups
---= East/West-Coast Server Farms - Total Privacy via Encryption =---

[Art S. Kagel]

Mark Townsend wrote:
<SNIP

MySQL 4.0.1's new, extremely fast query cache is also quite notable, as
no other database we tested had this feature. If the text of an incoming

<SNIP>
Actually, Mark, IBM's Informix Dynamic Server (IDS) version 9.40 and later
have a very similar statement cache. The IDS version has the additional
attribute that even if the data has dirtied since the original cache, the
previously produced query plan us reused so that even then much time can be
saved for complex queries.

Art S. Kagel

[Mark Townsend]

Art S. Kagel wrote:

Mark Townsend wrote:
<SNIP

MySQL 4.0.1's new, extremely fast query cache is also quite notable,
as no other database we tested had this feature. If the text of an
incoming

<SNIP>
Actually, Mark, IBM's Informix Dynamic Server (IDS) version 9.40 and
later have a very similar statement cache. The IDS version has the
additional attribute that even if the data has dirtied since the
original cache, the previously produced query plan us reused so that
even then much time can be saved for complex queries.

Art S. Kagel

Nearly all of the vendors have a statement cache (including Oracle,
since version 7). What is being described here is a query cache where
the result set is being kept and reused, not just the parse tree and
query plan. Sort of like an inbuilt TimesTen solution.

In practice its actually a little less than optimal. We did try
something like this with a forward data cache awhile back (across the
network under clients - typically webservers), however we found that the
cost of the misses far outweighed any benefits accrued from the hits,
under most workloads. If you drop the network out of the picture then
there may be some benefit in largely read only access, but if you are
caching data anyhow in the DB proper then just giving the memory back to
the main cache seemed to win. There is still some research being done in
this area, but I would suspect that MySQL is not maintaining a good data
cache for this feature to add some benefit in these benchmarks.

[Mark Townsend]

Heikki Tuuri wrote:

Mark,

It's interesting that Heikki calls this exercise a "benchmark that
compares MySQL/InnoDB with DB2" when in fact not a single part of this
statement is actually true.

please clarify which parts of the statement are not true, and why they are
not true?

...

Regards,

Heikki

It's not a benchmark and it didn't compare MySQL/InnoDB with DB2.

1) It's not a benchmark

From the definition of benchmark you provided yourself
something
that serves as a standard by which others may be measured or judged c : a
standardized problem or test that serves as a basis for evaluation or
comparison (as of computer system performance)
The PC Week exercise was not a standard or an example of a standardized
problem or test. Standard as in
(http://www.webster.com/cgi-bin/dictionary?va=standard)
3 : something established by authority, custom, or general consent as a model or example
4 : something set up and established by authority as a rule for the measure of quantity, weight, extent, value, or quality
PC Week is not an authority on performance testing, the benchmark used
is not one customarily used in the industry, and PC Week specifically
did not have consent to measure performance in this way - at least two
of the vendors said no.

2) It didn't compare MySQL/InnoDB with DB2

The operative word is compare. From Webster again
(http://www.webster.com/cgi-bin/dicti...ompare&x=0&y=0)
1 : to represent as similar
2 a : to examine the character or qualities of especially in order to

discover resemblances or differences

This test didn't compare two like things at all.

It actually contrasted a query engine, with some of the data stored in
tables under transactional control, with some of the data stored in
files, not under transactional control, configured with an additional
front end query cache that could return stale results, using a hand
crafted JDBC driver, that required multiple development engineers many
days to set up and configure, called MySQL; with an off-the-shelf
relational database management system, called DB2.

[Mark Townsend]

Heikki Tuuri wrote:

Usually, database vendors do not allow people
to publish their measurements. eWeek was able to get that permission.
This is an incorrect statement. Who at eWeek told you this ?
The problem is that we cannot discuss the results publicly in the Internet
because many DBMS vendors prohibit publication of benchmarks.

Hmm - if you believe this to be true then why does MySQL specifically
publish the results of this exercise - see
http://www.mysql.com/it-resources/benchmarks/