Any gotchas for STMM
in [DB2]
|
From: Jean-Marc Blaise on 14 Jun 2010 15:00 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(a)nowhere.com> wrote in message news:hv5d9n$v60$1(a)news.eternal-september.org... > "Richard" <rsl101(a)gmail.com> wrote in message > news:065314d9-0315-48bc-80a8-0f130f792ee5(a)37g2000vbj.googlegroups.com... >> Thanks for your input. >> How about the AUTOMATIC database memory - the close cousin to STMM, >> how do they fair in real production ? >> >> RL > > Automatic settings are generally recommended, and I have seen no problems > with it. >
From: Mark A on 15 Jun 2010 01:00 "ajstorm(a)ca.ibm.com" <ajstorm(a)gmail.com> wrote in message news:7b8510a1-1bdc-4d1f-8359-d7f41356d998(a)j4g2000yqh.googlegroups.com... > 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 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?
From: Mark A on 15 Jun 2010 01:02 "Jean-Marc Blaise" <jmblaise(a)hotmail.com> wrote in message news:4c167c5e$0$24821$426a74cc(a)news.free.fr... >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 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.
From: ajstorm on 15 Jun 2010 10:20 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. > 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). If you're willing, I'd be interested in learning more about these problems. You can send me these details via email. > 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. 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. > 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. I'd be interested to hear more about these situations, perhaps over email. > 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. 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. > 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? 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. > 6. The DPF Balanced Warehouse Configurations (DPF) do not use STMM and > specifically advise against it in the Balanced Warehouse manuals. 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/infocenter/db2luw/v9r7/topic/com.ibm.db2.luw.admin.perf.doc/doc/c0023815.html > 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. That is unfortunate because that's not the official IBM position. > 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). 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. > 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. Mark, I've been personally involved in almost all of the STMM APARs to date. > 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. 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. > Also, I would like to know why those DB2 certification tests have such nasty > and complicated questions about STMM? 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
From: Richard on 16 Jun 2010 00:45
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 |