473,890 Members | 1,359 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

DB2 v MySql

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
Nov 12 '05
39 8441
DG,

"Data Goob" <da******@hotma il.com> kirjoitti
viestissä:u7*** ********@fe66.u senetserver.com ...
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


there are the following mechanisms to place InnoDB data on the chosen disk
drives:

1) create several ibdata files, and distribute them on the drives; this is
simple kind of 'striping';

2) use per-table tablespaces in 4.1, and symlink database directories (or
individual tables) where you want. That 4.1 feature is still in beta,
though.

The reason why we have not implemented the complete Oracle-like tablespace
support is that no one has come up with a reason why we should do it. If you
want disk striping to balance the load between individual physical disk
drives, you can use OS or hardware solutions. Or you can use the techniques
1 and 2 described above.

Partitioned tables are currently in development for MySQL, and they may
appear in some 5.x version.
.... -DG-


Best regards,

Heikki Tuuri
Innobase Oy
Foreign keys, transactions, and row level locking for MySQL
http://www.innodb.com
Nov 12 '05 #11
>> 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
....<<

what do you mean by this?
Do you mean it should ALWAYS cause an exception?

Nov 12 '05 #12
ak************@ yahoo.com wrote:
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
...<<

what do you mean by this?
Do you mean it should ALWAYS cause an exception?


I think ALWAYS would be a reasonable expectation.
--
Daniel A. Morgan
University of Washington
da******@x.wash ington.edu
(replace 'x' with 'u' to respond)
Nov 12 '05 #13
I'm by no means an expert in MySQL, but from the little I have read it
seems like following any sort of standard isn't its strength.
Not looking ahead, standardize and try to get it right at the first shot
means either an excess of "compatibil ity switches", application breakage
on upgrade or worse that the product will run itself into the
technological ditch within a couple of releases.

Just my two cents.

Cheers
Serge
Nov 12 '05 #14
>> I think ALWAYS would be a reasonable expectation
....<<

Of course, I'm not an expert on MySQL, but one doesn't have to eat the
whole egg...
If MySQL ever gets out of diapers, I'll be the first to agree with you.
Right now this "feature" seems to be quite consistent with the rest of
the product.

Nov 12 '05 #15
Well,

I can say this - the discussion is/was insightful and lively. But it
never got nasty! YAY for the discussion group.

I now have a LOT of material to read/study/follow-up on. Thanks all for
the input.

I was never trying, by the way, to avoid using MySql. I was just hit
with a question I couldn't answer. Now, at least, I can read and study
and answer the question with a bit of intelligence (I usually lack enough
intelligence to answer the door-bell on time). :)

Again, thanks for being very professional and helpful. I am, as ever, in
awe of my fellow comp.datases.ib m-db2 travellers !!!

Mairhtin


"Mairhtin O'Feannag" <ir********@roc ketmail.com> wrote in
news:Xn******** *************** *******@64.164. 98.7:
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


Nov 12 '05 #16
kenfar,

"kenfar" <ke******@yahoo .com> kirjoitti
viestissä:11*** *************** ****@f14g2000cw b.googlegroups. com...
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:


what features in InnoDB are beta that we have called 'stable'?

.... 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
He is comparing an untuned InnoDB installation to MyISAM. He should write
my.cnf as explained in the 'simple my.cnf example' at
http://dev.mysql.com/doc/mysql/en/In...iguration.html
Tuning is not difficult for InnoDB.

It makes little sense to benchmark an untuned DB2 against an untuned Oracle,
for example.

The main reason why read-only applications may run much faster with MyISAM
than InnoDB is that MyISAM stores tables in a very compact format. MyISAM
tables are often 1/4 of the size of same data in the InnoDB or PostgreSQL
format. I recall someone measuring that Oracle tables take approximately the
same space as PostgreSQL tables. Does anyone have space usage comparisons
between DB2 and Oracle?

InnoDB will have transparently compressed/decompressed tables in 2005, and
we can close the gap to MyISAM.

.... 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
Query optimization is almost always heuristic anyway. It is not that big a
disadvantage to gather statistics through a few random samples of the B-tree
leaves.

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


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

http://www.eweek.com/slideshow_viewe...20&po=1,00.asp

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.

....
Thousands of people use MySQL's replication feature for high

availability
and 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.


I think you are now confusing high availability with performance(?) through
clustering.

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.

.... 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
Nov 12 '05 #17
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
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 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?

kenfar

Nov 12 '05 #18

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.

Note also the statement

"We invited each database vendor to have staff on-site when their
products were tested at PC Magazine's New York lab facility. MySQL and
Sybase both accepted and had staffers tune their own databases as they
wished. IBM didn't send personnel, but we exchanged several rounds of
e-mail with IBM engineers to get tuning advice. Microsoft and Oracle
both declined to be involved in the test—with their database servers, we
did all tuning ourselves with no vendor input."

There is a reason for this. The industry has some clearly defined
benchmarks that all vendors agree to and that are very carefully
audited. There is absolutely no value to anybody (other than a few
magazine advertisers) in "back street" benchmarks, no matter how
professionally they are done, and it's shame to see a vendor promoting them.

Nov 12 '05 #19
Heikki Tuuri wrote:
The only independent benchmark that compares DB2 and MySQL/InnoDB is this:

http://www.eweek.com/slideshow_viewe...20&po=1,00.asp

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.


The DB2 result was crippled. Anyone looking at the graph can see that
something bad happend when DB2 actually regressed to a third of the
throughput just 10% of the max.
DB2 Development worked with eWeek to repro the problem and was never
able to.
Similar things can be said about the SQL Server result btw. If I recall
correctly the benchmark was not done using the recommended (native) MS
interface.

As you said: Tuning is key

Cheers
Serge
Nov 12 '05 #20

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
10304
by: francescomoi | last post by:
Hi. I'm trying to build 'MySQL-python-1.2.0' on my Linux FC2: ---------------------------------- # export PATH=$PATH:/usr/local/mysql/bin/ # export mysqlclient=mysqlclient_r # python setup.py clean # python setup.py build running build running build_py
4
1305
by: mikey | last post by:
Hi all, I'm having great problems trying to install the latest MySQl RPM package onto my Red Hat Linux OS. There is already MySQL v 3.0 pre-installed with the RH Linux distribution disk but I can not seem to get any of the functions working on it. So I have now dopwnloaded the latest version hoping that this will fix the error. I have managed to install the MySQL-shared-compat-4.0.1-0.i386.rpm but
0
1471
by: Yun Guan | last post by:
Hello mysql gurus, I am trying to run perl on mysql database on Red Hat box. I want to install DBI and DBD:mysql using CPAN: perl -MCPAN -e shell cpan>install DBI The above succeeded, but cpan>install DBD::mysql
0
3960
by: Mike Chirico | last post by:
Interesting Things to Know about MySQL Mike Chirico (mchirico@users.sourceforge.net) Copyright (GPU Free Documentation License) 2004 Last Updated: Mon Jun 7 10:37:28 EDT 2004 The latest version of this document can be found at: http://prdownloads.sourceforge.net/souptonuts/README_mysql.txt?download
2
2370
by: Saqib Ali | last post by:
I installed mySQL and have it running.... but I think I made a mistake somewhere along the line...... I believe I did follow the instructions that were provided with the distribution at: http://dev.mysql.com/downloads/ The database is up. It seems that a test database instance has been created (named "test"). I can see it. However, the other database instance that should have been created, named "mysql" doesn't show up when I do "show...
1
3838
by: Alex Hunsley | last post by:
I am trying to install the DBD::mysql perl module. However, it claims I need mysql.h: cpan> install DBD::mysql CPAN: Storable loaded ok Going to read /home/alex/.cpan/Metadata Database was generated on Mon, 29 Nov 2004 16:01:05 GMT Running install for module DBD::mysql Running make for R/RU/RUDY/DBD-mysql-2.9004.tar.gz CPAN: Digest::MD5 loaded ok
0
1890
by: ./Rob & | last post by:
Hi gang: I'm experiencing a problem with MySQL -- I updated MySQL from version 4.1.0 to 4.1.10 and now when I login as root it doesn't show all the databases I should have access to, nor it doesn't recognize me being logged in as root (via CURRENT_USER().) Here it is, line-by-line. Inline comments are denoted by '//'
2
2910
by: trihanhcie | last post by:
I m currently working on a Unix server with a fedora 3 as an os My current version of mysql is 3.23.58. I'd like to upgrade the version to 5.0.18. After downloading from MYSQL.COM the package on the site, I made : rpm -i MySQL-server-5.0.18-0.i386.rpm then i have errors that relate to many conflicts. I cannot figure out why -and- cannot upgrade. Please Help !
1
3236
by: manish deshpande | last post by:
Hi, When i'm installing MySQL-server-standard-5.0.24a-0.rhel3.i386.rpm by the following command: rpm -i MySQL-server-standard-5.0.24a-0.rhel3.i386.rpm the following error is being shown: warning: MySQL-server-standard-5.0.24a-0.rhel3.i386.rpm: V3 DSA signature: NOKEY, key ID 5072e1f5 file /etc/my.cnf from install of MySQL-server-standard-5.0.24a-0.rhel3 conflicts with file from package mysql-3.23.58-1 file...
3
8842
by: menzies | last post by:
Hi, I"m new to this forum, but I have been trying all day to install DBD::mysql onto my Intel MacBook. I've read lots of forums pages and none have gotten me to a successful 'make test' or a successful 'sudo make install.' Before every attempt I even do a sudo make distclean to make sure I haven't gotten things mucked up from a prior attempt. OS: Mac OS X 10.4.10 MySQL: v5.0.41 for Mac OSX-i686 DBI: v1.58 (installed fine using CPAN)...
0
10830
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
10925
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
10468
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9641
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
8018
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
7172
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
6061
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4682
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
4276
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.