1 / 23

CoPhy : A Scalable, Portable, and Interactive Index Advisor for Large Workloads

CoPhy : A Scalable, Portable, and Interactive Index Advisor for Large Workloads. Debabrata Dash, Anastasia Ailamaki, Neoklis Polyzotis. High Cost of DB Tuning. Sources: MS Azure, Forrester Research; 2010. oracle.com/us/products/database/039433.pdf.

restivo
Télécharger la présentation

CoPhy : A Scalable, Portable, and Interactive Index Advisor for Large Workloads

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. CoPhy: A Scalable, Portable, and Interactive Index Advisorfor Large Workloads Debabrata Dash, Anastasia Ailamaki, NeoklisPolyzotis

  2. High Cost of DB Tuning Sources: MS Azure, Forrester Research; 2010 oracle.com/us/products/database/039433.pdf Need to reduce administration and tuning cost Enterprises spend a lot on DBMS

  3. A New Approach to Index Tuning Index Tuning: Select indexes that maximize performance • CoPhy: Convert to a compact Binary Integer Program (BIP). Solve using mature solvers. • BIP:

  4. Outline • Introduction • BIP formulation • Existing formulation • Discovering structure • Exploiting the structure • Benefits • Experimental Results • Conclusion

  5. Index Tuning Problem T1 Join T2 Workload Candidates I1 I2 I3 I4 T1 T2 Index Tuning Constraints Optimal Indexes ? ?

  6. Existing Approaches What-If DBMS Optimizer What-If DBMS Optimizer Index Advisor Index Advisor Fast What-If Optimizer [INUM07,C-PQO08] • Greedy approaches • Bottom-up [CN97, VZ+00] • Top-down [BC05] • BIP-based approach [CS96, PA07]

  7. Existing BIP are atomic configurations are corresponding costs t1 t2 {0,1} I1 I2 I3 I4 T1 T2 x1x2 x3 x4 Min. Cost {0,1} Select one atomic conf. Index presence Program size = O(# T1 Indexes x # T2 Indexes)

  8. CoPhy vs. Existing Approaches What-If DBMS Optimizer Index Advisor Fast What-If Optimizer [INUM07,C-PQO08] CoPhy What-If DBMS Optimizer Fast What-If Optimizer Index Advisor

  9. Fast What-If: INUM What-If Optimizer Plan T1 Join T2 Template Plan Place Holder Place Holder I4 I1 I1 I3 I3 I1 I4 I1 Instantiated Plan Instantiated Plan A template plan can be reused for many index combinations

  10. Cost Structure Cost of template plan under A Atomic Configuration Cost of optimal plan under A LinearComposability is exhibited by both INUM, C-PQO Linear Composability of Query Costs

  11. Exploiting Linear Composability t1 t2 I1 I2 I3 I4 T1 T2 x1x2 x3 x4 Program size = O(# T1 Indexes + # T2 Indexes) BIP Solver explores the index combinations with the knowledge of the objective Exposing the cost model leads to linearly growing BIPs

  12. More Complex BIPs We extend the BIP to handle: BIP formulation does not restrict the expressive power of the DBA • Complex queries • Update costs • Complex Constraints [Bruno08]: • Storage constraint • Index constraints • Column constraints • Generators • Soft constraints

  13. CoPhy’s Architecture Workload What-If DBMS Optimizer Candidate Generator INUM BIP Generator Constraints BIP Solver CoPhy Selected Indexes Bounds Theorem: CoPhy computes an optimal index configuration

  14. Unique Features Enabled by the BIP • Portability: No change to the optimizer • Requires only the what-if APIs • Scalability: By solving large BIPs in seconds • No need to select workload, candidate indexes • Generality: The formulation can be reused • Quality feedback: All modern BIP solvers provide this • Can stop at near-optimal values • Interactive tuning: By solving BIPs incrementally • Interactively add/drop candidate indexes • Enables efficient multi-objective optimization

  15. Outline • Introduction • BIP formulation • Existing formulation • Discovering structure • Exploiting the structure • Benefits • Experimental Results • Conclusion

  16. Experimental Setup • Two commercial DBMS -- SystemA, SystemB • 1 GB TPC-H database • 1 GB index size constraint • Algorithms: • ToolA, ToolB – the commercial designers • ILP – The state of the art BIP [PA07] • CoPhyA, CoPhyB – Our approach on the systems • Queries generated using 15 TPC-H templates • Metric:

  17. Speedup Comparison Better # of queries SystemA # of queries SystemB # Candidates: CoPhy ~2000, ToolA ~200, ToolB ~50 • Replacing heuristic algorithms improves savings • Using larger set of candidates also helps

  18. Tool Execution Time Comparison Better # of queries SystemA # of queries SystemA # of queries SystemB Scalable index tuning eliminates the workload selection problem

  19. Conclusion • Index tuning using a novel compact BIP • Generic, scalable, efficient, and high quality • Quality feedback • Incremental index selection • Multi-objective optimization • Future Work: • Incorporating other workload types • Applying the approach to other tuning problems

  20. Backup Sildes

  21. BIP for Multiple Plans Matching logic I1 I2 I3 I4 25 One plan per query 15 Minimize cost T1 T2 x23x24 x25x26

  22. More Complex BIPs Build indexes when used Size under a fixed constant Storage constraint

  23. CoPhy vs. FLP Better Offloading the search process to the solver improves both the problem construction and solving times

More Related