Understanding the advantages of DB2 9 autonomic computing features

Learn how the revolutionary capabilities of DB2 9 can change your business

The self tuning memory manager (STMM) is a revolutionary memory tuning feature that was first introduced in IBM ® DB2 ® 9. The STMM eases the task of memory configuration by automatically setting optimal values for most memory configuration parameters, including buffer pools, package cache, locking memory, sort heap, and total database shared memory. When STMM is enabled, the memory tuner dynamically distributes the available memory among the various memory consumers. This article explains the function of the STMM, teaches you to enable the feature, and also discusses how STMM can bring real benefits to your business environment.

Mohankumar Saraswatipura (mohankumarsp@gmail.com), DB2 Database Specialist, Lehman Brothers, Inc

Mohan works as a DB2 Database Specialist, focusing on DB2 Q-Replication, DB2 performance, and DB2 administration. While with the IBM Software Lab, India, he worked with the High Performance On Demand Solutions (HiPODS) team to provide the performance solutions to clients. Earlier he worked with DB2 Product Development team, India. Mohan is an IBM certified DB2 Advanced Database Administrator, DB2 Application Developer and DB2 Problem Determination Master.



Swetha Prasad (swetha.prasad@in.ibm.com), Software Engineer, IBM

Swetha Prasad works as a DB2 Application Developer at IBM Software Labs, India. She works with the DB2 ID Samples Development Team in developing DB2 sample applications



29 November 2007

Also available in Russian

Introduction

Today's business challenges require that databases be optimized for the best possible performance. The database engine must be highly tunable and easily configured for the best performance regardless of the skill level of the DBA. To face these challenges, DB2 9 comes with a rich set of autonomic features for tuning the configuration parameters adaptively and accurately; one such feature is the self-tuning memory manager (STMM). This article discusses the current industry challenges and demonstrates how the STMM feature can help you overcome these challenges.

Current industry challenges

As the database technology is growing to fulfill the needs of current industry requirements, it is necessary to have sophisticated autonomic computing features embedded in the database. Modern database engines are very complex. And while complexity provides many enriched features and a greater degree of flexibility, it can also cause configuration challenges.

Some of the major business challenges that affect database tuning include:

  • Since the current database business requirements are huge, the complexity of the database engine must grow at a fast pace to meet these requirements.
  • As the complexity of the database engine grows, the number of parameters involved in tuning the database increases.
  • In most cases, the memory parameters are inter-linked. Changing one parameter may affect another related one. This, in turn, affects the database performance. Memory configuration can have a dramatic effect on the database management system (DBMS) performance.
  • With increasing complexity of the database engine, industries need very experienced DBAs to manage the database servers.
  • Most of the time, the tuning of memory through educated trial and error is difficult and time-consuming.
  • In many business scenarios, the workload on the database servers is variable. In such situations, database tuning for any skilled DBA is a difficult task. Optimal configuration may not be static.

How DB2 9 overcomes the challenges

Self tuning memory manager (STMM)

System performance benefits when memory is adaptively tuned based on workload requirements. To achieve the greatest performance, it is necessary to adapt the memory configuration as workload shifts.

The process of tuning the DB2 database memory (DATABASE_MEMORY) and buffer pools for optimal performance is effortless with the new, self-tuning memory manager (STMM) feature in DB2 9. This feature automatically configures most of the memory settings and adjusts them at the run time to optimize performance. Best of all, STMM does not require any DBA intervention in tuning the memory parameters based on workload change.

How does STMM work?

The STMM in DB2 9 simplifies the task of setting up several memory configuration parameters during database startup and during run time. The STMM has an intelligent control mechanism to keep track of characteristics of varying workload, memory consumption by each memory parameter and demand for numerous shared memory resources in the database. It dynamically adjusts the resources' memory usage as needed.

STMM constantly monitors the system to make use of any operating system memory that is available only if the system is set to tune the total database memory consumption.

It works iteratively to determine an optimal memory configuration for the following memory parameters: sort (sheapthres_shr and sortheap), package cache (pckcachesz), lock list (locklist and maxlocks) and the buffer pools. STMM uses an iterative approach, in an attempt to prevent system instability.

Figure 1 shows the STMM work flow. The work flow consists of the following:

  1. The tuner wakes up (1) and checks to see if any memory parameter needs more memory (2).
  2. If the answer is "yes", it examines the amount of free operating system memory (if DATABASE_MEMORY is set to automatic) (5). If enough free physical memory is available at the operating system level, it takes some of that memory and gives it to the needy heap (6). If memory is not available, it finds another memory heap which can donate memory to the needy heap (7).
  3. This continues until no more memory can be moved.
  4. The tuner then goes to sleep until required (4). The wakeup frequency is determined by the workload characteristics (3).

Figure 1. STMM work flow

STMM modes of operation

STMM works in four different modes

  • Mode 1: When DATABASE_MEMORY= AUTOMATIC
    In this case, the required memory is taken from and returned to the OS when required. The total amount of memory used by DB2 can grow over time and is limited only by the operating system's memory availability.
    Note: DATABASE_MEMORY = AUTOMATIC is only supported on AIX and Windows.
  • Mode 2: When DATABASE_MEMORY= <NUMERIC VALUE> AUTOMATIC
    In this case, memory tuning still occurs, but the total memory used by the database would start from the NUMERIC value and can grow over time. It is only limited by the operating system's available memory.
    Note: DATABASE_MEMORY = AUTOMATIC is only supported on AIX and Windows.
  • Mode 3: When DATABASE_MEMORY=<NUMERIC VALUE>
    DB2 will allocate this amount of memory during startup and this memory setting is static. It can not take or give memory to the operating system on demand.
  • Mode 4: When DATABASE_MEMORY=COMPUTED
    The database memory is computed based on the sum of initial values of the database memory heaps during the database startup. In this case DATABASE_MEMORY is not enabled for self tuning.

STMM summary

  • STMM tunes total database memory consumption if DATABASE_MEMORY is set to AUTOMATIC or the combination of AUTOMATIC and a numeric value.
  • If it is set to AUTOMATIC, memory is taken from, and returned to, the operating system if required by the database.
  • If it is set to a numeric value, the tuning still occurs between memory heaps which are set to AUTOMATIC. Only restriction being more than one memory heap should set to AUTOMATIC to tune the memory.
  • If it is set to COMPUTED, no DATABASE_MEMORY tuning occurs.

Figure 2 shows the STMM memory structure:

Figure 2. STMM memory structure

STMM works on the database shared-memory parameters. These parameters include:

  • Buffer pools are controlled by the ALTER BUFFERPOOL and CREATE BUFFERPOOL statements. The database buffer pool is a chunk of memory used to cache tables' index and data pages as they are being read from disk to be selected or updated.
  • Package cache is controlled by the pckcachesz configuration parameter. This parameter is allocated from the database shared memory and is used for buffering the sections for static SQL, dynamic SQL and XQuery statements.
  • Locking memory is controlled by the locklist and maxlocks configuration parameters. Locklist parameter indicates the amount of storage that is allocated to the lock list. There is one locklist per database, and it contains the locks held by all applications concurrently connected to the database. Maxlocks parameter defines a percentage of the lock list held by an application that must be filled before the database manager performs lock escalation.
  • Sort memory is controlled by the sheapthres_shr and the sortheap configuration parameters. The total size of the shared sort memory allocated is based on the value of the SHEAPTHRES_SHR parameter during database activation and the sortheap determines the maximum number of memory pages that can be used for each sort.
  • Total database shared memory is controlled by the database_memory configuration parameter. This parameter specifies the amount of shared memory that is reserved for the database shared memory region.

Enabling STMM

How to enable STMM

You may be wondering how you go about enabling STMM for DB2 9. First, update the database configuration parameter SELF_TUNING_MEM to ON using the command in Listing 1:

Listing 1. Update the database configuration parameter to ON
db2 "connect to OLTPDB"
db2 "update database configuration for OLTPDB using SELF_TUNING_MEM ON"

or through the Change Database Configuration Parameter window in the Control Center.

Next, to enable self-tuning of memory areas controlled by memory configuration parameters, set the relevant configuration parameters to AUTOMATIC using the UPDATE DATABASE CONFIGURATION command or through the Change Database Configuration Parameter window in the Control Center. Listing 2 shows this command:

Listing 2. Enable self-tuning of memory areas
db2 "connect to OLTPDB"
db2 "update database configuration for oltpdb using PCKCACHESZ AUTOMATIC"
db2 "update database configuration for oltpdb using LOCKLIST AUTOMATIC"
db2 "update database configuration for oltpdb using MAXLOCKS automatic"
db2 "update database configuration for oltpdb using SORTHEAP AUTOMATIC"
db2 "update database configuration for oltpdb using SHEAPTHRES_SHR AUTOMATIC"
db2 "update database configuration for oltpdb using DATABASE_MEMORY AUTOMATIC"

Finally, to enable self-tuning of buffer pools, set the buffer pool size to AUTOMATIC. You can do this by using the ALTER BUFFER POOL statement for existing buffer pools or the CREATE BUFFER POOL statement for new buffer pools. Listing 3 shows this command:

Listing 3. Enable self-tuning of buffer posts
db2 "connect to OLTPDB"
db2 "create bufferpool bpool8k size AUTOMATIC pagesize 8 k"

If the buffer pool is already present, use the following code: db2 "alter bufferpool bpool4k size automatic"

Important Notes:

  • Self-tuning re-distributes memory between different memory parameters. When database_memory is set to a numeric value, only one other memory consumer must be set to AUTOMATIC for tuning to occur.
  • In order to enable the locklist configuration parameter for self-tuning, maxlocks must also be enabled.
  • In order to enable the sheapthres_shr configuration parameter for self-tuning, sortheap must also be enabled.
  • Automatic tuning of sheapthres_shr or sortheap is allowed only when the database manager configuration parameter sheapthres is set to 0.
  • Self-tuning memory runs only on the HADR primary server. When self-tuning memory is activated on an HADR pair, it does not run on the secondary server but runs only on the primary server. If the HADR TAKEOVER command is run on the current secondary machine, self-tuning memory operations also switch to the new primary machine.

How to enable STMM in both active and inactive modes

One can execute the db2 get database configuration command along with show detail to see the current STMM setting which is in active or in-active state.

Listing 4. View the current STMM settings
Self Tuning Memory                                   (SELF_TUNING_MEM) = OFF
Self Tuning Memory                                   (SELF_TUNING_MEM) = ON (Active)
Self Tuning Memory                                   (SELF_TUNING_MEM) = ON (Inactive)

If the parameter shows "ON (Active)", it means that the memory tuner is actively tuning the memory on the system. If the parameter shows "ON (Inactive)", it means that, although the parameter is set ON, self-tuning is not occurring because there are less than two memory parameters enabled for self-tuning.


Business scenarios

Importance of STMM when many number of poorly tuned buffer pools present

In any production database, one can expect database performance degradation due to poorly tuned buffer pool. As the number of buffer pools increases, it is quite difficult to administer the buffer pool based on memory requirements.

This article takes an OLTP-like workload to test the bufferpool tuning. The system has the following specifications:

  • System configuration: AIX, 8 GB of RAM, 220 GB storage space
  • Database name: OLTPDB
  • Database size:830 MB
  • Temporary table spaces:Tablespaces TEMP4K , TEMP8K, TEMP16K, TEMP32K of pagesize 4K, 8K, 16K and 32K respectively.
  • Regular system managed table spaces:Tablespace SMS_4K, SMS_8K, SMS_16K, SMS_32K of pagesize 4K, 8K, 16K and 32K respectively
  • Regular database managed table spaces:Tablespace DMS_4K, DMS_8K, DMS_16K, DMS_32K of pagesize 4K, 8K, 16K and 32K respectively
  • Index table spaces:Tablespace INDEX_4K, INDEX_8K, INDEX_16K, INDEX_32K of pagesize 4K, 8K, 16K, and 32K respectively
  • Large tablespace:Tablespace LARGE_32K of 32K page size
  • Bufferpools:We have created 4 buffer pools BPOOL_4K, BPOOL_8K, BPOOL_16K and BPOOL_32K of page size 4K, 8K, 16K and 32K respectively.

For any experienced DBA, it would be very difficult to monitor all the buffer pools and tune them at run time based on the requirements. In the case of memory-varied workload, it would be virtually impossible.

STMM works with multiple buffer pools regardless of page size. It trades memory between many buffer pools.

In this case, to set up STMM-enabled database, we executed the following DB cfg parameters:

Listing 5. Parameters to set up STMM-enabled databases
db2 "update database configuration for OLTPDB using SELF_TUNING_MEM ON"
db2 "update database configuration for OLTPDB using PCKCACHESZ AUTOMATIC"
db2 "update database configuration for OLTPDB using LOCKLIST AUTOMATIC"
db2 "update database configuration for OLTPDB using MAXLOCKS AUTOMATIC"
db2 "update database configuration for OLTPDB using SORTHEAP AUTOMATIC"
db2 "update database configuration for OLTPDB using SHEAPTHRES_SHR AUTOMATIC"
db2 "update database configuration for OLTPDB using DATABASE_MEMORY AUTOMATIC"
db2 "connect to OLTPDB"
db2 "alter bufferpool bpool_4k size automatic"
db2 "alter bufferpool bpool_8k size automatic"
db2 "alter bufferpool bpool_16k size automatic"
db2 "alter bufferpool bpool_32k size automatic"

To test the STMM action on bufferpools, we have created the bufferpools with the default size of 1000 pages. Initially, we ran concurrent applications which spawned more than 100 clients on the data server and the transaction per second (TPS) is measured using iwatch.

Figures 3 and 4 show STMM action on the various bufferpools we tested:

Figure 3. STMM action on poorly tuned bufferpools

Figure 4. STMM action on bufferpool run time page allocation

When we started running the application, the initial TPS was very low and it started increasing as time progressed. STMM took the responsibilty of tuning the bufferpools which resulted in improved performance.

When many databases are present in an instance

One can expect multiple databases to be present in an instance. Tuning multiple databases is a costly exercise. With the help of STMM, the administrator can easily tune multiple databases. STMM trades the memory between different databases to use the system memory optimally.

We have two databases OLTPDB (from the previous sections) and SAMPLE in the instance DB2INST1, and both the databases are STMM enabled, as shown below:

SELF_TUNING_MEM = ON
DATABASE_MEMORY = AUTOMATIC

This case study takes an example of an online banking system that has a typical OLTP workload. The system has the following specifications:

  • System configuration:AIX, 8 GB of RAM, 220 GB storage space
  • Databases:OLTPDB and SAMPLE
  • Database size: OLTPDB 830 MB and SAMPLE 600 MB

Figures 5 and 6 show the STMM action on OLTPDB and the SAMPLE DB:

Figure 5. STMM action on OLTPDB

Figure 6. STMM action on SAMPLE DB

When the first database OLTPDB is started, it consumes most of the available system memory. After 1hour 15 minutes, the second database SAMPLE gets started, and the STMM trades the database memory between the two databases. After 4 hours 54 minutes, SAMPLE stops; STMM takes care of allocating the complete memory to the database OLTPDB.

Note: This test has been executed on testing server using testing workloads. Though the workloads are similar to OLTP, they are not exact OLTP applications. The test result may not reflect the actual test on OLTP real time system.

Business benefits summary

As you can see, the STMM benefits businesses because it does the important, often time-consuming, tasks of memory management. When many buffer pools are present in a database, it automatically allocates memory space amongst the different buffer pools. The STMM has the ability to tune a database on a per-second basis and can easily distribute memory when more than one database is present in an instance. The STMM also has the ability to tune databases across instances. Businesses benefit from the self-tuning memory management feature of DB2 9 because it automatically does the tasks that would take a DBA many hours and much effort to achieve.

In Conclusion

The DB2 9 self-tuning memory manager feature continues to help DB2 database administrators in managing the data servers effectively and efficiently.

In most of the enterprise computing, DB2 Information Management software is located at the core. DB2 9 is responsible for storing and retrieving wide range of information. In case of varied memory requirements, DB2 9 itself takes care of managing the memory with out DBA intervention. It is also helpful in case of a dramatically changing workload which the database administrators find difficult to manage efficiently.

All in all, STMM is a revolutionary feature in DB2 9 which does memory tuning by itself.

Acknowledgements

Special thanks to Adam Storm (Lead STMM Developer) for helping us in understanding the STMM concepts better and fine-tuning the article.

Resources

Learn

Get products and technologies

  • Download IBM product evaluation versions and get your hands on application development tools and middleware products from DB2, Lotus®, Rational®, Tivoli®, and WebSphere®.

Discuss

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Information management on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Information Management
ArticleID=255254
ArticleTitle=Understanding the advantages of DB2 9 autonomic computing features
publish-date=11292007