![Close [x]](http://dw1.s81c.com/i/c.gif){kind=small}
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.
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.
Self-tuning memory works on the database shared-memory resources. These resources include:
- Buffer pools (controlled by the
ALTER BUFFERPOOLandCREATE BUFFERPOOLstatements) - Package cache (controlled by the
pckcacheszconfiguration parameter) - Locking memory (controlled by the
locklistandmaxlocksconfiguration parameters) - Sort memory (controlled by the
sheapthres_shrand thesortheapconfiguration parameters) - Total database shared memory (controlled by the
database_memoryconfiguration 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.
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.
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
-
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.
Discuss
- Participate in the discussion forum.
-
Participate in developerWorks
blogs and get involved in the developerWorks community.
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.
Table of contents
Next steps from IBM
Tags
Use the slider bar to see more or fewer tags.
For articles in technology zones (such as Java technology, Linux, Open source, XML), Popular tags shows the top tags for all technology zones. For articles in product zones (such as Info Mgmt, Rational, WebSphere), Popular tags shows the top tags for just that product zone.
For articles in technology zones (such as Java technology, Linux, Open source, XML), My tags shows your tags for all technology zones. For articles in product zones (such as Info Mgmt, Rational, WebSphere), My tags shows your tags for just that product zone.
Popular article tags |
My article tagsSkip to tags list
Popular article tags |
My article tags
