on-line index selection for physical database tuning
DESCRIPTION
On-line Index Selection for Physical Database Tuning. Karl Schnaitter UCSC & Aster Data Advisor: Neoklis Polyzotis ISSDM Mentor: John Bent SRL/ISSDM Symposium 2010. Index Tuning for Databases. SELECT b FROM R WHERE a = 10. Execution Plan 1. Reads whole table - PowerPoint PPT PresentationTRANSCRIPT
![Page 1: On-line Index Selection for Physical Database Tuning](https://reader035.vdocuments.us/reader035/viewer/2022062500/568157c2550346895dc54656/html5/thumbnails/1.jpg)
On-line Index Selection for Physical Database Tuning
Karl SchnaitterUCSC & Aster Data
Advisor: Neoklis PolyzotisISSDM Mentor: John Bent
SRL/ISSDM Symposium 2010
![Page 2: On-line Index Selection for Physical Database Tuning](https://reader035.vdocuments.us/reader035/viewer/2022062500/568157c2550346895dc54656/html5/thumbnails/2.jpg)
Index Tuning for Databases
2
R a b5 “blue”
10 “red”
97 “green”
10 “blue”
· · · · · ·
Index Scan index(R(a))
• Only accesses rows where a = 10
• May be much faster than a table scan
R a b5 “blue”
10 “red”
97 “green”
10 “blue”
· · · · · ·
Table Scan
• Reads whole table• Discards rows
where a ≠ 10• Returns b from
remaining rows
SELECT b FROM R WHERE a = 10
· · ·
Execution Plan 1
Execution Plan 2
![Page 3: On-line Index Selection for Physical Database Tuning](https://reader035.vdocuments.us/reader035/viewer/2022062500/568157c2550346895dc54656/html5/thumbnails/3.jpg)
3
Introduction
The index selection problem for databases: The goal is to choose the indexes that improve
database performance A crucial part of performance tuning
Index selection is very challenging Query benefit must be weighed against the
cost of creation, maintenance and storage Dynamic query loads add more difficulty In practice, index selection is typically handled
by an experienced database administrator
![Page 4: On-line Index Selection for Physical Database Tuning](https://reader035.vdocuments.us/reader035/viewer/2022062500/568157c2550346895dc54656/html5/thumbnails/4.jpg)
Previous Work
4
Auto-index tuning is an active research topic
Hammer and Chan, SIGMOD 1976 Finkelstein et al., TODS 1988 Chaudhuri and Narasaya, VLDB 1997 Bruno and Chaudhuri, ICDE 2007
…and numerous other references
![Page 5: On-line Index Selection for Physical Database Tuning](https://reader035.vdocuments.us/reader035/viewer/2022062500/568157c2550346895dc54656/html5/thumbnails/5.jpg)
DBA
Off-line Index Advisor
Database Server
What-if Optimizer
TuningWorkload
RecommendedIndexes
Create/DropIndexes
User
Actual Workload
+ DBA controls index maintenance
- DBA must know the workload in advance
- Limited support for interactive tuning
Off-line Tuning
![Page 6: On-line Index Selection for Physical Database Tuning](https://reader035.vdocuments.us/reader035/viewer/2022062500/568157c2550346895dc54656/html5/thumbnails/6.jpg)
On-line Tuning
6
DBA User
On-line Index Advisor
Database Server
What-if Optimizer
Actual Workload
Create/DropIndexes
+Tuning is based on actual workload
-DBA is out of the loop
-Performance can become unpredictable
![Page 7: On-line Index Selection for Physical Database Tuning](https://reader035.vdocuments.us/reader035/viewer/2022062500/568157c2550346895dc54656/html5/thumbnails/7.jpg)
Our Proposal: Semi-automatic Tuning
7
DBA User
Semi-automatic Index Advisor
Database Server
What-if Optimizer
Create/DropIndexes
Actual Workload
RecommendedIndexes
Feedback
![Page 8: On-line Index Selection for Physical Database Tuning](https://reader035.vdocuments.us/reader035/viewer/2022062500/568157c2550346895dc54656/html5/thumbnails/8.jpg)
Example Session
8
DBA User
Database Server
What-if Optimizer
{a, b, c}
Create/DropIndexes
Actual Workload
I like a; I want d I don’t like b
{a, d, e, c}{a, f, c}
Semi-automatic Index Advisor
+ DBA makes decisions on index-maintenance
+ Workload is analyzed online
+ DBA expertise is coupled with index advisor
![Page 9: On-line Index Selection for Physical Database Tuning](https://reader035.vdocuments.us/reader035/viewer/2022062500/568157c2550346895dc54656/html5/thumbnails/9.jpg)
Our Proposal: Semi-automatic Tuning
9
DBA User
Semi-automatic Index Advisor
Database Server
What-if Optimizer
Create/DropIndexes
Actual Workload
RecommendedIndexes
Feedback
![Page 10: On-line Index Selection for Physical Database Tuning](https://reader035.vdocuments.us/reader035/viewer/2022062500/568157c2550346895dc54656/html5/thumbnails/10.jpg)
Generating Recommendations
10
candidate selection
index candidates
Work FunctionAlgorithm
recommended index set
recommendation logic
DBA
User
workload
![Page 11: On-line Index Selection for Physical Database Tuning](https://reader035.vdocuments.us/reader035/viewer/2022062500/568157c2550346895dc54656/html5/thumbnails/11.jpg)
11
Example of Index Interaction
R a b5 “blue”
10 “red”
97 “green”
10 “blue”
· · · · · ·
Index Scan index(R(a))
• Only accesses rows where a = 10
• May be much faster than a table scan
SELECT b FROM R WHERE a = 10
Execution Plan 1
Index-Only Scan index(R(a,b))
• Only accesses index• If plan 2 is cheaper,
the index on R.a becomes obsolete
Execution Plan 2
![Page 12: On-line Index Selection for Physical Database Tuning](https://reader035.vdocuments.us/reader035/viewer/2022062500/568157c2550346895dc54656/html5/thumbnails/12.jpg)
12
Interaction-Based Partitioning
Consider an undirected graph Vertex for each candidate index Edge between interacting indexes
Different components have independent benefits Different components have independent benefits
C1
C3
C2
We call this a stable partition of the indexesWe call this a stable partition of the indexes
![Page 13: On-line Index Selection for Physical Database Tuning](https://reader035.vdocuments.us/reader035/viewer/2022062500/568157c2550346895dc54656/html5/thumbnails/13.jpg)
Improvement: Candidate Partitioning
13
candidate selection
partitioned index
candidates
WFA
recommended index set
recommendation logic
DBA
User
workload
WFA WFANeed to handle feedback
![Page 14: On-line Index Selection for Physical Database Tuning](https://reader035.vdocuments.us/reader035/viewer/2022062500/568157c2550346895dc54656/html5/thumbnails/14.jpg)
14
Feedback Model
User provides votes for some indexes Positive votes for indexes they want Negative votes for indexes they don’t want
Votes may be explicit E.g., user may reject the system’s
recommendation to create an index
Votes may also be implicit Creating an index casts a positive vote Dropping an index casts a negative vote
![Page 15: On-line Index Selection for Physical Database Tuning](https://reader035.vdocuments.us/reader035/viewer/2022062500/568157c2550346895dc54656/html5/thumbnails/15.jpg)
15
Handling Feedback
Votes create a soft constraint on future recommendations Include indexes with positive votes Exclude indexes with negative votes Reverse these decisions if sufficient evidence is
seen after the votes were cast
![Page 16: On-line Index Selection for Physical Database Tuning](https://reader035.vdocuments.us/reader035/viewer/2022062500/568157c2550346895dc54656/html5/thumbnails/16.jpg)
Final Solution: WFIT
16
candidate selection
partitioned index
candidates
WFA
recommended index set
recommendation logic
DBA
feedback
User
workload
WFA WFA
![Page 17: On-line Index Selection for Physical Database Tuning](https://reader035.vdocuments.us/reader035/viewer/2022062500/568157c2550346895dc54656/html5/thumbnails/17.jpg)
17
Behavior of WFIT with Feedback “Good” votes
follow the best indexes chosen off-line
“Bad” votes are opposite of “Good” votes
Normalized against optimal schedule
![Page 18: On-line Index Selection for Physical Database Tuning](https://reader035.vdocuments.us/reader035/viewer/2022062500/568157c2550346895dc54656/html5/thumbnails/18.jpg)
18
Conclusions
Semi-automatic tuning is a promising new approach to physical database design Combines best features of existing approaches User feedback is the key ingredient
We recommend indexes with provable performance guarantees Using work function algorithm as the foundation
Future work may include: More efficient workload analysis A GUI frontend to present recommendations