ABSTRACT
Tuning tools attempt to configure a database to achieve optimal performance for a given workload. Selecting an optimal set of physical structures is computationally hard since it involves searching a vast space of possible configurations. Commercial DBMSs offer tools that can address this problem. The usefulness of such tools, however, is limited by their dependence on greedy heuristics, the need for a-priori (offline) knowledge of the workload, and lack of an optimal materialization schedule to get the best out of suggested design features. Moreover, the open source DBMSs do not provide any automated tuning tools.
This demonstration introduces a comprehensive physical designer for the PostgreSQL open source DBMS. The tool suggests design features for both offline and online workloads. It provides close to optimal suggestions for indexes for a given workload by modeling the problem as a combinatorial optimization problem and solving it by sophisticated and mature solvers. It also determines the interaction between indexes to suggest an effective materialization strategy for the selected indexes. The tool is interactive as it allows the database administrator (DBA) to suggest a set of candidate features and shows their benefits and interactions visually. For the demonstration we use large real-world scientific datasets and query workloads.
- S. Agrawal et al. Database Tuning Advisor for Microsoft SQL Server 2005. In Proceedings of the International Conference on Very Large Databases (VLDB), 2004.Google ScholarCross Ref
- N. Bruno and S. Chaudhuri. An Online Approach to Physical Design Tuning. ICDE'07.Google Scholar
- N. Bruno and Surajit Chaudhuri. Automatic physical database tuning: a relaxation-based approach. In Proceedings of the SIGMOD Conference, 2005. Google ScholarDigital Library
- D. Dash, A. Ailamaki. CoPhy: Automated Physical Design with Quality Guarantees. Technical Report CMU-CS-10-109.Google Scholar
- S. Finkelstein, M. Schkolnick,P. Tiberio: Physical database design for relational databases. ACM ToDS. 1988. Google ScholarDigital Library
- Kao, K., Liao, I. 2009. An index selection method without repeated optimizer estimations. Inf. Sci. 179, 13 (Jun. 09) Google ScholarDigital Library
- Monteiro, J. M., Lifschitz, S. and Brayner, A.: An Architecture for Automated Index Tuning. In V Ph.D. and M.S. SBBD, 2006.Google Scholar
- S. Papadomanolakis, A. Ailamaki, AutoPart: Automating Schema Design for Large Scientific Databases Using Data Partitioning, 6th International Conference on Scientific and Statistical Database Management (SSDBM'04), 2004. Google ScholarDigital Library
- S. Papadomanolakis, D. Dash, A. Ailamaki. Efficient Use of the Query Optimizer for Automated Physical Design. VLDB 2007. Google ScholarDigital Library
- Performance Tuning using the SQLAccess Advisor. http://www.oracle.com/technology/products/bi/db/10g/pdf/twp_general_perf_tuning_using_sqlaccess_advisor_10gr1_1203.pdfGoogle Scholar
- K. Schnaitter, S. Abiteboul, T. Milo, and N. Polyzotis. Colt: continuous on-line tuning. In proceedings of the 2006 ACM SIGMOD, pages 793--795, 2006. Google ScholarDigital Library
- K. Schnaitter, N. Polyzotis, L. Getoor: Index Interactions in Physical Design Tuning: Modeling, Analysis, and Applications. PVLDB 2(1): 1234--1245 (2009). Google ScholarDigital Library
- Sloan Digital Sky Survey, http://www.sdss.org/Google Scholar
- D. C. Zilio, J. Rao, et al. DB2 Design Advisor: Integrated Automatic Physical Data-base Design. VLDB'04. Google ScholarDigital Library
Comments