Download Advanced Database Systems-Lecture 13 Slides-Computer Science and more Slides Database Management Systems (DBMS) in PDF only on Docsity!
Query Processing: A Systems View
CPS 216
Advanced Database Systems
2
Announcements (March 1)
Ā Reading assignment due Wednesday
Ā Buffer management
Ā Homework #2 due this Thursday
Ā Course project proposal due in one week
Ā Midterm next Thursday in class
Ā Open book, open notes
3
Physical (execution) plan
Ā A complex query may involve multiple tables and
various query processing processing algorithms
Ā E.g., table scan, index nested-loop join, sort-merge join,
hash-based duplicate eliminationā¦
Ā A physical plan for a query tells the DBMS query
processor how to execute the query
Ā A tree of physical plan operators
Ā Each operator implements a query processing algorithm
Ā Each operator accepts a number of input tables/streams
and produces a single output table/stream
Examples of physical plans
Ā Many physical plans for a single query
Ā Equivalent results, but different costs and assumptions! )DBMS query optimizer picks the ābestā possible physical plan
PROJECT ( title ) INDEX-NESTED-LOOP-JOIN ( CID )
Index on Enroll( SID )
Index on Course ( CID )
Index on Student ( name )
INDEX-SCAN ( name = āBartā)
INDEX-NESTED-LOOP-JOIN ( SID )
PROJECT ( title ) MERGE-JOIN ( CID )
SORT ( CID )SCAN (Course) MERGE-JOIN ( SID )
SCAN ( Enroll )
SORT ( SID )
SCAN ( Student )
FILTER ( name = āBartā)
SELECT Course.title FROM Student, Enroll, Course WHERE Student.name = āBartā AND Student.SID = Enroll.SID AND Enroll.CID = Course.CID;
5
Physical plan execution
Ā How are intermediate results passed from child
operators to parent operators?
Ā Temporary files
- Compute the tree bottom-up
- Children write intermediate results to temporary files
- Parents read temporary files
Ā Iterators
- Do not materialize intermediate results
- Children pipeline their results to parents
6
Iterator interface
Ā Every physical operator maintains its own execution
state and implements the following methods:
Ā open(): Initialize state and get ready for processing
Ā getNext(): Return the next tuple in the result (or a null
pointer if there are no more tuples); adjust state to allow
subsequent tuples to be obtained
Ā close(): Clean up
Blocking vs. non-blocking iterators
Ā A blocking iterator must call getNext()
exhaustively (or nearly exhaustively) on its children
before returning its first output tuple
Ā Examples:
Ā A non-blocking iterator expects to make only a few
getNext() calls on its children before returning its
first (or next) output tuple
Ā Examples:
11
Execution of an iterator tree
Ā Call root.open()
Ā Call root.getNext() repeatedly until it returns null
Ā Call root.close()
) Requests go down the tree
) Intermediate result tuples go up the tree
) No intermediate files are needed
12
Memory management for DBMS
Ā DBMS operations require main memory
Ā While data resides on disk, it is manipulated in memory Ā Sometimes the more memory the better, e.g., sort
Ā One approach: let each operation pre-allocate some amount
of āprivateā memory and manage it explicitly
Ā Not very flexible Ā Limits sharing and reuse
Ā Alternative approach: use a buffer manager
Ā Responsible for reading/writing data blocks from/to disk as needed Ā Higher-level code can be written without worrying about whether data is in memory or not
Buffer manager basics
Ā Buffer pool: a global pool of frames (main-memory blocks)
)Some systems use separate pools for different objects (e.g., tables and indexes) and for different operations (e.g., sorting and others)
Ā Higher-level code can pin and unpin a frame
Ā Pin: I need to work on this frame in memory Ā Unpin: I no longer need this frame Ā A completely unpinned frame is a candidate for replacement )In some systems you can hate a frame (i.e., suggesting it for replacement)
Ā A frame becomes dirty when it is modified
Ā Only dirty frames need to be written back to disk )Related to transaction processing
14
Standard OS replacement policies
Ā Example
Ā Current buffer pool: 0, 1, 2 Ā Past requests: 0, 1, 2 Ā Incoming requests: 3, 0, 1, 2, 3, 0, 1, 2, 3, 4, 5, 6, 7, ā¦ )Which frame to replace?
Ā Optimal: replace the frame that will not be used for the
longest time (2)
Ā Random (0, 1, or 2 with equal probability)
Ā LRU: least recently used (0)
Ā LRU approximation: clock, aging
Ā MRU: most recently used (2)
15
Problems with OS buffer management
Stonebraker. āOperating System Support for Database Management.ā CACM , 1981.
Ā Performance problems
Ā Getting a page from the OS to user space is usually a system call (process switch) and copy
Ā Replacement policy
Ā LRU, clock, etc. often ineffective Ā DBMS knows access pattern in advance and therefore should dictate policy ā major OS/DBMS distinction
Ā Prefetch policy
Ā DBMS knows of multiple āordersā for a set of records; OS only knows physical order
Ā Crash recovery
Ā DBMS needs more control
Hot set algorithm
) Exploit query behavior more!
Ā A set of pages that are accessed over and over form a hot set
Ā āHot pointsā in the graph of buffer size vs. number of page faults Ā Example: For nested-loop join R S , size of hot set is B ( S ) + 1 (under LRU)
Ā Each query is given enough memory for its hot set
Ā Admission control: Do not let a query into the system
unless its hot set fits in memory
Ā Replacement: LRU within each hot set (seems arbitrary)
Ā Derivation of hot set assumes LRU, which may be
suboptimal
Ā Example: What is better for nested-loop join?
20
Query locality set model
Ā Observations
Ā DBMS supports a limited set of operations
Ā Reference patterns are regular and predictable
Ā Reference patterns can be decomposed into simple
patterns
Ā Reference pattern classification
Ā Sequential
Ā Random
Ā Hierarchical
21
Sequential reference patterns
Ā Straight sequential: read something sequentially once
Ā Example: selection on unordered table )Each page is only touched once, so just buffer one page
Ā Clustered sequential: repeatedly read a āchunkā sequentially
Ā Example: merge join; rows with the same join column value are scanned multiple times )Keep all pages in the chunk in buffer
Ā Looping sequential: repeatedly read something sequentially
Ā Example: nested-loop join )Keep as many pages as possible in buffer, with MRU replacement
Random reference patterns
Ā Independent random: truly random accesses
Ā Example: index scan through a non-clustered (e.g.,
secondary) index yields random data page access
)The larger the buffer the better?
Ā Clustered random: random accesses that happen to
demonstrate some locality
Ā Example: in an index nested-loop join, inner index is
non-clustered and non-unique, while outer table is
clustered and non-unique
)Try to keep in buffer data pages of the inner table
accessed in one cluster
23
Hierarchical reference patterns
Ā Example: operations on tree indexes
Ā Straight hierarchical: regular root-to-leaf traversal
Ā Hierarchical with straight sequential: traversal
followed by straight sequential on leaves
Ā Hierarchical with clustered sequential: traversal
followed by clustered sequential on leaves
Ā Looping hierarchical: repeatedly traverse an index
Ā Example: index nested-loop join
)Keep the root index page in buffer
24
DBMIN algorithm
Ā Associate a chunk of memory with each file instance (each
table in FROM)
Ā This chunk is called the file instanceās locality set Ā Instances of the same table may share buffered pages Ā But each locality set has its own replacement policy )Based on how query processing uses each relation (finally!) )No single policy for all pages accessed by a query )No single policy for all pages in a table
Ā Estimate locality set sizes by examining the query plan and
database statistics
Ā Admission control: a query is allowed to run if its locality
sets fit in free frames
Locality sets for more ref. patterns
Ā Straight hierarchical, hierarchical/straight sequential: just
like straight sequential
Ā Size = 1 Ā Just replace as needed
Ā Hierarchical/clustered sequential: like clustered sequential
Ā Size = number of index pages in the largest cluster Ā FIFO or LRU
Ā Looping hierarchical
Ā At each level of the index you have random access among pages Ā Use Yaoās formula to figure out how many pages need to be accessed at each level Ā Size = sum over all levels that you choose to worry about Ā LIFO with 3-4 buffers should be okay
29
Simulation study
Ā Hybrid simulation model
Ā Trace-driven simulation
- Recorded from a real system (running Wisconsin Benchmark)
- For each query, record its execution trace
- Page read/write, file open/close, etc.
Ā Distribution-driven simulation
- Generated by some stochastic model
- Synthesize the workload by merging query execution traces
Ā Simulator models CPU, memory, and one disk
Ā Performance metric: query throughput
30
Workload
Ā Mix 1: all six types equally likely
Ā Mix 2: I and II together appear 50% of the time
Ā Mix 3: I and II together appear 75% of the time
Mix 1 (no data sharing)
Ā Thrashing is evident
for simple algorithms
with no load control
Ā Working set (a popular
OS choice) fails to
capture join loops for
queries with high
memory demand (types
V and VI)
Ā It still functions (though suboptimally) with large number of current queries (NCQ)
DBMIN Hot set
Working set
32
Mix 3 (no data sharing)
Ā Thrashing is still
evident
Ā Working set fares
better because mix 3
has more simple
queries and fewer
ones of types V and
VI
DBMIN Hot set
Working set
33
Mix 1 (full data sharing)
Ā With full data
sharing, locality is
easier to capture
Ā Performance
improves across the
board and the gap
disappears
Ā Random and FIFO
do not capture
locality as effectively
as others
DBMIN and others Random and FIFO
Conclusion
Ā Same basic access patterns come up again and again
in query processing
Ā Make buffer manager aware of these access patterns
) Look at the workload, not just the content
Ā Contents can at best offer guesses at likely workloads
