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.
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.
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.
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:
- The tuner wakes up (1) and checks to see if any memory parameter needs more memory (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).
- This continues until no more memory can be moved.
- The tuner then goes to sleep until required (4). The wakeup frequency is determined by the workload characteristics (3).
STMM works in four different modes
Mode 1: When DATABASE_MEMORY=
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
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 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:
STMM works on the database shared-memory parameters. These parameters include:
Buffer pools are controlled by the
CREATE BUFFERPOOLstatements. 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.
You may be wondering how you go about enabling STMM for DB2 9. First, update the
database configuration parameter
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"
- 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 TAKEOVERcommand is run on the current secondary machine, self-tuning memory operations also switch to the new primary machine.
One can execute the
db2 get database configuration
command along with
to see the current STMM setting which is in active or in-active state.
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 "
(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.
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:
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:
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.
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:
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.
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.
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.
Special thanks to Adam Storm (Lead STMM Developer) for helping us in understanding the STMM concepts better and fine-tuning the article.
IBM DB2 9
Database for Linux, UNIX, and Windows Information Center: Find information that you need to use the DB2 9 family of products and features.
What's new in DB2 Viper (developerWorks, February 2006): Explore Db2 9's new XML technology and learn why IBM now considers DB2 a "hybrid" or multi-structured DBMS.
9 self-tuning memory management (developerWorks, November 2006): Manage your business, not your database.
Self-Tuning Memory Manager (STMM) (pdf; Technical White Paper, June 2007): Discover
everything you wanted to know about the STMM .
Adaptive Self-Tuning Memory in DB2
(pdf): Learn about the adaptive Self-tuning memory in DB2.
Introduction to DB2 9 STMM, Part 3 (developerWorks, January 2006): Learn about the
self-tuning memory feature in DB2 9
DB2 9 Development Lab Chat: Listen to a discussion on automatic database
configuration and memory tuning led by Adam Storm.
self-tuning memory manager log parser(developerWorks, August 2007): Discover a simple tool to parse the self tuning memory manager log file for easy monitoring.
STMM Road Map: Complete self-tuning memory information on DB2 9 Information Center
DB2 Express-C the no-charge version of DB2 Express Edition for the community.
Get products and technologies
IBM product evaluation versions
and get your hands on application development tools and middleware products from
DB2, Lotus®, Rational®, Tivoli®, and
- Participate in the discussion forum.
- Check out
get involved in the
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.