tutorial

Adaptive indexing in modern database kernels

Authors:

Stratos Idreos,

Stefan Manegold,

Goetz GraefeAuthors Info & Claims

EDBT '12: Proceedings of the 15th International Conference on Extending Database Technology

Pages 566 - 569

https://doi.org/10.1145/2247596.2247667

Published: 27 March 2012 Publication History

Abstract

Physical design represents one of the hardest problems for database management systems. Without proper tuning, systems cannot achieve good performance. Offline indexing creates indexes a priori assuming good workload knowledge and idle time. More recently, online indexing monitors the workload trends and creates or drops indexes online. Adaptive indexing takes another step towards completely automating the tuning process of a database system, by enabling incremental and partial online indexing. The main idea is that physical design changes continuously, adaptively, partially, incrementally and on demand while processing queries as part of the execution operators. As such it brings a plethora of opportunities for rethinking and improving every single corner of database system design.

We will analyze the indexing space between offline, online and adaptive indexing through several state of the art indexing techniques, e. g., what-if analysis and soft indexes. We will discuss in detail adaptive indexing techniques such as database cracking, adaptive merging, sideways cracking and various hybrids that try to balance the online tuning overhead with the convergence speed to optimal performance. In addition, we will discuss how various aspects of modern techniques for database architectures, such as vectorization, bulk processing, column-store execution and storage affect adaptive indexing. Finally, we will discuss several open research topics towards fully automomous database kernels.

References

[1]

S. Agrawal et al. Database Tuning Advisor for Microsoft SQL Server. VLDB 2004.

[2]

N. Bruno and S. Chaudhuri. To Tune or not to Tune? A Lightweight Physical Design Alerter. VLDB 2006.

Digital Library

[3]

N. Bruno and S. Chaudhuri. An online approach to physical design tuning. In ICDE, 2007.

[4]

N. Bruno and S. Chaudhuri. Physical design refinement: the 'merge-reduce' approach. ACM TODS, 32(4):28:1--28:41, 2007.

Digital Library

[5]

S. Chaudhuri and V. Narasayya. An efficient cost-driven index selection tool for Microsoft SQL Server. VLDB. 1997.

Digital Library

[6]

S. Chaudhuri and V. R. Narasayya. Self-tuning database systems: A decade of progress. VLDB, pages 3--14, 2007.

Digital Library

[7]

S. J. Finkelstein, M. Schkolnick, and P. Tiberio. Physical database design for relational databases. ACM TODS, 13(1):91--128, 1988.

Digital Library

[8]

G. Graefe, S. Idreos, H. Kuno, and S. Manegold. Benchmarking adaptive indexing. TPCTC, pages 169--184, 2010.

Digital Library

[9]

G. Graefe and H. Kuno. Adaptive indexing for relational keys. SMDB, pages 69--74, 2010.

[10]

G. Graefe and H. Kuno. Self-selecting, self-tuning, incrementally optimized indexes. EDBT, pages 371--381, 2010.

Digital Library

[11]

T. Härder. Selecting an optimal set of secondary indices. Lecture Notes in Computer Science, 44:146--160, 1976.

Digital Library

[12]

S. Idreos, M. L. Kersten, and S. Manegold. Database cracking. CIDR, pages 68--78, 2007.

[13]

S. Idreos, M. L. Kersten, and S. Manegold. Updating a cracked database. SIGMOD, pages 413--424, 2007.

Digital Library

[14]

S. Idreos, M. L. Kersten, and S. Manegold. Self-organizing tuple reconstruction in column stores. SIGMOD, pages 297--308, 2009.

Digital Library

[15]

S. Idreos, S. Manegold, H. Kuno, and G. Graefe. Merging what's cracked, cracking what's merged: Adaptive indexing in main-memory column-stores. PVLDB, 2011.

Digital Library

[16]

M. Lühring, K.-U. Sattler, K. Schmidt, and E. Schallehn. Autonomous management of soft indexes. SMDB, pages 450--458, 2007.

Digital Library

[17]

A. N. S. Praveen Seshadri. Generalized partial indexes. ICDE, pages 420--427, 1995.

Digital Library

[18]

K. Schnaitter et al. COLT: Continuous On-Line Database Tuning. SIGMOD 2006.

Digital Library

[19]

M. Stonebraker. The case for partial indexes. SIGMOD Record, 18(4):4--11, 1989.

Digital Library

[20]

G. Valentin et al. DB2 Advisor: An Optimizer Smart Enough to Recommend Its Own Indexes. ICDE. 2000.

[21]

D. C. Zilio et al. DB2 Design Advisor: Integrated Automatic Physical Database Design. VLDB 2004.

Digital Library

Cited By

Kvet MKvet M(2024)Top data analysis performance – case study2024 IEEE 22nd World Symposium on Applied Machine Intelligence and Informatics (SAMI)10.1109/SAMI60510.2024.10432792(000271-000276)Online publication date: 25-Jan-2024
https://doi.org/10.1109/SAMI60510.2024.10432792
Kvet MKvet MPukalík PSudora FBušik MTvrdý M(2024)Information System for Police Force: Application for Analyzing Traffic Accidents Data2024 International Conference on Emerging eLearning Technologies and Applications (ICETA)10.1109/ICETA63795.2024.10850737(389-394)Online publication date: 24-Oct-2024
https://doi.org/10.1109/ICETA63795.2024.10850737
Wei YWang HJin P(2024)Optimizing the B+tree Index with Hotness Awareness and AdaptivityAdvanced Intelligent Computing Technology and Applications10.1007/978-981-97-5581-3_29(356-367)Online publication date: 1-Aug-2024
https://doi.org/10.1007/978-981-97-5581-3_29
Show More Cited By

Adaptive indexing in modern database kernels
1. Information systems
  1. Data management systems
  2. Information retrieval
    1. Search engine architectures and scalability
2. Theory of computation
  1. Theory and algorithms for application domains
    1. Database theory

Recommendations

Expert Indexing in Oracle Database 11g: Maximum Performance for your Database
Modern Database Management
Indexing on modern hardware: hekaton and beyond
SIGMOD '14: Proceedings of the 2014 ACM SIGMOD International Conference on Management of Data

Recent OLTP support exploits new techniques, running on modern hardware, to achieve unprecedented performance compared with prior approaches. In SQL Server, the Hekaton main-memory database engine embodies this new OLTP support. Hekaton uses the Bw-tree ...

Comments

Information & Contributors

Information

Published In

cover image ACM Other conferences

EDBT '12: Proceedings of the 15th International Conference on Extending Database Technology

March 2012

643 pages

ISBN:9781450307901

DOI:10.1145/2247596

Editors:
Elke Rundensteiner
Worcester Polytechnic Institute
,
Volker Markl
Technische Universität Berlin, Germany
,
Ioana Manolescu
INRIA, France
,
Sihem Amer-Yahia
QCRI, Doha, Qatar
,
Felix Naumann
Hasso Plattner Institute, Potsdam, Germany
,
Ismail Ari
Ozyegin University, Turkey

Copyright © 2012 Authors.

Publisher

Association for Computing Machinery

New York, NY, United States

Publication History

Published: 27 March 2012

Permissions

Request permissions for this article.

Request Permissions

Check for updates

Qualifiers

Tutorial

Conference

EDBT '12

EDBT '12: 15th International Conference on Extending Database Technology

March 27 - 30, 2012

Berlin, Germany

Acceptance Rates

Overall Acceptance Rate 7 of 10 submissions, 70%

Contributors

Other Metrics

View Article Metrics

Bibliometrics & Citations

Bibliometrics

Article Metrics

13
Total Citations
View Citations
335
Total Downloads

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

Reflects downloads up to 11 Feb 2025

Other Metrics

View Author Metrics

Citations

Cited By

Kvet MKvet M(2024)Top data analysis performance – case study2024 IEEE 22nd World Symposium on Applied Machine Intelligence and Informatics (SAMI)10.1109/SAMI60510.2024.10432792(000271-000276)Online publication date: 25-Jan-2024
https://doi.org/10.1109/SAMI60510.2024.10432792
Kvet MKvet MPukalík PSudora FBušik MTvrdý M(2024)Information System for Police Force: Application for Analyzing Traffic Accidents Data2024 International Conference on Emerging eLearning Technologies and Applications (ICETA)10.1109/ICETA63795.2024.10850737(389-394)Online publication date: 24-Oct-2024
https://doi.org/10.1109/ICETA63795.2024.10850737
Wei YWang HJin P(2024)Optimizing the B+tree Index with Hotness Awareness and AdaptivityAdvanced Intelligent Computing Technology and Applications10.1007/978-981-97-5581-3_29(356-367)Online publication date: 1-Aug-2024
https://doi.org/10.1007/978-981-97-5581-3_29
Kvet M(2023)Referencing validity assignment using B+tree index enhancements2023 World Symposium on Digital Intelligence for Systems and Machines (DISA)10.1109/DISA59116.2023.10308931(145-153)Online publication date: 21-Sep-2023
https://doi.org/10.1109/DISA59116.2023.10308931
Balakrishnan DZiarek LKennedy OCheung ANguyễn K(2019)Fluid data structuresProceedings of the 17th ACM SIGPLAN International Symposium on Database Programming Languages10.1145/3315507.3330197(3-17)Online publication date: 23-Jun-2019
https://dl.acm.org/doi/10.1145/3315507.3330197
Ding BDas SMarcus RWu WChaudhuri SNarasayya VBoncz PManegold SAilamaki ADeshpande AKraska T(2019)AI Meets AIProceedings of the 2019 International Conference on Management of Data10.1145/3299869.3324957(1241-1258)Online publication date: 25-Jun-2019
https://dl.acm.org/doi/10.1145/3299869.3324957
Li FWu BYi KZhao Z(2019)Wander Join and XDBACM Transactions on Database Systems10.1145/328455144:1(1-41)Online publication date: 29-Jan-2019
https://dl.acm.org/doi/10.1145/3284551
Mottin DMüller EChirkova RYang JSuciu D(2017)Graph ExplorationProceedings of the 2017 ACM International Conference on Management of Data10.1145/3035918.3054778(1737-1740)Online publication date: 9-May-2017
https://dl.acm.org/doi/10.1145/3035918.3054778
El-Fadly HSallam EShoaib M(2017)Incremental branching adaptive radix tree2017 12th International Conference on Computer Engineering and Systems (ICCES)10.1109/ICCES.2017.8275358(493-499)Online publication date: Dec-2017
https://doi.org/10.1109/ICCES.2017.8275358
Liu HLiu Z(2017)Adaptive indexing approach for main memory column storeThe Journal of Engineering10.1049/joe.2016.00682017:2(26-32)Online publication date: 23-Feb-2017
https://doi.org/10.1049/joe.2016.0068
Show More Cited By

View Options

Login options

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

Full Access

Get this Publication

View options

PDF

View or Download as a PDF file.

eReader

View online with eReader.

Figures

Tables

Media

View Table of Conten