System for multi-store analytics execution environments with storage constraints
View Patent ↗Systems and methods are disclosed for managing a multi-store execution environment by applying opportunistic materialized views to improve workload performance and executing a plan on multiple database engines to increase query processing speed by leveraging unique capabilities of each engine by enabling stages of a query to execute on multiple engines, and by moving materialized views across engines.
1. A method for managing an execution environment, comprising:
applying opportunistic materialized views;
executing a plan on multiple database engines to increase query processing speed by enabling stages of a query to execute on multiple engines, and by moving materialized views across engines;
determining a multi-store execution plan P, as:
P =( G,ƒ,t )
where G represents a directed acyclic graph (DAG) for a plan, and is ordered topologically with a vertex in G corresponding to a stage s in plan P, ƒ:P E is a mapping function that maps a stage in P to an execution engine in E, t:V E is a transfer function that indicates transfer of views in V to an engine in E, and a stage sεP may be executed on any engine; and
determining a multi-store configuration created after executing all plans prior to P i by:
inputDesign
(
P
i
,
D
)
=
{
D
,
i
=
1
outputDesign
(
P
i
-
1
,
inputDesign
(
P
i
-
1
,
D
)
)
,
otherwise
where outputDesign(P,D)=outputDesign(s′,D), where s′ is the last stage of P.
2. The method of claim 1 , comprising moving views across engines by piggybacking moves along a query execution path.
3. The method of claim 1 , comprising minimizing an execution time of a sequence of queries by creating beneficial physical designs through the movement of views during stages of query execution.
4. The method of claim 1 , comprising selecting subset of views for each store such that a storage budget and a transfer budget constraints are satisfied.
5. The method of claim 1 , comprising retaining opportunistic views from previous versions of the query for future versions of the query.
6. The method of claim 1 , comprising generating a sequence of multi-store execution plans P 1 , P 2 , . . . , Pn that minimizes a cost of executing an input query sequence.
7. The method of claim 6 , comprising ensuring a predetermined set of views are moved to a correct engine early during workload processing.
8. The method of claim 6 , comprising minimizing the cost of the workload sequence by moving views at a predetermined time in the sequence.
9. The method of claim 6 , comprising identifying a sequence that provides the opportunity to move views at a predetermined time.
10. The method of claim 1 , comprising benefit computations to a sequence of the next m queries, rather than all remaining queries to optimize locally using a sliding window over a future query sequence.
11. The method of claim 1 , comprising determining:
inputDesign
(
s
,
D
)
=
D
f
(
s
)
⋃
{
v
:
∃
s
′
∈
G
,
v
∈
t
(
s
′
,
s
)
,
(
s
′
<
s
)
,
(
f
(
s
′
)
=
f
(
s
)
)
}
⋃
outputDesign
(
prev
(
s
,
f
(
s
)
)
,
D
)
;
where outputDesign(s,D) is a union of inputDesign(s,D) and all opportunistic views created by the engine executing stage s, and is defined as follows:
outputDesign( s,D )=inputDesign( s,D )∪opviews( s ,ƒ( s )).
12. The method of claim 1 , comprising determining a cost of executing a stage on an engine as:
cost( s ,ƒ( s ),inputDesign( s,D )).
13. The method of claim 1 , comprising determining a cost to transfer a view is given by:
t cost( v ,ƒ( s a ),ƒ( s b )).
14. The method of claim 1 , comprising determining a cost for a plan P given a multistore configuration D as:
cost
(
P
,
D
)
=
∑
s
∈
G
cost
(
s
,
f
(
s
)
,
inputDesign
(
s
)
)
+
∑
(
s
a
,
s
b
)
∈
G
∑
v
∈
t
(
s
a
,
s
b
)
tcost
(
v
,
f
(
s
a
)
,
f
(
s
b
)
)
.
15. The method of claim 1 , comprising optimizing an objective function given as input a sequence of queries q 1 , q 2 , . . . , q n and a multi-store design D, generating a sequence of multi-store execution plans P 1 , P 2 , . . . , P n that minimizes the cost of executing the input query sequence with objective function as:
minimize
∑
i
cost
(
P
i
,
inputDesign
(
P
i
,
D
)
)
.