仅使用> 1容器进行预取？ [英] Prefetching only with >1 container??
问题描述
有帮助的人，
我在IBM发布的
高级DBA认证指南和UDB V8参考中发现了一个令我感到相当困惑的声明。在页107上
作者正在解释为什么数据库中出现非常少量的预取可能会产生
。可能的低
预取活动的原因之一是：
"数据库中的表空间只设置了一个容器
每个都不能正常进行预取。
我对此断言感到非常惊讶。无论容器的数量如何，我原以为预取都可以
。
这本书是否正确？
Helpful folks,
I ran across a statement I found rather confusing in the IBM-published
Advanced DBA Certification Guide and Reference for UDB V8. On page 107
the author is explaining why the a very low amount of prefetching may
occur in a database. One of the reasons given for possible low
prefetch activity is:
"The table spaces in the database are set up with only one container
each so that prefetching cannot normally take place."
I was quite surprised by this assertion. I had thought prefetching can
take place regardless of the number of containers.
Is the book correct?
推荐答案
Sean，默认情况下，如果只有一个容器，DB2将不会预取。
只允许预取一个容器（特别是如果你有一个容器） RAID
文件系统）
您需要设置DB2注册表变量DB2_PARALLEL_IO = x其中x是
表空间ID（或者其中x = *适用于所有餐桌空间。）
谢谢，
.... Dwaine Snow
"肖恩C. <分贝***** @ yahoo.com>在留言中写道
新闻：2f ************************** @ posting.google.c om ...
Sean, By default DB2 will not prefetch if there is only one container.
To allow prefetching with only one container (especially if you have a RAID
file system)
you need to set the DB2 registry variable DB2_PARALLEL_IO= x where x is the
table space ID (or where x=* for all table spaces).
Thanks,
....Dwaine Snow
"Sean C." <db*****@yahoo.com> wrote in message
news:2f**************************@posting.google.c om...
有帮助的人，
我在IBM发布的高级DBA认证指南和UDB V8参考中发现了一个令我感到相当困惑的声明。在页107上作者正在解释为什么数据库中可能会发生非常少量的预取。可能的低预取活动的原因之一是：
数据库中的表空间每个只设置一个容器，因此通常不会进行预取。
我对此断言感到非常惊讶。无论容器数量多少，我原以为都可以进行预取。
这本书是否正确？
Helpful folks,
I ran across a statement I found rather confusing in the IBM-published
Advanced DBA Certification Guide and Reference for UDB V8. On page 107
the author is explaining why the a very low amount of prefetching may
occur in a database. One of the reasons given for possible low
prefetch activity is:
"The table spaces in the database are set up with only one container
each so that prefetching cannot normally take place."
I was quite surprised by this assertion. I had thought prefetching can
take place regardless of the number of containers.
Is the book correct?
" dbtnews.torolab.ibm.com" < DS *** @ ca.ibm.com>在消息中写道
news：c2 ********** @ hanover.torolab.ibm.com ...
"dbtnews.torolab.ibm.com" <ds***@ca.ibm.com> wrote in message
news:c2**********@hanover.torolab.ibm.com...
Sean，默认情况下DB2不会如果只有一个容器，则预取。
只允许使用一个容器进行预取（特别是如果你有一个
的RAID文件系统）
你需要设置DB2注册表变量DB2_PARALLEL_IO = x其中x是
表空间ID（或者所有表空间的x = *）。
谢谢，
... Dwaine Snow
Sean, By default DB2 will not prefetch if there is only one container.
To allow prefetching with only one container (especially if you have a RAID file system)
you need to set the DB2 registry variable DB2_PARALLEL_IO= x where x is the table space ID (or where x=* for all table spaces).
Thanks,
...Dwaine Snow
你能解释一下这个的基本原理吗？
Can you explain the rationale for this?
" Mark A" < ma@switchboard.net>在消息中写道
news：_c *************** @ news.uswest.net ...
"Mark A" <ma@switchboard.net> wrote in message
news:_c***************@news.uswest.net...
" dbtnews。 torolab.ibm.com" < DS *** @ ca.ibm.com>在消息中写道
新闻：c2 ********** @ hanover.torolab.ibm.com ...
"dbtnews.torolab.ibm.com" <ds***@ca.ibm.com> wrote in message
news:c2**********@hanover.torolab.ibm.com...
Sean，默认情况下，如果有，DB2将不会预取只有一个容器。
只允许一个容器预取（特别是如果你有一个
Sean, By default DB2 will not prefetch if there is only one container.
To allow prefetching with only one container (especially if you have a
RAID
RAID
文件系统）
你需要设置DB2注册表变量DB2_PARALLEL_IO = x，其中x是
file system)
you need to set the DB2 registry variable DB2_PARALLEL_IO= x where x is
表空间ID（或者所有表空间的x = *）。
<谢谢，
... Dwaine Snow
table space ID (or where x=* for all table spaces).
Thanks,
...Dwaine Snow
你能解释一下这个理由吗？
Can you explain the rationale for this?
预取的理由是双重的：
1）在查询需要行之前将行放入缓冲池中
（防止查询等待I / O）
2）利用底层磁盘的并行特性
子系统。
案例1：一个物理磁盘上的一个容器。 />
在这里，执行N预取读取不会加快查询速度，因为读取将在物理磁盘级别连续执行。在许多情况下，由于操作系统/硬件正在处理刚刚排队的一连串b $ b磁盘请求，因此实际上会降低速度。在这个设置中，N会连续读取
会更快。
案例2：RAID阵列上的一个容器（多个物理磁盘）
这里，进行N个预取读取是有利的，特别是如果你设置了
extensize和prefetchsize是你的RAID条带大小的倍数。如果
配置正确，N个预取读取中的每一个都将在RAID阵列的单独的
phsyical磁盘上完成。没有磁盘查询的序列化，
因此磁盘子系统没有性能损失。由于DB2
不知道RAID阵列的物理配置，因此必须设置
DB2_PARALLEL_IO才能启用并行I / O（预取）。 />
案例3：许多物理磁盘上的多个容器
由于DB2在容器中以循环方式分配扩展区，因此DB2
默认情况下知道读取连续扩展区（用于预取）将在不同的物理磁盘上完成 - 不会对
物理磁盘造成瓶颈。因此，它可以自动进行预取。
您可能会看到一些非IBM出版物建议您始终创建多个
容器，即使使用单个物理磁盘也是如此。
这个建议的唯一原因是自动启用预取（案例3）。
然而，由于
磁盘层请求（如案例1中所示）。
希望这会有所帮助。
-
Matt Emmerton
DB2 OLTP性能
IBM多伦多实验室
The rationale behind prefetching is twofold:
1) To bring rows into the bufferpool before they are needed by the query
(prevents the query from waiting on I/O)
2) To take advantage of the parallel nature of the underlying disk
subsystem.
Case 1: One container on one physical disk.
Here, doing N prefetch reads won''t speed up the query since the reads will
be executed serially at the physical disk level. In many cases, this will
actually slow things down since the OS/hardware is dealing with a barrage of
disk requests that just queue up. In this setup, it would be faster to do
the N reads serially.
Case 2: One container on a RAID array (multiple physical disks)
Here, it is advantageous to do N prefetch reads, especially if you''ve set up
extensize and prefetchsize to be a multiple of your RAID stripe size. If
configured properly, each of the N prefetch reads will be done on a separate
phsyical disk of the RAID array. There is no serialization of disk queries,
and therefore no performance penalty in the disk subsystem. Since DB2
doesn''t know the physical configuration of your RAID array, you have to set
DB2_PARALLEL_IO in order to enable parallel I/O (prefetching).
Case 3: Multiple containers on many physical disks
Since DB2 allocates extents on a round-robin basis among containers, DB2
knows by default that reading consecutive extents (for prefetch) will be
done on separate physical disks -- no potential for a bottleneck on the
physical disk. Thus, it enables prefetching automatically.
You may see some non-IBM publications advising you to always create multiple
containers, even when using a single physical disk. The only reason for
this suggestion is so that prefetching gets enabled automatically (Case 3).
However, performance is often sub-optimal because of the serialization of
requests at the disk layer (as in Case 1).
Hope this helps.
--
Matt Emmerton
DB2 OLTP Performance
IBM Toronto Lab
这篇关于仅使用> 1容器进行预取？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
