Proactive database index tuning through data threshold prediction

Abstract

For queries to be efficiently processed in a database, the query optimizer must be presented with an efficient set of indexes for the query workload. Due to the dynamic and complex nature of modern database workloads, database administrators and developers often rely on autonomic tuning tools to determine an optimal set of indexes for the workload. These autonomic tuning tools rely on the use of virtual, or "what-if", indexes. Virtual indexes are limited in the respect that they only provide tuning recommendations given the sizes and distribution of data currently housed in the database. In an active database system, relations and the distribution of data within them are constantly changing at different relative rates. The optimal set of indexes for completing a query will change when related data distributions grow relative to one another past some data threshold. In this paper, we propose a novel extension to the virtual index concept that allows estimating query behavior and index utilization at future points in time. Thus, data thresholds can be proactively detected and the future viability of a given index set can be assessed. The implementation and various applications of this new technology are discussed.

Combinação E Seleção Automática De Ações De Sintonia Fina

“…Vale ressaltar, que ao contrário da otimização do plano de execução realizada pelo otimizador do SGBD que precisa ser online, a tarefa de sintonia fina é por natureza offline. Alguns trabalhos da literatura [64,65] ainda tratam a abordagem online como contínua (continuous tuning) para explicitar que é uma tarefa realizada offline mas continuamente para monitorar mudanças na carga de trabalho. Logo, não existe uma limitação crítica de tempo para a tarefa de sintonia fina como em aplicações online tradicionais.…”