Automated SQL Tuning through Trial and Sometimes Error

1
Automated SQL Tuning through Trial and
(Sometimes) Error

• Herodotos Herodotou
• Shivnath Babu
• Duke University

2
SQL Tuning

• Query Optimizer
• Select good query execution plans
• Can make mistakes
• Causes of mistakes
• Imprecise or stale statistics
• Invalid assumptions (uniformity, independence)
• Inefficient physical layout
• Suboptimal configuration parameter settings
• Solution SQL Tuning
• Improve poorly-performing execution plans

3
DBAs Course of action

• Get insight and collect monitoring data
• Form hypotheses regarding potential fixes
• Perform experiments to refine/confirm hypotheses
• Validate fixes
• Why use experiments?
• SQL tuning too complex to capture with models
• Quantify/Guarantee performance improvement

Hash Join
4
DBAs Course of action
DBA surveys estimate that this process consumes
a significant fraction of a DBAs daily work time
Important to automate the experiment-driven
process of SQL Tuning
5
zTuned Automated SQL Tuning

• Input
• Poorly-performing query
• Output
• (Better) Execution plan
• Approach
• Use experiments to perform SQL Tuning
• What is an experiment?
• Execute full query plan or sub-plan
• Execute partially or to completion
• Execute on entire tables or samples
• Cost plans with newly-obtained statistics

6
Design Decisions

• What is the query optimizers role here?
• How to plan the sequence of experiments?
• Where to run the experiments?

7
Integration with Query Optimizer

• Current Approaches Tight Integration
• Query Execution Feedback
• Learning Optimizer (LEO from IBM Research)
• Pay-As-You-Go approach from Microsoft Research
• Oracles Automatic Tuning Optimizer
• Validate important estimates used by the
  optimizer
• Our Approach Decouple
• Different exploration of plan space from what
  optimizer does
• Portable across various databases
• Need a well-defined interface with query optimizer

8
Interface with Query Optimizer

• Get selected execution plan
• Get info leading to selection
• Cardinality estimates
• Plan operator costs
• Cost valid plans for a query
• Execute valid plans for a query

9
Explain_Plan Command

• Command
• EXPLAIN_PLAN ANALYZE Plan FOR ltQuerygt
• Output
• Plan node information (cardinalities, costing,
  etc)
• Purpose
• Cost any valid plan using database cost model and
  estimated cardinality values
• Cost any valid plan using provided cardinality
  values
• Execute a plan

10
Experiment Planning in zTuned
11
Plan Neighborhood

• Plan Cardinality Set
• Set of cardinality values needed to cost a plan
• Plan Neighborhood
• Set of all plans with the same cardinality set
• What do we get?
• Can accurately cost all plans in a neighborhood
  after running only one plan

12
Same Neighborhoods
13
Different Neighborhoods
??
14
Experiment Planning in zTuned (2)
Plan Neighborhood 3
Plan Neighborhood 2
Plan Neighborhood 1
Plan Neighborhood 4
Plan Neighborhood 5
Challenge picking neighborhoods and plan per
neighborhood based on data available so far
15
Explore Within Neighborhood

• Execute Point-Of-Entry plan
• Prioritize operators based on
• Execution Cost Estimation Error
• Cardinality Estimation Error
• Perform Intra-Transformations
• Single operator transformations that generate a
  plan within the same neighborhood
• Cost generated plans
• Select plan with minimum estimated cost

16
Explore Across Neighborhoods

• Perform Inter-Transformations
• Multiple-operator transformations that generate a
  plan in a different neighborhood
• Prioritize neighborhoods based on
• Estimated cost of Point-Of-Entry plan
• Explore within each neighborhood
• Execute best plans from each neighborhood
• Select plan with minimum actual runtime

17
Where to Execute Experiments

• New workbench for experiments
• Take advantage of underutilized resources

• K. Yagoub, P. Belknap, B. Dageville, K. Dias, S.
  Joshi, and H. Yu. Oracle's SQL Performance
  Analyzer, Data Engineering Bulletin, March 2008
• S. Duan, V. Thummala, and S. Babu. Tuning
  Database Configuration Parameters with iTuned. In
  Proc. of the 35th Intl. Conf. on Very Large Data
  Bases, August, 2009

18
Preliminary Results
19
Summary

• SQL tuning using an experiment-driven approach
• zTuned prototype under development
• Plan space exploration using neighborhoods
• Explain_Plan interface with optimizer
• Workbench for experiments

Thank You!