插入/记录速度慢 [英] Slow insert/logging performance
问题描述
我有一个进程，插件运行速度比Oracle慢50％，而b / b
Mssql。
查询通常比正常情况慢50％。
DB2，Oracle，Mssql都配置在同一个操作系统上，相同的磁盘阵列（raid
1/0 - 15k磁盘）但DB2 I / O似乎要慢得多。
表空间是带有自动预取集的短信。
我的想法是创建尽可能大的日志缓冲区数组所以我
不必太多切换到磁盘。我正在进行循环记录。数据
表空间缓冲池也很大（80000）
这是我的设置：
LOGFILSIZ 60000
LOGPRIMARY 10
NUM_IOCLEANERS 50
NUM_IOSERVERS 50
maxfilop 100
avg_apps 10
app_ctl_heap_sz 256
sortheap 9600
stmtheap 4096
applheapsz 1200
pckcachesz 1200
dlchktime 60000
表空间是SMS而不是DMS。
切换到DMS可以达到什么样的％增长？
我需要关注哪些其他参数来提高i / o
性能？
DB2应该在这个raid阵列上快速尖叫它只是好像
爬行。
注意：此服务器/ raid阵列上没有其他运行的东西
是db2diag文件中没有错误。
dunleav1写道：I有一个进程，插件运行速度比Oracle和Mssql慢50％。
查询通常比正常情况慢50％。
DB2，Oracle，Mssql都配置在相同的操作系统，相同的磁盘阵列（raid
1/0 - 15k磁盘）但DB2 I / O似乎要慢得多。
表空间是带有自动预取集的SMS。
我的想法是尽可能大地创建日志缓冲区数组，所以我不需要太多切换到磁盘。我正在进行循环记录。数据表空间缓冲池也很大（80000）
以下是我的设置：
LOGFILSIZ 60000
LOGPRIMARY 10
NUM_IOCLEANERS 50
NUM_IOSERVERS 50
maxfilop 100
avg_apps 10
app_ctl_heap_sz 256
sortheap 9600
stmtheap 4096
applheapsz 1200
pckcachesz 1200
dlchktime 60000
表空间是SMS而不是DMS。
切换到DMS可以期待什么样的％增加？
我需要关注哪些其他参数来改善i / o
性能？
DB2应该在这个raid阵列上快速尖叫，它似乎只是抓取。
注意：没有别的东西在运行这个服务器/ raid数组在db2diag文件中没有错误。
一些通用建议（也用于更新性能）：
*确保你有多个IO服务器（默认为1） - 你这样做，确定
Thumbrule是这个数字应该等于disc-spindles。
* db2 update db cfg using SEQDETECT yes
启用db2检测到更新正在进行表扫描和
预取的踢（当然不适用于插入）
* db2set DB2_PARALLEL_IO = *
启用并行IO，即使你的
$中只有一个容器b $ b表空间
* db2set DB2_USE_ALTERNATE_PAGE_CLEANING = yes
使用更好的页面清理算法（在OLTP中总是很好，
并不总是在BI中这就是为什么它不是（还）默认的）
干杯
Serge
PS：你不要使用像MDC那样的任何东西，对吧？当MDC尺寸过细时，我看到可怜的插入
性能
（延伸爆炸）。
-
Serge Rielau
DB2解决方案开发
IBM多伦多实验室
IOD会议
http://www.ibm.com/software/data/ond .. .ness / conf2006 /
Serge Rielau写道：dunleav1写道：我有一个进程，插件运行速度比Oracle和Mssql慢50％。
查询通常比正常速度慢50％。
DB2，Oracle，Mssql都配置在相同的操作系统，相同的磁盘阵列（raid
1/0 - 15k磁盘）上，但DB2 I / O似乎要慢得多。
表空间是具有自动预取功能的SMS设置。
我的想法是创建大小的日志缓冲区数组可能所以我不需要太多切换到磁盘。我正在进行循环记录。数据表空间缓冲池也很大（80000）
以下是我的设置：
LOGFILSIZ 60000
LOGPRIMARY 10
NUM_IOCLEANERS 50
NUM_IOSERVERS 50
maxfilop 100
avg_apps 10
app_ctl_heap_sz 256
sortheap 9600
stmtheap 4096
applheapsz 1200
pckcachesz 1200
dlchktime 60000
表空间是SMS而不是DMS。
切换到DMS可以期待什么样的％增加？
我可以期待性能转换为DMS的增加吗？还有其他任何我需要关注的参数来提高i / o
性能吗？
DB2应该在这个raid阵列上快速尖叫，它似乎只是抓取。
一些通用建议（也用于更新性能）：
*确保您有多个IO服务器（默认为1） - 您可以，确定
Thumbrule是该数字应该等于圆盘轴。
我将ioservers减少到数组中的磁盘数量 - 6. * db2 update db cfg using SEQDETECT yes
允许db2检测到更新正在进行表扫描和取消预取（当然不需要插入）
* db2set DB2_PARALLEL_IO = *
即使你的表空间中只有一个容器，也启用并行IO
我会给db2_parallel_io = *一枪。
我知道o将表空间的页面大小设置为
raid数组的条带大小，
但是当在表空间上设置了预取自动时，请执行
开销/扩展，prefetchsize，传输表空间问题
？
* db2set DB2_USE_ALTERNATE_PAGE_CLEANING = yes
使用更好的页面清理算法（在OLTP中总是很好，
并不总是在BI中，这就是为什么它（不是）默认的）
DB2_ALTERNATE_PAGE_CLEANING设置为是。
干杯
Serge
PS：你不会使用像MDC那样的任何东西，对吧？当MDC尺寸过细时，我发现插入性能不佳
（延伸爆炸）。
此数据库中未使用MDC。 -
Serge Rielau
DB2解决方案开发
IBM多伦多实验室
IOD会议
http://www.ibm.com/software/data/ond...ness/conf2006/
Serge Rielau写道：dunleav1写道：我有一个进程，插件运行速度比Oracle和Mssql慢50％。
查询通常比正常速度慢50％。
DB2，Oracle ，Mssql都配置在相同的操作系统，相同的磁盘阵列（raid
1/0 - 15k磁盘）上，但DB2 I / O似乎要慢得多。
表空间是带有自动预取集的SMS。 />
我的想法是尽可能大地创建日志缓冲区数组，所以我不必太多地切换到磁盘。我正在进行循环记录。数据表空间缓冲池也很大（80000）
以下是我的设置：
LOGFILSIZ 60000
LOGPRIMARY 10
NUM_IOCLEANERS 50
NUM_IOSERVERS 50
maxfilop 100
avg_apps 10
app_ctl_heap_sz 256
sortheap 9600
stmtheap 4096
applheapsz 1200
pckcachesz 1200
dlchktime 60000
表空间是SMS而不是DMS。
切换到DMS可以期待什么样的％增加？
我可以期待性能转换为DMS的增加吗？还有其他任何我需要关注的参数来提高i / o
性能吗？
DB2应该在这个raid阵列上快速尖叫，它似乎只是抓取。
一些通用建议（也用于更新性能）：
*确保您有多个IO服务器（默认为1） - 您可以，确定
Thumbrule是该数字应该等于圆盘轴。
我将ioservers减少到数组中的磁盘数量 - 6. * db2 update db cfg using SEQDETECT yes
允许db2检测到更新正在进行表扫描和取消预取（当然不需要插入）
* db2set DB2_PARALLEL_IO = *
即使你的表空间中只有一个容器，也启用并行IO
我会给db2_parallel_io = *一枪。
我知道o将表空间的页面大小设置为
raid数组的条带大小，
但是当在表空间上设置了预取自动时，请执行
开销/扩展，prefetchsize，传输表空间问题
？
* db2set DB2_USE_ALTERNATE_PAGE_CLEANING = yes
使用更好的页面清理算法（在OLTP中总是很好，
并不总是在BI中，这就是为什么它（不是）默认的）
DB2_ALTERNATE_PAGE_CLEANING设置为是。
干杯
Serge
PS：你不会使用像MDC那样的任何东西，对吧？当MDC尺寸过细时，我发现插入性能不佳
（延伸爆炸）。
此数据库中未使用MDC。 -
Serge Rielau
DB2解决方案开发
IBM多伦多实验室
IOD会议
http://www.ibm.com/software/data/ond...ness/conf2006/
I have a process that does inserts that runs 50% slower that Oracle and
Mssql.
Queries normally take 50% slower than normal.
DB2, Oracle, Mssql all are configured on same os, same disk array (raid
1/0 - 15k disks) but DB2 I/O seems to be significantly slower.
Tablespaces are SMS with automatic prefetch set.
My thought was to create the log buffer array as large as possible so I
don''t have to switch to disk much. I''m running circular logging. data
tablespace buffer pools are large as well (80000)
Here are my settings:
LOGFILSIZ 60000
LOGPRIMARY 10
NUM_IOCLEANERS 50
NUM_IOSERVERS 50
maxfilop 100
avg_apps 10
app_ctl_heap_sz 256
sortheap 9600
stmtheap 4096
applheapsz 1200
pckcachesz 1200
dlchktime 60000
Tablespaces are SMS instead of DMS.
What kind of % increase can I expect from switching to DMS?
Are there any other parameters I need to focus to improve i/o
performance?
DB2 should be screaming fast on this raid array and it just seems to
crawl.
Note: There is nothing else running on this server/raid array and there
are no errors in the db2diag file.
dunleav1 wrote:I have a process that does inserts that runs 50% slower that Oracle and
Mssql.
Queries normally take 50% slower than normal.
DB2, Oracle, Mssql all are configured on same os, same disk array (raid
1/0 - 15k disks) but DB2 I/O seems to be significantly slower.
Tablespaces are SMS with automatic prefetch set.
My thought was to create the log buffer array as large as possible so I
don''t have to switch to disk much. I''m running circular logging. data
tablespace buffer pools are large as well (80000)
Here are my settings:
LOGFILSIZ 60000
LOGPRIMARY 10
NUM_IOCLEANERS 50
NUM_IOSERVERS 50
maxfilop 100
avg_apps 10
app_ctl_heap_sz 256
sortheap 9600
stmtheap 4096
applheapsz 1200
pckcachesz 1200
dlchktime 60000
Tablespaces are SMS instead of DMS.
What kind of % increase can I expect from switching to DMS?
Are there any other parameters I need to focus to improve i/o
performance?
DB2 should be screaming fast on this raid array and it just seems to
crawl.
Note: There is nothing else running on this server/raid array and there
are no errors in the db2diag file.
Some generic recommendations (also for update performance):
* Make sure you have more than one IO server (default is 1) -- You do,OK
Thumbrule is that the number should be equal to the disc-spindles.
* db2 update db cfg using SEQDETECT yes
Enables that db2 detects that the update is doing a table scan and
kicks of prefetching (not relevant for insert of course)
* db2set DB2_PARALLEL_IO=*
Enables parallel IO even in case you only have one container in your
tablespace
* db2set DB2_USE_ALTERNATE_PAGE_CLEANING=yes
Uses the better page cleaner algorithm (always good in OLTP,
not always in BI which is why it''s not (yet) the default)
Cheers
Serge
PS: You don''t use anything fancy like MDC, right? I''ve seen poor insert
performance when the MDC dimensions were too fine grained
(explosion of extends).
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/
Serge Rielau wrote:dunleav1 wrote:I have a process that does inserts that runs 50% slower that Oracle and
Mssql.
Queries normally take 50% slower than normal.
DB2, Oracle, Mssql all are configured on same os, same disk array (raid
1/0 - 15k disks) but DB2 I/O seems to be significantly slower.
Tablespaces are SMS with automatic prefetch set.
My thought was to create the log buffer array as large as possible so I
don''t have to switch to disk much. I''m running circular logging. data
tablespace buffer pools are large as well (80000)
Here are my settings:
LOGFILSIZ 60000
LOGPRIMARY 10
NUM_IOCLEANERS 50
NUM_IOSERVERS 50
maxfilop 100
avg_apps 10
app_ctl_heap_sz 256
sortheap 9600
stmtheap 4096
applheapsz 1200
pckcachesz 1200
dlchktime 60000
Tablespaces are SMS instead of DMS.
What kind of % increase can I expect from switching to DMS? Can I expect an increase in performance switching to DMS ? Are there any other parameters I need to focus to improve i/o
performance?
DB2 should be screaming fast on this raid array and it just seems to
crawl.
Note: There is nothing else running on this server/raid array and there
are no errors in the db2diag file.
Some generic recommendations (also for update performance):
* Make sure you have more than one IO server (default is 1) -- You do,OK
Thumbrule is that the number should be equal to the disc-spindles.
I reduced the ioservers to the numbers of disks in the array - 6. * db2 update db cfg using SEQDETECT yes
Enables that db2 detects that the update is doing a table scan and
kicks of prefetching (not relevant for insert of course)
* db2set DB2_PARALLEL_IO=*
Enables parallel IO even in case you only have one container in your
tablespace I''ll give the db2_parallel_io=* a shot.
I know to set the pagesize of the tablespace to the stripe size of the
raid array,
but when prefetch automatic is set on the tablespace do the
overhead/extensize,prefetchsize,transferrate for the tablespace matter
?
* db2set DB2_USE_ALTERNATE_PAGE_CLEANING=yes
Uses the better page cleaner algorithm (always good in OLTP,
not always in BI which is why it''s not (yet) the default)
DB2_ALTERNATE_PAGE_CLEANING is set to yes.
Cheers
Serge
PS: You don''t use anything fancy like MDC, right? I''ve seen poor insert
performance when the MDC dimensions were too fine grained
(explosion of extends). No MDC is used in this database. --
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/
Serge Rielau wrote:dunleav1 wrote:I have a process that does inserts that runs 50% slower that Oracle and
Mssql.
Queries normally take 50% slower than normal.
DB2, Oracle, Mssql all are configured on same os, same disk array (raid
1/0 - 15k disks) but DB2 I/O seems to be significantly slower.
Tablespaces are SMS with automatic prefetch set.
My thought was to create the log buffer array as large as possible so I
don''t have to switch to disk much. I''m running circular logging. data
tablespace buffer pools are large as well (80000)
Here are my settings:
LOGFILSIZ 60000
LOGPRIMARY 10
NUM_IOCLEANERS 50
NUM_IOSERVERS 50
maxfilop 100
avg_apps 10
app_ctl_heap_sz 256
sortheap 9600
stmtheap 4096
applheapsz 1200
pckcachesz 1200
dlchktime 60000
Tablespaces are SMS instead of DMS.
What kind of % increase can I expect from switching to DMS? Can I expect an increase in performance switching to DMS ? Are there any other parameters I need to focus to improve i/o
performance?
DB2 should be screaming fast on this raid array and it just seems to
crawl.
Note: There is nothing else running on this server/raid array and there
are no errors in the db2diag file.
Some generic recommendations (also for update performance):
* Make sure you have more than one IO server (default is 1) -- You do,OK
Thumbrule is that the number should be equal to the disc-spindles.
I reduced the ioservers to the numbers of disks in the array - 6. * db2 update db cfg using SEQDETECT yes
Enables that db2 detects that the update is doing a table scan and
kicks of prefetching (not relevant for insert of course)
* db2set DB2_PARALLEL_IO=*
Enables parallel IO even in case you only have one container in your
tablespace I''ll give the db2_parallel_io=* a shot.
I know to set the pagesize of the tablespace to the stripe size of the
raid array,
but when prefetch automatic is set on the tablespace do the
overhead/extensize,prefetchsize,transferrate for the tablespace matter
?
* db2set DB2_USE_ALTERNATE_PAGE_CLEANING=yes
Uses the better page cleaner algorithm (always good in OLTP,
not always in BI which is why it''s not (yet) the default)
DB2_ALTERNATE_PAGE_CLEANING is set to yes.
Cheers
Serge
PS: You don''t use anything fancy like MDC, right? I''ve seen poor insert
performance when the MDC dimensions were too fine grained
(explosion of extends). No MDC is used in this database. --
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/
这篇关于插入/记录速度慢的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
