Win2k内存分配 - BP v Sortheap [英] Win2k Memory Allocation - BP v Sortheap
问题描述
大家好，
我正在遵循DB2 8.2.2的一般内存分配指南
带有4gb物理的Win2k Server FP4上的ESE内存。
看到一切都是32位和win2k服务器不支持/ 3gb
切换，我们限制内存明智地分配内存到
db2syscs.exe大约1.75gb所以我明白，分配内存的一般
经验法则是什么？
我的流程目前开始激活后大约1.5gb，使用几天后，
增长到最大值1.68gb。
我有4个缓冲池，每个1个到3个表空间和默认值。
表空间特定于Rawdata，结果和索引。我将大部分BP页面分配给索引缓冲池[170000 4k页]
结果[100000]排名第2，rawdata排名最后[50000]。
是否更好地为实际数据或索引分配更大的BP？
在所有这些中，哪一个优先考虑sortheap和sheapthresh？ />
并且是否有更多关键区域应该分配给配置顾问推荐的
？是否有百分比指南
，可以说分享最大内存30/70 sort / bp？等等
配置顾问只推荐1428 4k页的分类和
sheapthresh 32000 4k页面似乎总是产生排序
在快照中溢出。是否更好的分配更高的BP和
更低的排序堆，或更高的sortheap和更低的BP。
只用1.75gb就可以得到它令人沮丧的是，当我知道
时，如果没有win2k，我至少可以分配额外的1.5。
任何帮助将不胜感激。
Hi folks,
I''m after some guidelines on general memory allocations for DB2 8.2.2
ESE on Win2k Server FP4 with 4gb physical memory.
Seeing as everything is 32bit and win2k server does not support /3gb
switch, we are restricted memory wise for allocating memory to
db2syscs.exe to around 1.75gb so I understand, what are the general
rules of thumb for assigning memory ?
My process presently starts at around 1.5gb following activation and
grows to a max of around 1.68gb after being used for a few days.
I have 4 bufferpools, 1 each to 3 tables spaces and the default.
Tablespaces are specific for Rawdata, results and indexes. I assign
most of the BP pages to the index bufferpool [170000 4k pages] with
results [100000] ranked 2nd and rawdata last [50000]. IS it better to
assign a larger BP to the actual data or the indexes ?
Where does one prioritise the sortheap and sheapthresh in all of this
and are there more critical areas that should be assigned more than say
the configuration advisor recommends ?. Are there percentage guidelines
that can say share the max memory 30/70 sort/bp ? etc.
The config advisor is only recommending a sortheap of 1428 4k pages and
sheapthresh of 32000 4k pages which seems to always produce sort
overflows in the snapshots. Is it better to assign a higher BP and
lower sort heap, or higher sortheap and lower BPs.
With only 1.75gb to play with it gets very frustrating when I know
there is at least an additional 1.5 I could assign if not for win2k.
Any help would be greatly appreciated.
推荐答案
内存调整的目标是最小化所有I / O处理。大的
缓冲池和许多排序I / O可能不是最佳性能的最佳组合。几乎不可能设置排序工作区以消除
各种各样。您需要选择一个最小化溢出的大小，而不会导致较小的缓冲池中的表I / O增加太多。
我会尝试通过调整
缓冲池大小来平衡表空间的I / O速率。所有表空间的I / O速率为1 / sec，对于索引而言，优于
而不是0.01 /秒，对于数据则为10 / sec。从一对快照，
使用计数差异除以经过的时间来获得率/秒。
如果你有SQL导致表空间扫描，你会无法消除由这些陈述引起的I / O.
调整总是在管理资源获取的权衡取舍
最好的福利。
Phil Sherman
p175写道：
THe objective of memory tuning is to minimize ALL I/O processing. Large
buffer pools and lots of sort I/O may not be the best mix for optimal
performance. It''s almost impossible to setup sort workspace to eliminate
all sorts. You need to pick a size that will minimize overflows without
causing too much increase in table I/O from smaller buffer pools.
I''d try to balance the I/O rates for the tablespaces by adjusting the
bufferpool sizes. AN I/O rate of 1/sec for all tablespaces is better
than 0.01/sec for indexes and 10/sec for data. From a pair of snapshots,
use the count differences divided by the elapsed time to get rates/sec.
If you have SQL that causes tablespace scans, you''ll be unable to
eliminate the I/O caused by these statements.
Tuning is always managing tradeoffs of where to use the resources to get
the best benefits.
Phil Sherman
p175 wrote:
嗨伙计们，
我正在遵循关于DB2 8.2.2的一般内存分配的指导原则。在具有4GB物理内存的Win2k Server FP4上使用ESE。
看到一切都是32位和win2k服务器不支持/ 3gb
切换，我们限制内存明智地分配内存到db2syscs.exe到大约1.75gb所以我明白，分配的一般
经验法则是什么记忆？
我的过程目前在激活后大约1.5gb开始，并且最多增加到1.68g左右b。使用几天后。
我有4个缓冲池，每个1到3个表空间和默认值。
表空间特定于Rawdata，结果和索引。我将大部分BP页面分配给索引缓冲池[170000 4k页]，结果[100000]排名第2，rawdata排名最后[50000]。是否更好地为实际数据或索引分配更大的BP？
在哪里优先考虑sortheap和sheapthresh所有这些
并且有更多关键领域应该分配多于配置顾问推荐的配置？是否有百分比指南
可以说共享最大内存30/70 sort / bp？
配置顾问只推荐1428 4k页的分页和
32000 4k页的sheapthresh，这似乎总是在快照中产生排序
溢出。分配更高的BP和更低的排序堆，或更高的sortheap和更低的BP更好。
当我知道
时，它只有1.75gb就会非常令人沮丧如果不是win2k，至少还有1.5我可以分配。
任何帮助将不胜感激。
Hi folks,
I''m after some guidelines on general memory allocations for DB2 8.2.2
ESE on Win2k Server FP4 with 4gb physical memory.
Seeing as everything is 32bit and win2k server does not support /3gb
switch, we are restricted memory wise for allocating memory to
db2syscs.exe to around 1.75gb so I understand, what are the general
rules of thumb for assigning memory ?
My process presently starts at around 1.5gb following activation and
grows to a max of around 1.68gb after being used for a few days.
I have 4 bufferpools, 1 each to 3 tables spaces and the default.
Tablespaces are specific for Rawdata, results and indexes. I assign
most of the BP pages to the index bufferpool [170000 4k pages] with
results [100000] ranked 2nd and rawdata last [50000]. IS it better to
assign a larger BP to the actual data or the indexes ?
Where does one prioritise the sortheap and sheapthresh in all of this
and are there more critical areas that should be assigned more than say
the configuration advisor recommends ?. Are there percentage guidelines
that can say share the max memory 30/70 sort/bp ? etc.
The config advisor is only recommending a sortheap of 1428 4k pages and
sheapthresh of 32000 4k pages which seems to always produce sort
overflows in the snapshots. Is it better to assign a higher BP and
lower sort heap, or higher sortheap and lower BPs.
With only 1.75gb to play with it gets very frustrating when I know
there is at least an additional 1.5 I could assign if not for win2k.
Any help would be greatly appreciated.
p175写道：
p175 wrote:
我有4个缓冲池，每个1到3个表空间和默认值。
表空间特定于Rawdata，结果和索引。我将大部分BP页面分配给索引缓冲池[170000 4k页]，结果[100000]排名第2，rawdata排名最后[50000]。是否更好地为实际数据或索引分配更大的BP？
I have 4 bufferpools, 1 each to 3 tables spaces and the default.
Tablespaces are specific for Rawdata, results and indexes. I assign
most of the BP pages to the index bufferpool [170000 4k pages] with
results [100000] ranked 2nd and rawdata last [50000]. IS it better to
assign a larger BP to the actual data or the indexes ?
DB2有一个解释工具 - 所以如果你的所有访问平台都是这样的只有扫描才能获得
索引 - 您的优惠指数的策略会得到回报;在任何其他
的情况下（你进行索引扫描，然后对符合条件的行进行数据访问）你可能会想要重新考虑这个策略。
原始数据 - 您是否正在调整只读访问权限？原始数据是如何找到的; s /
进入数据库（一次加载 - 或每小时更新）？
结果 - 你只是存储结果而且从不查询他们？或者在存储
结果时，很多用户可以阅读它们并且更可取吗？
你是如何分开这三个数据的？它们是jon相同的
物理设备吗？
您可能会发现提问问题 - 您可能无法获得任何答案
但是会以指数级增长的数字产生更多问题。
但是一个答案总是正确的：这取决于......
Jan M. Nelken
DB2 has an explain facility - so if your all access plana are such that there is
index only scan - your strategy to favourize indexes would pay off; in any other
case (you do index scan followed by data access for qualifying rows) you may
want to rethink this strategy rather quickly.
Raw data - are you tuning for read-only access? How the raw data found it;s way
into database (one time load - or hourly updates)?
Results - are you just storing results and never query them? Or while storing
results it is possible and preferable that many users can read them?
How did you separate those three classess of data? Are they jon the same
physical device?
You may find out that asking question as you did - you may not get any answers
but generate more questions in exponentially growing numbers.
But one answer is always correct: It depends..
Jan M. Nelken
分配给缓冲池的内存是全局共享内存的一部分，
在1.75GB限制内。
SORTHEAP是应用程序专用内存和OUTSIDE（没有？）全局的一部分
共享内存。
每个可以使用sortheap的语句都在自己的私有内容中执行。 >
内存。
有了4Gb内存，让我们假设你使用350MB用于Win2K，Comms。，DB2-DAS
和你的实例。 br />
1.75 GB的全局共享内存。总计2.1 GB，并为所有
私人代理商留下1.9。
假设你有20个同时执行的排序，你设置
SORTHEAP在2048页（8MB），然后你会希望SHEAPTHRESH设置为
20x8MB = 160MB或40000页。由于您的代理商有1.9 GB，因此160 MB
不应该打扰太多，甚至可以在必要时为您增加空间。
您的快照应该告诉您高水位线在实例中执行的代理商，然后你就可以使用那个数字代替20代码。
使用带有排序开关的快照来查找哪个appls。
遇到溢出。溢出，而不是后阈值排序，
通常是因为优化器认为它可以排序并适合
导致sortheap，但在执行时，它结束于更多行到
处理并溢出到tempspace。
HTH，Pierre。
-
Pierre Saint-Jacques
SES顾问公司
514-737-4515
" p175" < TD ****** @ hotmail.com> écritdansle message de news：
11**********************@g43g2000cwa.googlegroups。 com ...
Memory assigned to Buffer Pools is part of the Global Shared Memory and
WITHIN the 1.75GB limit.
SORTHEAP is part of Application Private memory and OUTSIDE (WITHOUT?) Global
Shared Memory.
Each statement that can use the sortheap does so within its own private
memory.
With 4Gb of memory, let''s presume you use 350MB for Win2K, Comms., DB2-DAS
and your instance.
1.75 GB of Global Shared Mem. gives a total of 2.1 GB and leaves 1.9 for all
Private Agents.
Let''s assume that you have 20 concurrently executing sorts and you set
SORTHEAP at 2048 pages (8MB), then you''ll want SHEAPTHRESH set at
20x8MB=160MB or 40000 pages. Since you have 1.9 GB for your agents, 160MB
should not bother too much and even gives you room to increase if necessary.
Your snapshots should tell you the high water mark of agents executing in
the instance and you can then use that number instead of 20 as I did.
Use snapshots with the sort switch on to find out which appls. are
encountering overflows. Overflows, as opposed to post threshold sorts,
happen typically because the optimizer thought it could sort and fit the
results in the sortheap, but at execute time, it ended with more rows to
handle and overflowed to tempspace.
HTH, Pierre.
--
Pierre Saint-Jacques
SES Consultants Inc.
514-737-4515
"p175" <td******@hotmail.com> a écrit dans le message de news:
11**********************@g43g2000cwa.googlegroups. com...
大家好，
我正在遵循关于DB2 8.2.2的一般内存分配的一些指导原则在Win2k上的ESE具有4GB物理内存的服务器FP4。
看到一切都是32位且win2k服务器不支持/ 3gb
切换，我们限制内存明智地分配内存到db2syscs.exe大约1.75gb，所以我明白，分配内存有哪些一般的经验法则？
我的过程目前从激活后的1.5gb开始，并且增长到最大使用几天后大约1.68gb。
我有4个缓冲池，每个1到3个表空间和默认值。
表空间特定于Rawdata，结果和索引。我将大部分BP页面分配给索引缓冲池[170000 4k页]，结果[100000]排名第2，rawdata排名最后[50000]。是否更好地为实际数据或索引分配更大的BP？
在哪里优先考虑sortheap和sheapthresh所有这些
并且有更多关键领域应该分配多于配置顾问推荐的配置？是否有百分比指南
可以说共享最大内存30/70 sort / bp？
配置顾问只推荐1428 4k页的分页和
32000 4k页的sheapthresh，这似乎总是在快照中产生排序
溢出。分配更高的BP和更低的排序堆，或更高的sortheap和更低的BP更好。
当我知道
时，它只有1.75gb就会非常令人沮丧如果不是win2k，至少还有1.5我可以分配。
任何帮助将不胜感激。
Hi folks,
I''m after some guidelines on general memory allocations for DB2 8.2.2
ESE on Win2k Server FP4 with 4gb physical memory.
Seeing as everything is 32bit and win2k server does not support /3gb
switch, we are restricted memory wise for allocating memory to
db2syscs.exe to around 1.75gb so I understand, what are the general
rules of thumb for assigning memory ?
My process presently starts at around 1.5gb following activation and
grows to a max of around 1.68gb after being used for a few days.
I have 4 bufferpools, 1 each to 3 tables spaces and the default.
Tablespaces are specific for Rawdata, results and indexes. I assign
most of the BP pages to the index bufferpool [170000 4k pages] with
results [100000] ranked 2nd and rawdata last [50000]. IS it better to
assign a larger BP to the actual data or the indexes ?
Where does one prioritise the sortheap and sheapthresh in all of this
and are there more critical areas that should be assigned more than say
the configuration advisor recommends ?. Are there percentage guidelines
that can say share the max memory 30/70 sort/bp ? etc.
The config advisor is only recommending a sortheap of 1428 4k pages and
sheapthresh of 32000 4k pages which seems to always produce sort
overflows in the snapshots. Is it better to assign a higher BP and
lower sort heap, or higher sortheap and lower BPs.
With only 1.75gb to play with it gets very frustrating when I know
there is at least an additional 1.5 I could assign if not for win2k.
Any help would be greatly appreciated.
这篇关于Win2k内存分配 - BP v Sortheap的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
