1 / 25

A Scalable, Predictable Join Operator for Highly Concurrent Data Warehouses

A Scalable, Predictable Join Operator for Highly Concurrent Data Warehouses . George Candea (EPFL & Aster Data) Neoklis Polyzotis (UC Santa Cruz) Radek Vingralek (Aster Data). Highly Concurrent Data Warehouses. Data analytics is a core service of any DW.

candy
Download Presentation

A Scalable, Predictable Join Operator for Highly Concurrent Data Warehouses

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  1. A Scalable, Predictable Join Operator for Highly Concurrent Data Warehouses George Candea (EPFL & Aster Data) Neoklis Polyzotis (UC Santa Cruz) Radek Vingralek (Aster Data)

  2. Highly Concurrent Data Warehouses • Data analytics is a core service of any DW. • High query concurrency is becoming important. • At the same time, customers need predictability. • Requirement of actual customer: Increasing concurrency from one query to 40 should not increase latency by more than 6x.

  3. Shortcoming of Existing Systems • DWs employ the query-at-a-time model. • Each query executes as a separate physical plan. • Result: Concurrent plans contend for resources. • This creates a situation of “workload fear”.

  4. Our Contribution: CJOIN • A novel physical operator for star queries. • Star queries arise frequently in ad-hoc analytics. • Main ideas: • A single physical plan for all concurrent queries. • The plan is always ``on’’. • Deep work sharing: I/O, join processing, storage.

  5. Outline • Preliminaries • The CJOIN operator • Experimental study • Conclusions

  6. Setting • We assume a star-schema DW. • We target the class of star queries. • Goal: Executing efficiently concurrent star queries. • Low latency. • Graceful scale-up.

  7. Further Assumptions • Fact table is too large to fit in main memory. • Dimension tables are “small”. • Example from TPC-DS: 2.5GB of dimension data for 1TB warehouse. • Indices and materialized views may exist. • Workload is volatile.

  8. Outline • Preliminaries • The CJOIN operator • Experimental study • Conclusions

  9. Design Overview CJOIN Preprocessor Filter Filter Distributor Star Queries Query Stream Optimizer Other Queries Conventional Query Processor

  10. Running Example Schema Queries select COUNT(*) from F join X join Y where φ1(X) and ψ1 (Y) Dimension X Q1 m Fact Table F Dimension Y select SUM(F.m) from F join Y where ψ2 (Y) join X and TRUE(X) Q2

  11. The CJOIN Operator COUNT Q1 SUM Q2 Preprocessor Filter Filter Distributor Fact Table F Continuous Scan

  12. The CJOIN Operator Q1 Q1 Q2 Q2 Hash Table X Hash Table Y Query Start COUNT 1 1 1 0 Q1 Q1: a 0 1 Q2: b * 1 1 SUM 0 1 Q2 0 0 * Preprocessor Filter Filter Distributor Fact Table F Dimension X Dimension Y Continuous Scan Q1 Q1∧ −Q2 a −Q1∧ Q2 b Q1∧ Q2

  13. Processing Fact Tuples Q1 Q1 Q2 Q2 Hash Table X Hash Table Y Query Start COUNT 1 1 0 1 Q1 Q1: a 0 1 Q2: b * 1 0 1 0 1 1 1 1 SUM 0 1 Q2 0 0 * 1 0 Q1 Q2 Q1 Q1 Q1 Q2 Q2 Q2 1 1 Preprocessor Filter Filter Distributor Fact Table F Continuous Scan a b

  14. Registering New Queries Q1 Q1 Q2 Q2 Hash Table X Hash Table Y Query Start Q3 COUNT Q1 Q2 Q3 1 1 0 1 Q1 Q1: a 1 1 1 0 0 1 Q2: b * 1 1 1 1 1 1 1 1 SUM 1 1 0 1 0 1 Q2 1 0 0 0 1 0 * * 1 Q1 Q2 Q1 Q1 Q1 Q2 Q2 Q2 1 1 Preprocessor Filter Filter Distributor select * from X where φ3(Χ) Fact Table F Dimension X select AVG(F.m) from F join X where φ3(X) Continuous Scan Q1 ∧ −Q3 join Y and TRUE(Y) Q3 a −Q1 ∧ Q3 b

  15. Registering New Queries Q1 Q2 Hash Table X Hash Table Y Query Start Q3 COUNT Q1 Q2 Q3 1 0 Q1 Q1: a 1 1 1 0 Q2: b 0 1 1 0 1 1 1 1 1 1 1 SUM 1 1 0 1 0 1 Q2 0 1 1 Q3: c 1 0 0 0 1 0 * * Q3 1 AVG Q1 Q2 Q3 Q1 Q1 Q1 Q2 Q3 Q2 Q3 Q2 Q3 1 1 1 c: Begin Q3 Begin Q3 Begin Q3 Preprocessor Filter Filter Distributor Fact Table F select AVG(F.m) from F join X where φ3(X) Continuous Scan join Y and TRUE(Y) a c b

  16. Properties of CJOIN Processing • CJOIN enables a deep form of work sharing: • Join computation. • Tuple storage. • I/O. • Computational cost per tuple is low. • Hence, CJOIN can sustain a high I/O throughput. • Predictable query latency. • Continuous scan can provide a progress indicator.

  17. Other Details (in the paper) • Run-time optimization of Filter ordering. • Updates. • Implementation on multi-core systems. • Extensions: • Column stores. • Fact table partitioning. • Galaxy schemata. x n Preprocessor Filter Filter Filter Distributor

  18. Outline • Preliminaries • The CJOIN operator • Experimental study • Conclusions

  19. Experimental Methodology • Systems: • CJOIN Prototype on top of Postgres. • Postgres with shared scans enabled. • Commercial system X. • We use the Star Schema Benchmark (SSB). • Scale factor = 100 (100GB of data). • Workload comprises parameterized SSB queries. • Hardware: • Quad-core Intel Xeon. • 8GB of shared RAM. • RAID-5 array of four 15K RPM SAS disks.

  20. Effect of Concurrency Throughput increases with more concurrent queries.

  21. Response Time Predictability Query latency is predictable; no more workload fear.

  22. Influence of Data Scale Concurrency level: 128 CJOIN is effective even for small data sets.

  23. Related Work • Materialized views [R+95,HRU96]. • Multiple query Optimization [T88]. • Work Sharing. • Staged DBs [HSA05]. • Scan Sharing [F94, Z+07, Q+08]. • Aggregation [CR07]. • BLINK [R+08]. • Streaming database systems [M+02, B+04].

  24. Conclusions • High query concurrency is crucial for DWs. • Query-at-a-time leads to poor performance. • Our solution: CJOIN. • Target: Class of star queries. • Deep work sharing: I/O, join, tuple storage. • Efficient realization on multi-core architectures. • Experiments show an order of magnitude improvement over commercial system.

  25. http://people.epfl.ch/george.candea http://www.cs.ucsc.edu/~alkis http://www.asterdata.com THANK YOU!

More Related