Storage and Indexes Introduction to Databases Computer Science 557

1. Storage and IndexesIntroduction to DatabasesComputer Science 557 Instructor: Joe Bockhorst University of Wisconsin - Milwaukee

2. Announcements • Any problems logging in to course accounts? • Use grid3, grid5 or weise for course work • Send bugs on weise to ceas-labstaff@uwm.edu • Reading Assignment: Chapter 14 in the textbook • Program 1 assigned today, due next Friday

3. Hard Disks • DBMS typically store information on “hard” disks • I/O operations (“Read” and “Write”) are costly • Should be planned carefully • A block is amount of data transferred in one operation (example block size ~ 1024 bytes) Main Memory (RAM) Disk Read Write

4. Our First Equation seek time transfer time rotational delay

5. Anatomy of a Hard Disk

6. Hard Drive Glossary • Disks are divided into concentric circular tracks on each disk surface. • Track capacities vary typically from ~ 4 to 50 Kbytes • The division of a track into sectors is hard-coded on the disk surface and cannot be changed • A block is an integer number of sectors • The block size B is fixed for each system. • Typical block sizes range from B=512 bytes to B=4096 bytes. • Whole blocks are transferred between disk and main memory

7. Typical Disk Parameters (Courtesy of Seagate Technology)

8. Accessing a Disk Block • to Read or Write block give block addr to disk controller • Hardware block address – cylinder #, surface #, block # • Logical block addressing allows higher levels to refer to hardware block address using a block_id • Seek time – move head to correct cylinder (~10ms) • Rotation time – rotate start of block under head • @ 15000 rpm, average rotation time is 4ms • Transfer time – transfer entire block • Accessing consecutive blocks only need to pay the seek and rotation time once • Compare to typical main memory access times which are measured in micro (10-6) or nano (10-9) seconds

9. Coming soon: Solid State “Disks”? + Random access devices eliminate seek times + faster startup • $$$$ much more expensive than hard disks • ($8 / GB vs $0.25 / GB) • Capacities are smaller We will assume hard disk storage in this course

10. Why not store DB in main memory? • $$$$ • cost of RAM is > 100 X hard drive cost • main memory is volatile • 32 bit addressing

11. Managing the Hard Disk Query Optimization The DSM provides an abstraction of the block as a unit of data DSM interface includes commands to read and write block commands Relational Operators Files and Access Methods Buffer Management Disk Space Management I/O requests

12. Operations Supported by DSM • allocate_blocks(num_blocks) • Add blocks to DB • deallocate block(blockID) • Remove block from DB • write_block(blockID, blockPtr) • Write block to disk • read_block(blockID, blockPtr) • Read block from disk

13. Managing the Hard Disk Disk Space Manager block ID 1 2 3 4 5 6 7 N-1 N allocated? yes yes yes no no no no no no hardware addr 792 793 794 * * * * * *

14. Example: Managing the Hard Disk allocate_blocks(3) write_block(5,data) read_block(5) deallocate block(2) Disk Space Manager block ID 1 2 3 4 5 6 7 N-1 N allocated? yes yes yes no no no no no no hardware addr 792 793 794 * * * * * *

15. Example: Managing the Hard Disk allocate_blocks(3) write_block(5,data) read_block(5) deallocate block(2) //allocate three consecutive blocks Disk Space Manager block ID 1 2 3 4 5 6 7 N-1 N allocated? yes yes yes yes yes yes no no no hardware addr 792 793 794 902 903 904 * * *

16. Example: Managing the Hard Disk allocate_blocks(3) write_block(5,data) read_block(5) deallocate block(2) //write blockID 5 to disk Disk Space Manager block ID 1 2 3 4 5 6 7 N-1 N allocated? yes yes yes yes yes yes no no no hardware addr 792 793 794 902 903 904 * * * write_block(903,data)

17. Example: Managing the Hard Disk allocate_blocks(3) write_block(5,data) read_block(5) deallocate block(2) // read blockID 5 to buffer Disk Space Manager block ID 1 2 3 4 5 6 7 N-1 N allocated? yes yes yes yes yes yes no no no hardware addr 792 793 794 902 903 904 * * * read_block(903)

18. Example: Managing the Hard Disk allocate_blocks(3) write_block(5,data) read_block(5) deallocate block(2) // read blockID 5 to buffer Disk Space Manager block ID 1 2 3 4 5 6 7 N-1 N allocated? yes no yes yes yes yes no no no hardware addr 792 * 794 902 903 904 * * *

19. Buffer Management • Responsible for managing region of main memory called the buffer pool • MM pages are called frames (slots that can hold one block) • Higher levels of the DBMS need not worry if the page is in memory or not... Just ask for it. Query Optimization Relational Operators Files and Access Methods Buffer Management Disk Space Management

20. Buffer Manager Operations • add_blocks_to_DB(num_blocks) • add new blocks to DB • delete_block_from_DB(block_id) • delete block from the DB • pin_block(block_id) • bring block from disk to buffer pool if not in BP • increment pin count for block • unpin_block(block_id) • decrement pin count for block • mark_dirty(block_id) • Buffer Manager maintains for each frame • pin count • dirty bit

21. Buffer Manager Example buffer pool with M frames 1 2 3 M-1 M block ID - - - - - initial state of buffer manager pin count 0 0 0 0 0 dirty no no no no no 1 2 3 N-1 N

22. Buffer Manager Example buffer pool with M frames 1 2 3 M-1 M block ID 76 22 - - - initial state of buffer manager pin count 2 1 0 0 0 dirty no no no no no 1 2 3 N-1 N draw on whiteboard

23. Buffer Manager Example add_blocks_to_DB(3) pin_block(76) pin_block(13) mark_dirty(13) pin_block(76) unpin_block(13) // now assume all frames are filled and blk 22 // is not in the buffer pool pin_block(22)// BufMgr flushes blk w/ pin count = 0 delete_block(35)