SpringerLink
Log in

Transactional support for adaptive indexing

  • Special Issue Paper
  • Published:
The VLDB Journal Aims and scope Submit manuscript

Abstract

Adaptive indexing initializes and optimizes indexes incrementally, as a side effect of query processing. The goal is to achieve the benefits of indexes while hiding or minimizing the costs of index creation. However, index-optimizing side effects seem to turn read-only queries into update transactions that might, for example, create lock contention. This paper studies concurrency control and recovery in the context of adaptive indexing. We show that the design and implementation of adaptive indexing rigorously separates index structures from index contents; this relaxes constraints and requirements during adaptive indexing compared to those of traditional index updates. Our design adapts to the fact that an adaptive index is refined continuously and exploits any concurrency opportunities in a dynamic way. A detailed experimental analysis demonstrates that (a) adaptive indexing maintains its adaptive properties even when running concurrent queries, (b) adaptive indexing can exploit the opportunity for parallelism due to concurrent queries, (c) the number of concurrency conflicts and any concurrency administration overheads follow an adaptive behavior, decreasing as the workload evolves and adapting to the workload needs.

This is a preview of subscription content, log in via an institution to check access.

Access this article

Log in via an institution

Subscribe and save

Springer+ Basic
EUR 32.99 /Month
  • Get 10 units per month
  • Download Article/Chapter or Ebook
  • 1 Unit = 1 Article or 1 Chapter
  • Cancel anytime
Subscribe now

Buy Now

Price excludes VAT (USA)
Tax calculation will be finalised during checkout.

Instant access to the full article PDF.

Institutional subscriptions

Fig. 1
Fig. 2
Fig. 3
Fig. 4
Fig. 5
Fig. 6
Fig. 7
Fig. 8
Fig. 9
Fig. 10
Fig. 11
Fig. 12
Fig. 13
Fig. 14
Fig. 15
Fig. 16
Fig. 17
Fig. 18
Fig. 19
Fig. 20
Fig. 21
Fig. 22
Fig. 23
Fig. 24
Fig. 25

Similar content being viewed by others

References

  1. Bayer, R., Schkolnick, M.: Concurrency of operations on B-trees. Acta Inf. 9, 1–21 (1977)

    Article  MATH  MathSciNet  Google Scholar 

  2. Bayer, R., Unterauer, K.: Prefix b-trees. ACM TODS 2(1), 11–26 (1977)

    Article  Google Scholar 

  3. Bruno, N., Chaudhuri, S.: An online approach to physical design tuning. In ICDE, pp. 826–835 (2007)

  4. Bruno, N., Chaudhuri, S.: Physical design refinement: The ‘merge-reduce’ approach. ACM TODS 32(4), 1–41 (2007)

    Article  Google Scholar 

  5. Chaudhuri, S. Narasayya, V.R.: Self-tuning database systems: A decade of progress. In VLDB, pp. 3–14 (2007)

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

    Article  Google Scholar 

  7. Graefe, G.: Sorting and indexing with partitioned B-trees. In CIDR (2003)

  8. Graefe, G.: Hierarchical locking in b-tree indexes. In BTW, pp. 18–42 (2007)

  9. Graefe, G.: A survey of B-tree locking techniques. ACM TODS 35(2), 16:1–16:26 (2010)

    Google Scholar 

  10. Graefe, G.: Modern B-tree techniques. Found Trends Databases 3(4), 203–402 (2011)

    Article  Google Scholar 

  11. Graefe, G.: A survey of B-tree logging and recovery techniques. ACM TODS 37(1), 1:1–1:35 (2012)

    Article  Google Scholar 

  12. Graefe, G., Idreos, S., Kuno, H., Manegold, S.: Benchmarking adaptive indexing. In TPCTC, pp. 169–184 (2010)

  13. Graefe, G., Kimura, H., Kuno, H.: Foster b-trees. ACM Trans. Database Syst. (TODS) 37(3), 17 (2012)

    Article  Google Scholar 

  14. Graefe, G., Kuno, H.: Adaptive indexing for relational keys. In SMDB, pp. 69–74 (2010)

  15. Graefe, G., Kuno, H.: Fast loads and queries. Trans. Large Scale Data Knowl. Cent. Syst. 4, 31–72 (2010)

    Article  Google Scholar 

  16. Graefe, G., Kuno, H.: Self-selecting, self-tuning, incrementally optimized indexes. In EDBT, pp. 371–381 (2010)

  17. Graefe, G., Kuno, H.: Definition, detection, and recovery of single-page failures, a fourth class of database failures. PVLDB 5(7), 646–655 (2012)

    Google Scholar 

  18. Graefe, G., Seeger, B.: Logical recovery from single-page failures. In BTW, pp. 113–132 (2013)

  19. Gray, J., Reuter, A.: Transaction Processing: Concepts and Techniques. Morgan Kaufmann, Los Altos, CA (1993)

    MATH  Google Scholar 

  20. Halim, F., Idreos, S., Karras, P., Yap, R.H.C.: Stochastic database cracking: Towards robust adaptive indexing in main-memory column-stores. PVLDB 5(6), 502–513 (2012)

    Google Scholar 

  21. Härder, T.: Selecting an optimal set of secondary indices. In ECI, pp. 146–160 (1976)

  22. Härder, T., Reuter, A.: Principles of transaction-oriented database recovery. ACM Comput. Surv. 15(4), 287–317 (1983)

    Article  Google Scholar 

  23. Harris, T., Larus, J.R., Rajwar, R.: Transactional Memory, 2nd edition. Synthesis Lectures on Computer Architecture. Morgan & Claypool Publishers (2010)

  24. Hoshino, T., Goda, K., Kitsuregawa, M.: Online monitoring and visualisation of database structural deterioration. In IJAC, pp. 297–323 (2010)

  25. Idreos, S.: Database cracking: Towards auto-tuning database kernels. CWI, PhD Thesis (2010)

  26. Idreos, S., Kersten, M.L., Manegold, S.: Database cracking. In CIDR, pp. 68–78 (2007)

  27. Idreos, S., Kersten, M.L., Manegold, S.: Updating a cracked database. In SIGMOD, pp. 413–424 (2007)

  28. Idreos, S., Kersten, M.L., Manegold, S.: Self-organizing tuple reconstruction in column stores. In SIGMOD, pp. 297–308 (2009)

  29. Idreos, S., Manegold, S., Kuno, H., Graefe, G.: Merging what’s cracked, cracking what’s merged: Adaptive indexing in main-memory column-stores. PVLDB 4(9), 585–597 (2011)

    Google Scholar 

  30. Kersten, M.L., Manegold, S.: Cracking the database store. In CIDR, pp. 213–224 (2005)

  31. Lehman, P.L., Yao, S.B.: Efficient locking for concurrent operations on B-trees. ACM Trans. Database Syst. 6, 650–670 (1981)

    Article  MATH  Google Scholar 

  32. Lühring, M., Sattler, K.-U., Schmidt, K., Schallehn, E.: Autonomous management of soft indexes. In SMDB, pp. 450–458 (2007)

  33. Mohan, C., Haderle, D.J., Lindsay, B.G., Pirahesh, H., Schwarz, P.M.: ARIES: A transaction recovery method supporting fine-granularity locking and partial rollbacks using write-ahead logging. ACM TODS 17(1), 94–162 (1992)

    Article  Google Scholar 

  34. Mohan, C., Narang, I.: Algorithms for creating indexes for very large tables without quiescing updates. In SIGMOD Conference, pp. 361–370 (1992)

  35. Praveen Seshadri, A.N.S.: Generalized partial indexes. In ICDE, pp. 420–427 (1995)

  36. Saracco, C.M., Bontempo, C.J.: Getting a lock on integrity and concurrency. Database Program. Des. (1997)

  37. Schnaitter, K., Abiteboul, S., Milo, T., Polyzotis, N.: COLT: Continuous on-line tuning. In SIGMOD, pp. 793–795 (2006)

  38. Severance, D.G., Lohman, G.M.: Differential files: Their application to the maintenance of large databases. ACM TODS 1(3), 256–267 (1976)

    Article  Google Scholar 

  39. Srinivasan, V., Carey, M.: Performance of on-line index construction algorithms. In EDBT, pp. 293–309 (1992)

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

    Article  Google Scholar 

  41. Weikum, G.: Principles and realization strategies of multilevel transaction management. ACM Trans. Database Syst. 16(1), 132–180 (1991)

    Article  Google Scholar 

  42. Weikum, G., Schek, H.-J.: Concepts and applications of multilevel transactions and open nested transactions. In Database Transaction Models for Advanced Applications, pp. 515–553. Morgan Kaufmann, Los Altos, CA (1992)

Download references

Author information

Authors and Affiliations

  1. HP Labs, Palo Alto, CA, USA

    Goetz Graefe & Harumi Kuno

  2. Google, Mountain View, CA, USA

    Felix Halim

  3. Harvard University, Cambridge, MA, USA

    Stratos Idreos

  4. CWI, Amsterdam, The Netherlands

    Stefan Manegold

  5. University of Marburg, Marburg, Germany

    Bernhard Seeger

Authors
  1. Goetz Graefe
    View author publications

    You can also search for this author in PubMed Google Scholar

  2. Felix Halim
    View author publications

    You can also search for this author in PubMed Google Scholar

  3. Stratos Idreos
    View author publications

    You can also search for this author in PubMed Google Scholar

  4. Harumi Kuno
    View author publications

    You can also search for this author in PubMed Google Scholar

  5. Stefan Manegold
    View author publications

    You can also search for this author in PubMed Google Scholar

  6. Bernhard Seeger
    View author publications

    You can also search for this author in PubMed Google Scholar

Corresponding author

Correspondence to Harumi Kuno.

Rights and permissions

Reprints and permissions

About this article

Cite this article

Graefe, G., Halim, F., Idreos, S. et al. Transactional support for adaptive indexing. The VLDB Journal 23, 303–328 (2014). https://doi.org/10.1007/s00778-013-0345-7

Download citation

  • Received:

  • Revised:

  • Accepted:

  • Published:

  • Issue Date:

  • DOI: https://doi.org/10.1007/s00778-013-0345-7

Keywords

Search

Navigation