research-article

A storage advisor for hybrid-store databases

Authors:

Philipp Rösch,

Lars Dannecker,

Gregor HackenbroichAuthors Info & Claims

Proceedings of the VLDB Endowment, Volume 5, Issue 12

Pages 1748 - 1758

https://doi.org/10.14778/2367502.2367514

Published: 01 August 2012 Publication History

Abstract

With the SAP HANA database, SAP offers a high-performance in-memory hybrid-store database. Hybrid-store databases---that is, databases supporting row- and column-oriented data management---are getting more and more prominent. While the columnar management offers high-performance capabilities for analyzing large quantities of data, the row-oriented store can handle transactional point queries as well as inserts and updates more efficiently. To effectively take advantage of both stores at the same time the novel question whether to store the given data row- or column-oriented arises. We tackle this problem with a storage advisor tool that supports database administrators at this decision. Our proposed storage advisor recommends the optimal store based on data and query characteristics; its core is a cost model to estimate and compare query execution times for the different stores. Besides a per-table decision, our tool also considers to horizontally and vertically partition the data and manage the partitions on different stores. We evaluated the storage advisor for the use in the SAP HANA database; we show the recommendation quality as well as the benefit of having the data in the optimal store with respect to increased query performance.

References

[1]

S. Agrawal, S. Chaudhuri, L. Kollar, A. Marathe, V. Narasayya, and M. Syamala. Database Tuning Advisor for Microsoft SQL Server 2005: Demo. In Proceedings of the 2005 ACM SIGMOD International Conference on Management of Data, pages 930--932, 2005.

[2]

S. Agrawal, S. Chaudhuri, and V. R. Narasayya. Automated Selection of Materialized Views and Indexes in SQL Databases. In Proceedings of the 26th International Conference on Very Large Data Bases, pages 496--505, 2000.

[3]

S. Agrawal, V. Narasayya, and B. Yang. Integrating Vertical and Horizontal Partitioning Into Automated Physical Database Design. In Proceedings of the 2004 ACM SIGMOD International Conference on Management of Data, pages 359--370, 2004.

[4]

A. Ailamaki, D. J. DeWitt, M. D. Hill, and M. Skounakis. Weaving Relations for Cache Performance. In Proceedings of the 27th International Conference on Very Large Data Bases, pages 169--180, 2001.

[5]

P. A. Boncz, S. Manegold, and M. L. Kersten. Database Architecture Optimized for the new Bottleneck: Memory Access. In Proceedings of the 25th International Confernce on Very Large Data Bases, pages 54--65, 1999.

[6]

P. A. Boncz, M. Zukowski, and N. Nes. MonetDB/X100: Hyper-Pipelining Query Execution. In Proceedings of the 2nd Biennial Conference on Innovative Data Systems Research, pages 225--237, 2005.

[7]

S. Chaudhuri and V. R. Narasayya. An Efficient Cost-Driven Index Selection Tool for Microsoft SQL Server. In Proceedings of the 23rd International Conference on Very Large Data Bases, pages 146--155, 1997.

[8]

F. Faerber, S. K. Cha, J. Primsch, C. Bornhoevd, S. Sigg, and W. Lehner. SAP HANA Database - Data Management for Modern Business Applications. ACM Sigmod Record, 40(4):45--51, 2011.

[9]

M. Grund, J. Krüger, H. Plattner, A. Zeier, P. Cudre-Mauroux, and S. Madden. HYRISE: A Main Memory Hybrid Storage Engine. Proceedings of the 37th International Conference on Very Large Data Bases, pages 105--116, 2010.

[10]

H. Gupta and I. Mumick. Selection of Views to Materialize in a Data Warehouse. Proceedings of the 6th International Conference on Database Theory, pages 98--112, 1997.

[11]

Y. He, R. Lee, Y. Huai, Z. Shao, N. Jain, X. Zhang, and Z. Xu. RCFile: A Fast and Space-efficient Data Placement Structure in MapReduce-based Warehouse Systems. In In Proceedings of the 27th International Conference on Data Engineering, pages 1199--1208, 2011.

[12]

C. Lemke, K.-U. Sattler, F. Faerber, and A. Zeier. Speeding Up Queries in Column Stores: A Case for Compression. In Proceedings of the 12th International Conference on Data Warehousing and Knowledge Discovery, pages 117--129, 2010.

[13]

H. Plattner. A Common Database Approach for OLTP and OLAP Using an In-Memory Column Database. In Proceedings of the 35th SIGMOD international conference on Management of data, pages 1--2, 2009.

[14]

R. Ramamurthy, D. J. DeWitt, and Q. Su. A Case for Fractured Mirrors. In Proceedings of the 28th International Conference on Very Large Databases, pages 89--101, 2002.

[15]

V. Raman, G. Swart, L. Qiao, F. Reiss, V. Dialani, D. Kossmann, I. Narang, and R. Sidle. Constant-Time Query Processing. In Proceedings of the 24th International Conference on Data Engineering, pages 60--69, 2008.

[16]

R. Winter. Scaling The Data Warehouse. InformationWeek: Research & Reports, http://www.informationweek.com/news/211100262, 2008.

[17]

D. C. Zilio, J. Rao, S. Lightstone, G. Lohman, A. Storm, C. Garcia-Arellano, and S. Fadden. DB2 Design Advisor: Integrated Automatic Physical Database Design. In Proceedings of the 30th International Conference on Very Large Data Bases, pages 1087--1097, 2004.

Cited By

Nishikawa NFujiwara SHayamizu YGoda K(2023)Physical Database Design for Manufacturing Business Analytics2023 IEEE International Conference on Big Data (BigData)10.1109/BigData59044.2023.10386475(1793-1802)Online publication date: 15-Dec-2023
https://doi.org/10.1109/BigData59044.2023.10386475
Li LPu FLi YXu J(2023)A Comparative Study of Row and Column Storage for Time Series DataSpatial Data and Intelligence10.1007/978-3-031-32910-4_16(223-238)Online publication date: 13-Apr-2023
https://dl.acm.org/doi/10.1007/978-3-031-32910-4_16
Martin BDavis K(2021)Multi-Temperate Logical Data Warehouse Design for Large-Scale Healthcare DataBig Data Research10.1016/j.bdr.2021.10025525:COnline publication date: 15-Jul-2021
https://dl.acm.org/doi/10.1016/j.bdr.2021.100255
Show More Cited By

A storage advisor for hybrid-store databases
1. Information systems
  1. Data management systems
    1. Database management system engines
2. Theory of computation
  1. Theory and algorithms for application domains
    1. Database theory

Recommendations

A data management method for databases using hybrid storage systems

When applications require high I/O performance, solid-state drives (SSDs) are often preferable because they perform better than traditional hard-disk drives (HDDs). Therefore, database system response time can be improved by moving frequently used data ...
Finding the best between the column store and row store Databases
ICIST '20: Proceedings of the 10th International Conference on Information Systems and Technologies

Row store databases are unavoidable to manage data in Information Systems. However, Web growth and consumer high-connectivity generate incredible amount of data and change ways to manage it. In a matter of fact, traditional Row Stores hardly satisfy new ...
LSM-tree managed storage for large-scale key-value store
SoCC '17: Proceedings of the 2017 Symposium on Cloud Computing

Key-value stores are increasingly adopting LSM-trees as their enabling data structure in the backend storage, and persisting their clustered data through a file system. A file system is expected to not only provide file/directory abstraction to organize ...

Comments

Please enable JavaScript to view thecomments powered by Disqus.

Information & Contributors

Information

Published In

cover image Proceedings of the VLDB Endowment

Proceedings of the VLDB Endowment Volume 5, Issue 12

August 2012

340 pages

ISSN:2150-8097

Issue’s Table of Contents

Publisher

VLDB Endowment

Publication History

Published: 01 August 2012

Published in PVLDB Volume 5, Issue 12

Qualifiers

Research-article

Contributors

Other Metrics

View Article Metrics

Bibliometrics & Citations

Bibliometrics

Article Metrics

19
Total Citations
View Citations
336
Total Downloads

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

Reflects downloads up to 03 Mar 2025

Other Metrics

View Author Metrics

Citations

Cited By

Nishikawa NFujiwara SHayamizu YGoda K(2023)Physical Database Design for Manufacturing Business Analytics2023 IEEE International Conference on Big Data (BigData)10.1109/BigData59044.2023.10386475(1793-1802)Online publication date: 15-Dec-2023
https://doi.org/10.1109/BigData59044.2023.10386475
Li LPu FLi YXu J(2023)A Comparative Study of Row and Column Storage for Time Series DataSpatial Data and Intelligence10.1007/978-3-031-32910-4_16(223-238)Online publication date: 13-Apr-2023
https://dl.acm.org/doi/10.1007/978-3-031-32910-4_16
Martin BDavis K(2021)Multi-Temperate Logical Data Warehouse Design for Large-Scale Healthcare DataBig Data Research10.1016/j.bdr.2021.10025525:COnline publication date: 15-Jul-2021
https://dl.acm.org/doi/10.1016/j.bdr.2021.100255
Hamirwasia VKarlapalem KValluri S(2019)FASTProceedings of Real-Time Business Intelligence and Analytics10.1145/3350489.3350490(1-10)Online publication date: 26-Aug-2019
https://dl.acm.org/doi/10.1145/3350489.3350490
Durand GPinnecke MPiriyev RMohsen MBroneske DSaake GSekeran MRodriguez FBalami L(2018)GridFormationProceedings of the First International Workshop on Exploiting Artificial Intelligence Techniques for Data Management10.1145/3211954.3211956(1-7)Online publication date: 10-Jun-2018
https://dl.acm.org/doi/10.1145/3211954.3211956
Boissier MSchlosser RUflacker M(2018)Hybrid Data Layouts for Tiered HTAP Databases with Pareto-Optimal Data Placements2018 IEEE 34th International Conference on Data Engineering (ICDE)10.1109/ICDE.2018.00028(209-220)Online publication date: Apr-2018
https://doi.org/10.1109/ICDE.2018.00028
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
Kester MAthanassoulis MIdreos SChirkova RYang JSuciu D(2017)Access Path Selection in Main-Memory Optimized Data SystemsProceedings of the 2017 ACM International Conference on Management of Data10.1145/3035918.3064049(715-730)Online publication date: 9-May-2017
https://dl.acm.org/doi/10.1145/3035918.3064049
Pinnecke MBroneske DDurand GSaake G(2017)Are Databases Fit for Hybrid Workloads on GPUs? A Storage Engine's Perspective2017 IEEE 33rd International Conference on Data Engineering (ICDE)10.1109/ICDE.2017.237(1599-1606)Online publication date: Apr-2017
https://doi.org/10.1109/ICDE.2017.237
Vogt MStiemer ASchuldt H(2017)Icarus: Towards a multistore database system2017 IEEE International Conference on Big Data (Big Data)10.1109/BigData.2017.8258207(2490-2499)Online publication date: Dec-2017
https://doi.org/10.1109/BigData.2017.8258207
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 Article

View options

PDF

View or Download as a PDF file.

eReader

View online with eReader.

Figures

Tables

Media

View Issue’s Table of Contents