DB2 vs MySQL - 在大型表上的性能 [英] DB2 vs MySQL - performance on large tables
问题描述
大家好，
我正在运行一个包含大型数据集的数据库：
帧：2万行，
坐标：1.7亿行。
数据库已实现：
IBM DB2 v8.1
MySQL v3.23.54
使用类似的DDL和相同的索引。我已经运行了一个SQL连接两个表：
选择cx，cy，cz从坐标为c，帧为f
其中fid< 1000和f .tid = 1和f.id = c.fid
查询在3400亿个可能的关节上运行并返回大约850万条记录。 MySQL似乎在未来表现优于DB2：
DB2：7分钟
MySQL：3分钟
MySQL是在DB2运行时使用默认设置：
缓冲池：500 MB（1GB系统内存）
更多seetings请参阅：上一条标题为 -
提高大型表的查询性能
我是否应该假设MySQL在大型表上的运行速度比DB2快得多？有人可以建议吗？顺便说一下，我还应该提一下返回的数据格式：
DB2：1.279200e + 01 -1.596900e + 01 -3.979500e + 01
MySQL ：12.792 -15.969 -39.795
（x，y，z在两个数据库中定义为REAL）
我怀疑这可能会使性能差异。
祝你好运，
Bing
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
推荐答案
不，你不应该假设SQL Server在大的
表上胜过DB2。您是否从控制中心运行配置顾问
以获取配置参数的建议值？ db2advis
命令怎么样？
如果多次运行查询，那么缓冲池就会被填充
数据确实如此在任何一个系统或两个系你有没有在表上更新统计数据（runstats），分配值和
索引？
Bing Wu写道：
No, you should not assume that SQL Server outperforms DB2 on large
tables. Have you run the configuration advisor from the control center
to get suggested values on config parameters? What about the db2advis
command?
If you run the query several times so the buffer pool gets filled with
data does performance improve on either or both systems? Have you
updated statistics (runstats) on the tables, distribution of values, and
indexes?
Bing Wu wrote:
大家好，
我正在运行一个包含大型数据集的数据库：
框架：2万行，
坐标：1.7亿行。
数据库已经实现了：
IBM DB2 v8.1
MySQL v3.23.54
使用类似的DDL和相同的索引。我已经运行了一个SQL连接两个表：
选择cx，cy，cz从坐标为c，帧为f，其中fid< 1000
和f.tid = 1和f.id = c.fid
该查询在3400亿个可能的关节上运行，并返回大约8.5百万条记录。 MySQL似乎在性能上优于DB2：
DB2：7分钟
MySQL：3分钟
在DB2运行时，MySQL运行时使用默认设置：
<缓冲池：500 MB（1GB系统内存）
更多请参阅：上一条标题为
提高大型表的查询性能的消息
我应该假设MySQL在大型表上运行速度比DB2快得多吗？
有人可以建议吗？顺便说一下，我还应该提一下返回的数据格式：
DB2：1.279200e + 01 -1.596900e + 01 -3.979500e + 01
MySQL：12.792 -15.969 -39.795
（x，y，z在两个数据库中都被定义为REAL）
我怀疑这可能会带来性能差异。
Best问候，
Bing
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
" Blair Adamache" < BA ******* @ 2muchspam.yahoo.com>在消息中写道
news：bo ********** @ hanover.torolab.ibm.com ...
"Blair Adamache" <ba*******@2muchspam.yahoo.com> wrote in message
news:bo**********@hanover.torolab.ibm.com...
不，你不应该认为SQL Server在大型表上优于DB2。您是否从控制中心运行配置顾问程序以获取配置参数的建议值？ db2advis
命令怎么样？
如果您多次运行查询以便缓冲池充满数据，那么系统中的任何一个或两个系统的性能都会提高吗？你有没有更新表上的统计数据（runstats），值的分布和索引？
No, you should not assume that SQL Server outperforms DB2 on large
tables. Have you run the configuration advisor from the control center
to get suggested values on config parameters? What about the db2advis
command?
If you run the query several times so the buffer pool gets filled with
data does performance improve on either or both systems? Have you
updated statistics (runstats) on the tables, distribution of values, and
indexes?
他说的是MySQL，而不是MS SQL Server。 br />
He is talking about MySQL, not MS SQL Server.
抱歉。 MySQL的功能少于DB2，因此它可能必须为某些操作执行更少的b $ b代码路径，并且它们可能更快。我对DB2
的建议仍值得关注。如果DB2是过度优化的话。你可以试试
查询，优化级别为1 ro 4.
Mark A写道：
Sorry. MySQL has less function than DB2, so it may have to execute less
code path for some operations, and they may be faster. My advice on DB2
is still worth following. If DB2 is "over-optimizing" you could try the
query with an optimization level somewhere from 1 ro 4.
Mark A wrote:
Blair Adamache < BA ******* @ 2muchspam.yahoo.com>在消息中写道
新闻：bo ********** @ hanover.torolab.ibm.com ...
"Blair Adamache" <ba*******@2muchspam.yahoo.com> wrote in message
news:bo**********@hanover.torolab.ibm.com...
不，你不应该认为SQL Server在大型表上优于DB2。您是否从控制中心运行配置顾问程序以获取配置参数的建议值？ db2advis
命令怎么样？
如果您多次运行查询以便缓冲池充满数据，那么系统中的任何一个或两个系统的性能都会提高吗？你有没有更新表格上的统计数据（runstats），值的分布和索引？
No, you should not assume that SQL Server outperforms DB2 on large
tables. Have you run the configuration advisor from the control center
to get suggested values on config parameters? What about the db2advis
command?
If you run the query several times so the buffer pool gets filled with
data does performance improve on either or both systems? Have you
updated statistics (runstats) on the tables, distribution of values, and
indexes?
他说的是MySQL，而不是MS SQL Server。 br />
He is talking about MySQL, not MS SQL Server.
这篇关于DB2 vs MySQL - 在大型表上的性能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
