Ioannis Alagiannis
Neoklis Polyzotis
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 Scholar
Cross 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
Index Terms
- An automated, yet interactive and portable DB designer
Recommendations
OLE DB: A Component DBMS Architecture
ICDE '96: Proceedings of the Twelfth International Conference on Data EngineeringThe article describes an effort at Microsoft whose primary goal is to enable applications to have uniform access to data stored in diverse DBMS and non DBMS information containers. Applications continue to take advantage of the benefits of database ...
Comparing NoSQL MongoDB to an SQL DB
ACMSE '13: Proceedings of the 51st ACM Southeast ConferenceNoSQL database solutions are becoming more and more prevalent in a world currently dominated by SQL relational databases. NoSQL databases were designed to provide database solutions for large volumes of data that is not structured. However, the ...
Proactive database index tuning through data threshold prediction
ACM-SE 47: Proceedings of the 47th Annual Southeast Regional ConferenceFor queries to be efficiently processed in a database, the query optimizer must be presented with an efficient set of indexes for the query workload. Due to the dynamic and complex nature of modern database workloads, database administrators and ...
Comments