Lecture 19: Hard disk - The Final Frontier#

Indexing#

Announcements#

  • The comprehensive Final Exam is going to be on

    • December 16th, at 11:30AM in Sage 3303 (this room)

  • Hw#6 is out, due on November 19th.

    • Please read carefully and test extensively before submitting.

  • No lecture exercise today! Lecture exercises will resume next week.

  • Data is stored on hard disk, read to memory for any operation.

  • Hard disk is permanent storage, while memory is volatile.

  • Types of hard disk:

    • Magnetic disks: slower but cheaper

    • Solid state: faster but more expensive

Magnetic disk read:

  • To read a single page: seek time (to move the read head) + rotational latency (wait for the disk to spin to the correct track) + transfer time

-> RANDOM IO to read K random pages (in different parts of the disk): K * (seek + rl + tt)

-> SEQUENTIAL IO to read K pages on the same track seek + rl + K*tt

Table: Performance Characteristics of Fast Magnetic Disks

Disk Drive

Seek Time (ms)

Average Rotational Latency (ms)

Transfer Rate (MB/s)

Seagate Exos 32TB

4–9

4.16

285

Seagate Exos X16 16TB

4–9

4.16

261

WD Ultrastar 7K6000

7.6–8

4.16

Varies

HPE 26TB Enterprise HDD

4–9

4.16

278

  • Solid state hard drives do not have moving parts, hence reads are much faster.

  • Virtual machines can be much slower due to the additional overhead of the virtualization.

Example:

My laptop (solid state drive):

min/avg/max/mdev = 29 us / 32.6 us / 35 us / 2.58 us

Magnetic drive

min/avg/max/mdev = 157.2 us / 166.9 us / 183.5 us / 8.78 us

Virtual machine:

min/avg/max/mdev = 481.5 us / 586.4 us / 652.8 us / 56.5 us

Table: Approximate Time to Read a 4 KB Disk Page#

Performance Measure

HDD (Random Access)

HDD (Sequential Access)

SATA SSD

Average seek time

5 ms

~0 ms*

Negligible

Average rotational latency

4.17 ms

~0 ms*

None

4 KB data transfer time

0.015 ms

0.015 ms

0.03 ms

Total read time

~9.2 ms

~0.015 ms

~0.05–0.15 ms

Cost to storage capacity comparison#

Storage Capacity

Magnetic HDD Price

SATA SSD Price

NVMe SSD Price

500 GB

\(30–\)50

\(50–\)100

—

1 TB

\(40–\)80

\(100–\)180

\(120–\)190

2 TB

\(60–\)120

\(200–\)300

\(240–\)350

4 TB

\(100–\)200

\(400–\)700

\(450–\)700

8 TB

\(200–\)350

$1,000+

$1,000+

12–24 TB

\(300–\)700+

Limited availability

Limited availability

select * from bakers order by baker;

Assume:

  • The whole relation is stored in a single page: 10 ms

  • Assume that there are 4,000 values to sort:

Sorting 4,000 integers

Quicksort / Introsort 5–30 micro seconds
Mergesort 5–40 micro seconds
Heapsort 10–50 micro seconds
Bubble sort 1–5 ms

Reading the page is generally much slower than sorting.

Reliability of different storage technologies

Magnetic hard disk (HDD)

  • Contains moving mechanical parts.

  • Subject to mechanical wear, head crashes, and motor failures.

  • Can be affected by vibration and physical shock.

  • Suitable for long-term, high-capacity storage.

SATA SSD

  • Has no moving mechanical parts.

  • Resistant to mechanical shock and vibration.

  • Flash memory has finite write endurance.

  • Controller or electronic failures can occur.

NVMe SSD

  • Also uses flash memory, like SATA SSDs.

  • No moving mechanical components.

  • Can experience high temperatures under heavy workloads.

  • Reliability depends on NAND quality, controller, firmware, and write endurance.

RAID Array: Redundant Array of Inexpensive Disks

RAID array examples

A disk page in the database stores multiple tuples.

Postgresql disk page

Cost of most operations in a database are measured in terms of the number of disk pages read/written.

  • A disk page is the smallest unit of data that can be read/written to disk. (4K or 8K)

  • Whenever a disk page is read into memory, it is put into a memory block. Assume for now a block is the same size as a page.

  • We will use block and page interchangeably.

  • To READ (select): you must read data to memory.

  • To UPDATE/Change: you must read data to memory, change it in memory and write back to disk.

  • So, the cost a query:

select * from tableX;

  • is the cost of reading all the disk pages for the relation tableX: Sequential scan.

  • A single page stores many tuples (depending on the size of the attributes we store)

  • Number of pages depend on the size of a tuple and the number of tuples.

Indexing as a way to speed up queries#

select 
    actorid
from
    actors
where 
    name = 'PS' ;
  • Create a look up structure for the name attribute, to find where a tuple is stored for a specific name

  • Index on a key value (name) and a pointer to the tuple (page number for that tuple)

Actors relation has 149K tuples and 1088 pages.

Create an index on actorid, integer = 4 bytes

Create an entry for each tuple: integer(actorid) a tuple address (12 bytes) = 16 bytes per tuple

  • Each index page stored in an 8K disk page can index up to 8K/(16) = 512 tuples

  • So the leaf node of my index takes up (149000/512): 292 pages

  • The next level points to the leaf nodes, still can index 512 tuples at most.

B-trees - stores B entries in each node/disk page