Sort heap threshold configuration parameter for 32bits DB2

[Woody Ling]

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

[Phil Sherman]

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

[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.

[Woody Ling]

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?

[james_dey]

On 30 Mar, 08:00, "Woody Ling" <[e-mail]:

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.

[Woody Ling]

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.

[james_dey]

On 30 Mar, 12:53, "Woody Ling" <[e-mail]:

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
parallelismbefore 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.

[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.

[Woody Ling]

On Mar 31, 11:41 am, Ian <[e-mail]:

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.

[james_dey]

On Mar 31, 6:11 am, "Woody Ling" <[e-mail]:

On Mar 31, 11:41 am, Ian <[e-mail]:

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=yes, 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_sort_heap_allocated (db snapshot) +
Private_sort_heap_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.

[Woody Ling]

On Mar 31, 11:28 pm, [e-mail] wrote:

On Mar 31, 6:11 am, "Woody Ling" <[e-mail]:

On Mar 31, 11:41 am, Ian <[e-mail]:

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=yes, 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_sort_heap_allocated (db snapshot) +
Private_sort_heap_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.- Hide quoted text -

- Show quoted text -

Yes, Thanks Phil, lan and James. ^_^