DB2 vs MySQL - performance on large tables

[Daniel Morgan]

Re: DB2 vs MySQL - performance on large tables

Comments in-line.

Blair Adamache wrote:
[color=blue]
> I have three concerns about this url and why I feel it proves little:
>
> 1. it lists the DB2 Version as DB2 5 (the report is from 1997). Since
> then, DB2 has had 5 new releases (DB2 v5.2, 6.1, 7.1, 7.2, 8.1) and
> published multiple TPC-H benchmarks on Linux, and two TPC-C benchmarks
> on AIX. So I feel that DB2 performance has improved since 1997. A lot
> has changed in 1997: Clinton is no longer president, and DB2 has come
> out on Linux with 4 releases (v6.1 through v8.1).[/color]

And, of course, it is equally true that MySQL and Linux have changed
too. So I agree ... the comparison is meaningless in 2003.
[color=blue]
> 2. although the URL claims to be a benchmark, it can't prove anything,
> since a true benchmark needs a level playing field for comparisons.
> DB2 was not available on Linux in 1997, so they probably compared DB2
> on an old version of Windows, like NT.[/color]

Pure speculation on all sides. A benchmark without context is meaningless.
[color=blue]
> 3. the URL could be biased (just as I am) - after all, why list MySQL
> first. True benchmarks are released and audited by impartial
> organizations like TPC.
>
> Bing Wu wrote:
>[color=green]
>> Blair Adamache wrote:[/color]
>[/color]
I'd hardly call TPC impatial ... I'd call it useless. Performance is one
of very small part of the
rationale for purchasing an RDBMS. A more reasonable criteria would include:

1. Security
2. Stability
3. Scalability
4. Supportability
5. Third-party products that can be hosted such as financial systems
6. Price

From my experience on comparable hardware I think MySQL is indeed
faster. But that
doesn't make it better for any specific purpose. I can't recall the
last time a database
failed due to speed and speed alone. Most of the time speed issues can
be resolved by
just getting someone on the project that knows how to write and tune
decent code.

--
Daniel Morgan

http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp

http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp

damorgan@x.wash ington.edu
(replace 'x' with a 'u' to reply)

[Serge Rielau]

Re: DB2 vs MySQL - performance on large tables

The first link explains why the second link has to be viewed with
caution. And it shows the results of a rerun.
There was something odd about the original eweek setup.
Who knows. I recall an incident where a benchmark fell over because of
what turned out to be a dead battery in the disk cache.

From your earlier links e.g. I'm surprised by the Oracle numbers.
I don't buy a benchmark that makes our major competitor orders of
magnitudes worse than DB2, as much as I wish it were true ;-)

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[Database Guy]

Re: DB2 vs MySQL - performance on large tables

Bing Wu <bing@biop.ox.a c.uk> wrote in message news:<boucbi$se l$1@news.ox.ac. uk>...
While I run the query against the 'coordinate' table:[color=blue]
>
> select x,y,x from coordinate where fid<1000
>
> It takes more than 10 minutes to finish. Don't really understand why?[/color]

Sounds as though this is just illustrating my point that with DB2 (and
I think most DBMS's) exporting a large resultset is often a bottleneck
on (I think) the coordinator agent. mySQL by all accounts is unusually
lightweight, and suspect it has a very different architecture that may
be the ideal choice if your sole criterion is speed of dumping tables
to falt file. Also suspect your query may even by serviced by a single
agent (thread) - maybe the reduction in inter-agent communication
overhead is a big advantage for queries that just dump tables???

Nobody's come back yet with suggestions on how to increase fetch rate
over 20,000/sec, so I guess you can take that as tacit acceptance that
this is more-or-less optimal for DB2 right now. I'd love a technical
explanation of why mySQL might be so much quicker under these
(specific) circumstances - is this basically inevitable?


DG

[Ken]

Re: DB2 vs MySQL - performance on large tables

Daniel Morgan <damorgan@x.was hington.edu> wrote in message news:<106874599 0.118235@yasure >...
[color=blue]
> I'd hardly call TPC impatial ... I'd call it useless. Performance is one
> of very small part of the
> rationale for purchasing an RDBMS. A more reasonable criteria would include:
>
> 1. Security
> 2. Stability
> 3. Scalability
> 4. Supportability
> 5. Third-party products that can be hosted such as financial systems
> 6. Price[/color]

great response - a good solution requires a focus on more than just a
single element. In the past I've usually broken it a little
differently:
- adaptability
- manageability
- scalability and performance
- reliability, availability, and servicability
- affordability
Still, pretty similar.
[color=blue]
> From my experience on comparable hardware I think MySQL is indeed
> faster.[/color]

Hmmm, but I've never seen a mysql benchmark that wasn't questionable
in validity (such as their comparisions to postgresql), or trivial in
implementation.

They're typically read-only activities on single-cpu boxes using small
databases. While that's a valid scenario - it hardly covers all the
bases - such as SMP or MPP servers, mixed workloads, concurrency,
backups, etc. And they're typically implemented by folks that don't
really know databases. E-Week? Please. And they compared mysql 4.0
to db2 7.2? Did they use innodb or isam for mysql? If not innodb
then mysql didn't have RI and it was an apples to oranges comparison.

Ken

[Jan M. Nelken]

Re: DB2 vs MySQL - performance on large tables

Bing Wu wrote:[color=blue]
> Hi all,
>
> I am running a database containing large datasets:
>
> frames: 20 thousand rows,
> coordinates: 170 million row.
>
> The database has been implemented with:
>
> IBM DB2 v8.1
> MySQL v3.23.54
>
> using similar DDL and same indexes. And I have run a SQL joining two
> tables:
>
> select c.x,c.y,c.z from coordinate as c,frame as f where fid<1000
> and f.tid=1 and f.id=c.fid
>
> The query operates on 3400 billion possible joints and returns about 8.5
> million records. It seems MySQL outperforms DB2 well ahead:
>
> DB2: 7 minutes
> MySQL: 3 minutes
>[/color]

Would you mind posting DDL for both tables? Inquiring minds want to know
and play with configuration a bit.

Jan M. Nelken

[Daniel Morgan]

Re: DB2 vs MySQL - performance on large tables

Ken wrote:
[color=blue]
>Daniel Morgan <damorgan@x.was hington.edu> wrote in message news:<106874599 0.118235@yasure >...
>
>
>[color=green]
>>I'd hardly call TPC impatial ... I'd call it useless. Performance is one
>>of very small part of the
>>rationale for purchasing an RDBMS. A more reasonable criteria would include:
>>
>>1. Security
>>2. Stability
>>3. Scalability
>>4. Supportability
>>5. Third-party products that can be hosted such as financial systems
>>6. Price
>>
>>[/color]
>
>great response - a good solution requires a focus on more than just a
>single element. In the past I've usually broken it a little
>differently:
> - adaptability
> - manageability
> - scalability and performance
> - reliability, availability, and servicability
> - affordability
>Still, pretty similar.
>
>
>[color=green]
>>From my experience on comparable hardware I think MySQL is indeed
>>faster.
>>
>>[/color]
>
>Hmmm, but I've never seen a mysql benchmark that wasn't questionable
>in validity (such as their comparisions to postgresql), or trivial in
>implementation .
>
>They're typically read-only activities on single-cpu boxes using small
>databases. While that's a valid scenario - it hardly covers all the
>bases - such as SMP or MPP servers, mixed workloads, concurrency,
>backups, etc. And they're typically implemented by folks that don't
>really know databases. E-Week? Please. And they compared mysql 4.0
>to db2 7.2? Did they use innodb or isam for mysql? If not innodb
>then mysql didn't have RI and it was an apples to oranges comparison.
>
>Ken
>
>[/color]
Part of my point, perhaps, wasn't too clear.

One can easily achieve great speed if one is willing to sacrifice some
of the other items on the
list (either your list or mine).

The basic methodology for pulling information out of a SQL database has
been in the
literature for years. I would think most anyone could store and retrieve
information with
great speed.

The question is can you do it in an environment with thousands of
simultaneous transactions
while maintaining security, stability, etc. It is those other criteria,
beyond performance, that
are why DB2, Oracle, and Informix are where they are while MySQL is
still begin given
away for free. And it is precisely why I think performance benchmarks
aren't worth the
paper on which they are printed. Just fluff that should be used as kindling.

--
Daniel Morgan

http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp

http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp

damorgan@x.wash ington.edu
(replace 'x' with a 'u' to reply)

[Bing Wu]

Re: DB2 vs MySQL - performance on large tables

Jan M. Nelken wrote:[color=blue]
>
> Would you mind posting DDL for both tables? Inquiring minds want to know
> and play with configuration a bit.
>
> Jan M. Nelken
>[/color]

Full DDLs for these two tables attached below for your reference. Appericate for any comments.

Thanks,

Bing

DB2:-

CREATE TABLE "DB2ADMIN"."COO RDINATE" (
"FID" INTEGER NOT NULL ,
"AID" INTEGER NOT NULL ,
"X" REAL NOT NULL ,
"Y" REAL NOT NULL ,
"Z" REAL NOT NULL )
IN "USERSPACE1 " ;
ALTER TABLE "DB2ADMIN"."COO RDINATE" APPEND ON;

CREATE INDEX "DB2ADMIN"."IDX _COORDINATE1" ON "DB2ADMIN"."COO RDINATE"
("FID" ASC)
PCTFREE 0 ;

CREATE INDEX "DB2ADMIN"."IDX _COORDINATE2" ON "DB2ADMIN"."COO RDINATE"
("AID" ASC)
PCTFREE 0 ;

ALTER TABLE "DB2ADMIN"."COO RDINATE"
ADD CONSTRAINT "PK_COORDINATE8 " PRIMARY KEY
("FID",
"AID");

CREATE BUFFERPOOL "USERPOOL" SIZE 10820 PAGESIZE 32768 NOT EXTENDED STORAGE;

ALTER TABLESPACE SYSCATSPACE
PREFETCHSIZE 32
OVERHEAD 24.100000
TRANSFERRATE 0.900000;


ALTER TABLESPACE TEMPSPACE1
PREFETCHSIZE 32
OVERHEAD 24.100000
TRANSFERRATE 0.900000;


ALTER TABLESPACE USERSPACE1
PREFETCHSIZE 32
OVERHEAD 24.100000
TRANSFERRATE 0.900000;

CREATE TABLE "DB2ADMIN"."FRA ME" (
"ID" INTEGER NOT NULL ,
"TID" INTEGER NOT NULL ,
"SNUM" INTEGER NOT NULL WITH DEFAULT 1 ,
"TIME" REAL NOT NULL WITH DEFAULT 0 ,
"TYPE" CHAR(4) NOT NULL ,
"VOLUME" DOUBLE NOT NULL ,
"PRESSURE" DOUBLE NOT NULL ,
"TEMP" DOUBLE NOT NULL ,
"TEMP_S" DOUBLE NOT NULL ,
"TEMP_1" DOUBLE ,
"TEMP_2" DOUBLE ,
"TEMP_3" DOUBLE ,
"TEMP_4" DOUBLE ,
"TEMP_5" DOUBLE ,
"ENERGY" DOUBLE NOT NULL ,
"ENERGY_S" DOUBLE NOT NULL ,
"ENERGY_1" DOUBLE ,
"ENERGY_2" DOUBLE ,
"ENERGY_3" DOUBLE ,
"ENERGY_4" DOUBLE ,
"ENERGY_5" DOUBLE ,
"POTENTIAL" DOUBLE NOT NULL ,
"POTENTIAL_ S" DOUBLE NOT NULL ,
"POTENTIAL_ 1" DOUBLE ,
"POTENTIAL_ 2" DOUBLE ,
"POTENTIAL_ 3" DOUBLE ,
"POTENTIAL_ 4" DOUBLE ,
"POTENTIAL_ 5" DOUBLE ,
"RMSD" DOUBLE WITH DEFAULT 0 ,
"RADIUS_OF_GYRA TION" DOUBLE WITH DEFAULT 0 )
IN "USERSPACE1 " ;


CREATE INDEX "DB2ADMIN"."IDX _FRAME1" ON "DB2ADMIN"."FRA ME"
("TID" ASC)
PCTFREE 10 ;

CREATE INDEX "DB2ADMIN"."IDX _FRAME2" ON "DB2ADMIN"."FRA ME"
("TYPE" ASC)
PCTFREE 10 ;

CREATE INDEX "DB2ADMIN"."IDX _FRAME3" ON "DB2ADMIN"."FRA ME"
("SNUM" ASC)
PCTFREE 10 ;

CREATE INDEX "DB2ADMIN"."IDX _FRAME4" ON "DB2ADMIN"."FRA ME"
("TIME" ASC)
PCTFREE 10 ;

ALTER TABLE "DB2ADMIN"."FRA ME"
ADD CONSTRAINT "PK_FRAME3" PRIMARY KEY
("ID");


MySQL:-

CREATE TABLE FRAME (
ID INTEGER NOT NULL ,
TID INTEGER NOT NULL ,
SNUM INTEGER NOT NULL DEFAULT 1 ,
TIME REAL NOT NULL DEFAULT 0,
TYPE CHAR(4) NOT NULL ,
VOLUME DOUBLE NOT NULL ,
PRESSURE DOUBLE NOT NULL ,
TEMP DOUBLE NOT NULL ,
TEMP_S DOUBLE NOT NULL ,
TEMP_1 DOUBLE ,
TEMP_2 DOUBLE ,
TEMP_3 DOUBLE ,
TEMP_4 DOUBLE ,
TEMP_5 DOUBLE ,
ENERGY DOUBLE NOT NULL ,
ENERGY_S DOUBLE NOT NULL ,
ENERGY_1 DOUBLE ,
ENERGY_2 DOUBLE ,
ENERGY_3 DOUBLE ,
ENERGY_4 DOUBLE ,
ENERGY_5 DOUBLE ,
POTENTIAL DOUBLE NOT NULL ,
POTENTIAL_S DOUBLE NOT NULL ,
POTENTIAL_1 DOUBLE ,
POTENTIAL_2 DOUBLE ,
POTENTIAL_3 DOUBLE ,
POTENTIAL_4 DOUBLE ,
POTENTIAL_5 DOUBLE ,
RMSD DOUBLE DEFAULT 0 ,
RADIUS_OF_GYRAT ION DOUBLE DEFAULT 0,
PRIMARY KEY ID (ID),
INDEX (TID)
);


CREATE TABLE COORDINATE (
FID INTEGER NOT NULL ,
AID INTEGER NOT NULL ,
X REAL NOT NULL ,
Y REAL NOT NULL ,
Z REAL NOT NULL ,
PRIMARY KEY (FID,AID),
INDEX (FID),
INDEX (AID)
);

[Mark A]

Re: DB2 vs MySQL - performance on large tables

[color=blue]
> Full DDLs for these two tables attached below for your reference.[/color]
Appericate for any comments.[color=blue]
>
> Thanks,
>
> Bing
>
> DB2:-
>
> CREATE TABLE "DB2ADMIN"."COO RDINATE" (
> "FID" INTEGER NOT NULL ,
> "AID" INTEGER NOT NULL ,
> "X" REAL NOT NULL ,
> "Y" REAL NOT NULL ,
> "Z" REAL NOT NULL )
> IN "USERSPACE1 " ;
> ALTER TABLE "DB2ADMIN"."COO RDINATE" APPEND ON;
>
> CREATE INDEX "DB2ADMIN"."IDX _COORDINATE1" ON "DB2ADMIN"."COO RDINATE"
> ("FID" ASC)
> PCTFREE 0 ;
>
> CREATE INDEX "DB2ADMIN"."IDX _COORDINATE2" ON "DB2ADMIN"."COO RDINATE"
> ("AID" ASC)
> PCTFREE 0 ;
>
> ALTER TABLE "DB2ADMIN"."COO RDINATE"
> ADD CONSTRAINT "PK_COORDINATE8 " PRIMARY KEY
> ("FID",
> "AID");
>[/color]
You should create the index DB2ADMIN.IDX_CO ORDINATE1 to be UNIQUE. Later
when you define the primary key, DB2 cannot use the existing index that you
created. Same with all other tables that have primary keys.

CREATE UNIQUE INDEX "DB2ADMIN"."IDX _COORDINATE1" ON "DB2ADMIN"."COO RDINATE"
("FID" ASC)
PCTFREE 0 ;

With a large table, it would be better to have the index and table in
different tablespaces. If you are adding new rows to the table, then you
need to reconsider the PCTFREE 0 on the indexes. With the append clause, the
rows are inserted at the end of the table, but indexes are always in correct
order, and some percent free is probably advisable (depending on how often
your reorg). You are probably getting a lot of split index pages which can
slow down queries (unless you reorg them after each data load).

[Bing Wu]

Re: DB2 vs MySQL - performance on large tables

Mark A wrote:[color=blue][color=green]
>>
>> CREATE TABLE "DB2ADMIN"."COO RDINATE" (
>> "FID" INTEGER NOT NULL ,
>> "AID" INTEGER NOT NULL ,
>> "X" REAL NOT NULL ,
>> "Y" REAL NOT NULL ,
>> "Z" REAL NOT NULL )
>> IN "USERSPACE1 " ;
>>ALTER TABLE "DB2ADMIN"."COO RDINATE" APPEND ON;
>>
>>CREATE INDEX "DB2ADMIN"."IDX _COORDINATE1" ON "DB2ADMIN"."COO RDINATE"
>> ("FID" ASC)
>> PCTFREE 0 ;
>>
>>CREATE INDEX "DB2ADMIN"."IDX _COORDINATE2" ON "DB2ADMIN"."COO RDINATE"
>> ("AID" ASC)
>> PCTFREE 0 ;
>>
>>ALTER TABLE "DB2ADMIN"."COO RDINATE"
>> ADD CONSTRAINT "PK_COORDINATE8 " PRIMARY KEY
>> ("FID",
>> "AID");
>>[/color]
>
> You should create the index DB2ADMIN.IDX_CO ORDINATE1 to be UNIQUE. Later
> when you define the primary key, DB2 cannot use the existing index that you
> created. Same with all other tables that have primary keys.
>
> CREATE UNIQUE INDEX "DB2ADMIN"."IDX _COORDINATE1" ON "DB2ADMIN"."COO RDINATE"
> ("FID" ASC)
> PCTFREE 0 ;
>[/color]

Thanks for pointing out the problem. Just a doubt. Maybe I was wrong. Should the UNIQUE INDEX only be applied to columns with unique value? In the above table, 'fid' certainly is not unique.

Bing

[Mark A]

Re: DB2 vs MySQL - performance on large tables

> Thanks for pointing out the problem. Just a doubt. Maybe I was wrong.
Should the UNIQUE INDEX only be applied to columns with unique value? In the
above table, 'fid' certainly is not unique.[color=blue]
>
> Bing
>[/color]
My apologies. Your Primary key has 2 columns. In that case, you don't the
index DB2ADMIN.IDX_CO ORDINATE1, since DB2 will create an index on Primary
Key. Since the first column of the index created for the Primary Key is the
one you want, the other index is redundant, so you can just drop it.

[Bing Wu]

Re: DB2 vs MySQL - performance on large tables

Ken wrote:[color=blue]
> Daniel Morgan <damorgan@x.was hington.edu> wrote in message news:<106874599 0.118235@yasure >...
>
>[/color]
Hmmm, but I've never seen a mysql benchmark that wasn't questionable[color=blue]
> in validity (such as their comparisions to postgresql), or trivial in
> implementation.
>
> They're typically read-only activities on single-cpu boxes using small
> databases. While that's a valid scenario - it hardly covers all the
> bases - such as SMP or MPP servers, mixed workloads, concurrency,
> backups, etc. And they're typically implemented by folks that don't
> really know databases. E-Week? Please. And they compared mysql 4.0
> to db2 7.2? Did they use innodb or isam for mysql? If not innodb
> then mysql didn't have RI and it was an apples to oranges comparison.[/color]

MySQL has been very popular and active in the e-commerce world. I have a few projects implemented using MySQL in the past 4 years and no major complains. It's fast and cheap to run with native multi-thread support so it's ideal for SMP/MPP servers. It also has impressive concurrency support so multi-thousand access isn't a problem. However, one of the disadvantages is support. Since it's GPL-based, so not as well-supported (funded) as pure-commerical databases i.e. DB2 or Oracle. But it's growning very fast, esp. in SMB sectors. And it's free, so large ISPs are using MySQL to handle large transactions .

Bing

[Bing Wu]

Re: DB2 vs MySQL - performance on large tables

Mark A wrote:[color=blue]
> My apologies. Your Primary key has 2 columns. In that case, you don't the
> index DB2ADMIN.IDX_CO ORDINATE1, since DB2 will create an index on Primary
> Key. Since the first column of the index created for the Primary Key is the
> one you want, the other index is redundant, so you can just drop it.
>[/color]
More questions following. I have Primary Key (FID, AID) in the table, but I have queries on FID only as well on AID only. Since DB2 auto-indexes the Primary Key, do I still need create the individual indexes for FID and AID to get better search reault? And I can specify reverse scan in INDEX while is this auto-enabled in a PK index?

Many thanks,

Bing

[Mark A]

Re: DB2 vs MySQL - performance on large tables

"Bing Wu" <bing@biop.ox.a c.uk> wrote in message
news:bp2k4t$j4e $1@news.ox.ac.u k...[color=blue]
> Mark A wrote:[color=green]
> > My apologies. Your Primary key has 2 columns. In that case, you don't[/color][/color]
the[color=blue][color=green]
> > index DB2ADMIN.IDX_CO ORDINATE1, since DB2 will create an index on[/color][/color]
Primary[color=blue][color=green]
> > Key. Since the first column of the index created for the Primary Key is[/color][/color]
the[color=blue][color=green]
> > one you want, the other index is redundant, so you can just drop it.
> >[/color]
> More questions following. I have Primary Key (FID, AID) in the table, but[/color]
I have queries on FID only as well on AID only. Since DB2 auto-indexes the
Primary Key, do I still need create the individual indexes for FID and AID
to get better search reault? And I can specify reverse scan in INDEX while
is this auto-enabled in a PK index?[color=blue]
>
> Many thanks,
>
> Bing
>[/color]
Since DB2 creates a unique index on (FID, AID) if one does not already
exist, then you don't need another index on FID. But you do need an index on
AID since it is not the first column of the index for the primary key.
Another index on FID-only is redundant to the one created for the Primary
Key. Removing the redundant index will speed up data loads, reorgs, etc.

I don't know how that works in MYSQL, but for DB2 what I said applies..

[Mark A]

Re: DB2 vs MySQL - performance on large tables

> > More questions following. I have Primary Key (FID, AID) in the table,
but[color=blue]
> I have queries on FID only as well on AID only. Since DB2 auto-indexes the
> Primary Key, do I still need create the individual indexes for FID and AID
> to get better search reault? And I can specify reverse scan in INDEX while
> is this auto-enabled in a PK index?[color=green]
> >
> > Many thanks,
> >
> > Bing[/color][/color]
Reverse scan is not auto-enabled for the index created by the primary key.
You can create the primary key unique index first manually with allowing
reverse scans (before defining the primary key) and then DB2 will use the
index you created instead of creating one on its own.

[Bing Wu]

Re: DB2 vs MySQL - performance on large tables

Mark A wrote:[color=blue]
> Since DB2 creates a unique index on (FID, AID) if one does not already
> exist, then you don't need another index on FID. But you do need an index on
> AID since it is not the first column of the index for the primary key.
> Another index on FID-only is redundant to the one created for the Primary
> Key. Removing the redundant index will speed up data loads, reorgs, etc.
>[/color]
Thanks for the comments. I do have other queries based on AIDs. So I'd assume I should create separate AID index. Since reverse scan on (FID, AID) won't make any sense for a query if it only searches based on either AID or FID.

Bing