 | Level: Introductory Rav Ahuja (rsahuja@ca.ibm.com), DB2 Program Manager, IBM
01 Jun 2006 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.
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
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
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
About the author | | | 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. |
Rate this page
| |
