1 / 44

CSC 556 – DBMS II, Spring 2013

CSC 556 – DBMS II, Spring 2013. April 10 & 17, 2013 Storage media hierarchies, external sorts, B-trees. Storage medium abstraction. Storage medium as a subclass of an interface allows you to prototype storage medium semi-independently from DBMS structures atop it. Sequential File I/O.

dane
Download Presentation

CSC 556 – DBMS II, Spring 2013

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. CSC 556 – DBMS II, Spring 2013 April 10 & 17, 2013 Storage media hierarchies, external sorts, B-trees

  2. Storage medium abstraction • Storage medium as a subclass of an interface allows you to prototype storage medium semi-independently from DBMS structures atop it.

  3. Sequential File I/O • Queue abstraction supports sequential file I/O or core I/O via a series of enqueue, peek & dequeue calls. Supplies one-record lookahead. • External sorts, i.e., sorts where the data do not fit into memory, rely on this approach. • A record size entry can precede any variable length record in the file, OR • A sentinel value can mark a record’s end.

  4. Direct Access File I/O • Direct access (a.k.a. random access) file I/O uses a seek system call to locate a position with a direct-access (binary) data file. • man lseek, fseek, ftell • Offsets are from 0, or current seek position, or end. • Unix 3C library builds atop level 2 system calls. DBMS may access level 2 system calls directly. • It treats the file as an array where a seek address is an offset into the array. • Applications attempt to keep contiguous records in contiguous blocks.

  5. Merge sort & Sequential File I/O • The split phase partitions alternate runs from a queue into two helper queues, where the initial queue is the file to be sorted, and the helpers are temporary files. • A run is a sorted subsequence. • The merge phase doubles the run length by merging peer runs from the helper queues back into the initial queue. • Natural-length merge sort inspects the data to locate run boundaries. It may stumble onto big runs. • Another variants uses an internal sort such as quicksort to build larger initial runs in memory. • A file-based sort is an external sort.

  6. April 10 example of merge sort • End of split phase with run length = 1. • End-of-run record is underlined. • Destination of this phase is in bold.

  7. April 10 example of merge sort • End of split phase with run length = 1. • End of merge phase grows run length to 2.

  8. April 10 example of merge sort • End of split phase with run length = 2. • End of merge phase grows run length to 4.

  9. April 10 example of merge sort • End of split phase with run length = 4. • End of merge phase grows run length to 8.

  10. April 10 example of merge sort • End of split phase with run length = 8. • End of merge phase grows run length to 16.

  11. Merge sort is O(n log(n)) • Picture a merge sort as a tree growing up from N runs of length 1 to 1 run of length N.

  12. April 10 radix10 sort (bucket sort) • This sort also uses sequential file I.O. • Initial sequence of integers. • Sequence normalized to non-negative values with digits to accommodate the largest.

  13. Number temp queues = radix

  14. Number temp queues = radix

  15. Number temp queues = radix

  16. Number temp queues = radix • Sort is O(N x D) for N items and D digits, but • D is a constant and does not affect growth rate • Radix sort is therefore O(N) on data size • It requires a fixed-bit-width key field. • Fixed-width fields required for a relational DBMS. • It has a lot of copy overhead. • Use a radix with many bits, many (smaller) files.

  17. Base-2numbits code. void radixsort(interface_queueOfInts *queueToSort, int numbits, interface_queueOfInts * temporaryQueues[]) { const int numqueues = (1 << numbits); int mask = ~(~0 << numbits); const int allbits = sizeof(int) * 8 ; // bits per sorted value for (int shifter = 0 ; shifter < allbits ; shifter += numbits) { splitphase(queueToSort, temporaryQueues, shifter, mask); mergephase(queueToSort, temporaryQueues, numqueues); } }

  18. Base-2numbits code. static void splitphase(interface_queueOfInts *merger, interface_queueOfInts * splitter[], int shifter, int bitmask) { bool ignoreme ; // We only peek on queues with data, etc. while (merger->canPeek()) { // while there are runs to split int value = merger->peek(ignoreme); merger->dequeue(); int qid = (value >> shifter) & bitmask ; splitter[qid]->enqueue(value);}}

  19. Base 2numbits code. static void mergephase(interface_queueOfInts *merger, interface_queueOfInts * splitter[], int numqueues) { bool ignoreme ; for (int qtodrain = 0 ; qtodrain < numqueues ; qtodrain++) { while (splitter[qtodrain]->canPeek()) { merger->enqueue(splitter[qtodrain]->peek(ignoreme)); splitter[qtodrain]->dequeue(); } }}

  20. MultiSet is a B+-tree Map over various storage subclasses • DataMine/mset/MultiSet.h

  21. Relational DBMS • Flat, fixed width records (tuples) fit into contiguous memory locations & file blocks. • Take the least common multiple (LCM) of the record size and block size, and allocate using that. • Unix lseek and fcntl are the primary system calls. Windows has counterparts. The low-end cylinder allocation on the disk is managed by the operating system.

  22. B-trees and index files • B-trees are balanced binary trees; typically degree D > 2, as determined by block size. • How many B-tree node records fit into a disk block? • Each node holds between D/2 and D entries. • The root is an exception. It can hold < D/2 entries. • In B+-trees the leaves hold the actual data, typically as seek indices into the contiguous database file. B+-trees also link the leaves into a sorted chain for range-based serial access. • B+-tree interior nodes hold pointers to children. • A B-tree grows from the bottom up whenever an insertion causes a node to split.

  23. MultiSet • MultiSet uses sets of keys or multisets of keys (duplicate keys allowed) to map to application data elements. • Search includes ==, <, <=, > or >= key. • First or last key occurrence for actual multisets. • Greatest lesser value when key is not present. • Also supports least greater value. • Serial linked list at leaves (B+ tree) supports duplicate keys & iterating over results, including following operations. • A result is a MultiSet amenable to union, intersection and set difference with other MultiSet objects.

  24. MultiSet.h typedef unsigned long location ; const unsigned BTREEDEGREE = 16 ; template <class KeyType> struct treenode { // basic internal node location parent ; // type treenode location child[BTREEDEGREE] ; // treenodes or leafnodes KeyType key[BTREEDEGREE] ;} // min keys for those children template <class KeyType> struct leafnode { // leaf connects treenodes to treeelems location parent ; // type treenode location prev, next ; // siblings or cousins location child[BTREEDEGREE] ; // treeelems KeyType key[BTREEDEGREE] ;} // keys for those children

  25. MultiSet.h template <class ElementType> struct treeelem { /* a btree leaf element */ ElementType element ; // main contents location next ; // next avail. if needed for a free list } ; template <class ElementType, class KeyType> class MultiSet { /* ElementType is the type of the set's elements. KeyType is the type, typically part or possibly all of an ElementType object, that constitutes a search key. ... */

  26. Abstract location • Location is an unsigned long that is either a seek offset (file) or a cast of an object pointer. • MultiSet records the depth of the tree. • Interior nodes are type treenode. • Leaf nodes are type leafnode. • Leaf nodes point to treeelem application data. • Those may be app data or may be record indices into another file of flat data records.

  27. Searching through the B-tree • findleaf uses findsubtree on interior nodes. • Uses O(log n) binary search on interior node. • Calls findsubtree in a loop until hitting the leaves. • Multi-key version finds first or last instance. • findslot returns the array index inside a node.

  28. Insertion in the B-tree • randominsert starts at the root • Initial element is a special case. • Root node is always special because it may contain fewer than degree / 2 entries. • Otherwise, if it fits into a node, put it there. • When the node is full, split into two nodes of size N/2 and N/2 + 1. • Add the new node to its parent. • If that parent node was already full, split it recursively.

  29. Deletion from a B-tree • There is no problem when number of entries remains >= N/2. Just slide entries above the deleted entry down to cover the deleted one. • When entries < N/2, try merging with neighbors in a serial chain (siblings or cousins). • If there are too few for that, merge into one node. Other is empty, delete it from its parent. • If parent entries < N/2, perform recursive delete. • See deleteelem and deleteleaf in MultiSet.

  30. http://en.wikipedia.org/wiki/B-tree • keeps keys in sorted order for sequential traversing • uses a hierarchical index to minimize the number of disk reads • uses partially full blocks to speed insertions and deletions • keeps the index balanced with an elegant recursive algorithm • minimizes waste by making sure the interior nodes are at least half full

  31. CoreMultiSet & FileMultiSet • storage read / write via abstract location • readnode / readleaf / readelem • writenode / writeleaf / writeelem • allocnode / allocleaf / allocelem • freenode / freeleaf / freeelem • FileMultiSet maintains free lists of the above. • Flat file structure in main relational file makes storage management of free list “easy.”

  32. Other Indexing – Skip Lists • Skip lists support probabilistic log(N) lookup, insertion & deletion of a key -> value mapping. • We will review Pugh’s paper from the 1990’s. • A skip list links each mapping in a series of key-sorted linked lists, in which higher-order lists contain fewer members, acting as “highways” & “boulevards” in locating keys. • Skip lists provide better support for concurrency than some balanced tree algorithms by avoiding global tree restructuring on rebalancing.

  33. Other Indexing – Hashing • Hashing approaches O(1) (constant-time) lookup for an ideal hash function. • The ideal hash function preserves all of the bits of distinguishing information is a search key, while folding them into fewer bits to use as a lookup index into an array, an index file, or a flat file of fixed-width records. • Hash tables disambiguate key collisions either by storing colliding elements in a linked list (chained hashing) or by rehashing to a new bucket (open address hashing). • Hashing supports only == tests, not <, <=, >, >=.

  34. Other Indexing – Sorting • When a sequence of fixed-size records or indices are sorted in a file of contiguous records, binary search is a viable option for locating a key. • Hashing and sorting are particularly appropriate for indexing on-the-fly, temporary result sets to be combined via intersection, union or set difference. • Approximate O(1) hashing is fast when combining result sets based only on equality. • Sort-based search and merging are appropriate when a query requests a result set sorted on an attribute.

  35. Query Processing (Elmasri & Navathe chapter 19) • Translate SQL statements into abstract syntax tree using basic compilation techniques. • Interpret the abstract syntax tree. • ORDER-BY and elimination of duplicate tuples in a PROJECT are supported by external sort. • Duplicate elimination can use low-level memcmp byte comparison when comparing fixed-size records. • If SORT is based in an index key or composite index key, or if query does not entail PROJECT, then a B+-tree index avoids need for a sort.

  36. SELECT Processing • Use indexed fields as primary search keys. • Use index-based set intersection, union and difference operations to support AND, OR and NOT. • Use slow (O(n)) sequential search, or external sorting (O(n log(n)) where appropriate (ORDER-BY or duplicate elimination) combined with binary search. • Use hashing for == matching. • Use composite search key indexing or hashing. • Utilize selectivity where possible.

  37. JOIN Processing • Nested-loop is brute force approach (O(NK)). • Single-loop when join attributes are indexed. • Sort-merge join requires records to be sorted on the join attributes, and then merged. • Partition-hash join hashes smaller of two contributing relations into chained hash table. • Larger relation is then hashed on join attributes to retrieve tuples from the smaller.

  38. PROJECT Processing • If projection includes a distinguishing key, the projected tuples are already unique. Just select a subset of the query results. • Otherwise, DISTINCT projections require sorting based on entire tuple to eliminate duplicates. • It is also possible to hash on entire tuples to eliminate duplicates.

  39. Approaches to Query Optimization • Pipelined or stream-based processing of query stages across multiple thread / memory units. • Compiler optimization techniques such as common subexpression elimination. • Functional approaches such as lazy evaluation. • Use meta-data and heuristics such as size of contributing relations (smaller is faster and may fit into memory), key distribution data.

  40. Costs to consider • Access cost (disk I/O), e.g., NFS vs. local disk. • Disk storage cost for intermediate files. • Computation costs (O(?)) cost. • Memory usage cost (avoid thrashing). • Communication cost for distributed systems. • Maintenance cost in terms of resources & availability of the database.

  41. Physical Database Design • Chapter 20 in Elmasri & Navathe textbook. • Accumulate query statistics & data mine them. • What attributes to index? • When to use a clustered index on a non-key. • Actual dataset can be organized on only 1 key. • Hashing works well on equality-only joins. • Dynamic hashing for volatile files.

  42. Physical Database Techniques • CREATE [ UNIQUE ] INDEX <index name> ON <table name> (<column name> [ <order> ] { , <column name> [ <order> ] } ) [ CLUSTER ] ; • Denormalization demotes normalized tables to weaker forms for increased speed. • Vertical partitioning splits relations over attributes to speed projection dynamics. • Horizontal partitioning splits relations over indexed tuples to speed selection dynamics.

  43. Collect statistics • Storage statistics include data about table spaces, index spaces, buffer pools. DBMS may require preallocation and sizing / tuning for clustering. • I/O and device performance statistics include read / write (paging) on disk intents, hot spots, and thrashing for core memory. • NFS/local/in-core, number of network interface cards, amount of core memory, cache, memory topology. • Query / transaction statistics help determine attributes to index & query distributions.

  44. Tuning queries • Precompiled queries offer opportunities for profiling and speed improvement. • Avoid generating larger intermediate result sets when smaller ones are available. • Avoid nested queries that generate large cross-products in favor of sequential queries. P. 737 example potential to search all of M for each tuple from E. SELECT Ssn SELECT MAX(Salary) AS High_salary, Dno INTO TEMP FROM EMPLOYEE E FROM EMPLOYEE GROUP BY Dno ; WHERE SELECT EMPLOYEE.Ssn FROM EMPLOYEE, TEMP Salary = SELECT MAX(Salary) WHERE EMPLOYEE.Salary = TEMP.High_salary FROM EMPLOYEE AS M AND EMPLOYEE.Dno = TEMP.Dno ; WHERE M.Dno = E.Dno ;

More Related