The sheer number of DB2’s memory-related parameters can hit like a tidal wave.  Bufferpools, sort heaps, lock lists, package/statement caches, and various other heaps can all harm performance if undersized, and can waste or overcommit memory if oversized.  Further, many of the configuration parameters are interrelated; for example, increase logbufsz and you may also have to increase dbheap to accomodate it.  DBAs and performance tuners can feel overwhelmed by the number of knobs one must turn in each database.  And developers can feel overburdened when providing scripts and configuration tools to right-size their databases for each customer’s unique volume requirements.

Fortunately the latest (9.x) versions of DB2 LUW provide the Self-Tuning Memory Manager (STMM).  This feature significantly simplifies memory management in DB2, letting the database automatically size memory areas based on activity and needs.  For most parameters, you can also specify an optional initial size to avoid the overhead of progressively stretching memory after startup.

To turn on STMM for a database, specify:

update db cfg using self_tuning_mem on

You’ll likely also want database_memory set to automatic:

update db cfg using database_memory automatic

You can fix-size or initial-size database_memory, or use the computed option for back-compatibility.  The publib DB2 documentation has more information.

At this point, you can auto-size many of the memory areas.  For example, for dynamic lock list sizing, specify:

update db cfg using maxlocks automatic
update db cfg using locklist automatic

Depending on DB2 version, you can configure any of the following this way: dbheap, pckcachesz, sortheap, stmtheap, applheapsz, appl_memory, stat_heap_sz, dft_prefetch_sz, num_iocleaners, num_ioservers, maxappls, and avg_appls.   Anyone who has tried to package or build scripts that set these beyond the old (painfully too low) defaults can appreciate the benefits of “going automatic.”

Perhaps the greatest benefit comes when auto-sizing bufferpools.  For that, specify:

alter bufferpool <poolname> size automatic

To specify an initial (startup) size (in pages), place it before the automatic keyword.

In my experience so far, “going automatic” has worked swimmingly, with one exception: sort heaps.  OLTP systems can, at times, experience heavy loads of small sorts or sort-like activity (hash joins, grpby’s, etc.).  It’s not uncommon for too many of these to overflow (spill), or for a sub-optimal access plan to arise, even when more than enough memory is available for sorts.  This is partly because auto-sizing the sort heap requires some extra work and partly because the optimizer can’t predict future sizes.  More on this in a future post.