473,573 Members | 2,558 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Sort heap threshold configuration parameter for 32bits DB2

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

Mar 29 '07 #1
10 5585
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
Mar 30 '07 #2
Ian
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.

Mar 30 '07 #3
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?
Mar 30 '07 #4
On 30 Mar, 08:00, "Woody Ling" <woodyl...@gmai l.comwrote:
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?

Hi Woody,

It'll probably help if you know how much instance-wide private sort
heap you're using currently. The only reliable method that I've found
to do this is to look at the "Private Sort heap high water mark"
statistic in the "db2 get snapshot for dbm" snapshot monitor. If this
is higher than SHEAPTHRES, then you either need to tune your problem
sql, or increase the SHEAPTHRES parameter. The manuals tell you that
you can see if SHEAPTHRES has been exceeded by examining the "Post
threshold sorts" statistic. This may work but I'm on 8.1 fixpack 10
and it produces random results. The most effective way, I've found is
to look through your db2diag.log files and see if you are seeing
messages which look something like "Unable to allocate enough memory
for (private) sort heap. I believe you're right in your understanding
of the DB2 32-bit memory model. I'm currently on 64-bit AIX and have
allocated 3.6Gb to SHEAPTHRES without a problem. Max instance-wide
private sort heap on our database has been as high as 2.4Gb so far.
We're a very busy site.

In order to reliably monitor DB2, you'll need a decent monitoring
tool. We use Speedgain, which captures snapshot monitors and db2pd
from multiple databases every couple of minutes and stores it in DB2
tables. We mine this info and produce web graphs so we can determine
if we've got a problem or not.


Mar 30 '07 #5
Thanks James,

Seems that there is no way to use more that 256MB private sort heap in
32bits DB2 and I am now focusing on intra-parallel=YES options and
want to use database shared memory area for shared sort heap.

As I know that total shared sort heap is equal to "total number of
concurrent sort * sortheap size". I set the sheapthres to 102400 * 4K
= 400MB and sortheap to 10240 * 4K = 40MB so that suppose 10
concurrent applications can have its own sorting area.

Am I right? Or only 10 concurrent sorting agents/subagents are allowed
with this configuration?

Besides, I would like to know if there is any method to monitor the
shared sort heap memory utilization?

Thanks.

Mar 30 '07 #6
On 30 Mar, 12:53, "Woody Ling" <woodyl...@gmai l.comwrote:
Thanks James,

Seems that there is no way to use more that 256MB private sort heap in
32bits DB2 and I am now focusing on intra-parallel=YES options and
want to use database shared memory area for shared sort heap.

As I know that total shared sort heap is equal to "total number of
concurrent sort * sortheap size". I set the sheapthres to 102400 * 4K
= 400MB and sortheap to 10240 * 4K = 40MB so that suppose 10
concurrent applications can have its own sorting area.

Am I right? Or only 10 concurrent sorting agents/subagents are allowed
with this configuration?

Besides, I would like to know if there is any method to monitor the
shared sort heap memory utilization?

Thanks.
Hi Woody,

One thing to bear in mind is setting INTRAPARALLEL to yes doesn't
automatically force DB2 to use the shared sort heap. For example, our
database is a mixed environment. The reason why our SHEAPTHRES is set
so high is because of our concurrent private sort heaps during certain
points of our end of day. (During the day, the concurrent private sort
heaps don't reach anywhere near SHEAPTHRES). We have INTRAPARALLEL set
to yes at all times here, but during the day, we exclusively use
private sorting. During the end of day, some reports use the shared
sort heap and others use private sort heap. Those reports which use
the shared sort heap do so by setting current degree = <degree of
parallelismbefo re executing the query. You can see if your shared
sort heap has neared your SHEAPTHRES at any times since you restarted
your database by looking at the "Shared Sort heap high water mark"
which you'll find from your "get snapshot for db on <database>"
snapshot monitor.
2 other alternatives are to do:-

1. "db2mtrk -i -w" and look for the shsorth value (this gives you the
figure in real memory rather than pages).
2. "db2pd -db <database-mempools" and look under the LogHWM or
PhyHWM columns where the Poolname = 'shsorth'

If you're wondering if you can find total private sort heap using
db2mtrk and db2pd, as far as I'm aware you can't.

You can see if anything's using the shared sort heap currently by
looking at "Total Shared Sort heap allocated" in the snapshot monitor
mentioned above.

You can see what applications are likely to be using shared sort heap
by typing "db2 list applications show detail" and looking for those
which are executing and were "No. of agents" are greater than 0.

Remember that for shared sort heaps, SHEAPTHRES is a hard limit. If
your shared sorts exceed this parameter, they'll fail. I'm guessing
that you will then see something in the db2diag.log if this happens,
but we've not been in that situation. As mentioned previously, you'll
get errors in your db2diag.log if your total private sort heap
allocation exceeds SHEAPTHRES but this is a soft limit, your next
agent requiring a sort won't fail, it will be just allocated less and
less SORTHEAP memory so it's performance will be impacted.

Hopefully, that's answered all your questions.

Mar 30 '07 #7
Ian
Woody Ling wrote:
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.
I think you're missing something. The doc is a little murky here.

The amount of memory allocated for a particular _sort_ (whether
private or shared) is limited by the sortheap parameter. DB2
estimates how much memory will be required for a given sort, and
allocates only as much memory as is necessary. It doesn't automatically
allocate the full SORTHEAP for each sort.

In a warehouse, it is certainly possible to have multiple concurrent
sorts from one query (even with INTRA_PARALLEL = no).

Also, memory in the DBMS does not have to align on segment boundaries.
If your SHEAPTHRES is set to 100,000 (~390 Mb) you will be fine. It's
just that the memory consumers using the DBMS must stay below the 2 Gb
limit (or 1.75Gb, or 1.5Gb, depending various configurations) .

In a warehouse, you're generally balancing SHEAPTHRES against your
bufferpools.
Mar 31 '07 #8
On Mar 31, 11:41 am, Ian <ianb...@mobile audio.comwrote:
Woody Ling wrote:
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.

I think you're missing something. The doc is a little murky here.

The amount of memory allocated for a particular _sort_ (whether
private or shared) is limited by the sortheap parameter. DB2
estimates how much memory will be required for a given sort, and
allocates only as much memory as is necessary. It doesn't automatically
allocate the full SORTHEAP for each sort.

In a warehouse, it is certainly possible to have multiple concurrent
sorts from one query (even with INTRA_PARALLEL = no).

Also, memory in the DBMS does not have to align on segment boundaries.
If your SHEAPTHRES is set to 100,000 (~390 Mb) you will be fine. It's
just that the memory consumers using the DBMS must stay below the 2 Gb
limit (or 1.75Gb, or 1.5Gb, depending various configurations) .

In a warehouse, you're generally balancing SHEAPTHRES against your
bufferpools.


If using (INTRA_PARALLEL = yes), db can use both private and shared
sort and shared sort is using db shared memory in segment 4 to B so
that I can use for example 390MB for sortheap and 500MB for bufferpool
and not hitting the 2G limit.

But if using (INTRA_PARALLEL = no), db use private sort only. The
agent private
memory area is in segment 2 and 256MB only for 32bits.



Mar 31 '07 #9
On Mar 31, 6:11 am, "Woody Ling" <woodyl...@gmai l.comwrote:
On Mar 31, 11:41 am, Ian <ianb...@mobile audio.comwrote:


Woody Ling wrote:
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.
I think you're missing something. The doc is a little murky here.
The amount of memory allocated for a particular _sort_ (whether
private or shared) is limited by the sortheap parameter. DB2
estimates how much memory will be required for a given sort, and
allocates only as much memory as is necessary. It doesn't automatically
allocate the full SORTHEAP for each sort.
In a warehouse, it is certainly possible to have multiple concurrent
sorts from one query (even with INTRA_PARALLEL = no).
Also, memory in the DBMS does not have to align on segment boundaries.
If your SHEAPTHRES is set to 100,000 (~390 Mb) you will be fine. It's
just that the memory consumers using the DBMS must stay below the 2 Gb
limit (or 1.75Gb, or 1.5Gb, depending various configurations) .
In a warehouse, you're generally balancing SHEAPTHRES against your
bufferpools.

If using (INTRA_PARALLEL = yes), db can use both private and shared
sort and shared sort is using db shared memory in segment 4 to B so
that I can use for example 390MB for sortheap and 500MB for bufferpool
and not hitting the 2G limit.

But if using (INTRA_PARALLEL = no), db use private sort only. The
agent private
memory area is in segment 2 and 256MB only for 32bits.- Hide quoted text -

- Show quoted text -
Hi Woody, I believe your understanding of the 32-bit DB2 memory model
to be correct. I've also read in the DB2 manuals that the db2 engine
will allocate up to SORTHEAP amount of memory for each sort and not
the whole SORTHEAP straightaway. All I can say is that when my
SHEAPTHRES was being exceeded, the errors in the db2diag.log suggested
that the DB2 engine had tried to allocate an entire SORTHEAP worth of
memory to the next sort, had failed and then tried to allocate a
SORTHEAP/2 worth of memory etc. My current understanding of the manner
in which DB2 sorts works is by examining and graphing data stored in
my monitoring tool over time.

The other thing that I didn't point out is that when you have
INTRAPARALLEL=y es, then, as you say, both private and shared sort
memory can be allocated. SHEAPTHRES should therefore be set to a value
greater than total_shared_so rt_heap_allocat ed (db snapshot) +
Private_sort_he ap_allocated (dbm snapshot) at a given point in time.
You obviously need to take regular snapshots and store them somewhere,
in order to determine this. Once you've seen when your max sorting is
taking place, the next logical thing would be to find out what
statements are using the sort memory. This conversation could be a
long one.

Mar 31 '07 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

25
3530
by: Rainmaker | last post by:
Hi, Can anyone tell me an efficient algorithm to sort an array of strings? Keep in mind that this array is HUGE and so the algorithm should me efficient enough to deal with it. Thanks
3
10882
by: Raj | last post by:
Hi all, I recieve this error message SQL0954C when i execute an SQL, it is failry complex ... i've seen more complex sql's running with out any error. We are on partitioned database with intraparallelism should i increase the Appheapsz or app_ctrl_heap_sz??...
1
2388
by: Raj | last post by:
How can we increase the size of sortheap for individual sorts? sortheap db parameter is the max mem that can be allocated for private sorts or shared sorts so the parameter which would sorts should be app_ctrl_heap size or appheapsize rather than sort heap?? I read somewhere that e sort heap is allocated from agent private memory .. so where...
0
2735
by: Raj | last post by:
We are on a db2 v8.2 (fix 8) with DPF & intra parllelism. Below are sort related configuration settings ----------------------------------------------------------------------------------------------------------------------------------- Sort heap threshold (4KB) (SHEAPTHRES) = 200000 Sort heap thres for shared sorts (4KB)...
0
13287
by: JosAH | last post by:
Greetings, I was asked to write a Tip Of the Week; so here goes: a lot of topics are started here in this forum (and a lot of other forums too) mentioning a problem about sorting data. Examples of two main problem scenarios are like this: 1) an array containing a first name and another array containing a last name and possibly a...
0
1447
by: kavin | last post by:
Hi im having a list of db2 monitoring metrics but i have some confusions in setting the threshold levels.. can any one give me the threshold levels for the below mensioned mertics Applications connected currently in the database >>>>> Percent applications in the database that are waiting on locks >>>>> Maximum number of associated agents ...
3
3199
by: dotyet | last post by:
Hello Everyone, I have a mixed workload DB2 UDB 8.2 FP 14 Windows x64 database. The database performs as per the expectations. I have just one small question. The sort related parameters are configured as follows: Sort heap threshold (4KB) (SHEAPTHRES) = 50000 Sort list heap (4KB) (SORTHEAP) = 4096...
10
6244
by: ikarus | last post by:
Hello C++ Gurus! I'm comparing sorting algorithm for study goals. I've compared STL std::sort and hand-coded introsort on millions (tens of millions) of integers array sorting. It was tested on random, sorted, and sorted in reverse arrays. In all tests on such a huge arrays std::sort is faster by ~1/4 then my version. So it seems that it...
0
7750
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
0
7674
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
0
7989
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...
0
8039
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
0
6369
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
1
5559
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
0
3708
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
2170
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
1
1271
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.