Introduction

General query optimizer is far from optimal for a given customer and workloads, learned query optimizer works but

1. hard to understand why it is better.
2. high experimentation costs/pre-production cost (collecting training data)
3. regression to newer queries.

This paper makes steering actions more manageable, keeping the costs of steering within budget, and avoiding unexpected performance regressions in production.

1. Key idea: breaking down the steering process into smaller incremental steps that are easily explainable and reversible.
2. Use contextual bandit model to redue the pre-production cost.
3. Use validation model to accept or reject the suggested modifications to query plan.

Design

The paper propose QO-Advisor, which is recurrently triggered every day. It accepts historical metadata and return a list of job template identifiers and rule hint pairs.

Contextual Bandit => suggest one rule hint for each job.

1. Actions: rules. Context: features of query. Reward: optimizer’s estimated cost change.
2. It uses a tool named as Azure Personalizer to implement.

Flighting:

1. Execute job from low cost to high cost and outputs total DataRead and DataWritten features.

Validation model => detect regression.

1. It use a linear regression model to predict the PNhours delta based on DataRead and DataWritten features given job, and only when PNhours delta is below a predefined threshold, it is safe to execute.
2. Intuition: If job reads and writes less data, PNhours will be reduced.

---

• ← Previous Post
• Next Post →