A Layered Aggregate Engine for Analytics Workloads fdbresearch.github.io relational.ai Maximilian Schleich University of Oxford Dan Olteanu , University of Oxford Mahmoud Abo Khamis , relationalAI Hung Q. Ngo , relationalAI XuanLong Nguyen , University of Michigan University of Washington July, 2019 1 / 11
Recall relationalAI Keynote: Analytics over Databases Current State of Affairs in Analytics Workloads Customers Weather Features Sales Stores Inventory Samples Demographic Items Carefully crafted by domain experts Throws away relational structure Comes with relational structure Can be order-of-magnitude larger 2 / 11
Turn Analytics Workload into Database Workload! Database Workload: Batches of Aggregate Queries Advantages : 1. Use DB Tools for Optimization 2. Decompose Aggregates into Views over Join Tree ◮ Pushing aggregate computation past joins ◮ Using different roots and directional views 3. Avoid Materialization of Data Matrix Challenge : Workloads require many aggregate queries 3 / 11
Aggregates are at the Core of Analytics Workloads Workload Query Batch # Queries Linear Regression SUM ( X i * X j ) 140 Covariance Matrix SUM ( X i ) GROUP BY X j COUNT(*) GROUP BY X i , X j Regression Tree VARIANCE ( Y ) WHERE X j = c j 270 (1 Node) Mutual Information COUNT(*) GROUP BY X i 106 Chow-Liu Trees COUNT(*) GROUP BY X i , X j Data Cubes SUM ( M ) GROUP BY X 1 , . . . , X d 40 (# Queries shown for Favorita Kaggle dataset) 4 / 11
Existing DBMSs are NOT Designed for Query Batches Relative Speedup for Our Approach over DBX and MonetDB 1000 100 10 1 C R C R C R C R Retailer Favorita Yelp TPC-DS C = Covariance Matrix; R = Regression Tree Node; AWS d2.xlarge (4 vCPUs, 32GB) 5 / 11
Tools of a Database Researcher 1. Exploit structure in the data ◮ Algebraic structure: Factorized aggregate computation ◮ Combinatorial structure: Query complexity measures 2. Sharing computation and data access ◮ Aggregates decomposed into views over join tree ◮ Share data access across views 3. Specialization for workload and data ◮ Generate code specific to the query batch and dataset ◮ Improve cache locality for hot data 4. Parallelization ◮ Task and domain parallelism 6 / 11
LMFAO: Layered Multi Functional Aggregate Optimization App → LMFAO Logical Optimization Code Optimization Application Merge Views Group Views Multi-Output Optimization Aggregate Aggregates Pushdown Parallelization Compilation Join Tree Find Roots 7 / 11
The Layers of LMFAO: Logical Optimization Application Q 1 : SUM ( units ) Aggregates Q 2 : SUM ( item · f ( date , color )) GROUP BY store Q 3 : SUM ( units · item ) GROUP BY color Join Tree Find Roots Sales Aggregate Items Pushdown Merge Views Holidays Transactions Group Views Multi-Output Optimization Stores Oil Parallelization Favorita Kaggle Dataset: Compilation Units sold for different items, stores, date. 8 / 11
The Layers of LMFAO: Logical Optimization Application Q 1 : SUM ( units ) Aggregates Q 2 : SUM ( item · f ( date , color )) GROUP BY store Q 3 : SUM ( units · item ) GROUP BY color Join Tree Q 1 Q 2 Find Roots Q 3 Sales Aggregate Items Pushdown Merge Views Holidays Transactions Group Views Multi-Output Optimization Stores Oil Parallelization Find Roots Layer: Compilation For each query, decide its output (root) node. Choose root which minimizes sizes of views. 8 / 11
The Layers of LMFAO: Logical Optimization Application Q 1 : SUM ( units ) Aggregates Q 2 : SUM ( item · f ( date , color )) GROUP BY store Q 3 : SUM ( units · item ) GROUP BY color Join Tree Q 1 Q 2 Find Roots Q 3 V I → S V Sales ′ I → S Aggregate V V S Items Pushdown S → T → H → I V S Merge Views Holidays Transactions Group Views V O R → T → T V Multi-Output Optimization Stores Oil Parallelization Aggregate Pushdown Layer: Compilation Break down each query into directional views over the join tree. Reuse Partial Aggregates & Merge Views with same group-by attributes. 8 / 11
The Layers of LMFAO: Code Optimization Application Q 1 : SUM ( units ) Aggregates Q 2 : SUM ( item · f ( date , color )) GROUP BY store Q 3 : SUM ( units · item ) GROUP BY color Join Tree Q 1 Q 2 Q 3 Group 6 Find Roots Group 7 V Q 3 I → S V ′ Q 1 , Q 2 , V S → I Sales I → S V S → I Aggregate V H → S V T → S Items Pushdown V I → S , V ′ I → S Group 5 Group 4 Merge Views Transactions Holidays V T → S V H → S Group 3 V O → T T Group Views → R V Group 2 Group 1 Multi-Output Optimization V R → T V O → T Stores Oil Parallelization Group Views Layer: 1. Construct Dependency Graph Compilation 2. Group Views that are computed over same relation 9 / 11
The Layers of LMFAO: Code Optimization Application Q 1 : SUM ( units ) Aggregates Q 2 : SUM ( item · f ( date , color )) GROUP BY store Q 3 : SUM ( units · item ) GROUP BY color Join Tree Q 1 Q 2 Q 3 Group 6 Find Roots Group 7 V Q 3 I → S V ′ Q 1 , Q 2 , V S → I Sales I → S V S → I Aggregate V H → S V T → S Items Pushdown V I → S , V ′ I → S Group 5 Group 4 Merge Views Transactions Holidays V T → S V H → S Group 3 V O → T T Group Views → R V Group 2 Group 1 Multi-Output Optimization V R → T V O → T Stores Oil Parallelization Multi-Output Optimization Layer: View Group is a computational unit in LMFAO. Compilation All views in one group are computed in one scan over the relation. 9 / 11
The Layers of LMFAO: Code Optimization Application Q 1 : SUM ( units ) Aggregates Q 2 : SUM ( item · f ( date , color )) GROUP BY store Q 3 : SUM ( units · item ) GROUP BY color Join Tree Q 1 Q 2 Q 3 Group 6 Find Roots Group 7 V Q 3 I → S V ′ Q 1 , Q 2 , V S → I Sales I → S V S → I Aggregate V H → S V T → S Items Pushdown V I → S , V ′ I → S Group 5 Group 4 Merge Views Transactions Holidays V T → S V H → S Group 3 V O → T T Group Views → R V Group 2 Group 1 Multi-Output Optimization V R → T V O → T Stores Oil Parallelization Parallelization Layer: Task parallelism: Evaluate independent groups in parallel Compilation Domain parallelism: Partition the large relation used by each group 9 / 11
The Layers of LMFAO: Code Optimization Application Q 1 : SUM ( units ) Aggregates Q 2 : SUM ( item · f ( date , color )) GROUP BY store Q 3 : SUM ( units · item ) GROUP BY color Join Tree Q 1 Q 2 Q 3 Group 6 Find Roots Group 7 V Q 3 I → S V ′ Q 1 , Q 2 , V S → I Sales I → S V S → I Aggregate V H → S V T → S Items Pushdown V I → S , V ′ I → S Group 5 Group 4 Merge Views Transactions Holidays V T → S V H → S Group 3 V O → T T Group Views → R V Group 2 Group 1 Multi-Output Optimization V R → T V O → T Stores Oil Parallelization Compilation Layer: Generate C++ code to compute each View Group. Compilation 9 / 11
Code Generation for Executing View Group 6 over Sales item date store Q 1 : SUM ( units ) Traverse Sales as a trie following an order of its join attributes 10 / 11
Code Generation for Executing View Group 6 over Sales V I foreach i ∈ π item ( S ✶ item V I ✶ item V ′ I ) item V ′ I foreach d ∈ π date ( σ item = i S ✶ date V H ✶ date V T ) V H date foreach s ∈ π store ( σ item = i , date = d S ✶ store σ date = d V T ) V T store Q 1 : SUM ( units ) Lookup into incoming views, e.g., V H , as early as possible 10 / 11
Code Generation for Executing View Group 6 over Sales α 0 = 0 ; V I foreach i ∈ π item ( S ✶ item V I ✶ item V ′ I ) item V ′ I α 1 = V I ( i ) α 3 = 0 ; foreach d ∈ π date ( σ item = i S ✶ date V H ✶ date V T ) V H date α 4 = V H ( d ); α 6 = 0 ; foreach s ∈ π store ( σ item = i , date = d S ✶ store σ date = d V T ) V T store α 8 = V T ( d , s ); α 9 = 0 ; foreach u ∈ π units σ item = i , date = d , store = s S : α 9 += u ; α 6 += α 8 · α 9 ; α 3 += α 4 · α 6 ; α 0 += α 1 · α 3 Q 1 = α 0 ; Q 1 : SUM ( units ) Insert code for partial aggregates as early as possible Reduces number of executed instructions 10 / 11
Code Generation for Executing View Group 6 over Sales α 0 = 0 ; V I foreach i ∈ π item ( S ✶ item V I ✶ item V ′ I ) item V ′ I α 1 = V I ( i ) α 2 = i ; α 3 = 0 ; foreach d ∈ π date ( σ item = i S ✶ date V H ✶ date V T ) V H date α 4 = V H ( d ); α 6 = 0 ; foreach s ∈ π store ( σ item = i , date = d S ✶ store σ date = d V T ) V T store α 8 = V T ( d , s ); α 9 = 0 ; foreach u ∈ π units σ item = i , date = d , store = s S : α 9 += u ; α 6 += α 8 · α 9 ; α 3 += α 4 · α 6 ; α 0 += α 1 · α 3 V S → I ( i ) = α 3 · α 2 ; Q 1 = α 0 ; V S → I : SUM ( units · item ) GROUP BY item Different outputs share partial aggregates 10 / 11
Recommend
More recommend
