Data Storage & Disk Drives

What is stored in a Database System?

1. Relations
2. Metadata about relations
   1. Relation Schemas
      1. Structure of relations
      2. Constraints
      3. Triggers
   2. View Definitions
   3. Indexes
      1. Derived information to speed up access to relations
      2. Can be a B+ tree, hash table, etc.
   4. Statistical information about relations for use by query optimizer
   5. Log files
      1. Maintained for data recovery

Memory Hierarchy

1. Primary Memory
   1. Register
   2. Static RAM (SRAM)
   3. Dynamic RAM (DRAM)
2. Secondary Memory
   1. Magnetic Disk (HDD)
   2. Solid-State Disk (SSD)
3. Tertiary Memory
   1. Optical disks
   2. Tapes

As we move from primary to secondary to tertiary memory, the following changes

1. Capacity increases (For the same price)
2. Cost decreases (For the same capacity)
3. Access speed decreases
4. Primary storage is volatile while secondary and tertiary storage are non-volatile

DBMS Storage

• DBMS stores data on non-volatile disk for persistence
• DBMS processes data in RAM
• Main disk access operations
  • Read: Transfer data from disk to ram
  • Write: Transfer data from ram to disk

How does a Magnetic Disk Work?

A hard disk consists of number of disks called platters. Each platter is divided into number of tracks. Each track is divided into number of sectors. Each sector is divided into number of blocks. Each block is usually 512 bytes.

When a hard disk reads / writes data, the following happens:

1. The disk controller interpreters access command given by the OS. This incurs command processing time (C).
2. The arms of the disk is moved to the correct track. This incurs seek time (S).
3. The platter rotates and the read / write head moves to the correct sector. This incurs rotational delay (R).
4. The read / write head reads / writes the data. This incurs transfer time (T).

Based on the steps above, we can calculate the total disk access time using the formula below:

access time = C (negligible) + S + R + T

C = Command Processing Time
S = Seek Time
R = Rotational Delay
T = Transfer Time

As Command processing time is usually negligible, we can simplify the formula to:

access time = S + R + T

S = Seek Time
R = Rotational Delay
T = Transfer Time

Seek time

As mentioned above, the seek time is the time taken for the arms of the disk head to move to the correct track.

The average seek time for a HDD is 5ms to 6ms.

Rotational Delay (or Rotational Latency)

Rotational delay depends on the rotation speed of the HDD. It is usually measured in revolutions per minute (RPM).

The rotational delay is the time taken for the disk to rotate to the correct sector.

We can calculate the average rotational delay by using the formula below:

R = 0.5(60s / RPM)

R = Average Rotational Delay
RPM = Revolutions Per Minute

For example

Given RPM = 7200
R = 0.5(60s / 7200)
  = 0.5(0.00833s)
  = 0.00417s
  = 4.17ms

R = Average Rotational Delay
RPM = Revolutions Per Minute

Transfer Time

The transfer time is the time taken for the read / write head to read / write the data.

The average sector transfer time for a HDD is 100 - 200 microseconds.

The formula for calculating the average transfer time is:

T = n * (1/RPM) / (SpT)

n = number of requested sectors on the same track
SpT = Sectors Per Track
T = Transfer Time

Sequential I/O vs Random I/O

Sequential I/O

Sequential I/O refers to reading / writing data in a sequential manner. For example, reading / writing data to a file.

When the file is written to sequentially, the disk does the following:

1. Interpret the access command (Time negligible)
2. Move arm to position disk head on track (Seek time incurred)
3. Wait for block to rotate under head (Rotational delay incurred)
4. Move all the data to the disk Surface (Transfer time incurred)

Using the formulas we have above we will get the following:

Access Time = S + R + T
            = S + 0.5(60s / RPM) + n * (1/RPM) / (SpT)

RPM = Revolutions Per Minute
S = Seek Time
R = Rotational Delay
T = Transfer Time
N = Number of Sectors to be written
SpT = Sectors Per Track

As the data for a file with n chunks is all together, the seek time and rotational delay is only incurred once. This way, we can save time by writing data sequentially.

Random I/O

Random I/O refers to reading / writing data in a random manner. For example, reading / writing data to a database.

When the database is written to randomly, the disk does the following:

1. For each sector (for n sectors)
   1. Interpret the access command (Time negligible)
   2. Move arm to position disk head on track (Seek time incurred)
   3. Wait for block to rotate under head (Rotational delay incurred)
   4. Move all the data to the disk Surface (Transfer time incurred)

Using the formula we discussed above, we will get the following:

Access Time = n(S + R + T)
            = n(S + 0.5(60s / RPM) + n * (1/RPM) / (SpT))

RPM = Revolutions Per Minute
S = Seek Time
R = Rotational Delay
T = Transfer Time
n = Number of Sectors to be written
SpT = Sectors Per Track

As the data for a database with n chunks is not all together, the seek time and rotational delay is incurred for each chunk.

We are pessimistic in our assumption here as we assume that the random data is neither on the same track nor on a nearby sector.

Solid State Drives (SSD)

SSD is a type of non-volatile memory that uses flash memory to store data. They are built using NAND flash memory.

Here are some comparisons between HDD and SSD:

However, SSDs also have their own drawbacks

1. They are more expensive than HDDs
2. Updates to a page requires erasure of multiple pages (5ms) before overwriting page
3. SSDs have a limited number of write cycles (100,000 to 1,000,000)

Useful links

1. What is seek time
2. Hard disk (hard drive) format – the tracks and sectors of the hard disk