exam i grades
DESCRIPTION
Exam I Grades. Max: 96, Min: 37 Mean/Median:66, Std: 18 Distribution: >= 90 : 6 >= 80 : 12 >= 70 : 9 >= 60 : 9 >= 50 : 7 >= 40 : 11 >= 30 : 5 Letter Grades: >=85: A >= 60: B >= 40: C < 40: D. Buffer Management Record Formats, Files & Indexing. Query Optimization and Execution. - PowerPoint PPT PresentationTRANSCRIPT
Exam I Grades Max: 96, Min: 37 Mean/Median:66, Std: 18 Distribution:
>= 90 : 6 >= 80 : 12 >= 70 : 9 >= 60 : 9 >= 50 : 7 >= 40 : 11 >= 30 : 5
Letter Grades: >=85: A >= 60: B >= 40: C < 40: D
1
Buffer Management
Record Formats, Files & Indexing
3
Disks, Memory, and Files
Query Optimizationand Execution
Relational Operators
Files and Access Methods
Buffer Management
Disk Space Management
DB
The BIG picture…
Focus on: “Typical Disk”
Terms: Platter, Head, ActuatorCylinder, TrackSector (physical),Block (logical), Gap
…
Dis
kC
on
trolle
r
BU
S
Top View
Sector
Gap
Often different numbersof sectors per track
Track
Block(typically multiple sectors)
block xin memory
?
I wantblock X
Time = Seek Time (locate track) +Rotational Delay (locate sector)+Transfer Time (fetch block) +Other (disk controller, …)
Key Performance Metric:Time to Fetch Block
Arranging Pages on Disk Blocks are multiples of sector size Next block concept: blocks on same track, followed by blocks on same cylinder, followed by blocks on adjacent cylinder Blocks in a file should be arranged
sequentially on disk (by ‘next’), to minimize seek and rotational delay.
For a sequential scan, pre-fetching several pages at a time is a big win!
Disk Space Management Lowest layer of DBMS software manages
space on disk. Higher levels call upon this layer to:
allocate/de-allocate a page read/write a page
Request for a sequence of pages must be satisfied by allocating the pages sequentially on disk! Higher levels don’t need to know how this is done, or how free space is managed.
9
Context
Query Optimizationand Execution
Relational Operators
Files and Access Methods
Buffer Management
Disk Space Management
DB
Data Items
Records
Blocks/Pages
Files
Memory
Disk
Storage Overview
11
Reducing Number of Page Transfers
Keep cache of recently accessed pages in main memory Goal: request for page can be
satisfied from cache/buffer pool instead of disk
Purge pages when buffer pool is full• E.g., Use LRU algorithm• Record clean/dirty state of page
12
Accessing Data Through Buffer
Buffer pool
DBMS
Application
pageframe
Buffer Management in a DBMS
Data must be in RAM for DBMS to operate on it! Table of <frame#, pageid> pairs is maintained.
DB
MAIN MEMORY
DISK
disk page
free frame
Page Requests from Higher Levels
BUFFER POOL
choice of frame dictatedby replacement policy
When a Page is Requested ...
If requested page is not in pool: Choose a frame for replacement If frame is dirty, write it to disk Read requested page into chosen frame
Pin the page and return its address.
If requests can be predicted (e.g., sequential scans) pages can be pre-fetched several pages at a time!
More on Buffer Management
Requestor of page must unpin it, and indicate whether page has been modified: dirty bit is used for this.
Page in pool may be requested many times, a pin count is used. A page is a
candidate for replacement iff pin count = 0.
Buffer Replacement Policy Frame is chosen for replacement
by a replacement policy: Least-recently-used (LRU), Clock, MRU, etc.
Policy can have big impact on # of I/O’s; depends on the access pattern.
17
LRU Replacement Policy Least Recently Used (LRU)
for each page in buffer pool, keep track of time when last unpinned
replace the frame which has the oldest (earliest) time
very common policy: intuitive and simple• Works well for repeated accesses to popular pages
Problems? Problem: Sequential flooding
LRU + repeated sequential scans. # buffer frames < # pages in file means each page
request causes an I/O. Idea: MRU better in this scenario?
18
“Clock” Replacement Policy
An approximation of LRU Arrange frames into a cycle, store one
reference bit per frame Can think of this as the 2nd chance bit
When pin count reduces to 0, turn on ref. bit When replacement necessary
do for each page in cycle {if (pincount == 0 && ref bit is on)
turn off ref bit;else if (pincount == 0 && ref bit is
off)choose this page for
replacement;} until a page is chosen;
A(1)
B(p)
C(1)
D(1)
Physical vs. Logical Addresses
Device IDE.g., Record Cylinder #
Address = Track #or ID Block #
Offset in block
Block ID
Logical Addresses
E.g., Record ID is arbitrary bit string
maprec ID r address
a
Physicaladdr.Rec ID
Swizzling
Memory Disk
Rec A
block 1
Rec Ablock 2 block 2
block 1
Translation DB Addr Mem Addr
Table Rec-A Rec-A-inMem
One Option:
In memory pointers - need “type” bit
to disk
to memoryM
Another Option:
Swizzling
• Automatic• On-demand• No swizzling / program control
Record Formats, Files & Indexing
26
Context
Query Optimizationand Execution
Relational Operators
Files and Access Methods
Buffer Management
Disk Space Management
DB
Disk & Buffer Manager Disks provide cheap, non-volatile storage.
Random access, but cost depends on location of page on disk; important to arrange data sequentially to minimize seek and rotation delays.
Buffer manager brings pages into RAM. Page stays in RAM until released by requestor. Written to disk when frame chosen for
replacement (which is sometime after requestor releases the page).
Choice of frame to replace based on replacement policy.
Tries to pre-fetch several pages at a time.
28
Buffer Management and Files
Storage of Data Fields, either fixed or variable length... Stored in Records... Stored in Pages... Stored in Files
If data won’t fit in RAM, store on Disk Need Buffer Pool to hold pages in RAM Different strategies decide what to keep
in pool
Record Formats: Fixed Length
Information about field types same for all records in a file; stored in system catalogs.
Finding i’th field does not require scan of record.
Base address (B)
L1 L2 L3 L4
F1 F2 F3 F4
Address = B+L1+L2
Header
Record Formats: Variable Length
Two alternative formats (# fields is fixed):
Second offers direct access to i’th field, efficient storage of nulls (special don’t know value); small directory overhead.
4 $ $ $ $
FieldCount
Fields Delimited by Special Symbols
F1 F2 F3 F4
F1 F2 F3 F4
Header: Array of Field Offsets
Page Formats: Fixed Length Records
Record id = <page id, slot #>. In first alternative, moving records for free space management changes rid; may not be acceptable.
Slot 1Slot 2
Slot N
. . . . . .
N M10. . .
M ... 3 2 1PACKED UNPACKED, BITMAP
Slot 1Slot 2
Slot N
FreeSpace
Slot M
11
number of records
numberof slots
Page Formats: Variable Length Records
Can move records on page without changing rid; so, attractive for fixed-length records too.
Page iRid = (i,N)
Rid = (i,2)
Rid = (i,1)
Pointerto startof freespace
SLOT DIRECTORY
N . . . 2 120 16 24 N
# slots
• Unspanned: records must be within one block
block 1 block 2
...
• Spannedblock 1 block 2
...
Spanned vs. Unspanned Records
R1 R2
R1
R3 R4 R5
R2 R3(a)
R3(b) R6R5R4 R7
(a)
Block header - data at beginning that
describes blockMay contain:- File ID (or RELATION or DB ID)
- This block ID - Record directory
- Pointer to free space- Type of block (e.g. contains recs type 4;
is overflow, …)- Pointer to other blocks “like it”- Timestamp ...