Databases Reference
In-Depth Information
13.10.2 Automated Memory Tuning
This feature addresses the following main obstacles to end-user performance tuning:
1.
Inadequate knowledge of the product's memory use.
The documentation for a data-
base product as sophisticated as DB2 V9.1 can seem overwhelming to an inexpe-
rienced DBA. In fact, even database product developers and technical leaders are
frequently at a loss about how to allocate database memory, apart from the tradi-
tional trial-and-error approach. With this new functionality in DB2 V9.1, the
DBA will be relieved of the need to invest time in understanding how the data-
base uses memory before tuning can begin.
2.
Uncertain memory requirements for a given workload.
In some cases, even experi-
enced DBAs can find it difficult to tune a database's memory because the work-
load characteristics are unknown. With the introduction of this new feature, the
system will now be able to continuously monitor database memory usage and
tune when necessary to optimize performance based on the workload character-
istics. As a result, the user will require no knowledge of the workload for the
memory to be tuned well.
3.
Changing workload behavior.
For many industrial workloads, no single memory
configuration can provide optimal performance because, at different points in
time, the workload can exhibit dramatically different memory demands. If
STMM is running and the workload's memory demands shift, the system will
recognize the changing needs for memory and adapt the memory allocation
accordingly. As a result, the user will rarely (if ever) need to manually change
the affected memory configuration parameters to enhance performance.
4.
Performance tuning is time consuming.
Tuning a database's memory to achieve
high levels of performance is extremely costly and can take days or weeks of
experimentation. STMM solves this problem by iterating toward the optimal
memory distribution as the workload runs. As a result, the user will no longer
be required to collect and analyze monitor output from workload runs. This
should save a great deal of time and effort on the part of the DBA while at the
same time achieving performance levels similar to that of an expertly tuned sys-
tem. The net effect is a reduction in the product's total cost of ownership.
To further motivate the problem, we first discuss memory tuning of a relational
database management system (RDBMS) that does not have automatic memory tuning
functionality.
The academic investigation of the database memory tuning problem has produced
many interesting papers. The papers, however, suffer from two problems that prevent
their implementation in a commercial database product.
