473,854 Members | 1,721 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

DB2 vs MySQL - performance on large tables

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

MySQL is running with default settings while DB2:

Buffer pool: 500 MB (1GB system memory)
More seetings see: previous message titled -
"Improve query performance on large table"

Should I assume MySQL does run much faster than DB2 on large tables? Could anyone advice? BTW, one thing I should also mention that returned data formats:

DB2: 1.279200e+01 -1.596900e+01 -3.979500e+01
MySQL: 12.792 -15.969 -39.795

(x,y,z are defined as REAL in both databases)

I doubt this could make the performance difference.
Best regards,

Bing


Nov 12 '05
57 25547
Ken wrote:
Daniel Morgan <da******@x.was hington.edu> wrote in message news:<106874599 0.118235@yasure >...
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


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.
From my experience on comparable hardware I think MySQL is indeed
faster.


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

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/e...ad/oad_crs.asp
http://www.outreach.washington.edu/e...oa/aoa_crs.asp
da******@x.wash ington.edu
(replace 'x' with a 'u' to reply)
Nov 12 '05 #21
Jan M. Nelken wrote:

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

Jan M. Nelken


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)
);

Nov 12 '05 #22
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");

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).
Nov 12 '05 #23
Mark A wrote:

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");


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 ;


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
Nov 12 '05 #24
> 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

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.
Nov 12 '05 #25
Ken wrote:
Daniel Morgan <da******@x.was hington.edu> wrote in message news:<106874599 0.118235@yasure >...

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.


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
Nov 12 '05 #26
Mark A wrote:
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.

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

Nov 12 '05 #27
"Bing Wu" <bi**@biop.ox.a c.uk> wrote in message
news:bp******** **@news.ox.ac.u k...
Mark A wrote:
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.
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

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

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.
Nov 12 '05 #29
Mark A wrote:
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.

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
Nov 12 '05 #30

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

Similar topics

2
6827
by: Kevin Crosbie | last post by:
Hi all, (Sorry for the cross-post, there doesn't appear to be much activity on comp.database.oracle)I'm trying to get the last 300 rows from the difference between 2 large tables and my queries are taking at least 10 minutes to do this. I'm running on quite a fast server and the tables are not very large, 3,000,000 to 30,000,000 rows. I've tried the following:
0
1780
by: Joseph Norris | last post by:
Group, I have been working with Mysql for about 5 years - mainly in LAMP shops. The tables have been between 20-100 thousand records size. Now I have a project where the tables are in the millions of records. This is very new to me and I am noticing that my queries are really slowwwwww! What are the options that I have to speed my queries on the mysql side with
3
1575
by: Jeremy Howard | last post by:
I am finding delete queries on large InnoDB tables very slow - are there ways to speed this up? I have a table with about 100 million rows: I am trying to delete just a few of these rows (the following select takes a couple of seconds): > SELECT count(*) -> FROM UserSnap
1
2125
by: boonkit | last post by:
Hi all, I cannot find MySQl performance info at linux "top" command. I already tested on slow MySQL query that took more than 5 minutes (create fulltext index), but during the 5 minutes, i cannot find any mysql process at top command. Below is the system information: Linux version: 2.6.9-1.667smp (Red Hat 3.4.2-6.fc3) MySQL version: 4.1.11 (source installation)
1
1409
by: steve | last post by:
I am researching mysql performance optimization. Does anyone who good books, or good url’s for this. I have seen a couple of good books on Amazon, but if you have read anything good, let me know. -- Posted using the http://www.dbforumz.com interface, at author's request Articles individually checked for conformance to usenet standards Topic URL: http://www.dbforumz.com/mySQL-Good-resources-performance-optimization-ftopict220297.html...
2
2326
by: Shashikant Kore | last post by:
Hi, I am using MySQL for a table which will have 100M+ records, avg length of records being 130 bytes. When the number of records reach approx. 25M (and the file size close to 4GB), the rate of inserts falls drastically from 800 per second to 30-40 per second. Details: * MySQL 3.23.58 on Fedora Core 3
1
2014
by: Dino Vliet | last post by:
Hi folks, I'm busy with analyzing some data and therefore will have to store 2 big tables containing 50 million obs & 25 million obs. Selecting some interesting obs can decrease these tables with maybe a factor 10 but eventually I will have to combine them using a join. These tables contain almost 20 colums and after selecting the appropriate ones I will have maybe 10 colums.
1
2402
by: murray | last post by:
Large table performance problem with MYSQL performance with ASP.NET I am doing some benchmarking of an application using MYSQL under Windows and I confused about the results. Environment Emachine 6417 with amd3200+ 64 bit processor 3.3GB ram mysql 4.1 with odbc 3.51 MYISAM windows 2003 server std edition
0
1352
by: OctoTools | last post by:
Large table performance problem with MYSQL performance with ASP.NET I am doing some benchmarking of an application using MYSQL under Windows and I confused about the results. Environment Emachine 6417 with amd3200+ 64 bit processor 3.3GB ram mysql 4.1 with odbc 3.51 MYISAM windows 2003 server std edition
0
9901
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9752
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
10371
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
9518
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
7918
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
5750
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
5942
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
2
4162
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3188
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

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.