Data Storage

1. Data Storage Memory Hierarchy Disks Source: our textbook

2. Strawman Implementation • Use UNIX file system to store relations, e.g. • Students(name, id, dept) in file /usr/db/Students • One line per tuple, each component stored as character string, with # as a separator, e.g. • tuple could be: Smith#123#CS • Store schema in /usr/db/schema, e.g.: • Students#name#STR#id#INT#dept#STR

3. Strawman cont'd • To execute SELECT * FROM R WHERE <Condition>: • read schema file for R • check that <Condition> is valid for R • print out column headers • read file R and for each line • check the condition • print the line if true

4. Strawman cont'd • To execute a query involving a join of two relations R and S: for each tuple (line) in R do for each tuple (line) in S do if the condition is satisfied then display the desired attributes

5. What's Wrong? • The storage of the tuples on disk is inflexible: if a student changes major from EE to ECON, entire file must be rewritten • Search is very expensive (read entire relation) • Query processing is "brute force" -- there are faster ways to do joins, etc. • Data is not buffered between disk and main memory • No concurrency control • No reliability in case of a crash

6. How to Fix these Problems • Take advantage of the characteristics of computer hardware with clever algorithms to do things better • We will cover • data storage (predominantly disks) • how to represent data elements • indexes • query optimization • failure recovery • concurrency control

7. Memory Hierarchy faster, smaller, more expensive • cache • main memory • secondary storage (disk) • tertiary storage (tapes, CD-ROM) slower, larger, cheaper

8. Cache Memory • Transfer a few bytes at a time between cache and main memory: instruction, integer, floating point, short string • Processor operates on instruction and data in the cache • Typical size: 1 Mbyte (220 bytes) • Typical speed to/from main memory: 100 nanosec (1 nanosec = 10-9 sec)

9. Main Memory • Typical size: 100 Mbytes to 10 Gbytes (1 Gbyte = 230 bytes) • Typical access speed (to read or write): 10 to 100 nanosec • At least 100 times larger than cache • At least 10 times slower than cache

10. Secondary Storage • Usually disk • Divided logically into blocks, unit of transfer between main memory (called disk I/O) • Typical size: 100 Gbytes • Typical speed: 10 millisec (10-3 sec) • At least 100 times larger than main memory • Much slower than main memory and much much slower than cache: can execute several million instructions during one disk I/O

11. Tertiary Storage • Tape(s) • CD-ROM(s) • At least 1000 times slower than secondary storage • At least 1000 times larger than secondary storage

12. Volatile vs. Nonvolatile • Storage is volatile if the data is lost when the power is gone • Usually main memory is volatile • Usually secondary and tertiary storage is nonvolatile • Thus every change made to a database in main memory must be backed up on disk before it can be permanent.

13. Disks platters: each has two surfaces, each surface consists of tracks (concentric rings) one head per surface, very close to surface, does the reading and writing spindle disk heads

14. More on Disks orange ring is a track black squares are gaps, which don't hold data part of track between two gaps is a sector one or more sectors make a block

15. Disk Controller • controls mechanical actuator that moves the heads in and out (radius, distance from spindle) • one track from each surface at the same radius forms a cylinder • selects a surface • selects a sector (senses when that sector is under the corresponding head) • transfers bits

16. Typical Values • Rotation speed: 5400 rmp • Number of platters: 5 • Number of tracks/surface: 20,000 • Number of sectors/track: 500 • Number of bytes/sector: thousands

17. Disk Latency for a Read • Time between issuing command to read a block and when contents of block appear in main memory: • time for processor and disk controller to process request, including resolving any contention (negligible) • seek time: time to move heads to correct radius (0 to 40 millisec) • rotational latency: time until first sector of block is under the head (5 millisec) • transfer time: until all sectors of the block have passed under the head; depends on rotation speed and size of block

18. Disk Latency for Updates • For a write: like reading plus verification (read back and compare) • To modify a block: • read it into main memory • change it in main memory • write it back to disk

19. Moral of the Story • Disks accesses are orders of magnitude slower than accesses to main memory. • They are unavoidable in large databases. • Thus do everything possible to minimize them. • Can lead to different algorithms than for main memory model.

20. Speeding Up Disk Accesses • Place blocks accessed together on same cylinder • reduces seek time and rotational latency • Divide data among several disks • head assemblies can move in parallel • Mirror a disk: make copies of it • speeds up reads: get data from disk whose head is closest to desired block • no effect on writes: write to all copies • also helps with fault tolerance

21. Speeding up Disk Accesses • Be clever about order in which read and write requests are serviced, i.e., algorithm in OS or DBMS or disk controller • Ex: elevator algorithm • Prefetch blocks to main memory in anticipation of future use (buffering)

22. Elevator Algorithm • Works well when there are many "independent" read and write requests, i.e., don't need to be done in a particular order, that are randomly distributed over the disk. • Disk head assembly sweeps in and out repeatedly • When heads pass a cylinder with pending requests, they stop to do the request • When reaching a point with no pending requests ahead, change direction

23. Prefetching • Suppose you can predict order in which blocks will be requested from disk. • Load them into main memory buffers before they are needed. • Have flexibility to schedule the reads efficiently • Can also delay writing buffered blocks if the buffers are not needed immediately

24. Disk Failures • Intermittent failure: attempt to read or write a sector fails but a subsequent try succeeds • Impossible to read sector • Impossible to write a sector • Disk crash: entire disk becomes unreadable

25. Coping with Intermittent Failures • Use redundant bits in each sector • Store checksums in the redundant bits • After a read, check if checksums are correct; if not then try again • After a write, can do a read and compare with value written, or be optimistic and just check the checksum of the read

26. Checksums • Suppose we use one extra bit, a parity bit. • if the number of 1's in the data bits is odd, then set the parity bit to 1, otherwise to 0 • This is not foolproof: 101 and 110 both have even parity so checksum would be 0 for both • Use n parity bits in the checksum: • parity bit 1 stores parity of every n-th bit, starting with first bit, • parity bit 2 stores parity of every n-th bit, starting with second bit, etc. • Probability of missing an error is 1/2n

27. Coping with Permanent Read/Write Errors • Stable storage policy: • Each "virtual" sector X is represented by two real sectors, XL and XR. • To write value v to X: • repeat {write v to XL, read from XL} until read's checksum is correct or exceed max # of tries • do the same thing with XR • if XL or XR is discovered to be bad, then must find a substitute

28. Handling Write Failures • Suppose write(s) to XL all fail. • Then old value is safe in XR. • Suppose write(s) to XR all fail. • Then new value is safe in XL. • Assumption is that it is highly unlikely for two sectors to fail around the same time.

29. More on Stable Storage • To read from X: • repeatedly read XL until checksum is good or exceed max # tries • if read of XL failed then repeatedly read XR until checksum is good or exceed max # tries • Handles permanent read failures, unless both XL and XR fail about the same time (unlikely)

30. Coping with Disk Crashes • "Mean time to failure" of a disk is length of time by which 50% of such disks will have had a head crash • Goal is to have a much longer "mean time to data loss" for your system • Key idea: use redundancy • Discuss three such approaches next…

31. Mirroring (RAID Level 1) • Keep another copy of each disk: write to both, read from one. • Only way data can be lost is if second disk crashes while first is being repaired. • If mean time to crash of a single disk is 10 years and it takes 3 hours to repair a disk, then mean time to data loss is 146,000 years.

32. Parity Blocks (RAID Level 4) • Drawback of previous scheme is that you need double the number of disks. • Instead use one spare disk no matter how many data disks you have. • Block i of the spare disk contains the parity checks for block i of all the data disks. • If spare disk fails, get a new spare. • If a data disk fails, recompute its data from the other data disks and the spare.

33. RAID Level 5 • Drawback of previous scheme is that spare disk is a bottleneck. • Instead, let each data disk also serve as the spare disk for some blocks. All these assume only one crash at a time. RAID Level 6 uses error-correcting codes to be able to handle multiple crashes.