Practical Aspects in Physical Database Design - Automated Physical Database Design and Tuning - page 117

Chapter 7
Practical Aspects in Physical
Database Design
This chapter complements the various approaches for the physical design prob-
lem that we introduced earlier by discussing practical aspects that need to be
addressed in a real-world tuning tool. Figure 7.1 gives an overview of the
various issues that we discuss in this chapter. Specifically, we cover (1) how
to gather representative workloads, (2) how to clean and compress workloads
for scalable tuning, (3) variations on tuning models, (4) variations to limit
tuning time, (5) practical aspects of tuning production servers, (6) genera-
tion of reports, and (7) scheduling scripts for deploying the recommended
configurations.
7.1 Workload Gathering
The physical design problem consumes a query workload W and a storage
constraint B and produces the configuration C that minimizes the cost of W
while fitting in B . It is therefore very important that the input workload W be
representative of the real-world scenario on which configuration C would be
deployed. Otherwise, the resulting configuration, while very appropriate for
the input workload, might not be effective in the production system. We now
discuss different approaches to gather a representative input workload for the
physical design problem [see Figure 7.1(1)].
Manually designed queries: This approach is commonly used when we
have a good understanding of the database application and want to
do some amount of localized performance tuning. Suppose that a com-
monly executed query suddenly becomes much more expensive. In that
case, we can tune such query and obtain a configuration that solves
the performance problem (without considering other updates or stor-
age constraints). We can then use this configuration as a starting point
in a more comprehensive performance tuning investigation. If we need
117