Guided Automated Learning for query workload re-Optimization

by   Guilherme Damasio, et al.

Query optimization is a hallmark of database systems enabling complex SQL queries of today's applications to be run efficiently. The query optimizer often fails to find the best plan, when logical subtleties in business queries and schemas circumvent it. When a query runs more expensively than is viable or warranted, determination of the performance issues is usually performed manually in consultation with experts through the analysis of query's execution plan (QEP). However, this is an excessively time consuming, human error-prone, and costly process. Galo is a novel system that automates this process. The tool automatically learns recurring problem patterns in query plans over workloads in an offline learning phase, to build a knowledge base of plan-rewrite remedies. It then uses the knowledge base online to re-optimize queries queued for execution to improve performance, often quite drastically. Galo's knowledge base is built on RDF and SPARQL, W3C graph database standards, which is well suited for manipulating and querying over SQL query plans, which are graphs themselves. Galo acts as a third-tier of optimization, after query rewrite and cost-based optimization, as a query-plan rewrite. Galo's knowledge base is also an invaluable tool for database experts to debug query performance issues by tracking to known issues and solutions as well as refining the optimizer with new and better tunned techniques by the development team. We demonstrate an experimental study of the effectiveness of our techniques over synthetic TPC-DS and real IBM client query workloads.


page 3

page 4

page 6

page 9

page 10

page 13


Plan-Structured Deep Neural Network Models for Query Performance Prediction

Query performance prediction, the task of predicting the latency of a qu...

Facilitating SQL Query Composition and Analysis

Formulating efficient SQL queries requires several cycles of tuning and ...

Bitvector-aware Query Optimization for Decision Support Queries (extended version)

Bitvector filtering is an important query processing technique that can ...

Balsa: Learning a Query Optimizer Without Expert Demonstrations

Query optimizers are a performance-critical component in every database ...

BitE : Accelerating Learned Query Optimization in a Mixed-Workload Environment

Although the many efforts to apply deep reinforcement learning to query ...

Cleaning Denial Constraint Violations through Relaxation

Data cleaning is a time-consuming process which depends on the data anal...

ARENA: Towards Informative Alternative Query Plan Selection for Database Education

A key learning goal of learners taking database systems course is to und...

Please sign up or login with your details

Forgot password? Click here to reset