US 11,537,615 B2Granted

Using machine learning to estimate query resource consumption in MPPDB

AssigneeFuturewei Technologies, Inc.

InventorsLei Liu, Mingyi Zhang, Yu Dong, Huaizhi Li, Yantao Qiao

Life of this patent

Estimating the term…

Loading patent…

Abstract

Methods and apparatus are provided for using machine learning to estimate query resource consumption in a massively parallel processing database (MPPDB). In various embodiments, the machine learning may jointly perform query resource consumption estimation for a query and resource extreme events detection together, utilize an adaptive kernel that is configured to learn most optimal similarity relation metric for data from each system settings, and utilize multi-level stacking technology configured to leverage outputs of diverse base classifier models. Advantages and benefits of the disclosed embodiments include providing faster and more reliable system performance and avoiding resource issues such as out of memory (OOM) occurrences.

Claims16

Dependent claims

A method for executing queries in a query management device, the method comprising:

receiving a query from a client device;
generating a query plan for the query by parsing the query to determine operators of the query and a sequence of the operators;
generating, by using a machine learning technology configured to automate analytical model building, a predictive trained model;
predicting a query resource consumption and resource usage extreme events for the query based on the query plan, using the predictive trained model, the predictive trained model being a co-prediction model configured to jointly model the query resource consumption and resource usage extreme events concurrently by taking into account a resource utilization relationship between a first time slot and a second time slot, a resource peak value relationship between the first time slot and the second time slot, and a relationship between resource utilization of the first time slot and resource peak value of the second time slot, the co-prediction model being represented by:
min
w
u
,
b
u
,
e
,
β
⁢
w
p
,
b
p
,
e
,
α
ℒ
u
=
1
2
⁢
W
p
T
⁢
W
p
+
1
2
⁢
γ
2
⁢
∑
i
e
i
2
+
1
2
⁢
W
u
T
⁢
W
u
+
1
2
⁢
γ
1
⁢
∑
i
e
i
2
-
∑
i
α
i
⁢
{
y
i
{
p
}
[
w
p
T
⁢
x
i
{
p
}
+
b
p
]
-
1
+
ε
i
}
-
∑
i
β
i
⁢
{
y
i
{
p
}
[
w
u
T
⁢
x
i
{
p
}
+
b
p
]
-
1
+
ε
i
}
-
γ
3
⁢
∑
(
i
,
j
)
∈
E
u
σ
i
⁢
j
[
w
u
T
⁢
x
i
{
u
}
-
w
u
{
T
}
⁢
x
j
{
u
}
]
-
γ
4
⁢
∑
(
i
,
j
)
∈
E
p
η
i
⁢
j
[
w
p
T
⁢
x
i
{
p
}
-
w
p
{
T
}
⁢
x
j
{
p
}
]
-
γ
5
⁢
∑
(
i
,
j
)
∈
E
u
⁢
p
ϑ
i
⁢
j
[
w
u
T
⁢
x
i
{
u
}
+
b
u
-
w
p
{
T
}
⁢
x
j
{
p
}
-
b
p
]
where (i,j)∈Eu is the resource utilization relationship between time slot i and j, (i,j)∈Ep is the resource peak value relationship between time slot i and j, (i,j)∈Eup is the relationship between resource utilization of ith time slot and resource peak value of jth time slot;
determining that a currently available system resource is insufficient to initiate execution of the query based on the query resource consumption of the query;
placing the query into a query execution queue in response to the determination that the currently available system resource is insufficient for initiating the execution of the query;
determining that the currently available system resource is sufficient to initiate the execution of the query based on the query resource consumption of the query;
initiating the execution of the query in response to the determination that the currently available system resource is sufficient for initiating the execution of the query;
receiving a result of the query after the execution of the query is completed;
and
returning the result of the query to the client device.

267 more elements

A query management device, comprising:

a network communication interface configured to enable communication over a network;
a memory storage comprising instructions;
and
one or more processors in communication with the network communication interface and the memory storage, wherein the one or more processors execute the instructions to:
receive a query from a client device;
generate a query plan for the query by parsing the query to determine operators of the query and a sequence of the operators;
wherein the instructions comprise a machine learning technology configured to automate analytical model building:
generate, by using the machine learning technology, a predictive trained model;
predict a query resource consumption and resource usage extreme events for the query based on the query plan, using the predictive trained model, the predictive trained model being a co-prediction model configured to jointly model the query resource consumption and resource usage extreme events concurrently by taking into account a resource utilization relationship between a first time slot and a second time slot, a resource peak value relationship between the first time slot and the second time slot, and a relationship between resource utilization of the first time slot and resource peak value of the second time slot, the co-prediction model being represented by:
min
w
u
,
b
u
,
e
,
β
⁢
w
p
,
b
p
,
e
,
α
ℒ
u
=
1
2
⁢
W
p
T
⁢
W
p
+
1
2
⁢
γ
2
⁢
∑
i
e
i
2
+
1
2
⁢
W
u
T
⁢
W
u
+
1
2
⁢
γ
1
⁢
∑
i
e
i
2
-
∑
i
α
i
⁢
{
y
i
{
p
}
[
w
p
T
⁢
x
i
{
p
}
+
b
p
]
-
1
+
ε
i
}
-
∑
i
β
i
⁢
{
y
i
{
p
}
[
w
u
T
⁢
x
i
{
p
}
+
b
p
]
-
1
+
ε
i
}
-
γ
3
⁢
∑
(
i
,
j
)
∈
E
u
σ
i
⁢
j
[
w
u
T
⁢
x
i
{
u
}
-
w
u
{
T
}
⁢
x
j
{
u
}
]
-
γ
4
⁢
∑
(
i
,
j
)
∈
E
p
η
i
⁢
j
[
w
p
T
⁢
x
i
{
p
}
-
w
p
{
T
}
⁢
x
j
{
p
}
]
-
γ
5
⁢
∑
(
i
,
j
)
∈
E
u
⁢
p
ϑ
i
⁢
j
[
w
u
T
⁢
x
i
{
u
}
+
b
u
-
w
p
{
T
}
⁢
x
j
{
p
}
-
b
p
]
where (i,j)∈Eu is the resource utilization relationship between time slot i and j, (i,j)∈Ep is the resource peak value relationship between time slot i and j, (i,j)∈Eup is the relationship between resource utilization of ith time slot and resource peak value of jth time slot;
determine that a currently available system resource is insufficient to initiate execution of the query based on the query resource consumption of the query;
place the query into a query execution queue in response to the determination that the currently available system resource is insufficient for initiating the execution of the query;
determine that the currently available system resource is sufficient to initiate the execution of the query based on the query resource consumption of the query;
initiate the execution of the query in response to the determination that the currently available system resource is sufficient for initiating the execution of the query;
receive a result of the query after the execution of the query is completed;
and
return the result of the query to the client device.

272 more elements

Dependencies are inferred from the claim text.

Facts

Application: 15/959,442Filed 23 Apr 2018 · us provisional application
Priority: 1 May 2017
Published: US 2018/0314735 A11 Nov 2018
Granted: 27 Dec 2022
Term ends: Not estimatedThe source records don’t support an estimate.

Classification12 codes

G06F 16/24545
Plan optimisation: selectivity estimation or determination
G06F 16/217
Design, administration or maintenance of databases: database tuning
G06F 16/24532
Query optimisation: of parallel queries
G06F 16/24547
Query rewriting; Transformation: optimisations to support specific applications; Extensibility of optimisers