Storing and Retrieving Data Database Management Systems need to: - PDF document

Storing and Retrieving Data � Database Management Systems need to: – Store large volumes of data Storing Data: Disks and Files – Store data reliably (so that data is not lost!) – Retrieve data efficiently � Alternatives for storage – Main memory – Disks – Tape Database Management System, R. Ramakrishnan and J. Gehrke 1 Database Management System, R. Ramakrishnan and J. Gehrke 2 Why Not Store Everything in Main Memory? Why Not Store Everything in Tapes? � Costs too much . $500 will buy you either � No random access . Data has to be accessed 512MB of RAM or 100GB of disk today. sequentially – Not a great idea when accessing a small portion of � Main memory is volatile . We want data to be a terabyte of data saved between runs. (Obviously!) � Slow! Data access times are larger than for disks Database Management System, R. Ramakrishnan and J. Gehrke 3 Database Management System, R. Ramakrishnan and J. Gehrke 4 Solution 1: Techniques for making Disks disks faster � Secondary storage device of choice � Intelligent data layout on disk – Cheap – Put related data items together – Stable storage medium � Redundant Array of Inexpensive Disks (RAID) – Random access to data – Achieve parallelism by using many disks � Main problem – Data read/write times much larger than for main memory Database Management System, R. Ramakrishnan and J. Gehrke 5 Database Management System, R. Ramakrishnan and J. Gehrke 6

Solution 2: Buffer Management Outline � Keep “currently used” data in main memory � Disk technology and how to make disk read/writes faster – How do we do this efficiently? � Typical storage hierarchy: � Buffer management – Main memory (RAM) for currently used data � Storing “database files” on disk – Disks for the main database (secondary storage) – Tapes for archiving older versions of the data (tertiary storage) Database Management System, R. Ramakrishnan and J. Gehrke 7 Database Management System, R. Ramakrishnan and J. Gehrke 8 Components of a Disk Accessing a Disk Page Spindle Tracks Disk head � The platters spin (say, 100rps). � Time to access (read/write) a disk block: � The arm assembly is Sector – seek time ( moving arms to position disk head on track ) moved in or out to position – rotational delay ( waiting for block to rotate under head ) a head on a desired track. – transfer time ( actually moving data to/from disk surface ) Tracks under heads make � Seek time and rotational delay dominate. a cylinder (imaginary!). Platters – Seek time varies from about 1 to 20msec Arm movement � Only one head – Rotational delay varies from 0 to 10msec reads/writes at any – Transfer rate is about 0.5msec per 4KB page one time. � Key to lower I/O cost: reduce seek/rotation Arm assembly � Block size is a multiple delays! Hardware vs. software solutions? of sector size (which is fixed). Database Management System, R. Ramakrishnan and J. Gehrke 9 Database Management System, R. Ramakrishnan and J. Gehrke 10 Arranging Pages on Disk RAID � ` Next ’ block concept: � Disk Array: Arrangement of several disks – blocks on same track, followed by that gives abstraction of a single, large disk. – blocks on same cylinder, followed by � Goals: Increase performance and reliability. – blocks on adjacent cylinder � Two main techniques: � Blocks in a file should be arranged – Data striping: Data is partitioned; size of a sequentially on disk (by `next’), to minimize partition is called the striping unit. Partitions are distributed over several disks. seek and rotational delay. – Redundancy: More disks -> more failures. � For a sequential scan, pre-fetching several Redundant information allows reconstruction of pages at a time is a big win! data if a disk fails. Database Management System, R. Ramakrishnan and J. Gehrke 11 Database Management System, R. Ramakrishnan and J. Gehrke 12

RAID Levels RAID Levels (Contd.) � Level 3: Bit-Interleaved Parity – Striping Unit: One bit. One check disk. � Level 0: No redundancy – Each read and write request involves all disks; disk � Level 1: Mirrored (two identical copies) array can process one request at a time. – Each disk has a mirror image (check disk) � Level 4: Block-Interleaved Parity – Parallel reads, a write involves two disks. – Striping Unit: One disk block. One check disk. – Maximum transfer rate = transfer rate of one disk – Parallel reads possible for small requests, large requests can utilize full bandwidth � Level 0+1: Striping and Mirroring – Writes involve modified block and check disk – Parallel reads, a write involves two disks. � Level 5: Block-Interleaved Distributed Parity – Maximum transfer rate = aggregate bandwidth – Similar to RAID Level 4, but parity blocks are distributed over all disks Database Management System, R. Ramakrishnan and J. Gehrke 13 Database Management System, R. Ramakrishnan and J. Gehrke 14 Disk Space Management Outline � Lowest layer of DBMS software manages space � Disk technology and how to make disk on disk. read/writes faster � Higher levels call upon this layer to: � Buffer management – allocate/de-allocate a page � Storing “database files” on disk – 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. Database Management System, R. Ramakrishnan and J. Gehrke 15 Database Management System, R. Ramakrishnan and J. Gehrke 16 Buffer Management in a DBMS When a Page is Requested ... Page Requests from Higher Levels � If requested page is not in pool: BUFFER POOL – Choose a frame for replacement disk page – If frame is dirty, write it to disk – Read requested page into chosen frame free frame � Pin the page and return its address. MAIN MEMORY DISK choice of frame dictated � If requests can be predicted (e.g., sequential scans) DB by replacement policy pages can be pre-fetched several pages at a time! � Data must be in RAM for DBMS to operate on it! � Table of <frame#, pageid> pairs is maintained. Database Management System, R. Ramakrishnan and J. Gehrke 17 Database Management System, R. Ramakrishnan and J. Gehrke 18

More on Buffer Management Buffer Replacement Policy � Requestor of page must unpin it, and indicate � Frame is chosen for replacement by a replacement policy: whether page has been modified: – dirty bit is used for this. – Least-recently-used (LRU), Clock, MRU etc. � Page in pool may be requested many times, � Policy can have big impact on # of I/O’s; depends on the access pattern . – a pin count is used. A page is a candidate for replacement iff pin count = 0. � Sequential flooding : Nasty situation caused by � CC & recovery may entail additional I/O LRU + repeated sequential scans. when a frame is chosen for replacement. – # buffer frames < # pages in file means each page ( Write-Ahead Log protocol; more later.) request causes an I/O. MRU much better in this situation (but not in all situations, of course). Database Management System, R. Ramakrishnan and J. Gehrke 19 Database Management System, R. Ramakrishnan and J. Gehrke 20 DBMS vs. OS File System Outline OS does disk space & buffer mgmt: why not let OS manage these tasks? � Disk technology and how to make disk read/writes faster � Buffer management � Differences in OS support: portability issues � Storing “database files” on disk � 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. Database Management System, R. Ramakrishnan and J. Gehrke 21 Database Management System, R. Ramakrishnan and J. Gehrke 22 Files of Records Record Formats: Fixed Length � Page or block is OK when doing I/O, but F1 F2 F3 F4 higher levels of DBMS operate on records , and L1 L2 L3 L4 files of records . � FILE : A collection of pages, each containing a collection of records. Must support: Base address (B) Address = B+L1+L2 – insert/delete/modify record – read a particular record (specified using record id ) � Information about field types same for all – scan all records (possibly with some conditions on records in a file; stored in system catalogs. the records to be retrieved) � Finding i’th field requires scan of record. Database Management System, R. Ramakrishnan and J. Gehrke 23 Database Management System, R. Ramakrishnan and J. Gehrke 24