slide1 l.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
CPSC-608 Database Systems PowerPoint Presentation
Download Presentation
CPSC-608 Database Systems

Loading in 2 Seconds...

play fullscreen
1 / 25

CPSC-608 Database Systems - PowerPoint PPT Presentation


  • 177 Views
  • Uploaded on

CPSC-608 Database Systems. Fall 2010. Instructor: Jianer Chen Office: HRBB 315C Phone: 845-4259 Email: chen@cse.tamu.edu. Notes #5. database administrator. lock table. DDL complier. DDL language. file manager. logging & recovery. concurrency control. transaction manager.

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'CPSC-608 Database Systems' - sharne


An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
slide1

CPSC-608 Database Systems

Fall 2010

Instructor: Jianer Chen

Office: HRBB 315C

Phone: 845-4259

Email: chen@cse.tamu.edu

Notes #5

slide2

database

administrator

lock table

DDL

complier

DDL

language

file

manager

logging &

recovery

concurrency

control

transaction

manager

database

programmer

index/file

manager

buffer

manager

query

execution

engine

DML

complier

main

memory

buffers

DML (query)

language

secondary

storage

(disks)

DBMS

Graduate Database

slide3

database

administrator

lock table

DDL

complier

DDL

language

file

manager

logging &

recovery

concurrency

control

transaction

manager

database

programmer

index/file

manager

buffer

manager

query

execution

engine

DML

complier

main

memory

buffers

DML (query)

language

secondary

storage

(disks)

DBMS

Graduate Database

slide5

A Typical Computer

CPU

bus

...

main

memory

Disk

controller

Secondary

Storage

disks

slide6

Main Memory

fast

small capacity (gigabytes)

volatile

Disks

slow

large capacity (100’s gigabytes)

non-volatile

slide7

Typical Disk

Terms: Platter, Head, Actuator,

Cylinder, Track,

Sector, Gap

slide8

Top View

Track

Sector

Gap

a typical disk
A “typical” disk

5 platters (thus 10 surfaces)

A surface has 20,000 tracks

A track has 500 sectors (million bytes)

A sector has several thousand bytes

Disk makes 5000 revolutions per minute (so about 10 millisecond per rotation)

blocks
Blocks

A (logic) block = one or several sectors

Block address

Physical device

Cylinder #

Surface #

Sector

slide11

Disk Access Time

block X

in memory

I want

block X

?

Time = Seek Time + Rotational Delay +

Transfer Time + Other

seek time
Seek Time

3 or 5x

Time

x

1

N

Cylinders Traveled

average random seek time
Average Random Seek Time

N

N

 SEEKTIME (i  j)

S =

N(N-1)

j=1

ji

i=1

typical seek time: 10 ms  40 ms

rotational delay
Rotational Delay

Head here

Block I want

Average Rotational Delay

R = 1/2 revolution

typical rotational delay = 8 ms

transfer rate
Transfer Rate:

typical: t = 80 MB/second =

80 KB/millisecond

transfer time: block size / t

other delays
Other Delays

CPU time to issue I/O

Contention for controller

Contention for bus, memory

Typical value: ≈ 0

thus reading a block of 16k bytes
Thus, reading a block of 16K bytes:

Time = Seek Time + Rotational Delay +

Transfer Time + Other

~ 30 ms + 8 ms + 16/80 ms + 0

~ 40 ms

slide18

Disks

slow (read/write: 1~40 millisecond)

large capacity (100’s gigabytes)

non-volatile

Main Memory

fast (read/write: 10-100 nanosecond)

small capacity (gigabytes)

volatile

Disks are about 105~106 times slower than

main memory

i o model of computation
I/O Model of Computation

Dominance of I/O cost: if a block needs to be moved between disk and main memory, then the time taken to perform the read/write is much larger than the time likely to be used to manipulate that data in main memory.

The number of disk block reads/writes is a good approximation to the entire computation.

example sorting on disk
Example. Sorting on disk

Each tuple (with a key) takes 160 bytes

Each block holds 100 tuples (16KB)

A relation R has 10M tuples

(1.6 GB, 100K blocks)

Main memory has 100MB (6400 blocks)

A disk read/write: 40 ms

main memory sorting algorithms
Main memory sorting algorithms

heap sort:

10M * log2 (10M)

= 230M disk block read/write

= 9200M ms = 9200000 seconds > 100 day

quick sort and merge sort:

2 * 100K (blocks) * log2 (10M)

= 4.6M disk block read/write

= 184M ms = 184000 seconds > 2 day

two phase multiway mergesort
Two-phase Multiway MergeSort

Phase 1. making sorted sublist

repeat

fill the main memory with remaining tuples in

R and sort them;

write the sorted sublist (of 6400 blocks) back to disk

Phase 2. Merging

repeat

bring in a block from each of the sorted sublist;

merge them and put in an “output” block;

write the “output” block back to disk when it is full

two phase multiway mergesort23
Two-phase Multiway MergeSort

# sublists = 100K/6400 = 16

thus, in phase 2, we can easily hold a block for each sublist in the main memory

Disk block read/write:

100K (blocks) * 4

= 400K disk block read/write

= 16M ms = 16000 seconds < 4.5 hours

slide24

database

administrator

lock table

DDL

complier

DDL

language

file

manager

logging &

recovery

concurrency

control

transaction

manager

database

programmer

index/file

manager

buffer

manager

query

execution

engine

DML

complier

main

memory

buffers

DML (query)

language

secondary

storage

(disks)

DBMS

Graduate Database

slide25

database

administrator

lock table

DDL

complier

DDL

language

file

manager

logging &

recovery

concurrency

control

transaction

manager

database

programmer

index/file

manager

buffer

manager

query

execution

engine

DML

complier

main

memory

buffers

DML (query)

language

secondary

storage

(disks)

DBMS

Graduate Database