Introduction

On Monday, we said we would begin this semester by talking about how database management systemms (DBMSs) are implemented.
We began by looking at the memory hierarchy for computers going from the microprocessor registers and caches up through tertiary storage.
With respect to DBMS implementation we are concerned with the capacity, speed, and volality of different levels of this hierarchy.
So far, we have considered register, cache, and RAM memory and we have compared sizes of these memories with respect to commonly encountered file sizes and famous data sets.
We next look at secondary storage.

Secondary Storage

Secondary Storage is any kind of memory device other than main memory which is non-volatile. It is often also slower, but more capacious than main memory.
Usually, secondary storage means a hard disk drive (HDD) (these store data by magnetizing portions of a rotating disk) or a solid-state disk (SSD) (these are based on NAND flash and use transistors and a charge trap to store data).
Raw read speeds for RAM (high thousands of MB/s) are about 10x faster than SSD (about a thousand MB/s) which in turn 10x faster than HDDs (low hundred MB/s).
In practice all the data we want is typically not adjacent to each other on an HDD, so HDD speeds are much slower.
On the other hand, flash-based memory suffers memory wear and may only be overwritten (program-erase cycles) some fixed number of times (on the orders of a couple 100,000).
One confusing thing about HDD specs is they often report the SATA bus speed which is much higher (such as 6Gb/s --notice b not B). Slow HDD speeds are somewhat offset by their RAM buffers which are often a few hundred MBs.
Currently, (2020) a 16TB single drive HDD (fits nicely into a safety deposit box) goes for about $390, an 8TB SSD around $780. i.e., SSD's are about 4x the cost/TB.
Data on disks is organized in blocks.
Disk reads and disk writes (Disk I/Os) operate on the block level.
Unlike HDDs, on SSDs, an empty block starts as all 1's, changing 1's to 0's can be done at the bit level, but resetting 0's to 1's must be done at the block level, adding to SSD complexity.
Memory read off an HDD, is usually temporarily stored in a buffer SSD or RAM before transfer to RAM.

Tertiary Storage

Tertiary Storage is slower, (latency in the seconds rather than milliseconds), but more capacious and reliable memory than secondary storage.
Once written, but never accessed, data on SSD and HDD have lifespans in years. SSD data lifespans tend to be less than HDDs, on the order of 1-2yrs versus 6yrs or so for HDDs. This is especially so if the SSD is just stored or if the SSD suffers multiple power outages. On the other hand, tapes can last more than a decade.
Examples of tertiary storage are tape drives with robotic tape switchers or optical-disk juke boxes.

How HDDs Work

A given platter is broken into track which are further broken into arcs called sectors.

Disk Controller

A disk controller is a small processor in an HDD capable of:

Controlling the actuator arm that moves the head assembly
Selecting a surface to read/write from.
Transferring bits to/from the desired sector to/from the computer's memory.

Disk Storage Characteristics

Rotation speed: usually 5400RPMs, 7200RPMS, 10000RPMs (faster is better).
Number of Platters: 2 and 3 platter drives are common.
Number of tracks per surface: low 100,000s.
Number of bytes per track: between 1 and 10MB.
If you know drive size, tracks/surface, and number of platters, you can calculate bytes/track. For example, a 4TB drive with 2 platters, has 4 surfaces. If each surface had 200,000 tracks. Then the
number of bytes/track = 1TB/200,000tracks = `(10^{12}B)/(2 times 10^5 mbox{tracks}) = 5 times 10^6B = 5MB`.

In-Class Exercise

Assuming three platters and a 16TB capacity, but otherwise, leaving the characteristics of the drive of the last slide the same, how many bytes would a track need to store?

Post your solution to the Jan 29 In-Class Exercise Thread.

Disk Access Characteristics Review

The time to access a sector on the disk and read it into RAM from an HDD is split into the following steps:
- Processor/controller time
- Time to position head to correct cylinder (seek time). 5-10mS
- Time to rotate head over a sector (rotational latency) - this depends only on rotational speed. For example, for a 7200 RPM drive, Time/round = 1/(7200 rounds/min) = 60/7200 S/round = 8.3mS. Average rotational latency is half this, so about 4.2 mS.
- Transfer time - time for whole sector/gaps between adjacent sectors to rotate under head.
Let's use these steps in an example.

Disk Access Characteristics Example

Suppose we want to read a 16384 byte block from a disk (book calls Megatron 747) that rotates at 7200 RPM which has a total of 65536 track/surface with 256 sectors/track.
To move between cylinders takes one 1mS to start and stop, plus one additional 1mS for every 4000 cylinders, giving an average time of 1.00025mS to switch tracks. This gives a total time to move from innermost to outermost track of 17.38 mS.
10% of a track consists of gaps between cylinders. I.e., 360/10 = 36 degrees of arc are gaps.
The remaining 324 degrees of arc are sectors.
So if there are 4096bytes in a sector, the block we want fills four sectors.
To read 4 sectors, the head must pass over 4 sectors and 3 gaps. Hence, we need to read `(36 \times 3)/256 + (324 \times 4)/256 = 5.48` degrees of arc to read the data.
Thus, to read the whole block takes `8.3mS \times 5.48/360 = .13mS`. This is the best case and assumes the head was at the start of the block.
In the worst case, we have to add to this the time to go from innermost to outermost track, plus wait one full revolution to get to the data. 17.38 + 8.33 +0.13mS = 25.84mS.
For the average case, .13mS remains the same, the rotational latency is halved, and one takes roughly 1/3 the worst case for the cylinder seek time. This gives 10.76 mS.

Speeding Up Access to Secondary Storage

If you only have one HDD in use by your computer, the average time for the DB to get a block it wants will typically be larger than the average time as just calculated. This is because there will be other processes besides the DBMS making requests for blocks.
So one also has to consider disk I/O scheduling latency.
There are several techniques one can do to decrease the average disk access time, and thus increase the number of disks accesses/second that can be handled (throughput):
1. Place blocks that are accessed together on the same cylinder.
2. Use several smaller disks rather than a larger one. Each has an independent head, so can increase the number of block accesses/S.
3. "Mirror" a disk: Have two or more copies of the same data on different disks. This both allows several blocks to be accessed at the same time, and it helps protect against disk failures.
4. Use a better disk scheduling algorithm (in OS or DBMS) to choose which blocks should be read or written next.
5. Prefetch blocks to main memory in anticipation of latter use

I/O Model of Computation

We would like to speed up the performance of our DBMS when we need to use HDDs or SSDs.
In order to do this we need to fix a performance model that captures the idea that disk is much slower than RAM.
In trying to estimate performance of DBMS we assume:
- The database is too large to fit in RAM memory.
- That key parts are buffered
- Many users (so disk controller needs to queue requests)
- That disk requests will be for data on a random location on drive.
We will also assume the I/O Model of Computation:
- The time required to move a block to or from main memory is much greater than any time spent manipulating the data in memory. So the latter time will be ignored in our estimates

I/O Model Example

Suppose we make a DB query to a relation R for a record with key value `k`.
In the I/O computation model it is generally more useful to have an index on R to identify the disk block on which the tuple with `k` lives than to have the index also tell us where on the block this tuple appears.
To see this for the Megatron 747 drive of our earlier example, notice its time to read a block was 11mS.
In 11mS, a modern CPU can execute millions of instructions, so in particular can do a search within a 16KiB block using linear search around a 1/100 of this time.
So the cost of looking within a block is negligible compared to the I/O time.

Organizing Data by Cylinder

Seek time takes on average about half the time used to find a block on the disk.
By storing data, such as relations, that are likely to be accessed together in adjacent blocks we cut down on this time.
Example:
- Suppose relation `R` is stored 1024 disk blocks in our Megatron 747 drive.
- Suppose we need to access all of its tuples (say we want to compute a sum of something like salary).
- If the blocks were distributed at random around the drive, each block would take on average 10.76 mS to access, for a total time to access all blocks of 11S.
- The book says Megatron 747 has 8 platters for a total of 16 tracks/cylinder.
- As there are 256 sectors/track and 4 sectors/block, there are 64 blocks in a track, giving a total of 1024 blocks/cylinder.
- So it is possible to hold the whole relation on one cylinder.
- After an average seek time of 6.46mS to find the cylinder,and 16 revolutions to completely read all of the blocks of the relation (can only read 1 track at a time), we'd be done. This gives a total time of `(6.46 + 16 times 8.33) = 133 mS`.

Using Multiple Disks

Suppose we replace one disks with `n` disks each operating independently.
Provided the bus and main memory handle the higher transfer rates, this set-up should be able to read/write `n` times as much data/S.
However, the time to access any particular block will be unchanged.
If we want to speed up access to large database objects/relations, we can use a technique called striping to spread the objects blocks across the different disks.
Example:
- Suppose we have the same 1024 block relation of the previous slide, but are now using 4 disks.
- We can stripe the blocks as follows: on the first disk put blocks 1, 5, 9, etc; on the second disk 2, 6, 10, etc; on the third disk blocks 3, 7, 11, etc; and on the fourth disk blocks 4, 8, 12 etc.
- Each disk will have 256 blocks which can be stored on 4 tracks of a cylinder, so to read the whole relation will now take `(6.46 + 4 times 8.33) = 39.8 mS`
- In practice, there is some overhead (as highly like at least one disk will have a longer than average seek time), so would get a speed-up of 3 rather than 4 on average.

Mirroring

On the previous slide, we used multiple disks to hold distinct data from a DB.
It can also make sense to have two or more disks which hold the same data.
Such disks are said to be mirrors of each other.
One use case is if one disk fails, the other will still be available as a back up.
Another advantage is that with `n` disks, we can read `n` blocks from a relation in aout the same amount of time as one block in the single disk setting.
Even better, the disk controller can assign the same read request to any of the disks, so we can choose the disk whose head is closest to the block we want next getting a further speed-up.
Although read speeds will be faster with mirroring, write speeds will not have a speed-up as each new block must be written to all disks.

Disk Scheduling

A simple but effective way to schedule a large number of disk requests is to use the elevator algorithm.
Basically, the head roughly moves from the inner cylinder to the outer cylinder and then back down to the inner cylinder, etc.
As it passes a cylinder the head notices whether there has been a request for blocks on that cylinder. If so, it reads or writes those blocks.
Once done a cylinder, the head is moved to the next cylinder in the direction it was traveling
If there were no further requests in that direction, the direction of moving inward or outward is reversed.
The book gives an example showing this is about 2 to 3 times more efficient then doing things first come first serve

Prefetching (aka double buffering)

In some applications one can predict the order in which blocks will be requested from disk.
If so, one can load them in to memory before they are needed.
This can make it easier to schedule the disk.

Disk Failures

Let's now consider why disks drives might not work (before considering how to deal with disk failures).

Intermittent failures - we try to do a read or write but are unsuccessful. After repeated tries though we succeed.
Media decay - some bits on a sector become permanently corrupted and unreadable.
Write Failures - we write some data but fail. Worse still, the old data is also gone.
Disk crash -- entire disk is toasted. That is, suddenly the disk stops working and is permanently unreadable.

I/O Model, Disk Access, Scheduling, Failure

Outline