Win2k Memory Allocation - BP v Sortheap

[p175]

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.

[Phil Sherman]

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:

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.

[Jan M. Nelken]

p175 wrote:

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

[Pierre Saint-Jacques]

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

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.

[Phil Sherman]

Pierre Saint-Jacques wrote:

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. What did you mean to say here? (overflows vs post threshold sorts)

Overflows occur when the volume of data to be sorted is larger than fits
into the workspace (sortheap) allocated for the sort to use. I've never
heard of the optimizer choosing not to sort because the data is larger
than fits into sortheap.

Post threshold sorts occur when the total memory allocated to sortheaps
(plural) exceeds the sheapthresh configuration parameter. When this
happens, the sort that is attempting to allocate space for its workarea
doesn't fail; it just allocates a smaller sortheap and runs. If the data
fits into the smaller sortheap, there won't even be an overflow.

HTH, Pierre.

Phil Sherman

[p175]

Pierre,

Thats all well and good I'm afraid, unfortunately however win2k server
will not allow such a configuration. The OS will impose a hard limit of
around 1.75gb on the single process db2syscs.exe and will crash the
instance if it goes above such.

Therefore the entire pool of global heaps, BPs, instance memory, sorts
etc the whole thing must reside in a maximum of 1.75gb of physical
memory. Because the server has 4gb, I 'could' presumably run two
instances, however in my single partition system I am very restricted
in this regard.

I guess the question is how to configure the system the best possible
way to fit within 1.75gb

My environment does not support online transactions, everything is run
via batch processes which perform refresh various data on a daily
basis, then we run data mining algorithms over the data to produce the
results. Several SQL SP's cross reference the results back to the raw
data to produce statistics the algorithms do not provide. The remainder
of processing then concertrates on the results to profile, summarize
and provide outcomes based on historical performance.

Queries are then run on the results there after. So no OLTP is
necessary and I am at present the only user running SP's consecutively
in order. Avg applicatoons therefore are singular [ie: 1].

[Pierre Saint-Jacques]

See below ######

--
Pierre Saint-Jacques
SES Consultants Inc.
514-737-4515
"Phil Sherman" <ps******@ameritech.net> a écrit dans le message de news:
cl***************@newssvr22.news.prodigy.net...

Pierre Saint-Jacques wrote:

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. What did you mean to say here? (overflows vs post threshold sorts)

Overflows occur when the volume of data to be sorted is larger than fits
into the workspace (sortheap) allocated for the sort to use. I've never
heard of the optimizer choosing not to sort because the data is larger
than fits into sortheap.

How does one explain that EXPLAIN will indicate sort output to a temp.
table?
In DB"EXPLN, it will show ".. sort into temporary table t1...." and in
Visual Explain, it shows a SORT operator followed by a Table Scan operator
and the details show table name as "sysibm.dt" . How did the optomizer
figure out to do it that way if it did not think that the selectivity *
rowtobesorted size would not fit in the defined sortheap?
Post threshold sorts occur when the total memory allocated to sortheaps
(plural) exceeds the sheapthresh configuration parameter. When this
happens, the sort that is attempting to allocate space for its workarea
doesn't fail; it just allocates a smaller sortheap and runs. If the data
fits into the smaller sortheap, there won't even be an overflow.
Post threshold sorts we agree on. Just as an add on:
Someone from the lab told me two weeks ago that as sheapthresh is hit the
next sort gets half of the defined sortheap, and the next one aftwer that
gets half of that allocation, like: sortheap=4000p, 1st postthreshold gets
2000p, next gets 1000p and so on until all sortheaps are back again under
the sheapthresh limit.
I have scoured my docs but I can't find that info.

HTH, Pierre.

Phil Sherman

[Pierre Saint-Jacques]

If that is the case, and I did not know that Win2k would treat all under the
1.75 GB. Makes sense though when one stops to think about it.
If everything is serialized as you say with a single application doing the
sort work, I'm not sure I understand how you get multiple sorts going over
sheapthresh?
Be that as it may, since your sorts overflow to temp space, would it make
sense to use some of the leftover 4GB by altering the tempspace buffer pool
to use extended storage, define the parm. values in db cfg and at least let
the overflows go to memory as opposed to hitting actual disk space. Just a
thought.
Regards, Pierre.

--
Pierre Saint-Jacques
SES Consultants Inc.
514-737-4515
"p175" <td******@hotmail.com> a écrit dans le message de news:
11**********************@g47g2000cwa.googlegroups. com...

Pierre,

Thats all well and good I'm afraid, unfortunately however win2k server
will not allow such a configuration. The OS will impose a hard limit of
around 1.75gb on the single process db2syscs.exe and will crash the
instance if it goes above such.

Therefore the entire pool of global heaps, BPs, instance memory, sorts
etc the whole thing must reside in a maximum of 1.75gb of physical
memory. Because the server has 4gb, I 'could' presumably run two
instances, however in my single partition system I am very restricted
in this regard.

I guess the question is how to configure the system the best possible
way to fit within 1.75gb

My environment does not support online transactions, everything is run
via batch processes which perform refresh various data on a daily
basis, then we run data mining algorithms over the data to produce the
results. Several SQL SP's cross reference the results back to the raw
data to produce statistics the algorithms do not provide. The remainder
of processing then concertrates on the results to profile, summarize
and provide outcomes based on historical performance.

Queries are then run on the results there after. So no OLTP is
necessary and I am at present the only user running SP's consecutively
in order. Avg applicatoons therefore are singular [ie: 1].

[Mark Yudkin]

Win2K Server DOES support /3GB.

Also, you can use "extended storage" - storage above 4GB, although you may
want to go to W2K Advanced Server to really be able to use lots of it (Intel
call this "AWE - Address Windowing Extensions", if you need to look it up).
The DB2 documentation has details on how to configure and use extended
memory for buffer pools. Of course you'll need to install more memory in
your server (with 4GB RAM you have no extended memory).

"p175" <td******@hotmail.com> wrote in message
news:11**********************@g43g2000cwa.googlegr oups.com...

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]

I'm afraid your mistaken, win2k server will allow you to set the switch
but it will NOT allow physically 3gb of memory. According to the
windows website the /3gb switch is enabled but not active for testing
and environment purposes only. In other words it doesn't do anything.

[Mark Yudkin]

From the documentation:

Comments
On Windows, by default, the lower 2 GB are reserved for user-mode programs
and the upper 2 GB are reserved for kernel-mode programs. You can use this
parameter to test the performance of your driver when it is running in a 1
GB kernel.

The /userva subparameter is designed for computers that need more than 2 GB
but less than 3 GB of user-mode address space, particularly those that are
running memory-intensive user-mode programs. When used without the /3GB
parameter, /userva is ignored.

The /3GB and /userva parameters are valid only on boot entries for 32-bit
versions of Windows on computers with x86 or AMD64 processors.

This parameter is fully functional on Microsoft Windows NT Server 4.0,
Enterprise Edition with Service Pack 3, Microsoft Windows 2000 Advanced
Server, Microsoft Windows 2000 Datacenter Server, Windows XP, and Windows
Server 2003.

On other versions of Windows NT and Windows 2000, this parameter restricts
the kernel to addresses above the 3 GB boundary. However, user-mode
applications cannot access more than 2 GB of address space.

---

Presumably you're using using the entry level Windows 200 server, not the
Advanced Server or above. Since putting 4GB physical memory into an entry
server is essentially pointless to start with, I assumed you had Advanced
Server.

"p175" <td******@hotmail.com> wrote in message
news:11**********************@o13g2000cwo.googlegr oups.com...

I'm afraid your mistaken, win2k server will allow you to set the switch
but it will NOT allow physically 3gb of memory. According to the
windows website the /3gb switch is enabled but not active for testing
and environment purposes only. In other words it doesn't do anything.