Abstract
Index tuning is an activity typically performed by database administrators (DBAs) and advisors tools to decrease the response times of commands submitted to a database management system (DBMS). With the introduction of solid state drive (SSD) storage, a new challenge has arisen for DBAs and tools because SSDs provide fast read operations and low random-access costs, and these new features must be considered to perform index tuning of the database. In this paper, we use a learning classifier system (LCS), which is a machine learning approach that combines learning by reinforcement and genetic algorithms and allows the updating and discovery of new rules to provide an efficient and flexible index tuning mechanism applicable for hybrid storage environments (HDD/SSD). The proposed approach, termed Index Tuning with Learning Classifier System (ITLCS), builds a rule-based mechanism designed to represent the knowledge of the system. Experimental results with the TPC-H benchmark showed that the ITLCS performed better than well-known advisor tools, indicating the feasibility of the proposed approach.
This is a preview of subscription content, log in via an institution to check access.
Access this chapter
Tax calculation will be finalised at checkout
Purchases are for personal use only
Notes
- 1.
Hypothetical indexes are treated by the optimizer as if they existed physically in the DBMS.
References
Schnaitter, K., Polyzotis, N.: Semi-automatic index tuning: keeping DBAs in the loop. VLDB Endowment 5(5), 478–489 (2012)
Ramakrishnan, R., Gehrke, J.: Database Management Systems. McGraw-Hill, New York (2003)
Luhring, M., Sattler, K., Schmidt, K., Schallehn, E.: Autonomous management of soft indexes. In: International Conference on Data Engineering, ICDE 2007, Istanbul, Turkey, pp. 450–458 (2007)
Booker, L.B., Goldberg, D.E., Holland, J.H.: Classifier systems and genetic algorithms. J. Artif. Intell. 40, 235–282 (1989)
Costa, R.L.C., Lifschitz, S.: Index self-tuning and agent based databases. In: Latin-American Conference on Informatics, CLEI 2002, Montevideu, Uruguay, 12 pp. (2002)
Bernado-Mansilla, E., Garrell, J.M.: Accuracy-based LCS. Models, analysis and applications to classification tasks. Evol. Comput. 11(3), 209–238 (2003)
Butz, M.V., Goldberg, D.E., Stolzmann, W.: The anticipatory classifier system and genetic generalization. Nat. Comput. 1, 427–467 (2002)
Ghodsnia, P., Bowman, I., Nica, A.: Parallel I/O aware query optimization. In: ACM Sigmod Conference on Management of Data, Snowbird, UT, USA, pp. 349–360 (2014)
Holland, J.H.: Adaptation in Natural and Artificial Systems: An Introductory Analysis with Applications to Biology, Control and Artificial Intelligence. The MIT Press, Ann Arbor (1992)
Jimenez, I., Sanchez, H., Tran, Q., Polyzotis, K.: Kaizen: a semi-automatic index advisor. In: ACM Sigmod Conference on Management of Data, Scottsdale, AR, USA, pp. 685–688 (2012)
Butz, M.V., Kovacs, T., Lanzi, P.L., Wilson, S.W.: Toward a theory of generalization and learning in XCS. IEEE Trans. Evol. Comput. 8, 28–46 (2004)
Lee, S., Moon, B., Park, C., Kim, J., Kim. S.: A case for flash memory SSD in enterprise database applications. In: Sigmod, pp. 1075–1086 (2008)
Salles, M.V., Lifschitz, S.: Autonomic index management. In: International Conference on Autonomic Computing, ICAC 2005, Seattle, WA, USA, pp. 304–305 (2005)
EDB: Enterprise DB (2018). https://www.enterprisedb.com
HYPOPG: Hypothetical Indexes Support PostgreSQL (2018). http://dalibo.github.io/hypopg
OSDL DBT3 (2018). http://osdldbt.sourceforge.net/#dbt3
POWA: PostgreSQL Workload Analyzer (2018). http://powa.readthedocs.io
Richards, R.A.: Zeroth-order shape optimization utilizing learning classifier systems (1995)
Acknowledgements
This research was sponsored by grants from CAPES (Coordenação de Aperfeiçoamento de Pessoal de Nível Superior).
Author information
Authors and Affiliations
Corresponding author
Editor information
Editors and Affiliations
Rights and permissions
Copyright information
© 2018 Springer International Publishing AG, part of Springer Nature
About this paper
Cite this paper
Pedrozo, W.G., Nievola, J.C., Ribeiro, D.C. (2018). An Adaptive Approach for Index Tuning with Learning Classifier Systems on Hybrid Storage Environments. In: de Cos Juez, F., et al. Hybrid Artificial Intelligent Systems. HAIS 2018. Lecture Notes in Computer Science(), vol 10870. Springer, Cham. https://doi.org/10.1007/978-3-319-92639-1_60
Download citation
DOI: https://doi.org/10.1007/978-3-319-92639-1_60
Published:
Publisher Name: Springer, Cham
Print ISBN: 978-3-319-92638-4
Online ISBN: 978-3-319-92639-1
eBook Packages: Computer ScienceComputer Science (R0)