DB2’s Self-Tuning Memory Manager (STMM) was introduced in DB2 9.1. The default for new, single-partition databases was ON from the beginning.
What is STMM?
DB2’s Self-Tuning Memory Manager can allocate and change the size of specific memory areas including the total memory used by a DB2 instance on a server. It does this on a frequent basis, with the goal of not only optimally tuning DB2’s memory utilization, but of changing that tuning as database workloads change over the course of time – even over the course of a day or an hour. The general idea is that STMM can tune the database nearly as well as a competent DBA, but on a much more frequent basis.
The memory areas that STMM tunes are:
- DATABASE_MEMORY – Database shared memory size
- LOCKLIST – Maximum storage for lock list
- MAXLOCKS – Maximum percent of lock list before escalation
- PCKCACHESZ – Package cache size
- SHEAPTHRES_SHR – Sort heap threshold for shared sorts
- SORTHEAP – Sort heap size
- Bufferpools
STMM is enabled by setting the above parameters to AUTOMATIC, and setting the DB CFG parameter SELF_TUNING_MEM to ON.
You can manually set any of the above parameters to exclude them from STMM’s dynamic changes, even if STMM is active. Simply set them to a static value. The package cache is one area you might want to consider this for. If your applications do not make appropriate use of parameter markers, or if your SQL is highly variable, then DB2 might allocate too much memory to the package cache when that memory is actually better used in other areas.
You can also set a starting value for any of the STMM memory areas by specifying a specific value in addition to the AUTOMATIC keyword. This is useful for ensuring that when DB2 starts up for the first time that it starts with larger values or to force DB2 to use more memory on an over-sized system. It is also the only way to change the size of the bufferpools on an HADR standby system. The syntax for that looks like this:
db2 "update db cfg for sample using SORTHEAP 300 AUTOMATIC immediate" db2 "alter bufferpool IBMDEFAULTBP immediate size 1050 AUTOMATIC"
What STMM is not
In BLU databases, STMM does not tune the sort memory areas.
STMM does not tune every memory area. There are a large number of memory areas that can be set to “AUTOMATIC” that are not a part of STMM.
A Few Technical Details
STMM essentially wakes up every 60 seconds and does a cost-bennefit analysis of changing memory allocations. In each iteration, it will not increase any memory area by more than 50% or decrease any memory area by more than 25%. You can view logs of each consideration and change in the DIAGPATH, and there is a log parsing tool you can use to analyze these logs.
Potential Pitfalls
STMM works well for well-sized servers, particularly OLTP or mixed workloads where there is a single database on a single instance as the only DB2 instance on the server. Sometimes, particularly for Linux Data Warehousing systems, STMM has trouble with more than one instance on the server. If you have more than one database on a server, you’ll want to set INSTANCE_MEMORY for each instance to a hard number. The sum of all INSTANCE_MEMORY settings should leave ample memory for OS activities. Usually 10-20% of the memory on the server should be reserved for OS functions, though that depends on the server. If the server is not dedicated to DB2, you should also allow room for other applications on the server. If you have multiple databases, you may also want to consider setting DATABASE_MEMORY to fixed values. Ensure on Linux that your setting for vm.swappiness is appropriate – usually about 10. The IBM DB2 Knowledge Center recommended value of 0 is too low, and the Linux default of 60 is too high.
If you have a database with a mixed workload, and you want to prioritize the performance of one workload over another, then STMM is probably not appropriate unless you also make use of the Workload Manager(WLM). Often for prioritizing a certain kind of workload, say OLTP over reporting, you’ll want to set the memory parameters for what is optimal for the workload you want to prioritize. Allowing STMM to choose values may mean that sometimes your less critical workload gets better performance.
You still need to keep track of key performance indicators, and may need to jump in if they’re out of whack. It is also important to keep an eye out as data or transactional volume increases over time to be able to add additional memory as needed.
Summary
In many environments, STMM is a great tool. Using it intelligently can save DB2 DBAs a lot of time.