ML Classifier for Database Query Plan Regression

Resolve Bottlenecks,
Find Innovative SolutionsGenerate Solutions

Solution Overview

Problem

Database systems face performance regressions due to sub-optimal query execution plans caused by changes in data demographics or physical configurations, leading to reduced performance and service level issues, despite advancements in query optimizers.

Innovation Solution

A machine learning classifier is employed to predict performance regressions by comparing historical query execution plans, focusing on minimizing errors when transitioning from old to new plans, and executing the old plan if a regression is predicted, while training on logged resource usage metrics and retraining as necessary.

Engineering Contradictions & Design Principles

VSEngineering Contradiction Analysis

1Productivity

If the query optimizer generates a new query execution plan to improve overall workload performance, then overall workload performance is improved, but individual query performance may regress

Engineering Contradiction:

Improveoverall workload performanceVSAvoidindividual query performance stability

Core Design Contradiction:

ProductivityVSReliability

Solution Approach 1:

A machine learning classifier is introduced as an intermediary between the query optimizer and the query execution system. The classifier takes as input features describing both the old query execution plan and the new query execution plan, and outputs a prediction indicating whether the transition from old to new plan will cause performance regression. This intermediary component enables the system to evaluate potential plan transitions before execution, thereby preventing individual query regressions while still allowing overall workload performance improvements.

Inventive Principle:

Principle #24Intermediary (Mediator)

Solution Approach 2:

The system performs preliminary evaluation of the new query execution plan against the old plan using the machine learning classifier before actually executing the new plan. By predicting potential performance regressions in advance, the system can avoid executing plans that would harm individual query performance, thus preventing regressions before they occur.

Inventive Principle:

Principle #10Preliminary action

2Speed

If query execution plans are reused from cache to avoid parsing overhead, then query execution speed is improved, but plans become sub-optimal due to changes in data demographics or physical configuration

Engineering Contradiction:

Improvequery execution speedVSAvoidquery plan optimality

Core Design Contradiction:

SpeedVSReliability

Solution Approach 1:

The system implements a feedback mechanism where the machine learning classifier continuously evaluates whether cached query execution plans remain optimal given changes in data demographics or physical configuration. The classifier receives features about the current state and compares them against the cached plan, providing feedback that indicates whether the cached plan should still be used or needs to be regenerated. This feedback loop maintains both speed (by reusing valid cached plans) and optimality (by detecting when plans become sub-optimal).

Inventive Principle:

Principle #23Feedback

Data Source

PatentUS12001432B1Reducing query optimizer plan regressions with machine learning classification

Publication Date: 2024.06.04 TERADATA US INC

AI summary

A database system receives a query. The database system retrieves an old query execution plan (QEP), OldPlan, for the query. The database system submits the query to an optimizer. The optimizer returns a new QEP, NewPlan, for the query. The database system submits the OldPlan and the NewPlan to a machine learning classifier (ML classifier). The ML classifier predicts that executing the NewPlan will result in a performance regression as compared to executing the OldPlan. The database system executes the OldPlan instead of the NewPlan.