Databases Reference
In-Depth Information
when the memory consumer has a sufficient amount of memory, the addition of more
memory produces no more saved system time.
For example, adding more memory for the buffer pools will see diminishing
returns when the whole database, or the set of active pages, fits in memory or when the
data accesses, beyond the set that is kept in memory, are random. Alternatively, for
sort, the diminishing returns occur when there is enough memory to perform all sorts
in memory.
This nonlinear relationship can be modeled as an exponential function
. The maximum total saved system time can be achieved when the
partial derivative for each memory size is equal, that is,
13.3
where
i
,
j
= 1...
N
. This results is used as the memory tuning objective, namely, to equal-
ize the cost-benefit metrics for all memory consumers.
Varying the Memory Transfer Limits
To achieve the memory tuning objective without excessive oscillations, the memory
tuner varies the amount of memory by which each consumer can increase or decrease in
a given interval. To compute these amounts, the memory tuner uses the following two
algorithms:
MIMO controller
. The multi-input multi-output (MIMO) controller uses a
model-based control theory approach to determine the direction and step size of
memory tuning. A MIMO model is first constructed (and continually revised to cap-
ture system and workload changes) to model the relationship between the memory
size and the benefit value. After the model quality is verified, the MIMO control algo-
rithm uses this model information and the integral control law to determine the
proper tuning actions required to equalize the benefits for all memory consumers.
Oscillation dampening controller
. When a MIMO model is not available (i.e., before
the first model can be constructed), a fixed-step algorithm is used. In the fixed-step
algorithm, all memory resizes are sized using a fixed percentage of the consumer's size,
regardless of the benefit value. Since this can lead to significant size oscillation, an oscil-
lation avoidance algorithm is introduced to reduce the memory tuning size once oscil-
lating patterns are observed. The fixed-step tuning combined with the oscillation reduc-
tion is referred to as the oscillation dampening controller (or OD controller).
Figure 13.19 shows the STMM controller and the interaction between the MIMO
and OD controllers.
The OD controller is used in only two scenarios: (1) when a database is starting up
and lacks a tuning history, and (2) in the presence of large system noise. Because of
