Databases Reference
In-Depth Information
Figure 13.21
Workload shift—memory distribution.
converged, the database has reserved more than 8 GB of memory for sorting. As is illus-
trated in Figure 13.22, this memory distribution shift has a dramatic effect on the work-
load performance.
Figure 13.22 shows the workload performance during the run. In the first stage of
the run, the memory distribution is stable as the 16 streams of query 13 complete con-
sistently in about 280 seconds. Once the workload shifts, however, it is clear that the
system's memory is not properly configured for query 21. At this point, STMM begins
redistributing the database memory and the resultant dramatic effect on performance
can be observed as quickly as the second run of the queries, at which point performance
has already improved by 74%. After several more runs, the query response time stabi-
lizes and a performance improvement of 254% can be observed when compared to the
first execution of query 21. This not only shows how critical sort memory can be to a
database system, but also how effective STMM can be at supplying the sort memory
when necessary.
Finally, to test STMM in an environment where multiple databases are competing
for a single system's memory, an experiment was performed with two identical databases
running the same workload. In building the databases, it was necessary to ensure that
both databases had the same physical design, resided on the same number of disks, and
