为32位DB2排序堆阈值配置参数 [英] Sort heap threshold configuration parameter for 32bits DB2

问题描述

在32位DB2环境中，对于以下情况，将sheapthres设置为大于256MB是否有意义..


1.并行内部是ON

2.内部并行关闭

解决方案

我会更感兴趣的原因你需要那么大的价值

sheapthres。 32位环境限制你的内存少于4g，

其中许多是2g。 Sheapthres控制排序空间的硬限制

用于共享排序和软限制（允许微小的额外分配

一旦命中）用于私人排序。

大多数数据库足够大，不可能分配足够的内存来进行排序，以完全消除排序溢出。用于排序工作空间的非常大的内存分配将减少可用的内存数据库缓冲池的b
$ b通常会超过任何

排序性能提升。


Sheapthres与sortheap结合使用来管理并发排序使用的
存储量。 Sheapthres是一个实例范围的

参数，由

实例控制的所有开放数据库共享。


Philip谢尔曼

Woody Ling写道：

在32位DB2环境中，设置sheapthres更大是否有意义

以下情况不超过256MB ..


1.内部并行开启

2.内部并行关闭

Woody Ling写道：

在32位DB2环境中，设置sheapthres更大是否有意义
$ b以下情况$ b超过256MB ..


1.内部并行开启

2.内部并行关闭

当然。这取决于你设置了什么SORTHEAP，你做了多少并发

排序，你是否在一个实例中有多个数据库...


虽然在数据仓库中查看大量分配

的情况可能更为常见（我认为这就是为什么你要问的问题

INTRA_PARALLEL也是如此），我甚至已经看到过制作sheapthres的情况

大是OLTP环境中的一项要求，在一个系统上，同时连接6000个用户接近
，做200多笔交易

每秒。

其实我现在正在使用带有8G内存的IBM 6路AIX机箱。但是，
数据库是32位并且有3个逻辑节点，因此很难设计堆大小以适应每个节点的2G可寻址空间。


当我关闭内部并行选项时，数据库仅使用私有排序

，每个节点的所有代理的排序堆总大小为

等于分拣代理数量* sortheap这也受到DBM参数sheapthres的限制。并在代理私有中分配

内存区域。

>从DB2 UDB内存模型文档中，我发现代理私有

在32位环境中，内存区域仅为256MB（在段2中），因此

我想知道将sheapthres设置为大于没有意义

代理私有内存区域。


当我打开内部并行选项时，数据库可能会使用共享排序

，以便在数据库共享内存中分配排序堆。然而

通过查看快照我发现它总是等于零。


因为它是一个带有大约200G数据的OLAP数据库，我认为256MB sheapthres

还不够大，因为大约有10个应用程序在

的同时运行。


有什么想法？

In 32 bits DB2 environment, is it meaningful to set sheapthres larger
than 256MB for the following case..

1. Intra-parallel is ON
2. Intra-parallel is OFF

解决方案

I''d be more interested in why you need that large a value for
sheapthres. 32 bit environments limit you to less than 4g of memory,
many of them to 2g. Sheapthres controls the hard limit for sort space
for shared sorts and a soft limit (allow tiny additional allocations
once hit) for private sorts.

Most databases are large enough that it''s impossible to allocate enough
memory for sorting to totally eliminate sort overflows. A very large
memory allocation for sort work space will decrease the memory available
for the database buffer pools which usually more than offsets any
sorting performance gains.

Sheapthres is used in conjunction with sortheap to manage the amount of
storage used by concurrent sorts. Sheapthres is an instance-wide
parameter that is shared between all of the open databases controlled by
the instance.

Philip Sherman
Woody Ling wrote:

In 32 bits DB2 environment, is it meaningful to set sheapthres larger
than 256MB for the following case..

1. Intra-parallel is ON
2. Intra-parallel is OFF

Woody Ling wrote:

In 32 bits DB2 environment, is it meaningful to set sheapthres larger
than 256MB for the following case..

1. Intra-parallel is ON
2. Intra-parallel is OFF

Sure. It depends on what you''ve set SORTHEAP to, how many concurrent
sorts you do, whether you have multiple databases in an instance...

Although it''s probably more common to see large allocations for sorting
in a data warehouse (and I assume that''s why you''re asking about
INTRA_PARALLEL, too), I''ve even seen cases where making sheapthres
large was a requirement in an OLTP environment, on a system that had
close to 6000 users connected simultaneously, doing 200+ transactions
per second.

Actually I am now using a IBM 6-ways AIX box with 8G memory. However
the DB is 32bits and has 3 logical nodes so that it is hard to design
the heap size to fit into the 2G addressable space for each node.

When I turn off the intra-parallel options, the DB use private sort
only and the total size of sort heap of all agents for each node is
equal to "number of sorting agents * sortheap" which is also limited
by the DBM parameter "sheapthres" and allocated in the agent private
memory area.

>From the DB2 UDB memory model document, I found that the agent private

memory area is 256MB only (in segment 2) in 32bits environment so that
I wonder it is meaningless to set the sheapthres larger that the
"agent private memory area".

When I turn on the intra-parallel options, the DB may use shared sort
so that the sort heap is allocated in database shared memory. However
I found that it is always equal to zero by looking into the snapshot.

Since it is a OLAP DB with about 200G data, I think 256MB sheapthres
is not large enough because there are about 10 applications running in
the same time.

Any idea?

这篇关于为32位DB2排序堆阈值配置参数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！