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.
Similar content being viewed by others
References
Bayer, R., Schkolnick, M.: Concurrency of operations on B-trees. Acta Inf. 9, 1–21 (1977)
Bayer, R., Unterauer, K.: Prefix b-trees. ACM TODS 2(1), 11–26 (1977)
Bruno, N., Chaudhuri, S.: An online approach to physical design tuning. In ICDE, pp. 826–835 (2007)
Bruno, N., Chaudhuri, S.: Physical design refinement: The ‘merge-reduce’ approach. ACM TODS 32(4), 1–41 (2007)
Chaudhuri, S. Narasayya, V.R.: Self-tuning database systems: A decade of progress. In VLDB, pp. 3–14 (2007)
Finkelstein, S.J., Schkolnick, M., Tiberio, P.: Physical database design for relational databases. ACM TODS 13(1), 91–128 (1988)
Graefe, G.: Sorting and indexing with partitioned B-trees. In CIDR (2003)
Graefe, G.: Hierarchical locking in b-tree indexes. In BTW, pp. 18–42 (2007)
Graefe, G.: A survey of B-tree locking techniques. ACM TODS 35(2), 16:1–16:26 (2010)
Graefe, G.: Modern B-tree techniques. Found Trends Databases 3(4), 203–402 (2011)
Graefe, G.: A survey of B-tree logging and recovery techniques. ACM TODS 37(1), 1:1–1:35 (2012)
Graefe, G., Idreos, S., Kuno, H., Manegold, S.: Benchmarking adaptive indexing. In TPCTC, pp. 169–184 (2010)
Graefe, G., Kimura, H., Kuno, H.: Foster b-trees. ACM Trans. Database Syst. (TODS) 37(3), 17 (2012)
Graefe, G., Kuno, H.: Adaptive indexing for relational keys. In SMDB, pp. 69–74 (2010)
Graefe, G., Kuno, H.: Fast loads and queries. Trans. Large Scale Data Knowl. Cent. Syst. 4, 31–72 (2010)
Graefe, G., Kuno, H.: Self-selecting, self-tuning, incrementally optimized indexes. In EDBT, pp. 371–381 (2010)
Graefe, G., Kuno, H.: Definition, detection, and recovery of single-page failures, a fourth class of database failures. PVLDB 5(7), 646–655 (2012)
Graefe, G., Seeger, B.: Logical recovery from single-page failures. In BTW, pp. 113–132 (2013)
Gray, J., Reuter, A.: Transaction Processing: Concepts and Techniques. Morgan Kaufmann, Los Altos, CA (1993)
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)
Härder, T.: Selecting an optimal set of secondary indices. In ECI, pp. 146–160 (1976)
Härder, T., Reuter, A.: Principles of transaction-oriented database recovery. ACM Comput. Surv. 15(4), 287–317 (1983)
Harris, T., Larus, J.R., Rajwar, R.: Transactional Memory, 2nd edition. Synthesis Lectures on Computer Architecture. Morgan & Claypool Publishers (2010)
Hoshino, T., Goda, K., Kitsuregawa, M.: Online monitoring and visualisation of database structural deterioration. In IJAC, pp. 297–323 (2010)
Idreos, S.: Database cracking: Towards auto-tuning database kernels. CWI, PhD Thesis (2010)
Idreos, S., Kersten, M.L., Manegold, S.: Database cracking. In CIDR, pp. 68–78 (2007)
Idreos, S., Kersten, M.L., Manegold, S.: Updating a cracked database. In SIGMOD, pp. 413–424 (2007)
Idreos, S., Kersten, M.L., Manegold, S.: Self-organizing tuple reconstruction in column stores. In SIGMOD, pp. 297–308 (2009)
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)
Kersten, M.L., Manegold, S.: Cracking the database store. In CIDR, pp. 213–224 (2005)
Lehman, P.L., Yao, S.B.: Efficient locking for concurrent operations on B-trees. ACM Trans. Database Syst. 6, 650–670 (1981)
Lühring, M., Sattler, K.-U., Schmidt, K., Schallehn, E.: Autonomous management of soft indexes. In SMDB, pp. 450–458 (2007)
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)
Mohan, C., Narang, I.: Algorithms for creating indexes for very large tables without quiescing updates. In SIGMOD Conference, pp. 361–370 (1992)
Praveen Seshadri, A.N.S.: Generalized partial indexes. In ICDE, pp. 420–427 (1995)
Saracco, C.M., Bontempo, C.J.: Getting a lock on integrity and concurrency. Database Program. Des. (1997)
Schnaitter, K., Abiteboul, S., Milo, T., Polyzotis, N.: COLT: Continuous on-line tuning. In SIGMOD, pp. 793–795 (2006)
Severance, D.G., Lohman, G.M.: Differential files: Their application to the maintenance of large databases. ACM TODS 1(3), 256–267 (1976)
Srinivasan, V., Carey, M.: Performance of on-line index construction algorithms. In EDBT, pp. 293–309 (1992)
Stonebraker, M.: The case for partial indexes. SIGMOD Record 18(4), 4–11 (1989)
Weikum, G.: Principles and realization strategies of multilevel transaction management. ACM Trans. Database Syst. 16(1), 132–180 (1991)
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)
Author information
Authors and Affiliations
Corresponding author
Rights 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
Received:
Revised:
Accepted:
Published:
Issue Date:
DOI: https://doi.org/10.1007/s00778-013-0345-7