Has anyone experienced an instance crash related to the STMM?

Thank you.

On 17.08.10 22:43 , tg wrote:
There has been quite a heated discussion about this topic a while ago.

You might want to check out the thread 'Any gotchas for STMM (11.06.10 22:37)'.

Btw, people would probably answer sooner, if you added information about the
OS and DB2 level. What do you expect when asking someone 'What's on TV?'. I
couldn't answer the question until you tell me which channel and time you are
looking for.

--
Helmut K. C. Tessarek
DB2 Performance and Development

/*
Thou shalt not follow the NULL pointer for chaos and madness
await thee at its end.
*/

On Aug 17, 8:08*pm, Helmut Tessarek <tessa... (AT) evermeet (DOT) cx> wrote:
Agree with Helmut...depending on DB2 level..there are a ton of fixes
for STMM. We have experienced out of resource type crashes due to
STMM and had to re-tune our systems when we went from V8.2 to V9.5.
We are currently at fixpak 4 and are planning an upgrade for the new
fixpack but so far we are stable with our settings.

Many do not realize that STMM (or the part of STMM that causes the most
trouble) only controls a small number of parameters. They are located
together in the DB CFG:

Self tuning memory (SELF_TUNING_MEM) = ON
Size of database shared memory (4KB) (DATABASE_MEMORY) = COMPUTED
Database memory threshold (DB_MEM_THRESH) = 10
Max storage for lock list (4KB) (LOCKLIST) = 8192
Percent. of lock lists per application (MAXLOCKS) = 30
Package cache size (4KB) (PCKCACHESZ) = 8192
Sort heap thres for shared sorts (4KB) (SHEAPTHRES_SHR) = 50000
Sort list heap (4KB) (SORTHEAP) = 10000

If you hard-code the above parms (and also hard code the bufferpool sizes
instead of -2), then even though SELF_TUNING_MEM says ON, it will be
"Inactive". As a starting point, the sum of all bufferpools for all
databases on the server can be set to be about 50% (total) of the real
memory on the machine. But don't make the bufferpools larger than all the
underlying tablespaces assigned to that bufferpool, since that is just
wasting memory.

The above numbers are a good starting point for most databases, and can be
increased or decreased as necessary. All other parms that say AUTOMATIC can
be left as AUTOMATIC, and it is much less likely that you will have any more
memory problems. The sum total of the above settings (not counting
bufferpools) is about 256 MB of memory.

To tune the above parms do a snapshot for database:

If the package cache is frequently showing overflow, then increase package
cache setting.
If sorts are overflowing frequently, then increase the 2 sort parms
proportionately.

Check you bufferpool hit ratios for data and indexes, and adjust the
bufferpool sizes (if you have more memory) to keep it as high as possible.

On Aug 17, 11:08*pm, Helmut Tessarek <tessa... (AT) evermeet (DOT) cx> wrote:
Thank you all very much for your responses. You are also right.. my
apologies for not providing more info. The crash occurred on v9.1
fixpak 6a with 3 instances on an AIX server. We have been very stable
for 1 year so we were very shocked when this happened, especially
since it took 5 minutes for the server to be starved of memory. We
are migrating to 9.5 fixpak 5 and setting more parameters to auto. So
I am very concerned that STMM may not be as stable as IBM has led us
to believe.

IBM sees itself in a Titanic struggle right now for market share with Oracle
and MS SQL Server. One of the supposed knocks on DB2 is the difficulty
configuring it, and the fact that it runs poorly out of the box (with all
the defaults such as Bufferpool set to 1000 pages, etc). IBM sees STMM as
crucial to solving this "problem" and they are determined to claim that they
have lowered to "total cost" of using DB2.

So it is safe to say that STMM is designed to sell more DB2, not necessarily
to make current customers happy, since they have pushed it out the door and
made it the default even before they got all the bugs out. It's a little
hard to argue with that philosophy since that is basically how Oracle does
things. On the other hand, very few Oracle customers will even consider the
latest release, unlike most DB2 customers.

We have good experience with STMM especially in warehousing workloads.
If some STMM settings gives you trouble, just set it to fixed value.

On 18 aug, 22:48, "Mark A" <no... (AT) nowhere (DOT) com> wrote:
Having big pools and very good hit ratio is not always a guarantee for
performance improvements. It depends. It can even degrade performance.

Suppose a system, AIX, and storage file system based JFS2 (no raw
devices). For the 9.5 release DB2 defaults are DB2_MMAP_WRITE=OFF
DB2_MMAP_READ=OFF, a user can always enforce it by using db2set (as in
the past the default was ON).

As result of these settings, DB2 when having a non-hit in his cache,
will go to AIX and ask for the data. If AIX does not have the data in
it's memory, it will go to persistent storage. Before knowing if the
data is in his cache, DB2 has to search for it. As AIX has to search
when the kernel receives the request from DB2 for the in case of DB2
cache miss. DB2 search is application level and is less performant
than AIX JFS2 search, tuned at OS and hardware memory search level. So
if DB2 has a big cache, it can take more time to search the data in
it's cache than to search in a small DB2 cache and let AIX search in a
big OS managed memory cache (and when found in memory or persistentent
storage, taken account of memory transfer to the DB2 cache). So the
more systems (and memory transfer performance) and memory size
increase, the less DB2 cache and cache hit becomes critical, and
having very big DB2 caches can indeed impact negatively performance.

Bernard Dhooghe