Databases Reference
In-Depth Information
teristics. The DB2 technology tunes the buffer pools, sort and hash join memory, lock
memory, and the cache size for compiled SQL statements “package cache.”
DB2 adaptively determines how frequently to tune a used system stability test.
Because DB2 contains some of the most advanced strategies for memory management,
we will present some of the DB2 techniques here to highlight the techniques. A particu-
larly novel strategy of the DB2 approach is the incorporation of classical control theory
with feedback for memory tuning.
There are two main functional differences between the IBM Self-tuning Memory
Manager (STMM) feature and the Oracle Automatic Shared Memory Management
(ASMM) feature. The primary difference is that ASMM requires the user to put a limit
on the total amount of shared memory that the database can consume, a task that can be
nontrivial in the presence of multiple databases running on the same machine. STMM,
on the other hand, is able to adaptively determine the proper amount of memory that
each database should consume, thus alleviating the need for the user to calculate a total
memory value for every database. The second difference is that ASMM is unable to tune
two critical memory consumers that are automatically configured by STMM. These con-
sumers are sort and any buffer pools that store pages larger than 4 KB.
The approach by Storm et al. [2006] is the only industrially implemented approach
that combines total database memory tuning and a comprehensive memory distribution
algorithm with cost-benefit analysis and control theory techniques. We are reprinting
much of the approach described in that paper with permission of the original authors
and the VLDB Endowment.
7
13.10.3 Cutting Edge: The Latest Strategy in Self-tuning
Memory Management
This section describes in some detail the latest technology for self-tuning memory
described by Storm et al. [2006]. The principal component of the STMM feature is the
memory controller (Figure 13.17). During each tuning cycle, the memory controller (or
memory tuner) evaluates the memory distribution and determines if system perfor-
mance can be enhanced through the redistribution of the database memory. To do this,
the controller uses cost-benefit data as input into a control model whose output is an
improved memory distribution. The control model also determines an appropriate tun-
ing frequency, typically between 30 seconds and 10 minutes, based on the workload
characteristics (as defined through the input cost-benefit data).
7
The self-tuning strategy described is protected under several U.S. and international patents
filed prior to this publication. Publication here, and in other academic papers, does not provide
legal right for other vendors to reuse these methods where they are currently protected under
patent law.
