html5-img
1 / 80

Storage, Indexing and Joins Slides taken from Database Management Systems, R. Ramakrishnan

Storage, Indexing and Joins Slides taken from Database Management Systems, R. Ramakrishnan. CIS 550 Fall 2000 Handout 5. Disks and Files. DBMS stores information on (“hard”) disks. This has major implications for DBMS design! READ: transfer data from disk to main memory (RAM).

colorado-rich
Télécharger la présentation

Storage, Indexing and Joins Slides taken from Database Management Systems, R. Ramakrishnan

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. Storage, Indexing and Joins Slides taken fromDatabase Management Systems, R. Ramakrishnan CIS 550 Fall 2000 Handout 5 CIS 550, Fall 2001

  2. Disks and Files • DBMS stores information on (“hard”) disks. • This has major implications for DBMS design! • READ: transfer data from disk to main memory (RAM). • WRITE: transfer data from RAM to disk. • Both are high-cost operations, relative to in-memory operations, so must be planned carefully! CIS 550, Fall 2001

  3. Why Not Store Everything in Main Memory? • Costs too much. $1000 will buy you either 0.5GB of RAM or 50GB of disk today. • Main memory is volatile. We want data to be saved between runs. (Obviously!) • Typical storage hierarchy: • Main memory (RAM) for currently used data. • Disk for the main database (secondary storage). • Tapes for archiving older versions of the data (tertiary storage). CIS 550, Fall 2001

  4. Disks • Secondary storage device of choice. • Main advantage over tapes: random access vs.sequential. • Data is stored and retrieved in units called disk blocks or pages. • Unlike RAM, time to retrieve a disk page varies depending upon location on disk. • Therefore, relative placement of pages on disk has major impact on DBMS performance! CIS 550, Fall 2001

  5. Tracks Arm movement Arm assembly Components of a Disk Spindle Disk head • The platters spin (say, 90rps). • The arm assembly is moved in or out to position a head on a desired track. Tracks under heads make a cylinder(imaginary!). Sector Platters • Only one head reads/writes at any one time. • Block size is a multiple of sector size (which is fixed). CIS 550, Fall 2001

  6. Accessing a Disk Page • Time to access (read/write) a disk block: • seek time (moving arms to position disk head on track) • rotational delay (waiting for block to rotate under head) • transfer time (actually moving data to/from disk surface) • Seek time and rotational delay dominate. • Seek time varies from about 1 to 20msec • Rotational delay varies from 0 to 10msec • Transfer rate is about 1msec per 4KB page • Key to lower I/O cost: reduce seek/rotation delays! Hardware vs. software solutions? CIS 550, Fall 2001

  7. Arranging Pages on Disk • “Next” block concept: • blocks on same track, followed by • blocks on same cylinder, followed by • blocks on adjacent cylinder • Blocks in a file should be arranged sequentially on disk (by “next”), to minimize seek and rotational delay. • For a sequential scan, pre-fetchingseveral pages at a time is a big win! CIS 550, Fall 2001

  8. Disk Space Management • Lowest layer of DBMS software manages space on disk. • Higher levels call upon this layer to: • allocate/de-allocate a page • read/write a page • Request for a sequence of pages must be satisfied by allocating the pages sequentially on disk! Higher levels don’t need to know how this is done, or how free space is managed. CIS 550, Fall 2001

  9. DB Buffer Management in a DBMS Page Requests from Higher Levels • Data must be in RAM for DBMS to operate on it! • Table of <frame#, pageid> pairs is maintained. BUFFER POOL disk page free frame MAIN MEMORY DISK choice of frame dictated by replacement policy CIS 550, Fall 2001

  10. When a Page is Requested ... • If requested page is not in pool: • Choose a frame for replacement • If frame is dirty, write it to disk • Read requested page into chosen frame • Pin the page and return its address. • If requests can be predicted (e.g., sequential scans) • pages can be pre-fetchedseveral pages at a time! CIS 550, Fall 2001

  11. More on Buffer Management • Requestor of page must unpin it, and indicate whether page has been modified: • dirtybit is used for this. • Page in pool may be requested many times, • a pin count is used. A page is a candidate for replacement iff pin count = 0. • CC & recovery may entail additional I/O when a frame is chosen for replacement. CIS 550, Fall 2001

  12. Buffer Replacement Policy • Frame is chosen for replacement by a replacement policy: • Least-recently-used (LRU), Clock, MRU etc. • Policy can have big impact on # of I/O’s; depends on the access pattern. • Sequential flooding: Nasty situation caused by LRU + repeated sequential scans. • # buffer frames < # pages in file means each page request causes an I/O. MRU much better in this situation (but not in all situations, of course). CIS 550, Fall 2001

  13. DBMS vs. OS File System OS does disk space & buffer management: why not let OS manage these tasks? • Differences in OS support: portability issues • Some limitations, e.g., files can’t span disks. • Buffer management in DBMS requires ability to: • pin a page in buffer pool, force a page to disk (important for implementing CC & recovery), • adjust replacement policy, and pre-fetch pages based on access patterns in typical DB operations. CIS 550, Fall 2001

  14. Files of Records • Page or block is OK when doing I/O, but higher levels of DBMS operate on records, and files of records. • FILE: A collection of pages, each containing a collection of records. Must support: • insert/delete/modify record • read a particular record (specified using record id) • scan all records (possibly with some conditions on the records to be retrieved) CIS 550, Fall 2001

  15. Record Formats: Fixed Length • Information about field types same for all records in a file; stored in systemcatalogs. • Finding i’th field requires scan of record. F1 F2 F3 F4 L1 L2 L3 L4 Base address (B) Address = B+L1+L2 CIS 550, Fall 2001

  16. 4 $ $ $ $ Record Formats: Variable Length • Two alternative formats (# fields is fixed): F1 F2 F3 F4 Fields Delimited by Special Symbols Field Count F1 F2 F3 F4 Array of Field Offsets • Second offers direct access to i’th field, efficient storage • of nulls(special don’t know value); small directory overhead. CIS 550, Fall 2001

  17. Page Formats: Fixed Length Records Slot 1 Slot 1 Slot 2 Slot 2 • Record id = <page id, slot #>. In first alternative, moving records for free space management changes rid; may not be acceptable. Free Space . . . . . . Slot N Slot N Slot M N . . . 1 1 1 M 0 M ... 3 2 1 number of records number of slots PACKED UNPACKED, BITMAP CIS 550, Fall 2001

  18. Page Formats: Variable Length Records Rid = (i,N) Page i • Can move records on page without changing rid; so, attractive for fixed-length records too. Rid = (i,2) Rid = (i,1) N Pointer to start of free space 20 16 24 N . . . 2 1 # slots SLOT DIRECTORY CIS 550, Fall 2001

  19. Alternative File Organizations Many alternatives exist, each ideal for some situation, and not so good in others: • Heap files:Suitable when typical access is a file scan retrieving all records. • Sorted Files:Best if records must be retrieved in some order, or only a `range’ of records is needed. • Hashed Files:Good for equality selections. • File is a collection of buckets. Bucket = primary page plus zero or moreoverflow pages. • Hashing functionh: h(r) = bucket in which record r belongs. h looks at only some of the fields of r, called the search fields. CIS 550, Fall 2001

  20. Unordered (Heap) Files • Simplest file structure contains records in no particular order. • As file grows and shrinks, disk pages are allocated and de-allocated. • To support record level operations, we must: • keep track of the pages in a file • keep track of free space on pages • keep track of the records on a page • There are many alternatives for keeping track of this. CIS 550, Fall 2001

  21. Heap File Implemented as a List • The header page id and Heap file name must be stored someplace. • Each page contains 2 `pointers’ plus data. Data Page Data Page Data Page Full Pages Header Page Data Page Data Page Data Page Pages with Free Space CIS 550, Fall 2001

  22. Data Page 1 Header Page Data Page 2 Data Page N DIRECTORY Heap File Using a Page Directory • The entry for a page can include the number of free bytes on the page. • The directory is a collection of pages; linked list implementation is just one alternative. • Much smaller than linked list of all heap file pages! CIS 550, Fall 2001

  23. Analysis of file organizations We ignore CPU costs for simplicity, and use the following parameters in our cost model: • B: The number of data pages • R: Number of records per page • D: (Average) time to read or write disk page • Measuring number of page I/O’s ignores gains of pre-fetching blocks of pages; thus, even I/O cost is only approximated. • Average-case analysis; based on several simplistic assumptions. • Good enough to show the overall trends! CIS 550, Fall 2001

  24. Assumptions in Our Analysis • Single record insert and delete. • Heap Files: • Equality selection on key; exactly one match. • Insert always at end of file. • Sorted Files: • Files compacted after deletions. • Selections on sort field(s). • Hashed Files: • No overflow buckets, 80% page occupancy. CIS 550, Fall 2001

  25. Cost of Operations • Several assumptions underlie these (rough) estimates! CIS 550, Fall 2001

  26. Indexes • A Heap file allows us to retrieve records: • by specifying the rid, or • by scanning all records sequentially • Sometimes, we want to retrieve records by specifying the values in one or more fields, e.g., • Find all students in the “CS” department • Find all students with a gpa > 3 • Indexes are file structures that enable us to answer such value-based queries efficiently. • This will be topic of our next lecture! CIS 550, Fall 2001

  27. Indexing CIS 550, Fall 2001

  28. Indexes • An index on a file speeds up selections on the search key fields for the index. • Any subset of the fields of a relation can be the search key for an index on the relation. • Search key is not the same as key(minimal set of fields that uniquely identify a record in a relation). • An index contains a collection of data entries, and supports efficient retrieval of all data entries k* with a given key value k. CIS 550, Fall 2001

  29. Alternatives for Data Entry k* in Index • Three alternatives: • Data record with key value k • <k, rid of data record with search key value k> • <k, list of rids of data records with search key k> • Choice of alternative for data entries is orthogonal to the indexing technique used to locate data entries with a given key value k. • Examples of indexing techniques: B+ trees, hash-based structures • Typically, index contains auxiliary information that directs searches to the desired data entries CIS 550, Fall 2001

  30. Index Classification • Primary vs. secondary: If search key contains primary key, then called primary index. • Unique index: Search key contains a candidate key. • Clustered vs. unclustered: If order of data records is the same as, or `close to’, order of data entries, then called clustered index. • Alternative 1 implies clustered, but not vice-versa. • A file can be clustered on at most one search key. • Cost of retrieving data records through index varies greatly based on whether index is clustered or not! CIS 550, Fall 2001

  31. Clustered vs. Unclustered Index • Suppose that Alternative (2) is used for data entries, and that the data records are stored in a Heap file. • To build clustered index, first sort the Heap file (with some free space on each page for future inserts). • Overflow pages may be needed for inserts. (Thus, order of data recs is `close to’, but not identical to, the sort order.) Index entries UNCLUSTERED CLUSTERED direct search for data entries Data entries Data entries (Index File) (Data file) CIS 550, Fall 2001 Data Records Data Records

  32. Index Classification (Cont.) • Dense vs. Sparse: If there is at least one data entry per search key value (in some data record), then dense. • Alternative 1 always leads to dense index. • Every sparse index is clustered! • Sparse indexes are smaller; however, some useful optimizations are based on dense indexes. Ashby, 25, 3000 22 Basu, 33, 4003 25 Bristow, 30, 2007 30 Ashby 33 Cass, 50, 5004 Cass Smith Daniels, 22, 6003 40 Jones, 40, 6003 44 44 Smith, 44, 3000 50 Tracy, 44, 5004 Sparse Index Dense Index on on Data File Name Age CIS 550, Fall 2001

  33. Range Searches • ``Find all students with gpa > 3.0’’ • If data is in sorted file, do binary search to find first such student, then scan to find others. • Cost of binary search can be quite high. • Simple idea: Create an `index’ file. Index File kN k2 k1 Data File Page N Page 3 Page 1 Page 2 • Can do binary search on (smaller) index file! CIS 550, Fall 2001

  34. Overflow page index entry ISAM P K P K P P K m 0 1 2 1 m 2 • Index file may still be quite large. But we can apply the idea repeatedly! Non-leaf Pages Leaf Pages Primary pages • Leaf pages contain data entries. CIS 550, Fall 2001

  35. Comments on ISAM Data Pages Index Pages • File creation: Leaf (data) pages allocated sequentially, sorted by search key; then index pages allocated, then space for overflow pages. • Index entries: <search key value, page id>; they `direct’ search for data entries, which are in leaf pages. • Search: Start at root; use key comparisons to go to leaf. Cost log F N ; F = # entries/index pg, N = # leaf pgs • Insert: Find leaf data entry belongs to, and put it there. • Delete: Find and remove from leaf; if empty overflow page, de-allocate. Overflow pages • Static tree structure: inserts/deletes affect only leaf pages. CIS 550, Fall 2001

  36. Root 40 20 33 51 63 46* 55* 10* 15* 20* 27* 33* 37* 40* 51* 97* 63* Example ISAM Tree • Each node can hold 2 entries; no need for `next-leaf-page’ pointers. (Why?) CIS 550, Fall 2001

  37. After Inserting 23*, 48*, 41*, 42* ... Root 40 Index Pages 20 33 51 63 Primary Leaf 46* 55* 10* 15* 20* 27* 33* 37* 40* 51* 97* 63* Pages 41* 48* 23* Overflow Pages 42* CIS 550, Fall 2001

  38. ... Then Deleting 42*, 51*, 97* Root 40 20 33 51 63 46* 55* 10* 15* 20* 27* 33* 37* 40* 63* 41* 48* 23* • Note that 51* appears in index levels, but not in leaf! CIS 550, Fall 2001

  39. Index Entries (Direct search) Data Entries ("Sequence set") B+ Tree: The Most Widely Used Index • Insert/delete at log F N cost; keep tree height-balanced. (F = fanout, N = # leaf pages) • Minimum 50% occupancy (except for root). Each node contains d <= m <= 2d entries. The parameter d is called the order of the tree. • Supports equality and range-searches efficiently. CIS 550, Fall 2001

  40. Example B+ Tree • Search begins at root, and key comparisons direct it to a leaf. • Search for 5*, 15*, all data entries >= 24* ... Root 30 13 17 24 39* 3* 5* 19* 20* 22* 24* 27* 38* 2* 7* 14* 16* 29* 33* 34* • Based on the search for 15*, we know it is not in the tree! CIS 550, Fall 2001

  41. Inserting a Data Entry into a B+ Tree • Find correct leaf L. • Put data entry onto L. • If L has enough space, done! • Else, must splitL (into L and a new node L2) • Redistribute entries evenly, copy upmiddle key. • Insert index entry pointing to L2 into parent of L. • This can happen recursively • To split index node, redistribute entries evenly, but push upmiddle key. (Contrast with leaf splits.) • Splits “grow” tree; root split increases height. • Tree growth: gets wider or one level taller at top. CIS 550, Fall 2001

  42. Entry to be inserted in parent node. (Note that 17 is pushed up and only 17 this with a leaf split.) 5 13 24 30 Inserting 8* into Example B+ Tree Entry to be inserted in parent node. (Note that 5 is s copied up and • Observe how minimum occupancy is guaranteed in both leaf and index pg splits. • Note difference between copy-upand push-up; be sure you understand the reasons for this. 5 continues to appear in the leaf.) 3* 5* 2* 7* 8* appears once in the index. Contrast CIS 550, Fall 2001

  43. Example B+ Tree After Inserting 8* Root 17 24 5 13 30 39* 2* 3* 5* 7* 8* 19* 20* 22* 24* 27* 38* 29* 33* 34* 14* 16* • Notice that root was split, leading to increase in height. • In this example, we can avoid split by re-distributing entries; however, this is usually not done in practice. CIS 550, Fall 2001

  44. Deleting a Data Entry from a B+ Tree • Start at root, find leaf L where entry belongs. • Remove the entry. • If L is at least half-full, done! • If L has only d-1 entries, • Try to re-distribute, borrowing from sibling (adjacent node with same parent as L). • If re-distribution fails, mergeL and sibling. • If merge occurred, must delete entry (pointing to L or sibling) from parent of L. • Merge could propagate to root, decreasing height. CIS 550, Fall 2001

  45. Example Tree After (Inserting 8*, Then) Deleting 19* and 20* ... • Deleting 19* is easy. • Deleting 20* is done with re-distribution. Notice how middle key is copied up. Root 17 27 5 13 30 39* 2* 3* 5* 7* 8* 22* 24* 27* 29* 38* 33* 34* 14* 16* CIS 550, Fall 2001

  46. ... And Then Deleting 24* • Must merge. • Observe `toss’ of index entry (on right), and `pull down’ of index entry (below). 30 39* 22* 27* 38* 29* 33* 34* Root 5 13 17 30 3* 39* 2* 5* 7* 8* 22* 38* 27* 33* 34* 14* 16* 29* CIS 550, Fall 2001

  47. 2* 3* 5* 7* 8* 39* 17* 18* 38* 20* 21* 22* 27* 29* 33* 34* 14* 16* Example of Non-leaf Re-distribution • Tree is shown below during deletion of 24*. (What could be a possible initial tree?) • In contrast to previous example, can re-distribute entry from left child of root to right child. Root 22 30 17 20 5 13 CIS 550, Fall 2001

  48. After Re-distribution • Intuitively, entries are re-distributed by `pushingthrough’ the splitting entry in the parent node. • It suffices to re-distribute index entry with key 20; we’ve re-distributed 17 as well for illustration. Root 17 22 30 5 13 20 2* 3* 5* 7* 8* 39* 17* 18* 38* 20* 21* 22* 27* 29* 33* 34* 14* 16* CIS 550, Fall 2001

  49. B+ Trees in Practice • Typical order: 100. Typical fill-factor: 67%. • average fanout = 133 • Typical capacities: • Height 4: 1334 = 312,900,700 records • Height 3: 1333 = 2,352,637 records • Can often hold top levels in buffer pool: • Level 1 = 1 page = 8 Kbytes • Level 2 = 133 pages = 1 Mbyte • Level 3 = 17,689 pages = 133 MBytes CIS 550, Fall 2001

  50. 3* 6* 9* 10* 11* 12* 13* 23* 31* 36* 38* 41* 44* 4* 20* 22* 35* Bulk Loading of a B+ Tree • If we have a large collection of records, and we want to create a B+ tree on some field, doing so by repeatedly inserting records is very slow. • Bulk Loadingcan be done much more efficiently. • Initialization: Sort all data entries, insert pointer to first (leaf) page in a new (root) page. Root Sorted pages of data entries; not yet in B+ tree CIS 550, Fall 2001

More Related