DB2 LUW Performance: Building Trust with DB2 9 Autonomic Tuning

As with relationships between people, building trust takes time and requires a series of reliable and favorable experiences. In this blog post, we'll consider when and how to best engage STMM, and discuss how to build trust with this new DB2 9 capability.

STMM is DB2's scheme for dynamically adjusting memory configurations according to timely observations about database performance and fluctuations in statement workloads. STMM reacts to statement performance volatility to:

1. Compensate for physical design flaws
2. Optimize, or fine tune, performance for databases having high quality physical designs

As these objectives suggest, STMM provides optimum value when a quality physical design is in place. In the absence of a quality physical design, STMM will embark on a wild goose chase that frequently shifts memory heap sizes to react to statement performance problems.

How is STMM best used? And when?

This is a topic that could take hours to discuss and teach, but we'll include some highlights and general guidelines here.

Data Warehouse Databases

1. Use one large bufferpool for most tables, but perhaps use a second bufferpool for very popular or hot lookup tables.
2. Activate STMM for all 4 major categories: Locks, Package Cache, Sort Memory, and Bufferpools
   • For multi-partition databases, one partition is designated as the "king" and rules over all partitions. Choose the partition with the worst performance attributes or highest volume of activity to be "king" -- the partition with the highest number of Logical Reads per Transaction (BPLRTX) may be ideal.
3. Continue to actively tune the database physical design
4. Meticulously monitor changes made by STMM and the performance consequences (See Building Trust with STMM below).

Transactional (OLTP) Databases

1. Begin with at least four bufferpools (assumes only 1 page size is used for the database)
   • CATALOGBP - A bufferpool for SYSCATSPACE
   • TEMPSPCBP - A bufferpool for TEMPSPACE
   • USERSYNCBP - A bufferpool intended to support random/synchronous I/O. Assign tablespaces to USERSYNCBP that have high Synchronous Read Percentages (SRP).
   • USERASYNCBP - A bufferpool intended to support prefetch/asynchronous I/O. Assign tablespaces to USERASYNCBP that have low Synchronous Read Percentages (SRP). "Low" is defined as 65% or less.
2. Activate STMM for Locks and Package Cache automatic tuning
3. Continue to actively tune the database physical design - it is critically important that the physical design be relatively free of substantial defects. If you have table scans occurring (look for high IREF, low database SRP, high BPLITX or BPLRTX values as "signs"), automatic STMM tuning can alter the bufferpool sizes such that the entire table fits into memory (Read blog post Bufferpool Hit Ratios and Folly ), and this will eat your CPU alive.
4. After you are confident that there are very few, if any, physical design flaws in the database (high SRP, IREF < 10 for the database, IREF < 50 for the great majority of SQL), THEN additionally activate STMM to automatically tune sort and bufferpool memory.
5. Meticulously monitor changes made by STMM and the performance consequences related thereto (See Building Trust with STMM below).
6. After a few hours of fully automated STMM tuning, verify that improved or peak levels of performance have been achieved via "fine tuning", then disable, or shut off, STMM to "lock in" optimized values.
7. On a weekly basis, during a time of moderate-heavy activity, consider re-activating STMM tuning for a few hours to ensure that memory configurations are set to optimal values as per any changes in the nature of the workload or data volume changes. After STMM has had a few hours of fine tuning time, again disable it.
8. Meticulously monitor changes made by STMM and the performance consequences related thereto (See Building Trust with STMM below).

Building trust with STMM

So, now you've taken a leap of faith and surrendered your long time bufferpool, sort heap, locklist, and package cache tuning job over to DB2, and this probably feels a little uncomfortable at first. STMM is going to make changes for you, automatically. We're probably just a few years away from every household having a servant robot to clean our floors and do our dishes too.

Let's go back to the statement "Building trust requires a series of reliable and favorable experiences". Favorable experiences, in terms of DB2, means "performance got better". Reliable means "performance didn't get worse".

In this series of blog posts, so far we've covered a number of key performance metrics that can help us detect when performance problems are present, and also help us identify if performance is improving. Some of these included:

• The Synchronous Read Percentage ( SRP ) - higher is better for OLTP
• Index Read Efficiency ( IREF ) - lower is better for any database
• Bufferpool Logical Reads per Transaction ( BPLRTX ) - indirectly represents the CPU cost of doing logical I/O to the bufferpools

Here's the good news (tongue in cheek). Since STMM can make configuration changes rather rapidly to quickly address statement workloads, you now have the "opportunity" (requirement) to compute SRP, IREF, BPLRTX and other key metrics on a periodic and frequent basis like every 5, 10, or 15 minutes. Store this data in a table with the host, instance, database name, partition, and a timestamp. You also need to track the changes made by STMM and put them into a table with a timestamp, host, instance, database name, and partition.

Changes made by STMM are tracked in files named stmm.#.log in the stmmlog directory. This directory is under the SQLLIB directory for the instance owner in Linux and UNIX, and under the SQLLIB\Instance directory on Windows.

But wait, there's more. Not only can STMM make changes to the database which could influence performance, but DBAs can also drop or create indexes, run utilities, change DB and DBM configuration parameters, and change registry variables too. This requires more advanced monitoring of db2diag.log, the notify log, registry variables, configuration parameters, db2 history, the DB2 catalog, and more, and is beyond the scope of today's blog.

Assuming now that you have two tables - one with performance history of key metrics, and a second that tracks STMM changes, now all you need to do is write a bit of fancy SQL to join the two and create a report. Even better if you can graph the performance trends over time and plot the STMM change events ( and all other relevant change events ) on the same graph. Think of a stock price performance chart - you will see a $ when a dividend was declared, an "S" if the stock split, or maybe a triangle if there was an information news release. Events impact performance -- for stocks and databases. Which takes us to...

The Shameless Marketing Moment

Brother-Panther™ from DBI provides the performance trend charts with correlated change events plotted on the same graph which visually and clearly show the performance consequences of change events. And, not only does Brother-Panther track, record, and plot STMM changes, but other DB & DBM CFG changes, registry variable changes, and index changes are tracked too. DBI has done all the hard work for you. Now, instead of stressing out over trusting STMM, you can enable it with confidence. As a bonus, these performance trend charts can help you identify and "lock in" the optimum configuration values for maximum performance.

Other vendors might tell you that they have performance trend charts. Run away as fast as you can - a performance trend chart that doesn't help you understand the sources of changes in performance is simply eye candy.

Just for Fun

I'm enjoying the IDUG Europe conference in Athens Greece. I've attended several very good presentations by great presenters. IDUG has a new Premier Membership level that allows Premier members to view conference presentations online. If you attended IDUG North America in San Jose, you may already have Premier member status at no extra charge. Check out the IDUG web site at www.idug.org for details and maximize the value of your IDUG relationship.

Until next time,
Cheers,

Scott Hayes
President & CEO, DBI
IBM GOLD Consultant
www.Database-Performance.info
www.Database-Auditing.info