Disk Management in Database Management Systems - Prof. Kristen R. Lefevre, Study notes of Database Management Systems (DBMS)

Download Disk Management in Database Management Systems - Prof. Kristen R. Lefevre and more Study notes Database Management Systems (DBMS) in PDF only on Docsity! 1 2/3/09 EECS 484: Database Management Systems, Kristen LeFevre 1 Storing Data: Disks and Files Chapter 9 2/3/09 EECS 484: Database Management Systems, Kristen LeFevre 2 Disk Query Evaluation Engine SQL Query / Application Files & Access Methods Buffer Management Disk Space Management DBMS Organization This lecture 2/3/09 EECS 484: Database Management Systems, Kristen LeFevre 3 Non-volatile The Memory Hierarchy Price Speed cache main memory magnetic disk tape CPU Size Performance of Microprocessors and Memory improving faster than disks and tapes 2 2/3/09 EECS 484: Database Management Systems, Kristen LeFevre 4 Why Not Store Everything in Main Memory? • Too expensive: RAM: $90 per GB, Disk: $0.25 per GB • Main memory is volatile: Want data to persist between runs • Typical storage hierarchy: • Main memory (RAM) for currently used data • Disk for the main database (secondary storage) • Read and write data between disk and main memory • Both are high-cost operations, relative to in-memory operations, so must be planned carefully! • Tapes for archiving older versions of data (tertiary storage) • Sequential access devices 2/3/09 EECS 484: Database Management Systems, Kristen LeFevre 5 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! 2/3/09 EECS 484: Database Management Systems, Kristen LeFevre 6 Magnetic Disks Platters Disk head Arm movement Disk arm Tracks Sector Set of tracks with same diameter called a cylinder Data stored in blocks. Size of block is a multiple of sector size. Only one disk head reads or writes at a time. 5 2/3/09 EECS 484: Database Management Systems, Kristen LeFevre 13 Buffer Replacement Policy • Chose a frame for replacement • Least-recently-used (LRU), Clock, MRU etc. • LRU: queue of pointers to “empty” frames • Add to end of queue, grab frames from front of queue • Clock: variant of LRU, but lower overhead • 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 • Use MRU 2/3/09 EECS 484: Database Management Systems, Kristen LeFevre 14 Files of Records (tuples) • Pages are great for storage system, but DBMS views data as a collection of records • Important questions: • How is a record organized / implemented? • How are records laid out on pages? • How are pages organized into files? 2/3/09 EECS 484: Database Management Systems, Kristen LeFevre 15 Record Formats • Two kinds: • Fixed-length records • Each field has a fixed length, and the number of fields is fixed • Variable-length records • Some of the fields are of variable length • E.g., variable-length strings 6 2/3/09 EECS 484: Database Management Systems, Kristen LeFevre 16 Buffer Management in DBMS vs. OS • Obvious similarities between DBMS buffer management, virtual memory (OS) • Why not use the OS? • DBMS can better predict access patterns (limited set of operations) • Adjust replacement policy • Pre-fetch pages based on predictable access patterns • Portability issues • DBMS recovery requires the ability to force a page to disk 2/3/09 EECS 484: Database Management Systems, Kristen LeFevre 17 Record Formats: Fixed Length • All records are the same length • Information about field types same for all records in a file; stored in system catalogs. • What about null values? Base address (B) L1 L2 L3 L4 F1 F2 F3 F4 Address = B+L1+L2 2/3/09 EECS 484: Database Management Systems, Kristen LeFevre 18 Record Formats: Variable Length Two alternative formats: Array of Offsets 4 $ $ $ $ Field Count F1 F2 F3 F4 Field delimiter (special symbol)  Second alternative offers direct access to ith field  Efficient storage of nulls  Small directory overhead.  Issues with growing records!  Need to shift subsequent values  Record may exceed size of page 7 2/3/09 EECS 484: Database Management Systems, Kristen LeFevre 19 Page Formats • Page contains a collection of records • Think of page as a collection of slots, each of which contains a record • Record ID (RID): <page id, slot #> • Many different slotted page organizations. • Must support search, insert, delete records 2/3/09 EECS 484: Database Management Systems, Kristen LeFevre 20 Page Formats: Fixed Length Records Record id = <page id, slot #> First alternative: moving records changes rid  may not be acceptable. Slot 1 Slot 2 Slot N . . . N PACKED Free Space number of records . . . M10. . . M ... 3 2 1 UNPACKED, BITMAP Slot 1 Slot 2 Slot N Slot M 11 number of slots 2/3/09 EECS 484: Database Management Systems, Kristen LeFevre 21 Page Formats: Variable Length Records Book-keeping0, 70 -1, 0 560, 90 -1, 0 120, 40 6 70, 50 012345 Slot directory Free Space Pointer • Directory grows backwards! • Move records on same page • rid unchanged • Good for fixed-length records too. Rid=? (11, 1) Page num = 11 Delete a record? Slot Entry: Offset, reclen