demonstration

An automated, yet interactive and portable DB designer

Authors:

Neoklis PolyzotisAuthors Info & Claims

SIGMOD '10: Proceedings of the 2010 ACM SIGMOD International Conference on Management of data

Pages 1183 - 1186

https://doi.org/10.1145/1807167.1807314

Published: 06 June 2010 Publication History

Get Access

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.

References

[1]

S. Agrawal et al. Database Tuning Advisor for Microsoft SQL Server 2005. In Proceedings of the International Conference on Very Large Databases (VLDB), 2004.

Crossref

Google Scholar

[2]

N. Bruno and S. Chaudhuri. An Online Approach to Physical Design Tuning. ICDE'07.

Google Scholar

[3]

N. Bruno and Surajit Chaudhuri. Automatic physical database tuning: a relaxation-based approach. In Proceedings of the SIGMOD Conference, 2005.

Digital Library

Google Scholar

[4]

D. Dash, A. Ailamaki. CoPhy: Automated Physical Design with Quality Guarantees. Technical Report CMU-CS-10-109.

Google Scholar

[5]

S. Finkelstein, M. Schkolnick,P. Tiberio: Physical database design for relational databases. ACM ToDS. 1988.

Digital Library

Google Scholar

[6]

Kao, K., Liao, I. 2009. An index selection method without repeated optimizer estimations. Inf. Sci. 179, 13 (Jun. 09)

Digital Library

Google Scholar

[7]

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

[8]

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.

Digital Library

Google Scholar

[9]

S. Papadomanolakis, D. Dash, A. Ailamaki. Efficient Use of the Query Optimizer for Automated Physical Design. VLDB 2007.

Digital Library

Google Scholar

[10]

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.pdf

Google Scholar

[11]

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.

Digital Library

Google Scholar

[12]

K. Schnaitter, N. Polyzotis, L. Getoor: Index Interactions in Physical Design Tuning: Modeling, Analysis, and Applications. PVLDB 2(1): 1234--1245 (2009).

Digital Library

Google Scholar

[13]

Sloan Digital Sky Survey, http://www.sdss.org/

Google Scholar

[14]

D. C. Zilio, J. Rao, et al. DB2 Design Advisor: Integrated Automatic Physical Data-base Design. VLDB'04.

Digital Library

Google Scholar

Cited By

View all

Mozafari BBurcuta RCabrera AConstantin AFrancis DGrömling DJindal AKonkolowicz MMarian Spac VPark YCarranzo RRichardson NRoy ASrivastava ATarte IWestphal BZhang CDas SPandis ISelçuk Candan KAmer-Yahia S(2023)Making Data Clouds Smarter at Keebo: Automated Warehouse Optimization using Data LearningCompanion of the 2023 International Conference on Management of Data10.1145/3555041.3589681(239-251)Online publication date: 4-Jun-2023
https://dl.acm.org/doi/10.1145/3555041.3589681
Chernishev G(2017)The design of an adaptive column-store systemJournal of Big Data10.1186/s40537-017-0069-44:1Online publication date: 23-Mar-2017
https://doi.org/10.1186/s40537-017-0069-4
Bobrov NChernishev GGrigoriev DNovikov B(2017)An Evaluation of TANE Algorithm for Functional Dependency DetectionModel and Data Engineering10.1007/978-3-319-66854-3_16(208-222)Online publication date: 6-Sep-2017
https://doi.org/10.1007/978-3-319-66854-3_16
Show More Cited By

Index Terms

An automated, yet interactive and portable DB designer
1. Information systems
  1. Data management systems
    1. Data structures
      1. Data access methods

Recommendations

OLE DB: A Component DBMS Architecture
ICDE '96: Proceedings of the Twelfth International Conference on Data Engineering

The 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 annual ACM Southeast Conference

NoSQL 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 ...
PRISMA/DB: A Parallel, Main Memory Relational DBMS

PRISMA/DB, a full-fledged parallel, main memory relational database management system (DBMS) is described. PRISMA/DB's high performance is obtained by the use of parallelism for query processing and main memory storage of the entire database. A flexible ...

Comments

Please enable JavaScript to view thecomments powered by Disqus.

Information & Contributors

Information

Published In

SIGMOD '10: Proceedings of the 2010 ACM SIGMOD International Conference on Management of data

June 2010

1286 pages

ISBN:9781450300322

DOI:10.1145/1807167

General Chair:
Ahmed Elmagarmid
Purdue University, USA
,
Program Chair:
Divyakant Agrawal
University of California at Santa Barbara, USA

Permission to make digital or hard copies of all or part of this work for personal or classroom use is granted without fee provided that copies are not made or distributed for profit or commercial advantage and that copies bear this notice and the full citation on the first page. Copyrights for components of this work owned by others than ACM must be honored. Abstracting with credit is permitted. To copy otherwise, or republish, to post on servers or to redistribute to lists, requires prior specific permission and/or a fee. Request permissions from [email protected]

Publisher

Association for Computing Machinery

New York, NY, United States

Publication History

Published: 06 June 2010

Permissions

Request permissions for this article.

Request Permissions

Check for updates

Author Tags

Qualifiers

Demonstration

Conference

SIGMOD/PODS '10

Sponsor:

SIGMOD

SIGMOD/PODS '10: International Conference on Management of Data

June 6 - 10, 2010

Indiana, Indianapolis, USA

Acceptance Rates

Overall Acceptance Rate 785 of 4,003 submissions, 20%

Contributors

Other Metrics

View Article Metrics

Bibliometrics & Citations

Bibliometrics

Article Metrics

7
Total Citations
View Citations
282
Total Downloads

Downloads (Last 12 months)2
Downloads (Last 6 weeks)0

Reflects downloads up to 13 Dec 2024

Other Metrics

View Author Metrics

Citations

Cited By

View all

Mozafari BBurcuta RCabrera AConstantin AFrancis DGrömling DJindal AKonkolowicz MMarian Spac VPark YCarranzo RRichardson NRoy ASrivastava ATarte IWestphal BZhang CDas SPandis ISelçuk Candan KAmer-Yahia S(2023)Making Data Clouds Smarter at Keebo: Automated Warehouse Optimization using Data LearningCompanion of the 2023 International Conference on Management of Data10.1145/3555041.3589681(239-251)Online publication date: 4-Jun-2023
https://dl.acm.org/doi/10.1145/3555041.3589681
Chernishev G(2017)The design of an adaptive column-store systemJournal of Big Data10.1186/s40537-017-0069-44:1Online publication date: 23-Mar-2017
https://doi.org/10.1186/s40537-017-0069-4
Bobrov NChernishev GGrigoriev DNovikov B(2017)An Evaluation of TANE Algorithm for Functional Dependency DetectionModel and Data Engineering10.1007/978-3-319-66854-3_16(208-222)Online publication date: 6-Sep-2017
https://doi.org/10.1007/978-3-319-66854-3_16
Mozafari BGoh EYoon DSellis TDavidson SIves Z(2015)CliffGuardProceedings of the 2015 ACM SIGMOD International Conference on Management of Data10.1145/2723372.2749454(1167-1182)Online publication date: 27-May-2015
https://dl.acm.org/doi/10.1145/2723372.2749454
Chernishev G(2015)Towards Self-management in a Distributed Column-Store SystemNew Trends in Databases and Information Systems10.1007/978-3-319-23201-0_12(97-107)Online publication date: 28-Aug-2015
https://doi.org/10.1007/978-3-319-23201-0_12
Garcia-Alvarado CRaghavan VNarayanan SWaas F(2012)Automatic Data Placement in MPP DatabasesProceedings of the 2012 IEEE 28th International Conference on Data Engineering Workshops10.1109/ICDEW.2012.45(322-327)Online publication date: 1-Apr-2012
https://dl.acm.org/doi/10.1109/ICDEW.2012.45
Jindal ADittrich J(2012)Relax and Let the Database Do the Partitioning OnlineEnabling Real-Time Business Intelligence10.1007/978-3-642-33500-6_5(65-80)Online publication date: 2012
https://doi.org/10.1007/978-3-642-33500-6_5

View Options

Login options

Check if you have access through your login credentials or your institution to get full access on this article.

Cited By

Index Terms

Recommendations

OLE DB: A Component DBMS Architecture

Comparing NoSQL MongoDB to an SQL DB

PRISMA/DB: A Parallel, Main Memory Relational DBMS