Storing Data: Database Organization Yea, from the table of my - - PowerPoint PPT Presentation

▶
storing data database organization
SMART_READER_LITE
LIVE PREVIEW

Storing Data: Database Organization Yea, from the table of my - - PowerPoint PPT Presentation

May 10, 2023 300 likes •574 views

Storing Data: Database Organization Yea, from the table of my memory Ill wipe away all trivial fond records. -- Shakespeare, Hamlet 340151 Big Data & Cloud Computing (P. Baumann) 1 ...Now Databases, Again Buffering

slide-1
SLIDE 1

1 340151 Big Data & Cloud Computing (P. Baumann)

Storing Data: Database Organization

“Yea, from the table of my memory I’ll wipe away all trivial fond records.”

  • - Shakespeare, Hamlet
slide-2
SLIDE 2

2 340151 Big Data & Cloud Computing (P. Baumann)

...Now Databases, Again

  • Buffering
  • Fixed/variable length records
  • Arranging files on disk
  • Catalogs
slide-3
SLIDE 3

3 340151 Big Data & Cloud Computing (P. Baumann)

Disk Space Management

  • Lowest layer of DBMS manages space on disk
  • Higher levels call layer to:
  • allocate/de-allocate page
  • read/write page
slide-4
SLIDE 4

4 340151 Big Data & Cloud Computing (P. Baumann)

Buffer Management in a DBMS

  • Table of <frame#, pageid> pairs (plus more, see next)

MAIN MEMORY DISK disk page free frame

page requests from higher layers

BUFFER POOL

choice of frame dictated by replacement policy

DB

slide-5
SLIDE 5

5 340151 Big Data & Cloud Computing (P. Baumann)

When Page is Requested ...

  • If page not in pool:
  • Choose frame for replacement
  • If frame dirty, write to disk
  • Read page into frame
  • Pin page & return address
  • If possible, arrange blocks sequentially on disk
  • minimize seek and rotational delay
  • For sequential scan (access predictable!), pre-fetching is a big win

NB: 'page' 'block'

slide-6
SLIDE 6

6 340151 Big Data & Cloud Computing (P. Baumann)

More on Buffer Management

  • Page requestor must unpin

& indicate whether page has been modified

  • dirty bit
  • Page in pool may be requested many times
  • pin count: page is candidate for replacement iff pin count == 0
  • CC & recovery: additional I/O when replacing frame
  • Write-Ahead Log protocol
slide-7
SLIDE 7

7 340151 Big Data & Cloud Computing (P. Baumann)

Buffer Replacement Policy

  • Frame chosen for replacement by replacement policy:
  • Least-recently-used (LRU), Clock, MRU etc.
  • Policy can have big impact on # of I/O’s; depends on access pattern
slide-8
SLIDE 8

8 340151 Big Data & Cloud Computing (P. Baumann)

DBMS vs. OS File System

  • Differences in OS support: portability issues
  • Some limitations
  • e.g., files can’t span disks
  • Buffer management in DBMS requires ability to:
  • pin page in buffer pool, force page to disk (CC & recovery!)
  • adjust replacement policy + pre-fetch pages

based on access patterns in typical DB operations

OS does disk space & buffer mgmt: why not let OS manage these tasks?

slide-9
SLIDE 9

9 340151 Big Data & Cloud Computing (P. Baumann)

...Now Databases, Again

  • Buffering
  • Fixed/variable length records
  • Arranging files on disk
  • Catalogs
slide-10
SLIDE 10

10 340151 Big Data & Cloud Computing (P. Baumann)

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

slide-11
SLIDE 11

11 340151 Big Data & Cloud Computing (P. Baumann)

Record Formats: Variable Length

  • Two alternative formats (# fields fixed):

4 $ $ $ $

Field Count

Fields Delimited by Special Symbols (eg, '\0')

F1 F2 F3 F4

Variant 1: delimiters

F1 F2 F3 F4

Array of Field Offsets Variant 2:

  • ffset ptrs
  • Var2: direct access to i’th field; efficient storage of nulls; small directory
  • verhead
slide-12
SLIDE 12

12 340151 Big Data & Cloud Computing (P. Baumann)

Page Formats: Fixed Length Records

  • Record id = <page id, slot #>
  • In first alternative, moving records for free space mgmnt changes rid
  • may not be acceptable

Slot 1 Slot 2 Slot N

. . .

N PACKED Free Space number

  • f records

. . .

M 1 . . . M ... 3 2 1 UNPACKED, BITMAP Slot 1 Slot 2 Slot N Slot M 1 1 number

  • f slots
slide-13
SLIDE 13

13 340151 Big Data & Cloud Computing (P. Baumann)

Page Formats: Variable Length Records

Indirection can move records on page without changing rid attractive for fixed-length records too

Page i Rid = (i,N) Rid = (i,2) Rid = (i,1)

Pointer to start

  • f free

space

SLOT DIRECTORY

N . . . 2 1 20 16 24

N # slots

slide-14
SLIDE 14

14 340151 Big Data & Cloud Computing (P. Baumann)

...Now Databases, Again

  • Buffering
  • Fixed/variable length records
  • BLOBs
  • Arranging files on disk
  • Catalogs
slide-15
SLIDE 15

15 340151 Big Data & Cloud Computing (P. Baumann)

Files of Records

  • Page or block is OK when doing I/O
  • but higher levels of DBMS operate on records, and files of records
  • File = A collection of pages, each containing a collection of records.

Must support:

  • insert/delete/modify record
  • read a particular record (specified using record id)
  • scan all records (possibly with some conditions on the records to be retrieved)
slide-16
SLIDE 16

16 340151 Big Data & Cloud Computing (P. Baumann)

Unordered (Heap) Files

  • Simplest file structure -- contains records in no particular order
  • As file grows and shrinks, disk pages are allocated & de-allocated
  • To support record level operations, we must:
  • keep track of the pages in a file
  • keep track of free space on pages
  • keep track of the records on a page
  • many alternatives for keeping track of this
slide-17
SLIDE 17

17 340151 Big Data & Cloud Computing (P. Baumann)

Heap File Implemented as a List

  • header page id & Heap file name must be stored someplace
  • Each page contains 2 `pointers’ plus data

Header Page Data Page Data Page Data Page Data Page Data Page Data Page Pages with Free Space Full Pages

… …

slide-18
SLIDE 18

18 340151 Big Data & Cloud Computing (P. Baumann)

Heap File Using a Page Directory

  • directory = collection of pages
  • linked list implementation just one alternative
  • Much smaller than linked list of all HF pages!
  • entry for page can include number of free bytes on page

Data Page 1 Data Page 2 Data Page N Header Page

DIRECTORY

slide-19
SLIDE 19

19 340151 Big Data & Cloud Computing (P. Baumann)

...Now Databases, Again

  • Buffering
  • Fixed/variable length records
  • Arranging files on disk
  • Catalogs
slide-20
SLIDE 20

20 340151 Big Data & Cloud Computing (P. Baumann)

  • For each relation:
  • name, file name, file structure (e.g., Heap file)
  • attribute name and type, for each attribute
  • index name, for each index
  • integrity constraints
  • For each index:
  • structure (e.g., B+ tree) and search key fields
  • For each view:
  • view name and definition
  • Plus statistics, authorization, buffer pool size, etc.

System Catalogs

Catalogs themselves stored as relations!

slide-21
SLIDE 21

21 340151 Big Data & Cloud Computing (P. Baumann)

Sample Catalog Table

attr_name rel_name type position attr_name Attribute_Cat string 1 rel_name Attribute_Cat string 2 type Attribute_Cat string 3 position Attribute_Cat integer 4 sid Students string 1 name Students string 2 login Students string 3 age Students integer 4 gpa Students real 5 fid Faculty string 1 fname Faculty string 2 sal Faculty real 3

Attribute_Cat(attr_name, rel_name, type, position) 1st entry? Key(s)?

slide-22
SLIDE 22

22 340151 Big Data & Cloud Computing (P. Baumann)

Databases & Disk: Practitioner's Tips

  • File system type: ext4, or similar; not reiserfs!
  • Many discussions, though
  • Place redo logs (and Oracle control files) on separate partitions
  • If possible, same for index (higher traffic!)
  • fast disks for /tmp, cache files, log, and other high-traffic dirs
  • Big RAM never wrong

SSD

slide-23
SLIDE 23

23 340151 Big Data & Cloud Computing (P. Baumann)

Summary

  • Disks provide cheap, non-volatile storage
  • Random access, but cost depends on location of page on disk
  • important to arrange data sequentiallyto 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
slide-24
SLIDE 24

24 340151 Big Data & Cloud Computing (P. Baumann)

Summary (Contd.)

  • DBMS vs. OS File Support: DBMS needs features not found in many OS’s
  • forcing page to disk
  • controlling order of page writes to disk
  • files spanning disks
  • control pre-fetching + page replacement policy based on predictable access patterns,

etc.

  • Variable length record format
  • field offset directory
  • Slotted page format
slide-25
SLIDE 25

25 340151 Big Data & Cloud Computing (P. Baumann)

Summary (Contd.)

  • File layer
  • keeps track of pages in file + supports abstraction of "collection of records"
  • Pages with free space identified via linked list or directory structure
  • similar to how pages in file are kept track of
  • Indexes
  • support efficient retrieval of records based on the values in some fields
  • Catalog relations
  • store information about relations, indexes and views
  • Information that is common to all records in a given collection