Folks,

I wonder what the real-world results for turning STMM on, along with
many of the AUTOMATIC memory config parameters that's been introduced
since V9 ? We are turning STMM on in production. There's never enough
traffic or volume of activities in development to get good estimates
and find gotchas. Your experience with this in production settings and
recommendations will get us far.

Thank you in advance.
RL

"Richard" <rsl101 (AT) gmail (DOT) com> wrote

Only a donkey would use STMM.

IBM introduced STMM as a way to convince customer executives that DB2 needs
less expertise than other databases because it is self-tuning (or at least a
lot less expertise than needed by previous versions of DB2). This is
supposed to lower the cost of ownership. The reality is 100% opposite of the
claims.

If you want to use STMM, I would recommend that you use 9.7.2. Doesn't work
well in 9.5 and can crash your system, although I think 9.5.5 may be
noticeably better than previous fixpacks. Personally, I think STMM is
worthless anyway and I recommend that it be turned off (default is on for
new databases). If you do use STMM, and end up in a mental institution,
don't blame me.

One other thing. If you intend to take any DB2 certification exams, they
will ask you a bunch of questions about STMM that not even the DB2 STMM
developers would know the answers to.

Thanks for your input.
How about the AUTOMATIC database memory - the close cousin to STMM,
how do they fair in real production ?

RL

"Richard" <rsl101 (AT) gmail (DOT) com> wrote

Automatic settings are generally recommended, and I have seen no problems
with it.

On Jun 14, 10:11*am, "Mark A" <no... (AT) nowhere (DOT) com> wrote:
In general, I'd say that STMM is recommended both in test and in
production. We have thousands of customers happily running STMM in
production and have hit only a hand-full of issues. If you don't
believe me, here are some of our customer quotes around STMM:

"The self tuning memory manager (STMM) technology we now consider a
"must have". We don't let one database go live without this feature
enabled. STMM saves us 1 month of manual adjustments to the memory
model and the fact that it works across instances really helps us
consolidate databases and get the most out of our servers." - Canadian
Department of National Defence

"STMM in DB2 is rock solid, no matter how many transactions our
customers throw at DB2 it just auto configures and hums along." - TMW
Systems

"We are very impressed with the performance improvements achieved with
the DB2 Self-Tuning Memory Manager (STMM). Reports that took two to
three minutes to extract before are now extracted in less than 10
seconds!" - Automatos

Mark, it might help if you outline specific issues that you've hit
with STMM. The vagueness of your response leaves more questions than
answers. For instance, why do you say that STMM in DB2 9.5 can "crash
your system"?

Thanks,
Adam

I have many customers using STMM in France in DB2 9.5 (from FP3 to FP5) and
we have not problem with it.
We have only deactivated because of 1 application the tuning of LOCKLIST,
that's all.

Regards,

JM

"Mark A" <noone (AT) nowhere (DOT) com> wrote

"ajstorm (AT) ca (DOT) ibm.com" <ajstorm (AT) gmail (DOT) com> wrote


1. I cannot disclose the specific extent of the problems I have seen in our
applications due to confidentiality issues (some of which involves IBM).

2. If someone went from 2-3 minutes to 10 seconds on a query (I assume this
is a data warehouse) then they must have used the DB2 defaults previously,
including the pitifully small default bufferpools size of 1000 pages, or
pitifully small sort heaps. Just because the DB2 database defaults are
ridiculously small (most of them in 9.5 have not been changes since OS/2
Database Manager when the largest PC's had 16 MB of main memory), does not
mean STMM works well. Any half-competent DBA could change the defaults to
run quite well without STMM. Unfortunately, not all DBA's are competent, or
more often than not many managers don't think they even need DBA's.

3. I will admit that the problem may be worse with Linux, where DB2 STMM
gives up memory it doesn't need at the moment, but can't get it back when it
asks for it back a few seconds later. We experienced this big time with
locklist and with sort heaps (with disastrous results). However, I have also
heard of others complain about AIX also. I don't know about DB2 on Windows.

4. There have been known problems with multiple instances on the same server
with STMM, and these problems are irrefutable (although may have been fixed
with 9.5.5 or 9.7.x). Most likely STMM works better with one instance and
one database per server. That is not the norm in most enterprises.

5. The whole idea that STMM can manage bufferpools for you completely takes
away the advantage of having multiple bufferpools, since DB2 treats them all
the same. In other words if you have two bufferpools, how does STMM know
that you always want a 100% hit ratio on one of them, but can live with less
than 100% on the other?

6. The DPF Balanced Warehouse Configurations (DPF) do not use STMM and
specifically advise against it in the Balanced Warehouse manuals.

7. Current IBM DB2 Instructors have admitted that STMM does not work well in
9.5 and have recommended that you wait until 9.7. I don't want to mention
their names for obvious reasons.

8. Since I am a former IBM employee and former DB2 instructor when I worked
for IBM, I have some idea what I am talking about.

Yes, STMM may work better than the defaults, but that is not saying much.
And STMM can definitely crash or hang your system. Just ask any IBM support
person. I did admit that it works better in 9.7, but to be honest I fail to
see the benefit, especially for bufferpools (as I mention above when one
wants more than one bufferpool, each with different priorities).

One theoretical advantage of STMM is that is saves memory by giving it up
when not needed, so other parts of DB2 can use it. In practice, memory is
cheap and plentiful, and aside from bufferpools, the amounts of memory we
are talking about are not worth being stingy about. So if one just allocated
liberal amounts of memory to locklist, sortheaps, and the few other heaps
controlled by STMM, then one would almost always be better off (and also you
won't have your logs filled up with notices of STMM adjustments ad nauseam).

Adam, I would suggest that if want some education on this matter, that you
read each and every APAR that has been fixed with each 9.5 fixpack. You can
get the list at the fixpack download site.

In summary, the DB2 default memory allocations were atrocious, and poorly
documented as to how to rationally configure them. In response, we get a
totally automated overkill that doesn't yet work as advertised. Like most
thinks happening in Toronto these days, it is designed to sell DB2 to
executives who don't know any better, not to really help out DBA's.

Also, I would like to know why those DB2 certification tests have such nasty
and complicated questions about STMM?

"Jean-Marc Blaise" <jmblaise (AT) hotmail (DOT) com> wrote

Can you explain why it does not work with LOCKLIST for that particular
customer? Some of us (in the real world) don't have the luxury of things
that may, or may not work. We need things that work 100% of the time,
everytime.

Mark,

Thanks for the response. I can respond to some of your points here,
but others we should probably discuss in more detail through email as
they don't seem generally applicable to a broad audience.

If you're willing, I'd be interested in learning more about these
problems. You can send me these details via email.

You make a valid point, but it's only partially correct. I agree that
DB2's default configuration will not give you optimal performance.
That being said, there are two things that you're not considering.
First of all, DB2 defaults have changed substantially over the past 10
years. For example, it's now the case that the DB2 Configuration
Advisor will run automatically as part of database creation. After
the Configuration Advisor completes, it will have set 36 of the
database configuration parameters (including the size of the default
buffer pool) based on the machine specifications. The result is a
"default" configuration that is tailored to the environment in which
the database will be run.

The second thing that you may not be considering is that even
competent DBAs do not always have the time to optimally configure each
and every database created at their shop. I know many DBAs that will
devote hours and hours to optimally configure some of their databases
and yet, for their test environments, they're happy to let STMM do the
heavy lifting. That being said, some of these same DBAs have enabled
STMM on their most critical databases and found that its tuning
outperformed their hand tuned configuration.

I'd be interested to hear more about these situations, perhaps over
email.

I'd be interested in hearing more about the problems you've hit over
email. While there have been some issues with multiple instances that
we've fixed, they only affected a hand-full of customers.

I think you may not completely understand how STMM works with multiple
buffer pools. STMM works to optimize the configuration of multiple
buffer pools not by trying to increase their hit rates, but instead by
determining a configuration that will lead to the minimum possible
amount of time spent retrieving pages from disk. With this model, it
is valuable to treat all of the buffer pools the "same" since each of
them is caching pages in an attempt to prevent disk reads/writes. If
all you care about is overall database performance, the tuning that
STMM provides for multiple bufferpools is extremely effective.


That is correct. STMM is not recommended in the Balanced Warehouse
because in DPF environments, STMM must be used only on partitions that
have similar memory requirements. That being said, I know of several
customers who are happily running STMM in DPF after exercising the
necessary precautions. You can read more about the precautions here:

http://publib.boulder.ibm.com/infoce.../c0023815.html

That is unfortunate because that's not the official IBM position.

I would strongly disagree with your argument. While memory may be
cheap and plentiful in your shop, most of our customers are
consolidating servers to the point where many databases are all
fighting for the same small amount of memory. It is in these
environments where STMM can be the most effective at managing the
needs of the databases, especially if their peak workload requirements
are at different times of the day. In general, I think you're greatly
oversimplifying the configuration dilemma faced by a DBA in the
absence of tools like STMM.

Mark, I've been personally involved in almost all of the STMM APARs to
date.

There are a great many DBAs (one of which has already posted to this
thread) who are quite happy with STMM. I think it's a gross mis-
statement of the facts to say that STMM was designed to sell DB2 to
executives as opposed to helping out DBAs.

That's a good question, and something I can look into. Unfortunately,
I haven't taken a DB2 certification exam in a number of years so I
don't know the answer off-hand.

Again, I welcome feedback about STMM and am willing to help you
through any issues you may be having. Please follow-up via email.

Thanks,
Adam

Thanks for all the expert discussion on this important subject.

Many of Mark's points are valid at least in the understanding what are
some potential problems. I am hoping to get more supposition from
other dba's who have experienced STMM in production.

Please give us your feedback. What was your system like before STMM,
what kind of tuning you did to it, and how STMM improve or not improve
that status quo.

Thanks, Richard