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
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
- Package cache (controlled by the
- Locking memory (controlled by the
- Sort memory (controlled by the
- Total database shared memory (controlled by the
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.
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
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.
- Learn about DB2 Express-C, the no-charge version of DB2 Express Edition for the community.
- "IBM DB2 'Viper' revs XML engine" (InfoWorld, May 2006): Get a description of many of the new features of DB2 9.
- Read more articles about DB2 9 on developerWorks.
- developerWorks Information Management zone: Expand your skills on IBM Information Management products.
- developerWorks DB2 for Linux, UNIX, and Windows resource page: Read articles and tutorials, and link to documentation, support, learning resources, and more.
- Stay current with developerWorks technical events and Webcasts.
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.
- Participate in the discussion forum.
- Participate in developerWorks blogs and get involved in the developerWorks community.
Dig deeper into Information management on developerWorks
Get samples, articles, product docs, and community resources to help build, deploy, and manage your cloud apps.
Experiment with new directions in software development.
Software development in the cloud. Register today to create a project.
Evaluate IBM software and solutions, and transform challenges into opportunities.