Automated provisioning for database performance

Patent metadata

Field	Value
Publication number	US-2020125568-A1
Application number	US-201916382085-A
Country	US
Kind code	A1
Filing date	Apr 11, 2019
Priority date	Oct 18, 2018
Publication date	Apr 23, 2020
Grant date	—

Abstract

Embodiments utilize trained query performance machine learning (QP-ML) models to predict an optimal compute node cluster size for a given in-memory workload. The QP-ML models include models that predict query task runtimes at various compute node cardinalities, and models that predict network communication time between nodes of the cluster. Embodiments also utilize an analytical model to predict overlap between predicted task runtimes and predicted network communication times. Based on this data, an optimal cluster size is selected for the workload. Embodiments further utilize trained data capacity machine learning (DC-ML) models to predict a minimum number of compute nodes needed to run a workload. The DC-ML models include models that predict the size of the workload dataset in a target data encoding, models that predict the amount of memory needed to run the queries in the workload, and models that predict the memory needed to accommodate changes to the dataset.

First claim

What is claimed is:

1 . A computer-executed method comprising:
receiving workload information for a particular database workload, wherein the workload information includes at least (a) a portion of a dataset for the particular database workload and (b) one or more queries being run in the particular database workload; for each query of the one or more queries, predicting, based on the workload information and using one or more trained query performance machine learning models, a plurality of predicted query runtimes respectively corresponding to a plurality of different compute node cardinalities; based on the plurality of predicted query runtimes identified for each query, of the one or more queries, determining an optimal number of compute nodes for the particular database workload; and generating, within a memory, output that specifies the optimal number of compute nodes for the particular database workload; wherein the method is performed by one or more computing devices.

2 . The method of claim 1 , wherein:
the one or more trained query performance machine learning models comprises a plurality of trained task-specific runtime machine learning models; predicting, for a particular query of the one or more queries and based on the workload information and using the one or more trained query performance machine learning models, a plurality of predicted query runtimes respectively corresponding to the plurality of different compute node cardinalities comprises:
predicting, based on the workload information and using the one or more trained query performance machine learning models, a particular query runtime, of the plurality of predicted query runtimes, corresponding to a particular compute node cardinality, of the plurality of different compute node cardinalities;
predicting, based on the workload information and using the one or more trained query performance machine learning models, the particular query runtime, of the plurality of predicted query runtimes, corresponding to the particular compute node cardinality, of the plurality of different compute node cardinalities comprises:
identifying one or more task instances in a query plan for the particular query, predicting one or more task instance runtimes, for the one or more task instances, by:
for each of the one or more task instances, using a trained task-specific runtime machine learning model that is specific to a task type of the respective task instance to predict a runtime for the respective task instance for the particular compute node cardinality, and

determining the particular query runtime, for the particular query running on a cluster with the particular compute node cardinality, based at least in part on the one or more predicted task instance runtimes for the respective one or more task instances.

3 . The method of claim 2 , wherein:
the one or more trained query performance machine learning models further comprises one or more trained network communication machine learning models; and predicting, based on the workload information and using the one or more trained query performance machine learning models, the particular query runtime, of the plurality of predicted query runtimes, corresponding to the particular compute node cardinality, of the plurality of different compute node cardinalities further comprises:
predicting, based on the particular compute node cardinality and using the one or more trained network communication machine learning models, one or more predicted network communication times for the one or more task instances;
wherein determining the particular query runtime, for the particular query running on a cluster with the particular compute node cardinality, is further based, at least in part, on the one or more predicted network communication times for the one or more task instances.

4 . The method of claim 3 , wherein predicting, based on the workload information and using the one or more trained query performance machine learning models, the particular query runtime, of the plurality of predicted query runtimes, corresponding to the particular compute node cardinality, of the plurality of different compute node cardinalities further comprises:
determining one or more instances of runtime/communication overlap between (a) the one or more predicted task instance runtimes for the respective one or more task instances, and (b) the one or more predicted network communication times; wherein determining the particular query runtime, for the particular query running on a cluster with the particular compute node cardinality, is further based, at least in part, on the one or more instances of runtime/communication overlap.

5 . The method of claim 1 , further comprising automatically provisioning the determined optimal number of compute nodes to run the particular database workload.

6 . The method of claim 1 , wherein:
the one or more queries comprises a plurality of queries; and based on the plurality of predicted query runtimes identified for each query, of the one or more queries, determining the optimal number of compute nodes for the particular database workload comprises:
determining a plurality of cardinality-specific total workload runtimes by, for each compute node cardinality of the plurality of different compute node cardinalities:
identifying, for each query of the plurality of queries, a cardinality-specific query runtime for the respective query and the respective compute node cardinality to produce a plurality of cardinality-specific query runtimes, and
adding all of the plurality of cardinality-specific query runtimes to produce a cardinality-specific total workload runtime for the respective compute node cardinality;

identifying the optimal number of compute nodes to be a particular compute node cardinality, of the plurality of different compute node cardinalities, that is associated with a minimum cardinality-specific total workload runtime of the plurality of cardinality-specific total workload runtimes.

7 . The method of claim 1 , wherein:
the one or more queries comprises a plurality of queries; and determining the optimal number of compute nodes for the particular database workload comprises:
determining a plurality of cardinality-specific workload runtimes by, for each compute node cardinality of the plurality of different compute node cardinalities:
producing a plurality of cardinality-specific query runtimes by identifying, for each query of the plurality of queries, a cardinality-specific query runtime for the respective query and the respective compute node cardinality, and
adding all of the plurality of cardinality-specific query runtimes to produce a cardinality-specific total workload runtime for the respective compute node cardinality;

identifying a maximum cardinality-specific total workload runtime of the plurality of cardinality-specific workload runtimes;
determining a plurality of cardinality-specific workload speeds by, for each compute node cardinality of the plurality of different compute node cardinalities, determining a cardinality-specific workload speed of the respective compute node cardinality to be a ratio of:
the maximum cardinality-specific total workload runtime to
the cardinality-specific total workload runtime for the respective compute node cardinality;

identifying the optimal number of compute nodes to be a particular compute node cardinality, of the plurality of different compute node cardinalities, that is associated with both:
a minimum ratio of (a) a cost of the particular compute node cardinality, and (b) a cardinality-specific workload speed associated with the particular compute node cardinality, and
a fastest cardinality-specific workload speed among those compute node cardinali

Assignees

• Oracle Int Corp

Inventors

• Idicula Sam
• Karnagel Tomas
• Wen Jian
• Sundara Seema
• Agarwal Nipun
• Bency Mayur

Classifications

• G06F16/217 Primary

  Database tuning (G06F16/2282 takes precedence; database performance monitoring G06F11/3409) · CPC title

• G06N20/20

  Ensemble learning · CPC title

• G06N20/00

  Machine learning · CPC title

• G06F16/24545 Primary

  Selectivity estimation or determination · CPC title

• G06N3/044

  Recurrent networks, e.g. Hopfield networks · CPC title