AutoDbindexer - Expert System for automated database indexing

Abstract

The collection, processing and analysis capacity of large amounts of data is crucial for the purpose of extrapolating new learning in business, logical, and medicinal applications. In the era of Big Data, Database Management Systems are a vital element which acts as a repository where the data is aggregated and stored. For such valuable databases, database tuning is paramount. Tuning a database to improve performance is historically a difficult task in database administration. A research article by ACM SIGMOD states that tuning expense is evaluated to be right around half of the aggregate cost of a database, and numerous DBAs spend about a quarter of their time on database tuning activities. Database Tuning is not a one-time task, majority of the real-world applications’ workloads are highly dynamic, unpredictable and subject to change based on various factors such as time, weather, trends, politics, business processes etc. Therefore, tuning configurations such as indexes soon become invalid and cause performance issues. It is DBAs’ job to continuously watch the database for performance issues and tune it frequently to maintain the performance level. The process is very time consuming and includes several steps. It is impossible for a human operator to study and analyze thousands of performance statistics, historical tuning data and workload patterns in order to come up with optimal tuning recommendations. A technique called online-indexing was introduced to solve this problem by autonomously applying indexes based on the workloads. But the existing online-indexing systems has significant drawbacks in index selection and overhead management. Therefore, a continuous workload monitoring and automated tuning system called AutoDbIndexer is proposed, which unremittingly monitors the changing workloads of databases and applies indexes based on database access trends. The solution improves the query execution time by 28%, creating negligible overhead to the DBMS.