1 / 35

Speculative Query Processing

Speculative Query Processing. Alkis Polyzotis Yannis Ioannidis. Speculation. Definition (Webster dict.): “to assume a business risk in hope of gain” Success Stories: Computer Architecture Operating Systems Database Systems. Motivation. Timeline. Select :. name. From employee. From :.

azuka
Download Presentation

Speculative Query Processing

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. Speculative Query Processing Alkis Polyzotis Yannis Ioannidis

  2. Speculation • Definition (Webster dict.): “to assume a business risk in hope of gain” • Success Stories: • Computer Architecture • Operating Systems • Database Systems

  3. Motivation Timeline Select: name From employee From: employee Where age<25 Where: age<25 Select name GO! GO!

  4. From employee Where age<25 Select name GO Original Cost: New Cost: Applying Speculation Select * From employee Where age<25 Into T Select name From T

  5. Roadmap • Introduction • Speculative Processing Framework • Speculative Manipulations • Cost Optimization • Learning the User • Experimental Results • Conclusions

  6. Setting • Interactive exploration • Ad-hoc queries • Long sessions • Read-only database Final Query DB Query Interface Partial Query

  7. Speculator Learner Framework Query Interface DB Speculative Actions Speculative Query Partial Query Possible Manipulations Cost Optimizer Actions User Model Cost Model

  8. Example Application Query Interface DB Select * From employee Where age<25 Into T1 Speculative Actions Select … From employee Where age<25 Materialize age<25 Cost Optimizer Speculator Cost Model Learner

  9. Roadmap • Introduction • Speculative Processing Framework • Speculative Manipulations • Speculative Actions • Composition Rules • Cost Optimization • Learning the User • Experimental Results • Conclusions

  10. Speculative Actions • Prepare DB for speculated query • Types: • Data Staging • Histogram Creation • Index Creation • Materialization • Simple • Complex

  11. Data Staging • Pre-fetch pages of relations • Objective: warm up the buffer pool • Low risk • Difficult to implement!

  12. Histogram Creation • Create histograms on predicate fields • Objective: Help DB Optimizer • Low Risk

  13. Index Creation • Create indices on predicate fields • Objectives: • Provide additional access methods • Provide better statistics • Low Risk

  14. T S Simple Materializations • Materialize predicates in new tables • High gain • Increased risk S R

  15. T W Complex Materializations • Materialize joins in new tables • Very high gain • Very high risk! Y W R S

  16. Roadmap • Introduction • Speculative Processing Framework • Speculative Manipulations • Speculative Actions • Composition Rules • Cost Optimization • Learning the User • Experimental Results • Conclusions

  17. + Create Index on T Composition Rules S R

  18. R S S R Composition Rules • May cancel previous manipulations

  19. Roadmap • Introduction • Speculative Processing Framework • Speculative Manipulations • Cost Optimization • Learning the User • Experimental Results • Conclusions

  20. Cost Optimization • Select most promising manipulation • Evaluate cost in terms of: • Gain vs. risk • Possible final query • User’s behavior

  21. Roadmap • Introduction • Speculative Processing Framework • Speculative Manipulations • Cost Optimization • Learning the User • Experimental Results • Conclusions

  22. Survival of predicates • Will s appear in the final query? • Perceptron learner • Temporal Differences learning • Train on recent actions of the user • Large learning rate

  23. Probability of joins • Will R,S be joined in the final query? • Bayesian learner • Re-train on previous n queries

  24. Roadmap • Introduction • Speculative Processing Framework • Speculative Manipulations • Cost Optimization • Learning the User • Experimental Results • Conclusions

  25. Methodology • Collect actual traces • Users explore a TPC-H dataset • Query interface records actions & timestamps • Replay traces • Normal Processing • Speculative Processing

  26. Performance Evaluation • Metric: Absolute Improvement • Metric calculated as a histogram • Source parameter: execution time

  27. Configuration • Dual Pentium-III 500 MHz • 1GB physical memory • Linux RH 6.2 • Oracle Server 8.1.6 • DB uses files, not raw partition Thank you Remzi!

  28. Experiment I: Comparing Gain Buffer: 32MB Data Set: 100MB No Indices No Histograms

  29. Experiment I:Comparing Gain Buffer: 32MB Data Set: 100MB No Indices No Histograms

  30. Experiment II: Using the Cost Model Buffer: 32MB Data Set: 100MB Indices & Histograms

  31. Experiment III: Performance Buffer: 32MB Data Set: 100MB Indices & Histograms

  32. Experiment III: Performance Buffer: 320MB Data Set: 100MB Indices & Histograms

  33. Experiment III: Performance Buffer: 32MB Data Set: 300MB Indices & Histograms

  34. Conclusions • Speculative Processing Framework • Speculative Manipulations • Cost Optimization • Experimental Results: • Materializations offer most gain • Cost model avoids penalties • Performance improvement up to 40%

  35. Future Work • Evaluate speculation on: • larger dataset • bigger schema • Apply speculation in: • Search queries • Web queries • Multi-user database • Client collaboration • Use speculation in the server

More Related