Reducing query optimizer plan regressions with machine learning classification
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.
1. A method comprising:
a database system receiving a query;
the database system retrieving an old query execution plan (QEP), OldPlan, for the query;
the database system submitting the query to an optimizer;
the optimizer returning a new QEP, NewPlan, for the query;
the database system submitting the OldPlan and the NewPlan to a machine learning classifier (ML classifier);
the ML classifier computing a feature vector describing a difference between:
an OldPlan graph of the OldPlan, wherein the OldPlan graph includes:
a plurality of OldPlan nodes, each associated with a physical step in the OldPlan, each OldPlan node including regression-relevant properties and costs previously determined to be relevant to performance regression, and
one or more OldPlan edges, each associated with a connection between OldPlan nodes, and
a NewPlan graph of the NewPlan, wherein the NewPlan graph includes:
a plurality of NewPlan nodes, each associated with a physical step in the NewPlan, each NewPlan node including the regression-relevant properties and costs, and
one or more NewPlan edges, each associated with a connection between NewPlan nodes;
the ML classifier predicting, based on the feature vector, that executing the NewPlan will result in a performance regression as compared to executing the OldPlan; and
the database system executing the OldPlan instead of the NewPlan.
2. The method of claim 1 further comprising training the ML classifier by:
determining costs of historical QEPs for the query using logged resource usage metrics not impacted by extraneous system activities; and
comparing the determined costs of a pair of historical QEPs for the query and labeling a one of the pair as a regression if the determined cost of the one of the pair of historical QEPs exceeds the determined cost of the other of the pair of historical QEPs by a threshold percentage.
3. The method of claim 2 wherein the pair of historical QEPs is selected from an orchard of QEPs for the query wherein the orchard contains QEPs developed for the query for hardware configurations having the same complement of hardware processors and data storage facilities and for data volume levels within a threshold amount of each other.
4. The method of claim 3 further comprising:
the database system determining that, because of a recent change in hardware configuration or data storage facilities the NewPlan is in a different orchard than the OldPlan, and, as a result, not performing any of the elements of claim 1 except the database system receiving the query, the database submitting the query to the optimizer, and the optimizer returning the new QEP, NewPlan, for the query, further comprising:
the database system executing the NewPlan;
the database system including the NewPlan and statistics from executing the NewPlan in the logged resource usage metrics being used to train the ML classifier.
5. The method of claim 2 further comprising the database system determining that the query is being run in a training mode, a retraining mode, or that a query plan exists for the query in a cache or a cache-like persistent storage, and, as a result, not performing any of the elements of claim 1 except the database system receiving the query, the database submitting the query to the optimizer, and the optimizer returning the new QEP, NewPlan, for the query, further comprising:
the database system executing the NewPlan; and
the database system including the NewPlan and statistics from executing the NewPlan in the logged resource usage metrics being used to train the ML classifier.
6. The method of claim 5 wherein the database system determines that the query is being run in a retraining mode by the query being designated with a retraining query band.
7. The method of claim 5 further comprising:
the database system determining that the cache or cache-like persistent storage has become invalid dues to changes in a hardware configuration for the database system maintaining the cache or cache-like persistent storage, and as a result,
the database system spoiling the cache or cache-like persistent storage.
8. The method of claim 5 further comprising replacing the OldPlan in a memory store with the NewPlan.
9. The method of claim 1 wherein the database system retrieves the OldPlan from a cache.
10. The method of claim 1 further comprising notifying the optimizer that the NewPlan has been rejected.
11. The method of claim 1 wherein the ML classifier is encapsulated as an application programming interface (API) that can be called by the database system.
12. A non-transitory computer-readable tangible medium, on which is recorded a computer program, the computer program comprising executable instructions, that, when executed, perform a method comprising:
a database system receiving a query;
the database system retrieving an old query execution plan (QEP), OldPlan, for the query;
the database system submitting the query to an optimizer;
the optimizer returning a new QEP, NewPlan, for the query;
the database system submitting the OldPlan and the NewPlan to a machine learning classifier (ML classifier);
the ML classifier computing a feature vector describing a difference between:
an OldPlan graph of the OldPlan, wherein the OldPlan graph includes:
a plurality of OldPlan nodes, each associated with a physical step in the OldPlan, each OldPlan node including regression-relevant properties and costs previously determined to be relevant to performance regression, and
one or more OldPlan edges, each associated with a connection between OldPlan nodes, and
a NewPlan graph of the NewPlan, wherein the NewPlan graph includes:
a plurality of NewPlan nodes, each associated with a physical step in the NewPlan, each NewPlan node including the regression-relevant properties and costs, and
one or more NewPlan edges, each associated with a connection between NewPlan nodes;
the ML classifier predicting, based on the feature vector, that executing the NewPlan will result in a performance regression as compared to executing the OldPlan; and
the database system executing the OldPlan instead of the NewPlan.
13. The method of claim 12 further comprising training the ML classifier by:
determining costs of historical QEPs for the query using logged resource usage metrics not impacted by extraneous system activities; and
comparing the determined costs of a pair of historical QEPs for the query and labeling a one of the pair as a regression if the determined cost of the one of the pair of historical QEPs exceeds the determined cost of the other of the pair of historical QEPs by a threshold percentage.
14. The method of claim 13 wherein the pair of historical QEPs is selected from an orchard of QEPs for the query wherein the orchard contains QEPs developed for the query for hardware configurations having the same complement of hardware processors and data storage facilities and for data volume levels within a threshold amount of each other.
15. The method of claim 14 further comprising:
the database system determining that, because of a recent change in hardware configuration or data storage facilities the NewPlan is in a different orchard than the OldPlan, and, as a result, not performing any of the elements of claim 12 except the database system receiving the query, the database submitting the query to the optimizer, and the optimizer returning the new QEP, NewPlan, for the query, further comprising: the database system executing the NewPlan; and
the database system including the NewPlan and statistics from executing the NewPlan in the logged resource usage metrics being used to train the ML classifier.
16. The method of claim 13 further comprising the database system determining that the query is being run in a training mode, a retraining mode, or that a query plan exists for the query in a cache or a cache-like persistent storage, and, as a result, not performing any of the elements of claim 12 except the database system receiving the query, the database submitting the query to the optimizer, and the optimizer returning the new QEP, NewPlan, for the query, further comprising:
the database system executing the NewPlan; and
the database system including the NewPlan and statistics from executing the NewPlan in the logged resource usage metrics being used to train the ML classifier.
17. The method of claim 16 wherein the database system determines that the query is being run in a retraining mode by the query being designated with a retraining query band.
18. The method of claim 16 further comprising:
the database system determining that the cache or cache-like persistent storage has become invalid dues to changes in a hardware configuration for the database system maintaining the cache or cache-like persistent storage, and as a result,
the database system spoiling the cache or cache-like persistent storage.
19. The method of claim 12 further comprising notifying the optimizer that the NewPlan has been rejected.
20. The method of claim 12 wherein the ML classifier is encapsulated as an application programming interface (API) that can be called by the database system.