ParallelQueryOptimzation

ParallelQueryOptimzation - Parallel Query Optimisation...

Info iconThis preview shows pages 1–11. Sign up to view the full content.

View Full Document Right Arrow Icon

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
This is the end of the preview. Sign up to access the rest of the document.

Unformatted text preview: Parallel Query Optimisation Contents Objectives of parallel query optimisation Parallel query optimisation Two-Phase optimisation One-Phase optimisation Inter-operator parallelism oriented optimisation Search strategies used in optimisations Load balancing Objectives of Parallel Query Optimisation For a relational database query Several relational operators are executed Several execution orderings of the operators are possible Select the best ordering! The meaning of best ordering The trivial goal: the shortest response time for the query In multi-user databases: achieve maximal throughput with acceptable response time for every query Another goal: the least resource consumption A cost metric should be defined for the parallel system considering the resources shared either in time or in space for different operations. The optimiser counts the cost of the execution plans based on the given metric and searches for the minimum. Parallel Query Optimisation From sequential to parallel optimisation Two-Phase optimisation methodology One-Phase optimisation methodology Inter-operator parallelism oriented optimisation Summary From single-query to multi-query optimisation Schema of sequential relational query optimisation Execution of the query Normalisation Relational algebra expression Rewriting- Integration of integrity constraints- Push-down of selections and projections Normalized expression Processing Tree Join Ordering- Method choice Mapper Physical operator tree Parser Submitted Query Two-Phase parallel query optimisation Parallel resouce allocation- Allocation of the processors and the memory To the execution engine From the rewriter Annotated Processing Tree Join Ordering Module- Join ordering- Method choice Simplifies the optimisation process First phase can be a uni-processor optimiser industrial products choose such an approach Difficult to achieve optimal method choice without considering parallel resources Two-Phase optimisation in XPRS First phase A collection of good sequential processing trees for various memory sizes and processor numbers is retained If the optimiser cannot decide between two alternative strategies, a choose node is used hash probe index scan hash build R 1 CHOOSE R 1 R 2 seq scan index scan Two-Phase optimisation in XPRS Second phase The tree is split into tasks which could be executed in parallel (inter- operator parallelism) At any time only two tasks must be run in parallel (an I/O intensive and an another one) The processors and disks operate as close to their full utilisation as possible Dynamic parallel adjustment algorithm is used for the allocation of new tasks Good for shared-memory architecture and simple cost models Two and a half Phase method in Tandem NonStop SQL Two Phase method + post-pass optimisation to the join ordering (first phase) to optimise redistribution cost Developed for distributed memory systems (shared-nothing or shared-disk) First phase generates an optimal join ordering by taking into account the impact of redistribution Two and a half Phase method in...
View Full Document

Page1 / 34

ParallelQueryOptimzation - Parallel Query Optimisation...

This preview shows document pages 1 - 11. Sign up to view the full document.

View Full Document Right Arrow Icon
Ask a homework question - tutors are online