Introducing DB2 9, Part 3: Self-tuning memory in DB2 9

Tuning your database memory and buffers for optimum performance is effortless with the new self-tuning memory management feature in DB2® 9 (formerly codenamed "DB2 Viper"). It automatically configures database memory settings and adjusts them dynamically during run time to optimize performance and improve administrator productivity. Take a look at how it works, explore the benefits, and see the results of this feature in a benchmark setting. For more information on the new features of DB2 9, read the other articles in the series.

Rav Ahuja (rsahuja@ca.ibm.com), DB2 Program Manager, IBM

Rav Ahuja is a worldwide DB2 program manager based at the IBM Toronto Lab. He has been working with DB2 for Linux, UNIX, and Windows since version 1 and has held various roles in DB2 development, technical support, marketing, and product strategy. He works with customers and partners around the globe helping them build and benefit from DB2 and services-based solutions. Rav is a frequent contributor to DB2 papers, articles and books. He holds a Computer Engineering degree from McGill University and MBA from University of Western Ontario.



01 June 2006

Also available in Chinese

Introduction

Database workloads seldom remain static. Workloads and the environments they are run under can change over time due to a number of factors, including more users, change in the pattern of queries, running of maintenance tasks, changes in resources consumed by other applications, and so on. Therefore, a system tuned by even the most skilled administrator at one point in time may not be optimal at another time. And the changes may occur in seconds (rather than days or weeks), giving the administrator little time to respond. Database memory settings are especially vulnerable to such changes and can severely impact response times.

Get an introduction to the new self-tuning memory management feature in DB2 9 that automatically tunes database memory settings and adjusts them dynamically during run time to optimize performance and improve administrator productivity.

How it works

The self-tuning memory feature in DB2 9 simplifies the task of memory configuration by automatically setting values for several memory configuration parameters at startup. The self-tuning memory manager uses intelligent control and feedback mechanisms to keep track of changes in workload characteristics, memory consumption, and demand for the various shared resources in the database and dynamically adapts their memory usage as needed. For example, if more memory is needed for sort operations and some buffer pools have excess memory, the memory tuner frees up the excess buffer pool memory and allocates it to the sort heaps.

Figure 1. Database memory
Database memory

On Windows® and AIX® platforms, the self-tuning memory feature can also determine the overall database memory requirements and dynamically tune the total database memory usage. That is, on these platforms, in addition to dynamically adjusting the memory utilization between the database resources, DB2 can also consume more physical memory as the workload demands it and free up that memory to the operating system for other tasks and applications when database memory requirements are low.

Enabling self-tuning memory

Self-tuning memory works on the database shared-memory resources. These resources include:

  • Buffer pools (controlled by the ALTER BUFFERPOOL and CREATE BUFFERPOOL statements)
  • Package cache (controlled by the pckcachesz configuration parameter)
  • Locking memory (controlled by the locklist and maxlocks configuration parameters)
  • Sort memory (controlled by the sheapthres_shr and the sortheap configuration parameters)
  • Total database shared memory (controlled by the database_memory configuration parameter)

The self-tuning memory is enabled using the self_tuning_mem database configuration parameter. In DB2 9, self-tuning memory is automatically enabled (for non-partitioned databases) when you create a new database. That is, the self_tuning_mem is set to ON, and the database parameters for the resources listed above are set to AUTOMATIC. For existing databases migrated from earlier versions of DB2, self-tuning memory needs to be enabled manually. Rather than have all of the database memory resources managed automatically, you can also choose to set just the desired memory resources (parameters) to AUTOMATIC.

Benefits

Traditionally, configuring database memory parameters for optimal operation can be a complex and time-consuming task. By automatically setting the memory parameters for the database, DB2 9 simplifies the task of configuring the data server. This improves DBA productivity, freeing up the administrator’s time to focus on other important tasks

In addition to simplifying memory configuration, this new, adaptive self-tuning memory feature improves performance by providing a superior configuration that is dynamic and responsive to significant changes in workload.

This feature can also be very beneficial when there are several databases running on the same system, automatically allowing certain databases to consume more memory during peak times, and freeing it up for other databases and workloads when they need it more.

Self-tuning memory in action

The following graphs in Figure 2 show DB2 self-tuning memory in action for a demanding benchmark configuration. DB2 automatically increases the database shared memory (graph on the left) to satisfy the demands of the workload, resulting in a corresponding increase in query throughput (graph on the right).

Figure 2. Effect of automatic tuning on system performance
Effect of automatic tuning on system performance

Conclusion

Self-tuning memory in DB2 9 is a revolutionary feature that can result in significant time savings, simplify memory tuning, and automatically optimize performance on the fly. Download DB2 9 and try out this feature to experience the benefits for yourself.

Resources

Learn

Get products and technologies

  • Download DB2 9 to try out the features described in this article.
  • Now you can use DB2 for free. Download DB2 Express-C, a no-charge version of DB2 Express Edition for the community that offers the same core data features as DB2 Express Edtion and provides a solid base to build and deploy applications.
  • Build your next development project with IBM trial software, available for download directly from developerWorks.

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=125521
ArticleTitle=Introducing DB2 9, Part 3: Self-tuning memory in DB2 9
publish-date=06012006