Databases Reference
In-Depth Information
Figure 13.20
System performance during STMM tuning.
transactions per minute (a difference of 0.16%, which is within the interrun variability
of the workload on the test machine). The results of this second run illustrate how
STMM is able to converge to the optimal configuration when started from an out-of-
the-box configuration. The results also show that even with STMM actively tuning a
system, the performance can be within 1.4% of a hand-tuned result.
For the second experiment, to simulate such an environment an experiment was
devised where the database began by running one type of query and then, once the
memory configuration stabilized, the workload shifted to more complex queries. At
first the experiment ran 16 concurrent streams of TPC-H query 13, a decision-sup-
port query with low requirements for sort memory. Then, once the memory configu-
ration stabilized, the workload was changed to 16 concurrent streams of TPC-H
query 21, which is substantially more complex, contains multiple subqueries, and has
much higher requirements for sort memory. This shift from query 13 to query 21
places considerable pressure on the sort memory and should force the memory to be
dramatically reallocated.
Figure 13.21 shows the memory distribution shift over the course of the run. Once
the streams of query 13 stop and query 21 starts running, a dramatic increase in the
amount of sort memory allocated to the database occurs. By the time the system has
