An adaptive approach for index tuning with learning classifier systems on hybrid storage environments

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.