Continuous Physical Database Design - Automated Physical Database Design and Tuning - page 177

Chapter 10
Continuous Physical
Database Design
The techniques discussed in Part II are sophisticated and useful in many com-
mon scenarios. However, they take an o ine approach to the physical design
problem and still leave significant decisions to database applications (DBAs).
Specifically, DBAs need to explicitly identify representative workloads and
feed them to tuning tools. DBAs are also expected to guess when a tuning
session is needed and when to deploy recommendations. Naturally, this is not
a one-time process, but instead DBAs continuously monitor, diagnose, and
tune database installations.
These manual tasks become even more problematic in current complex sce-
narios. Consider, as an increasingly common example, large installations that
support the database-as-a-service paradigm. Database applications hosted in
such services may come and go and usually exhibit unexpected spikes in their
loads. At the same time, the hosting infrastructure might have some additional
amount of resources to globally tune the physical design, and all applications
would compete for these valuable resources. As another motivating exam-
ple, some applications exhibit periodic, sometimes unexpected changes in the
mix of SELECT and UPDATE queries in the workload. Consider, for instance, a
bug-tracking system. Most days the system is queried and browsed ( SELECT
load), but a few days—sometimes called bug-bash days—are used to primarily
identify and insert large numbers of bugs ( UPDATE load). If we gather a rep-
resentative workload over, say, a month, chances are that no index is globally
useful, as the gains in query processing are outweighed by the update costs
during bug-bash periods. It is very dicult to explicitly model the workload
in these scenarios and equally dicult to decide when to tune the database
and deploy the resulting recommendations (tuning too frequently results in
wasted resources, but tuning too sporadically misses critical opportunities to
improve performance).
With increasingly common database management system (DBMS) features
like online indexes (which allows query processing to continue in parallel with
indexes that are built in the background), it is appealing to explore more
automated solutions to the physical design problem. There are, however, new
and significant challenges to address. Such fully automated solutions should
177