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

[Jan M. Nelken]

Mairhtin O'Feannag wrote:

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

If I was asked this question - I would start from here - just to familiarize
myself with that beast:

http://www.redbooks.ibm.com/redbooks/pdfs/sg247093.pdf

Jan M. Nelken

[Re_endian_miss]

Is there a reason why you're NOT considering MySql? For example, if the
system is mission-critical and the belief is that DB2 is more
field-tested/stable and enterprise-feature-rich, and the support from
IBM exceeds that of third-party MySql support, then this may justify
Db2. Perhaps the solution you offer leverages some DB2
knowledge/experience. Again, none of this is a blunt "A is better than
B because..." argument, but if the customer has any business savvy,
they may appreciate your honesty, and that not all business decisions
can be made purely on technical merit.

Also, this article may be of interest:
http://www.devx.com/dbzone/Article/20743/1954?pf=true

[Christopher Browne]

"Mairhtin O'Feannag" <ir********@roc ketmail.com> writes:

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. :)

You might find it worthwhile to look at the following:

<http://sql-info.de/mysql/gotchas.html>

It is also rather stupid to claim that "MySQL is free" when their
price sheets declare otherwise. MySQL(tm) is certainly _not_ free
when their web site <https://order.mysql.com/> indicates:

"MySQL Database Server

The world's most popular open source database. Fast, stable,
compact.

Starting at $595.00"
--
let name="cbbrowne" and tld="cbbrowne.c om" in String.concat "@" [name;tld];;
http://www.ntlug.org/~cbbrowne/linuxxian.html
A VAX is virtually a computer, but not quite.

[kenfar]

Mairhtin O'Feannag wrote:

What are the deficiencies of MySql that I and my clients should be aware of?

Well, in my opinion this is a product that was at the right place at
the right time - and surged in popularity along with php, etc a few
years ago. It can be simple to use, but has such tremendous
disadvantages that many developers are now leaving it for its open
source rivals (postgresql & firebird). Anyhow, here's my $0.02.

1. integrity problems - of various types:
a. tons of silent errors in which data is truncated, converted,
etc without an exception passed to the application. Many of these are
documented widely, including at: //sql-info.de/mysql/gotchas.html
b. the leadership at this company has stated for years that 99%
of the developers out there don't need transactions, subselects, views,
etc. This was misleading from the begining - and they're still rushing
to quickly add these basics from 20 years ago into their product. In
the meanwhile, many of these newly-added features are still very beta -
such as the way that they get transactions through a completely
separate storage library (innodb). Use of this critical component
drastically reduces performance, and isn't completely integrated into
mysql.

2. performance issues:
a. it's very much oriented towards read-only transactional
queries (and it can be very fast at this). However, not many
applications meet that need except perhaps content management. It has
a much more difficult time with a balanced workload than most other
products in this category.
b. there are almost no reporting/dss features: no partitioning,
no bitmap indexes, no parallelism, etc. This means that a 2-way
oracle/db2 server using partitioning/mdc could easily out-perform an
8-way server running mysql. And these types of queries are becoming
increasinly common within operational/transaction applications - so
this isn't just a data warehousing issue.

3. availability & reliability:
a. mysql can maintain reasonable availability numbers - but lacks
a credible clustering offering for higher numbers.
b. given its silent errors, and penchant for associated data
corruption problems - it's entirely possible for mysql to stay up and
continue to serve data - in spite of the data being corrupted.

4. cost:
a. the licensing cost for mysql is about the same as any other
entry-level database. I think the only really inexpensive situation is
when you need quite a few small open-source databases. Online backups
are an additional cost,.

5. portability:
a. the missing features in mysql (views, etc) often require that
applications are written differently than if they were to be written
for a more full-featured database. This limitation results in
application complexity, database complexity and performance impacts.
Should the application need to be moved to another database for
improved performance, it may also require a more significant rewrite
than if it was built for a different database product.
b. lack of ansi compliance: mysql ab has shown a considerable
disregard for ANSI SQL standards (uses '#' instead of '--' for
comments, etc, etc). MySQL is probably the least compliant RDBMS I've
seen since the 80s.

6. Consistency: since there are so many data management jobs that
mysql can't do - that db2, oracle, etc can - it's not unlikely that a
shop that uses mysql will also have to use a second database as well.
As the number of database products grows - so does the labor cost to
maintain and develop for them. And since the entry-level versions of
db2, etc are very inexpensive (can be less than MySQL), a savings can
often be made on the labor side by just going with db2.

Right now, MySQL does have a few things going for it - primarily
marketing & momentum. It always gets press when open source databases
are mentioned, and most folks have heard of it. And it is improving
rapidly, though I suspect that it's years away from delivering what we
take for granted from most other products in this category.

Probably a bigger threat to db2, oracle, and sql server are Postgresql
& Firebird. Both are free and reasonably featured. There's still a
lot that DB2 & Oracle offer over these two products, but the sell has
to be harder if you're talking small departmental servers, embedded
databases, etc.

kenfar

[Gert van der Kooij]

In article <60************ @dba2.int.liber tyrms.com>, Christopher
Browne (cb******@acm.o rg) says...

It is also rather stupid to claim that "MySQL is free" when their
price sheets declare otherwise. MySQL(tm) is certainly _not_ free
when their web site <https://order.mysql.com/> indicates:

"MySQL Database Server

The world's most popular open source database. Fast, stable,
compact.

Starting at $595.00"

Weel, it is free as long as you don't have a problem with the GPL
license.

Check http://www.mysql.com/company/legal/licensing/faq.html for a
full description.

[Christopher Browne]

Gert van der Kooij <ge**@invalid.n l> writes:

In article <60************ @dba2.int.liber tyrms.com>, Christopher
Browne (cb******@acm.o rg) says...

It is also rather stupid to claim that "MySQL is free" when their
price sheets declare otherwise. MySQL(tm) is certainly _not_ free
when their web site <https://order.mysql.com/> indicates:

"MySQL Database Server

The world's most popular open source database. Fast, stable,
compact.

Starting at $595.00"

Weel, it is free as long as you don't have a problem with the GPL
license.

Check http://www.mysql.com/company/legal/licensing/faq.html for a
full description.

There's considerable nonsequitor there.

That is, what does it mean to "not have a problem with the GPL
license"? Actually, there are necessarily some problems with the GPL,
as if there were _no_ problems with it, they would not have felt the
need to create the "Library GPL" as an alternative for people to use.

But what MySQL AB _really_ mean is that if you're using it, they
suggest you consider buying a commercial license if you have any
concerns about "safety."

http://www.mysql.com/company/legal/licensing/

"In their simplest form, the following are general licensing guidelines:

* If your software is licensed under either the GPL-compatible
Free Software License as defined by the Free Software Foundation
or approved by OSI, then use our GPL licensed version.

* If you distribute a proprietary application in any way, and you
are not licensing and distributing your source code under GPL,
you need to purchase a commercial license of MySQL

If you are unsure, we recommend that you buy our cost effective
commercial licenses. That is the safest solution. Licensing
questions can submitted online for our advice, and we encourage you
to refer to the Free Software Foundation or a lawyer as appropriate."

I'm not certain what they intend might be considered "unsafe," but
frankly, it doesn't matter, because their recommendation is that if
there is any kind of uncertainty, you should be buying licenses.

There most certainly _is_ a big whack of uncertainty as to what rules
might apply to the sort of internal departmental application that
would fit well with MySQL.

The people I work for have a Bugzilla instance that presently runs
against a MySQL database; there's no ambiguity there since Bugzilla is
licensed under a suitable "free software" license, and nobody there is
making any changes to Bugzilla.

They used to run RT (a problem tracker) against MySQL; they helped
fund the port of RT/3 to PostgreSQL because of the uncertainties
surrounding cases where there would be a need to write custom reports
to do additional analysis. There is most definitely some uncertainty
as to the licensing of those reports, and the implications on how
licensing requirements would fall out of that.

I don't know if MySQL AB would require buying a license under that
sort of circumstance; what I do know is that:

"If you are unsure, we recommend that you buy our cost effective
commercial licenses. That is the safest solution."
--
let name="cbbrowne" and tld="cbbrowne.c om" in String.concat "@" [name;tld];;
http://www.ntlug.org/~cbbrowne/lsf.html
A VAX is virtually a computer, but not quite.

[Heikki Tuuri]

Kenfar,

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

Mairhtin O'Feannag wrote:

What are the deficiencies of MySql that I and my clients should be aware

of?

.... the meanwhile, many of these newly-added features are still very beta -
such as the way that they get transactions through a completely
separate storage library (innodb). Use of this critical component
drastically reduces performance, and isn't completely integrated into
mysql.
MySQL/InnoDB-3.23 was declared stable in 2002. Can you specify some reason
why you claim MySQL/InnoDB is 'still very beta'?

InnoDB is even faster than the default table type of MySQL, MyISAM, in many
simple benchmarks. Can you clarify why you claim InnoDB to 'drastically
reduce performance'?
2. performance issues:
a. it's very much oriented towards read-only transactional
queries (and it can be very fast at this). However, not many
applications meet that need except perhaps content management. It has
a much more difficult time with a balanced workload than most other
products in this category.
MySQL/InnoDB has an architecture rather close to Oracle and PostgreSQL.
MySQL/InnoDB is oriented towards a mixed workload of reads and writes. On
the other hand, DB2 lacks the consistent non-locking read capability of
Oracle, InnoDB, and PostgreSQL. In that respect DB2 is less suitable for a
mixed workload of reads and writes than those three databases.

http://dev.mysql.com/doc/mysql/en/In...ion_model.html

.... 3. availability & reliability:
a. mysql can maintain reasonable availability numbers - but lacks
a credible clustering offering for higher numbers.
Thousands of people use MySQL's replication feature for high availability
and greater performance of read-heavy workloads.

By the way, does somebody at this news group know how successful DB2's
clustering options are commercially? I know that Oracle RAC has been
moderately successful, but have not heard anything about DB2 offerings.
.... kenfar

Best regards,

Heikki Tuuri
Innobase Oy
Foreign keys, transactions, and row level locking for MySQL
InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM
tables
http://www.innodb.com/order.php

[Data Goob]

Heikki Tuuri wrote:

Kenfar,

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

Mairhtin O'Feannag wrote:

What are the deficiencies of MySql that I and my clients should be
aware

of?

...

MySQL disk management of "tablespace s" is practically non-existent, which is one
of my show-stoppers. Most of the deficiencies in the product can be overcome
by programming, but data management is very primitive. You must be willing
to live with almost no control over where and how you distribute data. This
is probably the single biggest area that needs to be improved upon. Yes, you
can have shared-nothing databases with the NDB database type, but this is so
primitive as to beg the question as to why you would use it rather than a more
polished product elsewhere, i.e. DB2. If cost is the factor blah blah blah.
These features are interesting and certainly worth investigating but as has
already been said, barely out of beta. I do think DB2 could be priced a bit
more competitively, but that begs the question of just who IBM are positioning
themselves with, and is MySQL in their space.

Download the product and see for yourself. But also consider that it may be
quite useful to use MySQL along with DB2, rather than an "either-or" mindset.

MySQL is great for prototyping applications because it's so easy to get
set up, and it makes perfect sense for widely distributed applications like
web sites. You can take the speed advantages for many components of
your web site with MySQL, and pull in parts of your DB2 database only when
you need to thus taking a lot of load off your "expensive" DB2 database. It
might be more instructive to have a "MySQL and DB2" approach rather than
either-or--taking note that MySQL has its place but as of today does not
have the maturity and features of DB2 or Oracle ( or even Sybase or Informix ).

-DG-

the meanwhile, many of these newly-added features are still very beta -
such as the way that they get transactions through a completely
separate storage library (innodb). Use of this critical component
drastically reduces performance, and isn't completely integrated into
mysql.

MySQL/InnoDB-3.23 was declared stable in 2002. Can you specify some reason
why you claim MySQL/InnoDB is 'still very beta'?

InnoDB is even faster than the default table type of MySQL, MyISAM, in many
simple benchmarks. Can you clarify why you claim InnoDB to 'drastically
reduce performance'?

2. performance issues:
a. it's very much oriented towards read-only transactional
queries (and it can be very fast at this). However, not many
application s meet that need except perhaps content management. It has
a much more difficult time with a balanced workload than most other
products in this category.

MySQL/InnoDB has an architecture rather close to Oracle and PostgreSQL.
MySQL/InnoDB is oriented towards a mixed workload of reads and writes. On
the other hand, DB2 lacks the consistent non-locking read capability of
Oracle, InnoDB, and PostgreSQL. In that respect DB2 is less suitable for a
mixed workload of reads and writes than those three databases.

http://dev.mysql.com/doc/mysql/en/In...ion_model.html

...

3. availability & reliability:
a. mysql can maintain reasonable availability numbers - but lacks
a credible clustering offering for higher numbers.

Thousands of people use MySQL's replication feature for high availability
and greater performance of read-heavy workloads.

By the way, does somebody at this news group know how successful DB2's
clustering options are commercially? I know that Oracle RAC has been
moderately successful, but have not heard anything about DB2 offerings.
...

kenfar

Best regards,

Heikki Tuuri
Innobase Oy
Foreign keys, transactions, and row level locking for MySQL
InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM
tables
http://www.innodb.com/order.php

[kenfar]

Heikki Tuuri wrote:

MySQL/InnoDB-3.23 was declared stable in 2002. Can you specify some
reason why you claim MySQL/InnoDB is 'still very beta'?

I said some of the new features are still beta. That's not really fair
- mysql ab calls them stable. It's just my opinion that they're only
halfway there with much of this functionality. Like:

1. RI & Transactions are now supported. However...

When creating tables in mysql if there's a problem with innodb mysql
will simply create the tables in myisam instead - and you will have
silently lost all transaction support for that table. Nice surprises
can result from this.

Exception handling - a weak spot in the mysql product is even worse
with subqueries, from the documentation: "Usually, failure of a
subquery causes the entire statement to fail." Ah, neat.

Primary Key definitions - are implicit if not provided, causing mysql
to confuse them with unique indexes, and requiring fk syntax to specify
the pk column.

Alter Table - is apparently half-implemented for innodb - since just
adding an index causes a table to be rebuilt. Note that this alter can
easily fail if you don't have enough storage to double the size of the
original table.

Identity columns are partially working with innodb - there's no way to
manually reset a value. Recommended solution is to insert a dummy row
when this is needed.

Innodb & replication is still missing some basic features. From the
doc: "The LOAD TABLE FROM MASTER statement for setting up replication
slave servers does not yet work for InnoDB tables. A workaround is to
alter the table to MyISAM on the master, do then the load, and after
that alter the master table back to InnoDB."

2. Subqueries are now supported. However...

Performance is pretty uneven - understandable since they've now got to
work on
the optimizer. MySQL doesn't have much to say about that except
"Developmen t is ongoing, so no optimization tip is reliable for the
long term". In other words - the application developer will likely
have to experiment with several versions of their query, then expect to
do it all over again when the performance nose-dives after a mysql
upgrade 1-2 years from now. Nothing really wrong with this - users of
most other databases had to do the same thing. Like around 1985-1989
or so...

etc, etc.
InnoDB is even faster than the default table type of MySQL, MyISAM, in many simple benchmarks.
Right - since all MyISAM writes involve table locks, it doesn't take
much writing to completely kill performance on that table type. Prior
to adding innodb, mysql probably had the worst write peformance (or
mixed-load) of any database product I can think of except for MS
Access. And even that's debatable.

So, Innodb brings acceptable write performance to mysql. That's cool.
But it doesn't improve on its read-performance. In fact, most
experiences I've seen seem to indicate that it's about 1/10th the speed
of the isam solution. Which isn't necessarily a killer - since you can
mix table types. But if you need performance from a table you're
writing a lot to, it looks like you're out of luck with mysql for now.

Typical comment:
"MyISAM will be faster than InnoDb for reading. In my tests, MyISAM was
about 10x faster than an untuned InnoDb installation for simple Select
statements. InnoDb is capable of faster speeds but requires a lot of
tuning to get the peak performance from it. InnoDb is great for updates
but for reading I prefer MyISAM hands down." -
http://lists.mysql.com/mysql/177588
From the documentation: "and another site handles an average load of 800 inserts/updates per second in InnoDB." I think that's about where
DB2 & Oracle were around 1995...

Then there's the odd way that mysql gathers stats for indexes: it
performs ten random samples of the index to gather stats. This is
acknowledged as often inaccurate with MySQL recommending the hint
"force index X" as a solution. That's great - nothing like having to
include index names in your application queries!

There may be some ways that innodb can leverage storage creatively for
additional speed - but since it already consumes 50-300% more space
than innodb, perhaps that isn't an economical option at this time.
MySQL/InnoDB has an architecture rather close to Oracle and
PostgreSQL. MySQL/InnoDB is oriented towards a mixed workload
of reads and writes. On the >other hand, DB2 lacks the
consistent non-locking read capability of Oracle, InnoDB, and
PostgreSQL. In that respect DB2 is less suitable for a mixed
workload of reads and writes than those three databases.
There are no published benchmarks of mysql on www.tpc.org, but if
mysql's claim to fame is 800 inserts / second then it's got a long way
to go before it can handle the mixed workload of db2.

Not really sure if MySQL's clustering or MAXDB enter the picture here.
MAXDB is really just adabase - a heap of pre-relational code. The
clustering solution that MySQL purchased sounds interesting - but it's
limited to data stored in memory. The lack of persistence sounds
unappealing for transactional data, and the cost of memory rules out
most large read-only systems.

Thousands of people use MySQL's replication feature for high availabilityand greater performance of read-heavy workloads.

Regarding replication for high availability - it's only HA for the
read-only data, since mysql only supports a single master.

Regarding replication for performance - this strategic is especially
problematic for several reasons:

- load-balancing across replicated servers can result in RI-like
inconsistencies in which your data appears & disappears, depending on
which server you're hitting.

- replication won't perform transformation, and you typically get *far*
better performance improvements from remodeling your oltp data than
from rehosting it. A simple ETL solution would often be preferable
here.

- mysql's lack of partitioning & parallelism is hardly compensated for
via replicated servers: since each server has 100% of the data, queries
aren't split up across servers - and the single performance strategy is
still "force an index". All that it achieves is to reduce the load by
distributing the queries across additional hardware. In an adhoc
environment with large data and few queries,mysql would get tanked.
And replication would offer no help whatsoever.

By the way, does somebody at this news group know how successful DB2's clustering options are commercially? I know that Oracle RAC has been moderately successful, but have not heard anything about DB2

offerings.

DB2 doesn't map directly to RAC - performance is achived through a
mature shared-nothing partitioning feature. This scales well to many
terrabytes and hundreds of nodes. Failover is usually provided via
HACMP, etc.

Although some folks slam MySQL for using both innodb & myisam, I think
it's fine. There's certainly an occasional need for non-logged DML.
And a number of the pieces in the overall mysql solution look good -
innodb & clustering for instance. But the code that glues them all
together into a solution that provides integrity and reliability is
completely lacking. If they can clean that up then they'll have a nice
product. And maybe a reason to look at mysql rather than a more robust
alternative such as postgresql, firebird, db2, oracle, etc.

kenfar