research-article

OLTP through the looking glass, and what we found there

Authors:

Stavros Harizopoulos,

Daniel J. Abadi,

Michael StonebrakerAuthors Info & Claims

SIGMOD '08: Proceedings of the 2008 ACM SIGMOD international conference on Management of data

Pages 981 - 992

https://doi.org/10.1145/1376616.1376713

Published: 09 June 2008 Publication History

Abstract

Online Transaction Processing (OLTP) databases include a suite of features - disk-resident B-trees and heap files, locking-based concurrency control, support for multi-threading - that were optimized for computer technology of the late 1970's. Advances in modern processors, memories, and networks mean that today's computers are vastly different from those of 30 years ago, such that many OLTP databases will now fit in main memory, and most OLTP transactions can be processed in milliseconds or less. Yet database architecture has changed little.

Based on this observation, we look at some interesting variants of conventional database systems that one might build that exploit recent hardware trends, and speculate on their performance through a detailed instruction-level breakdown of the major components involved in a transaction processing database system (Shore) running a subset of TPC-C. Rather than simply profiling Shore, we progressively modified it so that after every feature removal or optimization, we had a (faster) working system that fully ran our workload. Overall, we identify overheads and optimizations that explain a total difference of about a factor of 20x in raw performance. We also show that there is no single "high pole in the tent" in modern (memory resident) database systems, but that substantial time is spent in logging, latching, locking, B-tree, and buffer management operations.

References

[1]

Agrawal, R., Carey, M. J., and Livny, M. "Concurrency control performance modeling: alternatives and implications." ACM Trans. Database Syst. 12(4), Dec. 1987.

Digital Library

[2]

Aguilera, M., Merchant, A., Shah, M., Veitch, A. C., and Karamanolis, C. T. "Sinfonia: a new paradigm for building scalable distributed systems." In Proc. SOSP, 2007.

Digital Library

[3]

Aho, A. V., Hopcroft, J. E., and Ullman, J. D. "The Design and Analysis of Computer Algorithms." Addison-Wesley Publishing Company, 1974.

Digital Library

[4]

Ailamaki, A., DeWitt, D. J., Hill, M. .D., and Wood, D. A. "DBMSs on a Modern Processor: Where Does Time Go?" In Proc. VLDB, 1999, 266--277.

Digital Library

[5]

Ailamaki, A. "Database Architecture for New Hardware." Tutorial. In Proc. VLDB, 2004.

Digital Library

[6]

Anon et al. "A Measure of Transaction Processing Power." In Datamation, February 1985.

[7]

Baulier, J. D., Bohannon, P., Khivesara, A., et al. "The DataBlitz Main-Memory Storage Manager: Architecture, Performance, and Experience." In The VLDB Journal, 1998.

[8]

Bitton, D., DeWitt, D. J., and Turbyfill, C. "Benchmarking Database Systems, a Systematic Approach." In Proc. VLDB, 1983.

Digital Library

[9]

Bitton, D., Hanrahan, M., and Turbyfill, C. "Performance of Complex Queries in Main Memory Database Systems." In Proc. ICDE, 1987.

Digital Library

[10]

Boncz, P. A., Manegold, S., and Kersten, M. L. "Database Architecture Optimized for the New Bottleneck: Memory Access." In Proc. VLDB, 1999.

Digital Library

[11]

Brewer, E. A. "Towards robust distributed systems (abstract)." In Proc. PODC, 2000.

Digital Library

[12]

Bugnion, E., Devine, S., and Rosenblum, M. "Disco: running commodity operating systems on scalable multiprocessors." In Proc. SOSP, 1997.

Digital Library

[13]

Carey, M. J., DeWitt, D. J., Franklin, M. J. et al. "Shoring up persistent applications." In Proc. SIGMOD, 1994.

Digital Library

[14]

Chang, F., Dean, J., Ghemawat, S., Hsieh, W. C., Wallach, D. A., Burrows, M., Chandra, T., Fikes, A., and Gruber, R. E. "Bigtable: A Distributed Storage System for Structured Data." In Proc. OSDI, 2006.

Digital Library

[15]

Dean, J. and Ghemawat, S. "MapReduce: Simplified Data Processing on Large Clusters." In Proc. OSDI, 2004.

Digital Library

[16]

DeCandia, G., Hastorun, D., Jampani, M., Kakulapati, G., Lakshman, A., Pilchin, A., Sivasubramanian, S., Vosshall, P., and Vogels, W. "Dynamo: amazon's highly available key-value store." In Proc. SOSP, 2007.

Digital Library

[17]

DeWitt, D. J., Ghandeharizadeh, S., Schneider, D. A., Bricker, A., Hsiao, H., and Rasmussen, R. "The Gamma Database Machine Project." IEEE Transactions on Knowledge and Data Engineering 2(1):44--62, March 1990.

Digital Library

[18]

Eich, M. H. "MARS: The Design of A Main Memory Database Machine." In Proc. of the 1987 International workshop on Database Machines, October, 1987.

Digital Library

[19]

Garcia-Molina, H. and Salem, K. "Main Memory Database Systems: An Overview." IEEE Trans. Knowl. Data Eng. 4(6): 509--516 (1992).

Digital Library

[20]

Gray, J. and Reuter, A. "Transaction Processing: Concepts and Techniques." Morgan Kaufmann Publishers, Inc., 1993.

Digital Library

[21]

Gribble, S. D., Brewer, E. A., Hellerstein, J. M., and Culler, D .E. "Scalable, Distributed Data Structures for Internet Service Construction." In Proc. OSDI, 2000.

Digital Library

[22]

Helland, P. "Life beyond Distributed Transactions: an Apostate's Opinion." In Proc. CIDR, 2007.

[23]

Herlihy, M. P. and Moss, J. E. B. "Transactional Memory: architectural support for lock-free data structures." In Proc. ISCA, 1993.

Digital Library

[24]

Kung, H. T. and Robinson, J. T. "On optimistic methods for concurrency control." ACM Trans. Database Syst. 6(2):213--226, June 1981.

Digital Library

[25]

Lau, E. and Madden, S. "An Integrated Approach to Recovery and High Availability in an Updatable, Distributed Data Warehouse." In Proc. VLDB, 2006.

Digital Library

[26]

Lehman, T. J. and Carey, M. J. "A study of index structures for main memory database management systems." In Proc. VLDB, 1986.

Digital Library

[27]

Liskov, B., Ghemawat, S., Gruber, R., Johnson, P., Shrira, L., and Williams, M. "Replication in the harp file system." In Proc. SOSP, pages 226--238, 1991.

Digital Library

[28]

McWherter, D. T., Schroeder, B., Ailamaki, A., and Harchol-Balter, M. "Priority Mechanisms for OLTP and Transactional Web Applications." In Proc. ICDE, 2004.

Digital Library

[29]

Mohan, C., Haderle, D., Lindsay, B., Pirahesh, H., and Schwarz, P. "ARIES: a transaction recovery method supporting fine-granularity locking and partial rollbacks using write-ahead logging." ACM Trans. Database Syst. 17(1):94--162, 1992.

Digital Library

[30]

Mohan, C. "ARIES/KVL: A Key-Value Locking Method for Concurrency Control of Multiaction Transactions Operating on B-Tree Indexes." 1989, Research Report RJ 7008, Data Base Technology Institute, IBM Almaden Research Center.

[31]

Mohan, C. and Levine, F. "ARIES/IM: An Efficient and High Concurrency Index Management Method Using Write-Ahead Logging." 1989, Research Report RJ 6846, Data Base Technology Institute, IBM Almaden Research Center.

[32]

Mucci, P. J., Browne, S., Deane, C., and Ho, G. "PAPI: A Portable Interface to Hardware Performance Counters." In Proc. Department of Defense HPCMP Users Group Conference, Monterey, CA, June 1999.

[33]

Rao, J. and Ross, K. A. "Cache Conscious Indexing for Decision-Support in Main Memory." In Proc. VLDB, 1999.

Digital Library

[34]

Rao, J. and Ross, K. A. "Making B+- trees cache conscious in main memory." In SIGMOD Record, 29(2):475--486, June 2000.

Digital Library

[35]

Stoica, I., Morris, R., Karger, D. R., Kaashoek, M. F., and Balakrishnan, H. "Chord: A Scalable Peer-to-peer Lookup Protocol for Internet Applications." In Proc. SIGCOMM, 2001.

Digital Library

[36]

Stonebraker, M., Abadi, D. J., Batkin, A., Chen, X., Cherniack, M., Ferreira, M., Lau, E., Lin, A., Madden, S., O'Neil, E., O'Neil, P., Rasin, A., Tran, N., and Zdonik, S. "C-Store: A Column-oriented DBMS." In Proc. VLDB, 2005.

Digital Library

[37]

Stonebraker, M., Madden, S., Abadi, D. J., Harizopoulos, S., Hachem, N., and Helland, P. "The End of an Architectural Era (It's Time for a Complete Rewrite)." In Proc. VLDB, 2007.

Digital Library

[38]

Oracle TimesTen. http://www.oracle.com/timesten/index.html. 2007.

[39]

The Transaction Processing Council. TPC-C Benchmark (Rev. 5.8.0), 2006. http://www.tpc.org/tpcc/spec/tpcc_current.pdf

[40]

Whitney, A., Shasha, D., and Apter, S. "High Volume Transaction Processing Without Concurrency Control, Two Phase Commit, SQL or C." In Proc. HPTPS, 1997.

Cited By

Li TChandramouli BMadden S(2024)Performant almost-latch-free data structures using epoch protection in more depthThe VLDB Journal10.1007/s00778-024-00859-8Online publication date: 17-Jun-2024
https://doi.org/10.1007/s00778-024-00859-8
Gessert FWingerath WRitter NGessert FWingerath WRitter N(2024)Transaktionale Semantik für global verteilte AnwendungenSchnelles und skalierbares Cloud-Datenmanagement10.1007/978-3-031-54388-3_6(141-159)Online publication date: 3-May-2024
https://doi.org/10.1007/978-3-031-54388-3_6
Lee BAn MLee S(2023)LRU-C: Parallelizing Database I/Os for Flash SSDsProceedings of the VLDB Endowment10.14778/3598581.359860516:9(2364-2376)Online publication date: 10-Jul-2023
https://dl.acm.org/doi/10.14778/3598581.3598605
Show More Cited By

Index Terms

OLTP through the looking glass, and what we found there

Recommendations

Hekaton: SQL server's memory-optimized OLTP engine
SIGMOD '13: Proceedings of the 2013 ACM SIGMOD International Conference on Management of Data

Hekaton is a new database engine optimized for memory resident data and OLTP workloads. Hekaton is fully integrated into SQL Server; it is not a separate system. To take advantage of Hekaton, a user simply declares a table memory optimized. Hekaton ...
OLTP through the looking glass, and what we found there
Making Databases Work

Online Transaction Processing (OLTP) databases include a suite of features---disk-resident B-trees and heap files, locking-based concurrency control, support for multi-threading---that were optimized for computer technology of the late 1970's. Advances ...
Micro-architectural Analysis of In-memory OLTP
SIGMOD '16: Proceedings of the 2016 International Conference on Management of Data

Micro-architectural behavior of traditional disk-based online transaction processing (OLTP) systems has been investigated extensively over thepast couple of decades. Results show that traditional OLTP mostly under-utilize the available micro-...

Comments

Information & Contributors

Information

Published In

cover image ACM Conferences

SIGMOD '08: Proceedings of the 2008 ACM SIGMOD international conference on Management of data

June 2008

1396 pages

ISBN:9781605581026

DOI:10.1145/1376616

General Chairs:
Laks V. S. Lakshmanan
University of British Columbia, Canada
,
Raymond T. Ng
University of British Columbia, Canada
,
Dennis Shasha
New York University, USA

Copyright © 2008 ACM.

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]

Sponsors

Publisher

Association for Computing Machinery

New York, NY, United States

Publication History

Published: 09 June 2008

Permissions

Request permissions for this article.

Request Permissions

Check for updates

Author Tags

Qualifiers

Research-article

Conference

SIGMOD/PODS '08

Sponsor:

SIGMOD/PODS '08: SIGMOD/PODS '08 - International Conference on Management of Data

June 9 - 12, 2008

Vancouver, Canada

Acceptance Rates

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

Contributors

Other Metrics

View Article Metrics

Bibliometrics & Citations

Bibliometrics

Article Metrics

175
Total Citations
View Citations
3,357
Total Downloads

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

Reflects downloads up to 10 Oct 2024

Other Metrics

View Author Metrics

Citations

Cited By

Li TChandramouli BMadden S(2024)Performant almost-latch-free data structures using epoch protection in more depthThe VLDB Journal10.1007/s00778-024-00859-8Online publication date: 17-Jun-2024
https://doi.org/10.1007/s00778-024-00859-8
Gessert FWingerath WRitter NGessert FWingerath WRitter N(2024)Transaktionale Semantik für global verteilte AnwendungenSchnelles und skalierbares Cloud-Datenmanagement10.1007/978-3-031-54388-3_6(141-159)Online publication date: 3-May-2024
https://doi.org/10.1007/978-3-031-54388-3_6
Lee BAn MLee S(2023)LRU-C: Parallelizing Database I/Os for Flash SSDsProceedings of the VLDB Endowment10.14778/3598581.359860516:9(2364-2376)Online publication date: 10-Jul-2023
https://dl.acm.org/doi/10.14778/3598581.3598605
Faria NPereira JAlonso AVilaça RKoning YNes N(2023)TiQuE: Improving the Transactional Performance of Analytical Systems for True Hybrid WorkloadsProceedings of the VLDB Endowment10.14778/3598581.359859816:9(2274-2288)Online publication date: 10-Jul-2023
https://dl.acm.org/doi/10.14778/3598581.3598598
Zhang ZHu HZhou XWang J(2022)StarryProceedings of the VLDB Endowment10.14778/3561261.356126816:1(77-89)Online publication date: 1-Sep-2022
https://dl.acm.org/doi/10.14778/3561261.3561268
Zhou XYu XGraefe GStonebraker M(2022)LotusProceedings of the VLDB Endowment10.14778/3551793.355184315:11(2939-2952)Online publication date: 1-Jul-2022
https://dl.acm.org/doi/10.14778/3551793.3551843
Freitag MKemper ANeumann T(2022)Memory-optimized multi-version concurrency control for disk-based database systemsProceedings of the VLDB Endowment10.14778/3551793.355183215:11(2797-2810)Online publication date: 1-Jul-2022
https://dl.acm.org/doi/10.14778/3551793.3551832
Mostafa JWehbi SChilingaryan SKopmann A(2022)SciTS: A Benchmark for Time-Series Databases in Scientific Experiments and Industrial Internet of ThingsProceedings of the 34th International Conference on Scientific and Statistical Database Management10.1145/3538712.3538723(1-11)Online publication date: 6-Jul-2022
https://dl.acm.org/doi/10.1145/3538712.3538723
Li TChandramouli BMadden S(2022)Performant Almost-Latch-Free Data Structures Using Epoch ProtectionProceedings of the 18th International Workshop on Data Management on New Hardware10.1145/3533737.3535091(1-10)Online publication date: 12-Jun-2022
https://dl.acm.org/doi/10.1145/3533737.3535091
Schmeißer JSchüle MLeis VNeumann TKemper A(2022)B2-Tree: Page-Based String Indexing in Concurrent EnvironmentsDatenbank-Spektrum10.1007/s13222-022-00409-y22:1(11-22)Online publication date: 21-Feb-2022
https://doi.org/10.1007/s13222-022-00409-y
Show More Cited By

View Options

Get Access

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.

Media

Figures

Other

Tables

View Table of Contents