CS411 Database Systems

1
CS411Database Systems

• Storage Representation

2
(No Transcript)
3
Data Storage

• Storing Data Disks
• Buffer manager
• Representing data
• External Sorting

4
DisksBuffer Manager
5
The Memory Hierarchy (2002)
Main Memory Disk Cache

• Processor Cache
• access time 10 nanos

• Volatile
• 256M-1G
• expensive
• Access time
• 10-100 nanoseconds

Disk
Tape

• Persistent
• 2-10 GB storage
• speed
• Rate5-10 MB/S
• Access time
• 10-15 msecs.

• 1.5 MB/S transfer rate
• 280 GB typical
• capacity
• Only sequential access
• Not for operational
• data

6
?

• The complexity for minimum component costs has
  increased at a rate of roughly a factor of two
  per year ... Certainly over the short term this
  rate can be expected to continue, if not to
  increase. Over the longer term, the rate of
  increase is a bit more uncertain, although there
  is no reason to believe it will not remain nearly
  constant for at least 10 years.
• "Cramming more components onto integrated
  circuits", Electronics Magazine 19 April 1965

7
Gordon Moore

• The complexity for minimum component costs has
  increased at a rate of roughly a factor of two
  per year ... Certainly over the short term this
  rate can be expected to continue, if not to
  increase. Over the longer term, the rate of
  increase is a bit more uncertain, although there
  is no reason to believe it will not remain nearly
  constant for at least 10 years. That means by
  1975, the number of components per integrated
  circuit for minimum cost will be 65,000. I
  believe that such a large circuit can be built on
  a single wafer.
• "Cramming more components onto integrated
  circuits", Electronics Magazine 19 April 1965

8
Implications for Disk vs Memory?

• I/O Model of Computation

9
Main Memory

• Fastest, most expensive
• Today 256MB are common even on PCs
• Many databases could fit in memory
• New industry trend Main Memory Database
• E.g TimesTen
• Main issue is volatility

10
Secondary Storage

• Disks
• Slower, cheaper than main memory
• Persistent !!!
• The unit of disk I/O block
• Typically 1 block 4k
• Used with a main memory buffer

11
(No Transcript)
12
Important Disk Access Characteristics

• Disk latency time between when command is
  issued and when data is in memory
• Disk latency
• seek time rotational latency transfer time
• Seek time time for the head to reach cylinder
• 10ms 40ms
• Rotational latency time for the sector to
  rotate
• Rotation time 10ms
• Average latency 10ms/2
• Transfer time typically 5-10MB/s
• Disks read/write one block at a time (typically
  4kB)

13
Disk Crash?
14
RAIDs

• Redundant Array of Independent Disks
• Was inexpensive disks
• Idea use more disks, increase reliability
• Recall
• Database recovery helps after a systems crash,
  not after a disk crash
• 6 ways to use RAIDs. Most important
• Level 1 Simple mirror
• Level 4 use N-1 data disks, plus one parity disk
• Level 5 same, but alternate which disk is the
  parity
• Level 6 use Hamming codes instead of parity

15
Buffer Management
16
(No Transcript)
17
Buffer Manager

• When a page is first requested, it is read into a
  free frame
• The DBMS typically requests it to be pinned
• pin_count how many processes requested it
  pinned
• When the DBMS writes to it, the buffer manager
  marks it dirty
• When the DBMS doesnt need it any more,
  un-pinned
• pin_count is decremented
• Typical replacement policy (always chooses among
  un-pinned frames)
• LRU
• Clock
• FIFO
• In practice there is DBMS advice too

18
Clock (p.767)

• "Second chance algorithm"
• Round robin search for entry0
• As we move past entry1 set entry0
• Efficient approximation to LRU

19
Buffer Manager
Why not use the Operating System for the task??
20
Buffer Manager
Why not use the Operating System for the
task?? - DBMS may be able to anticipate access
patterns - Hence, may also be able to perform
prefetching - DBMS needs the ability to force
pages to disk.
21
Representing Data
22
Representing Data Elements

• Relational database elements
• CREATE TABLE Product (
• pid INT PRIMARY KEY,
• name CHAR(20),
• description VARCHAR(200),
• maker CHAR(10) REFERENCES Company(name))
• A tuple is represented as a record

23
Representing Data Elements

• Representing objects
• interface Company
• attribute string name
• relationship SetltProductgt makes
• inverse Productmaker
• An object is represented as a record plus object
  identifier
• What to do with repeating fields (e.g. makes)

24
(No Transcript)
25
(No Transcript)
26
(No Transcript)
27
(No Transcript)
28
Storing Records in Blocks

• Blocks have fixed size (typically 4k)

BLOCK
R1
R2
R3
R4
29
Spanning Records Across Blocks

• When records are very large
• Or even medium size saves space in blocks

block header
block header
R1
R2
R3
R2
30
BLOB

• Binary large objects
• Supported by modern database systems
• E.g. images, sounds, etc.
• Storage attempt to cluster blocks together

31
Modifications Insertion

• File is unsorted ( heap file)
• add it to the end (easy ?)
• File is sorted
• Is there space in the right block ?
• Yes we are lucky, store it there
• Is there space in a neighboring block ?
• Look 1-2 blocks to the left/right, shift records
• If anything else fails, create overflow block

32
(No Transcript)
33
Modifications Deletions

• Free space in block, shift records
• Maybe be able to eliminate an overflow block
• Can never really eliminate the record, because
  others may point to it
• Place a tombstone instead (a NULL record)

34
Modifications Updates

• If new record is shorter than previous, easy ?
• If it is longer, need to shift records, create
  overflow blocks

35
Logical/Physical AddressesPointer Swizzling
36
Physical Addresses

• Each block and each record have a physical
  address that consists of
• The host
• The disk
• The cylinder number
• The track number
• The block within the track
• For records an offset in the block
• sometimes this is in the blocks header

37
(No Transcript)
38
(No Transcript)
39
Optimization Pointer Swizzling

• the process of replacing a physical/logical
  pointer with a main memory pointer
• Still need translation table, but subsequent
  references are faster

40
Pointer Swizzling
Block 2
Block 1
Disk
read in memory
swizzled
Memory
unswizzled
41
Pointer Swizzling

• Automatic when block is read in main memory,
  swizzle all pointers in the block
• On demand swizzle only when user requests
• No swizzling always use translation table

42
Pointer Swizzling

• When blocks return to disk pointers need to be
  unswizzled
• Danger someone else is using this block
• Pinned blocks we dont allow it to return to
  disk
• Keep a list of references to this block

43
External Sorting
44
The I/O Model of Computation

• In main memory algorithms we care about CPU time
• In databases time is dominated by I/O cost
• Assumption cost is given only by I/O
• Consequence need to redesign certain algorithms
• Will illustrate here with sorting

45
Sorting

• Illustrates the difference in algorithm design
  when your data is not in main memory
• Problem sort 1Gb of data with 1Mb of RAM.
• Arises in many places in database systems
• Data requested in sorted order (ORDER BY)
• Needed for grouping operations
• First step in sort-merge join algorithm
• Duplicate removal
• Bulk loading of B-tree indexes.

46
(No Transcript)
47
Two-Way External Merge Sort

• Each pass we read write each page in file.
• N pages in the file gt the number of passes
• So total cost is
• Improvement start with larger runs
• Sort 1GB with 1MB memory in 10 passes

Input file
6,2
2
3,4
9,4
8,7
5,6
3,1
PASS 0
1-page runs
1,3
2
3,4
5,6
2,6
4,9
7,8
PASS 1
4,7
1,3
2,3
2-page runs
8,9
5,6
2
4,6
PASS 2
2,3
4,4
1,2
4-page runs
6,7
3,5
6
8,9
PASS 3
1,2
2,3
3,4
8-page runs
4,5
6,6
7,8
9
48
Animation Version 1
49
Two-Way External Merge Sort
2
3
6
8
1
4
5
3
4
9
6
2
7
3
4
6
2
4
9
8
7
5
1
3
6
2
5
Input file
PASS 0
2
6
5
3
1
7
8
4
9
6
2
4
3
4
1-page runs
PASS 1
2-page runs
PASS 2
50
2
3
4
7
1
3
7
4
2
3
3
1
2-page runs
4
6
6
8
9
5
2
9
8
6
4
6
5
2
PASS 2
2
3
4
4
1
2
4-page runs
7
6
5
3
6
8
9
PASS 3
8-page runs
51
Animation Version 2
52
Disk
Memory
PASS 0
3
4
2
6
Before
4
9
8
7
.
.
.
Disk
Memory
After
8
7
.
.
.
53
Disk
Memory
PASS 1
3
4
9
4
6
2
Before
9
4
7
8
8
7
.
.
.
Disk
Memory
After
.
.
.
54
Disk
Memory
PASS 2
6
4
3
2
2
3
6
4
1
3
9
8
7
4
Before
6
5
3
1
4
7
2
5
6
2
Disk
Memory
9
8
After
55
Disk
Memory
PASS 3
2
9
8
7
6
4
4
3
3
2
4
4
6
7
Before
3
2
1
5
6
1
2
3
5
6
8
9
Memory
Disk
After
9
56
Can We Do Better ?

• We have more main memory
• Should use it to improve performance

57
Cost Model for Our Analysis

• B Block size
• M Size of main memory
• N Number of records in the file
• R Size of one record

58
(No Transcript)
59
(No Transcript)
60
(No Transcript)
61
Cost of External Merge Sort

• Number of passes
• Think differently
• Given B 4KB, M 64MB, R 0.1KB
• Pass 1 runs of length M/R 640000
• Have now sorted runs of 640000 records
• Pass 2 runs increase by a factor of M/B 1
  16000
• Have now sorted runs of 10,240,000,000 1010
  records
• Pass 3 runs increase by a factor of M/B 1
  16000
• Have now sorted runs of 1014 records
• Nobody has so much data !
• Can sort everything in 2 or 3 passes !