Storage, Data Organization, and Buffering Walid G. Aref Memory - - PowerPoint PPT Presentation

Storage, Data Organization, and Buffering

Walid G. Aref

Memory Hierarchy

Walid G. Aref

CPU Registers Cache Memory Main Memory Disk Storage Network Storage Solid-State Disks Non- Volatile Volatile Higher Capacity Higher Price Higher Speed Block-oriented access Byte-oriented access Archival Storage (e.g., tapes)

Cache Memory

• L1 Cache (Level 1 Cache):
• Memory built inside the microprocessor chip
• Stores the CPU's recently accessed data
• Also, termed system cache, primary cache, internal cache
• Fastest (≈ 100 𝑢𝑗𝑛𝑓𝑡 𝑔𝑏𝑡𝑢𝑓𝑠 𝑢ℎ𝑏𝑜 𝑛𝑏𝑗𝑜 𝑛𝑓𝑛𝑝𝑠𝑧)
• For a multi-core CPU, every core will have a separate L1 case
• Not shared with the other cores
• Relatively small in size (~ 32K/core)
• L2 Cache (Level 2 Cache):
• For a multi-core CPU, L2 Cache Is in-chip memory that is shared among the multiple cores
• Larger in capacity than L1 cache
• ~4 MB per 4 cores (Intel Anon)
• 𝑇𝑢𝑗𝑚𝑚 𝐺𝑏𝑡𝑢𝑓𝑠 𝑢ℎ𝑏𝑜 𝑛𝑏𝑗𝑜 𝑛𝑓𝑛𝑝𝑠𝑧 (≈ 25 𝑢𝑗𝑛𝑓𝑡)
• L3 Cache (Level 3 Cache) or Level 4:
• Memory outside of the microprocessor chip in a separate chip
• 𝑇𝑚𝑝𝑥𝑓𝑠 𝑢ℎ𝑏𝑜 𝑀1 𝑏𝑜𝑒 𝑀2 𝑑𝑏𝑑ℎ𝑓𝑡
• Larger in capacity than L1 and L2 cache

Walid G. Aref

Disk-based Storage

• The focus of this course
• Assumption:
• Data cannot entirely fit in main memory
• Use memory as buffers
• Access: Block-based
• Disk Access:
• Seek time (~10 ms):
• Time to move the heads IN/Out
• Rotational Delay (~10 ms):
• Time to rotate around the spindle

(measured in RPM)

• Transmission delay (~1ms):
• Time to transfer a block of data

Walid G. Aref

Processor Main Memory Buffers Disk Storage Seek Time Rotational Delay Data Transfer Time

Disk Interface

• Each disk page has a disk block id
• Block Read(disk block id):
• Transmit the corresponding disk page from

the disk to the memory buffers

• Write(Block, disk block id):
• Transmit the data Block from the memory

buffers into the disk page corresponding to the given disk block id

Walid G. Aref

Data Retrieval From Disk

• Each disk page has a Disk Block identifier
• Disk Block Identifier gets mapped into:
• (Cylinder (or Track) Number, Platter Number,

Sector Number)

• Time needed is dependent on:
• The current location of the disk head, and
• The location of the requested page.
• Involves mechanical movement, and hence is

relatively slow

• How to retrieve the page?

1. Seek:

• Head moves to the corresponding cylinder number

(uses an elevator algorithm)

2. Rotation:

• Wait until head is on top of the correct Sector

Number

3. Transmission:

• Once head is at the right cylinder, right sector, direct

and give the signal to the head responsible for the block’s Platter Number to start reading the block and transmit the bits to the disk driver buffers

• Same is true for writing a page to disk

Walid G. Aref

• 1. Seek to

Cylinder Number

• 2. Sector Number
• 3. Data

Transfer Time

• 3. Platter Number

Track

How to Place Multiple Pages on Disk?

• Data Placement Problem
• Given a table with multiple data blocks (d1, d2, d3, …)
• How to place the data blocks on disk to minimize file read time?
• Assume we have p platters, s sectors per track
• Store d1 in Cylinder c1, Platter p1, Sector s1
• Where to store d2
• d2à c1, p2, s1 (Seek Time = 0, Rotational Delay = 0)
• d3à c1, p3, s1 (Seek Time = 0, Rotational Delay = 0)
• …
• dpà c1, pp, s1 (Seek Time = 0, Rotational Delay = 0)
• dp+1à c1, p1, s2 (Seek Time = 0, Rotational Delay = 1 sector)
• dp+2à c1, p2, s2 (Seek Time = 0, Rotational Delay = 0)
• ...
• dpsà c1, pp, ss (Seek Time = 0, Rotational Delay = 0)
• dps+1à c2, pp, ss (Seek Time = 1, Rotational Delay = 0)
• etc.

Walid G. Aref

• 1. Cylinder

Number

• 3. Data

Transfer Time

• 2. Sector Number
• 3. Platter Number

Track

Data Placement and Data Prefetching

• When Page 1 of the table is requested, Pre-retrieve Pages 2, 3, and 4 before

being requested (Prefetching)

• May result in saving time
• Then, when actually requesting Page 2, will find it in the memory buffers.
• Can also be wasted effort if Page 2 is not requested
• But will benefit from good data placement, and hence retrieval cost of Page 2 is small
• What else can make Data Placement work wasted?
• Multi-tenant databases or concurrent transactions accessing the disk at the same

time and each requesting to retrieve a different file at the same time.

• Proper and careful placement of data of one file will not help in this case.
• The reason is that requests for pages from multiple files will be interleaved.

Walid G. Aref

How to Access Pages of a Table?

• Assume for now that no indexes are available.
• Given a table name, how to know the list of disk pages that comprise

the table?

• Directory: Table name à Head of linked list of pages
• Each page has forward and backward pointers (disk page ids)
• Sequential access

Walid G. Aref

Head Page 1 Page 2 Page n

• To reduce the sequential access overhead for large tables, maintain a

two-level hierarchy (directory of pages)

• Directory Pages à
• Data Pages

à

Walid G. Aref

How to Access Pages of a Table? (2)

Page 1 Page 2 Page 3 1 2 3 4 5 n Page n

How to Store Tuples Inside a Disk Page?

• Tuples of a table can be of two types:
• Tuples in the table are all of the same length --> Fixed-length records
• Each tuple in the table can be of a different length than the other tuples

à Variable-length Records

Walid G. Aref

Tuple Identifiers: How to Refer to a Tuple in a Table?

• Using a Tuple Identifier or a Record Identifier (TID or RID, for short)
• Tuple Identifier: Used to physically locate a tuple
• What composes a TID?
• Disk page id of the page that contains the tuple
• The index (or offset) of the tuple inside the page (more details to follow)
• Where can a TID be used?
• In the leaf level of an index, e.g., B+-tree, to point to the tuple (key-value, TID) pairs
• In a foreign-key/primary-key relationship, the foreign key side can contain the TID of the

primary key side

• An alternative to storing the key value
• Helps avoid performing a join
• Important property of a TID:
• TID uniquely identifies a tuple
• Potentially should not change if the tuple changes its location
• Or at least, do not change it unless we have to
• Walid G. Aref

How to Store Tuples Inside a Disk Page?

• Tuples of a table can be of two types:
• Tuples in the table are all of the same length --> Fixed-length records
• Each tuple in the table can be of a different length than the other tuples

à Variable-length Records

• For each type, need to state what the TID is in each case
• Demonstrate how to enforce the TID properties

Walid G. Aref

Disk Page Layout Containing Fixed-length Tuples

• Attempt 1:
• Will need to keep track of the locations of

the empty slots

• TID: (page id, slot id)
• Can we recompact after tuple deletion?
• Not a good idea as TIDs will keep changing

Walid G. Aref

T3 T2 T1 Free Space Free Space Free Space TUPLE TUPLE

• Alternative 2:
• Will need to keep track of the locations of

the empty slots

• Search for empty slot and insert new

tuples

• Tuples guaranteed to fit
• TID: (page id, slot id) does not change

T3 T2 T1 Free Space Free Space TUPLE TUPLE 1 1

• Prev. Pg.
• Prev. Pg.

Next Pg. 1 Next Pg. 1 0 1 T3 T2 T1 Free Space Free Space TUPLE TUPLE Head of Free Space

• Prev. Pg.

Next Pg. Linked List

• f

Free Space

• Alternative 3:
• Keep track of the locations of the empty slots
• Add a linked list of free space tuples in the page
• Know slot-id from previous pointer
• Or can compute the offset given the fixed length

tuples

Disk Page Layout Containing Fixed-length Tuples: Comments

• Each page can have pointers to the previous and next pages (based on

chosen directory structure)

• Need to search for next empty slot (linear search for bit array)
• Alternative 3 is better but consumes more space
• How to know that page is full?
• Add field: Number of tuples in page
• May need other meta data per page, e.g., for logging and concurrency

control purposes

Walid G. Aref

Disk Page Layout Containing Variable-length Tuples

Walid G. Aref

• Tuple Identifier (page id, slot id)
• Slot-id: An index to an in-page directory entry that

contains offset of the tuple from the beginning of page

• If Tuple moves inside the page, slot-id does not change

but the offset value in the entry changes

• Records can be moved around within a page to keep

them contiguous with no empty space between them; entry in the header must be updated (Expensive – Should be avoided)

• In-page directory entries (Expandable array of size Num.
• f Slots)
• (Free Space bit, size, offset to tuple)
• 0 means free space
• 1 means busy
• Prev. Pg. Ptr.

Next Pg. Ptr. Free Space In-page Directory Free Space Ptr. Free Space Size

• No. of

Slots

Disk Page Layout Containing Variable-length Tuples – Issues

Walid G. Aref

• How do you know if there is space in this page to insert a

new tuple? Use Free Space Pointer and Free Space Size

• Need to update Free Space Size with every

insert/update

• And how do we know where to insert a new tuple?
• What happens when a tuple gets deleted?
• What happens when a tuple is updated and its length

increases?

• It will not fit into its current space
• Will need to be relocated inside the page.
• What if page does not have enough free space?
• How to handle the case when multiple tuples in the page

get deleted?

• Given multiple pages in a table, each having some free

space, and given a new tuple to be inserted

• How to figure out where to store the new tuple?
• We want to avoid sequentially checking the pages
• Prev. Pg. Ptr.

Next Pg. Ptr. Free Space In-page Directory Free Space Ptr. Free Space Size

• No. of

Slots

How to Manage Free Space in a Table?

• Heap table:
• Maintain two doubly-linked lists of pages in the table
• Linked list of pages without free space
• Linked list of pages with some free space
• If inserting a tuple of size N, search the linked list of pages with free space for

first page that contains space that can accommodate this tuple

• What happens if

page has more than one deleted tuple? Maintain priority queue inside each page?

Walid G. Aref

Head Page 1 Page 2 Page n Page F1 Page F2 Page Fm list of pages with some free space list of pages with no free space

How to Manage Free Space in a Table?

• In each directory entry, store

the size of the free space in the corresponding page

• This works for the

contiguous free space

• How to handle the free

space resulting from deletions of tuples?

• Introduce gaps in the middle.
• Continuous re-compaction?
• Expensive
• Can we maintain directory-

level priority queue for all the free space in all the individual pages?

Walid G. Aref

Page 1 Page 2 Page 3 1 2 3 4 5 n Page n Directory Pages Data Pages

How to Store Attribute Values Inside a Tuple? Tuples with Fixed-length Attributes

• Schema has all attribute types that are of fixed length
• Avoid scanning the tuple to access an attribute
• Access an attribute directly
• Pre-calculate the start address (offset) of each attribute relative to the begin of each

tuple

• Offset of Attribute i from Begin of Tuple = Offset[i]= ∑!"#

$%# 𝑀𝑓𝑜𝑕𝑢ℎ(𝐵𝑢𝑢𝑠𝑗𝑐𝑣𝑢𝑓 𝑘)

• Store all Offset[i] in System Catalog
• Do not have to recalculate it each time
• Tuple-Length = ∑!"#

&'(%)**+, 𝑀𝑓𝑜𝑕𝑢ℎ(𝐵𝑢𝑢𝑠𝑗𝑐𝑣𝑢𝑓 𝑘)

• Tuple length is also stored in the System Catalog
• For a given tuple with TID = (page-id, slot-id)
• To access Attribute i in this tuple
• Retrieve the page, given page-id
• Base = Tuple Base Address(slot-id) = Page-Header-Size + (slot-id-1)*Tuple-Length
• Attribute address = Base + Offset[i]

Walid G. Aref

• Attr. 1: Float
• Attr. 2: Int
• Attr. 3:

Boolean

• Attr. 4: Date
• Attr. 5: Fixed

Length String T3 T2 T1 Free Space Free Space TUPLE TUPLE 1 1

• Prev. Pg.

1 Next Pg. 1 0 1

How to Store Attribute Values Inside a Tuple? Tuples with Variable-length Attributes

• Schema has attribute types where

some attributes are of variable lengths

• Need to avoid scanning the tuple to

access an attribute

• Avoid end-of-attribute markers
• Need direct access to the attributes

Steve Bucks\0 Delphi\0 Tippecanoe County\0 “Null”\0 47905

• Prev. Pg. Ptr.

Next Pg. Ptr. Free Space In-page Directory Free Space Ptr. Free Space Size

• No. of

Slots

Walid G. Aref

How to Store Attribute Values Inside a Tuple? Tuples with Variable-length Attributes

• Add header for each tuple
• An offset array
• One entry per attribute
• Entry stores offset to begin of attribute
• Add one entry to designate end of tuple
• Length of Attribute i =
• Length(i) = Offset[i+1) – Offset[i]

= 0 à Attribute has null value (e.g., Attr. 4 below)

• For a given tuple with TID = (page-id, slot-id)
• To access Attribute i in this tuple
• Retrieve the page, given page-id
• Base = in-Page-Directory[slot-id][2]
• Attribute address = Base + Offset[i]
• Prev. Pg. Ptr.

Next Pg. Ptr. Free Space In-page Directory Free Space Ptr. Free Space Size

• No. of

Slots Steve Bucks Delphi Tippecanoe County 47905

Walid G. Aref

Header: Offset Array Notice there is no value for Attribute 4 (has Null value)

Ordering the Tuples within a Table

• Heap table order:
• Order is based on insertion time
• Tuples are appended at the end of the table
• Sorted table order:
• Order of the tuples is based on some key value
• Why is sorted order of the tuples in a table important?
• It facilitates search over the table
• It is needed to support clustered indexes (more to come about this later)

Walid G. Aref

How to Support Sorted Tables?

• Tuples are physically sorted in the disk pages
• If we need to insert a tuple in between two

tuples

• Will result in shifting the other tuples
• Hence, the slot-ids of the existing tuples will

need to change

• How to avoid this problem?
• May need to add one more level of indirection to

maintain the sorted order

• Store the tuples in any slots within the page
• Maintain a linked list of the sorted order

Walid G. Aref

T3 T2 T1 Free Space Free Space TUPLE TUPLE Head of Free Space

• Prev. Pg.

Next Pg. Linked List

• f

Free Space Sorted

• rder

Head of Sorted Table

How to Support Sorted Tables?

• What happens when we need to insert a tuple

in sorted order into a page and the page is entirely full?

• May need to use overflow page
• Keep pointer of overflow page in the original page
• Avoid changing TIDs pointing to the original table
• Sort pointer has to include (page-id, offset) to

accommodate for overflow page

• Alternatively, to support sorted tables
• Use Index-based tables

Walid G. Aref

T3 T2 T1 Free Space Free Space TUPLE TUPLE Head of Free Space

• Prev. Pg.

Next Pg. Linked List

• f

Free Space Head of Sorted Table T1.2 T1.1 Overflow page

Index-based Tables

• The entire table is stored inside an index
• Common: Hash and B-tree tables
• Hash-based table: Hash function takes the key of the tuple as input

and produces the bucket (disk page) that contains the tuple

• B-tree-based table: Search key of the b-tree is the key for the table.
• Need log time to access a tuple
• No need for tuple-identifiers (tid). Tuples are referenced by their key values
• Thus, not important if the tuples change location within a disk page and even

across pages, e.g., during b-tree page splits

• More about this topic later

Walid G. Aref

Storing Multiple Tables Together

• Sometimes termed “Clustered Tables”
• Combine tuples from multiple tuples into the same disk pages
• For example:
• Store in the same disk pages the tuples about registrations in the course (cid,

sid, grade) along with the tuple about the course info itself (cid, ctitle, credits, etc.)

• Can reduce the cost of a join operation between two tables
• Have to be variable-sized record format (to at least accommodate the

different tuple sizes of the two tables)

Walid G. Aref

System Catalogs

• System catalogs stores meta data about the tables in the database
• System catalogs are tables (relations) themselves
• For every table, we want to keep track of:
• Directory for disk-pages composing this table (Location of the table)
• How the table is stored (index table, sorted table, clustered with another table, etc.)
• Schema of this table
• Attributes and their types and sizes
• Fixed- or variable-length tuples
• Each attribute’s order in the tuple (1st , 2nd , 3rd, etc.)
• Each attribute’s offset from begin of tuple (in case of fixed-length records)
• Whether attribute is indexed or not, and type of index, e.g., B+-tree, etc.
• Which attributes are the primary keys, unique keys, foreign keys, etc.
• Whether attribute can take null values or not
• ….
• Integrity constraints

Walid G. Aref

System Catalogs (2)

• For indexes:
• Index type, on which relation, on which attribute, etc.
• For table views:
• View name, view definition, materialized or not, and if materialized, which

table represents this view, its directory entry, etc.

• System Catalogs tables themselves are stored in the System Catalogs

(System Catalog is self describing)

• For Users:
• User name, password (encrypted), privileges (Roles or rights to access

which tables)

Walid G. Aref

System Catalogs: Example Catalog

• Attributes_Catalog_Table(

relation_name, // Name of a table attribute_name, // Name of an attribute in the table attribute_type, // Type of this attribute attribute_order_in_table // Order of this attribute in the tuple, 1st , 2nd , 3rd, etc. )

Walid G. Aref

Attributes_Catalog_Table

relation_name attribute_name attribute_type attribute_order_in_table Attributes_Catalog_Table relation_name string 1 Attributes_Catalog_Table attribute_name string 2 Attributes_Catalog_Table attribute_type string 3 Attributes_Catalog_Table attribute_order_in_table integer 4 instructors iid integer 1 instructors iname string 2 instructors rank string 3

Walid G. Aref

Buffer Management in a DBMS (The Buffer Manager)

• Why is buffer management in the DBMS different from the OS’s buffer

management?

• In a DBMS, the access pattern for disk pages is highly predictive.
• Accesses are mostly through the Relational Algebra Operators (select,

join, project)

• For example, in a nested-loops join operator, the access pattern and the order
• f page retrievals are known in advance.
• Thus, we can do a better job as we know mostly which pages will be

accessed next.

• So, we can make more informed decisions about which pages to keep

in the buffers and which pages that can be removed.

Walid G. Aref

Buffer Management in a DBMS (The Buffer Manager)

• Data must be loaded from the disk to

memory for queries to operate on it.

• The memory buffer pool is where

these pages are stored.

• The target is to reduce the amount of

data that gets transferred between disk and memory (Buffer Manager)

• By increasing “Buffer Hits” and reducing

”Buffer Misses”

• Try to keep the needed pages as much

as possible in memory

• Maintain a mapping table to map disk

pages to buffer pages

Walid G. Aref

Disk Empty buffer pages Buffer pages filled with disk pages Disk Page Buffer Frame Number p1 b7 p12 b3 p4 b0 Buffer Mapping Table Buffer pool

Other Buffer Data Structures

• For every page, need the following:
• Dirty Bit: Indicates whether this page has

been updated in the buffer pool or not since when the page has been first read from disk

• Clean Page: The copy of the page in the

buffer is a copy of that on disk

• Dirty Page: The copy of the page in the

buffer is different from its corresponding page on disk

• Pin Count: Indicates the number of

transactions that requested the page to be pinned in the buffer (i.e., is not allowed to be preempted out of the buffer). If pin count = 0, then page is candidate for preemption if buffer gets full

Walid G. Aref

Disk Empty buffer pages Buffer pages filled with disk pages Disk Page Buffer Frame Number p1 b7 p12 b3 p4 b0 Buffer Mapping Table Buffer pool Dirty Pages

Functions of the Buffer Manager

• Check if a disk page is in the buffer or not
• When a page is requested, check if it is in the buffer or not
• Via a hash table on page-id (the Mapping table)
• If found, return the buffer
• Provide an empty buffer page to host a retrieved disk page
• When a disk page is retrieved, we want to find an empty buffer page slot to store this disk page
• Need to maintain a list of the free buffer frames
• Pin a buffered page
• Indicate that this page is not allowed to be removed out of the buffer
• In this case, the pin count will be incremented
• Unpin a buffered page
• Opposite of Pin
• Choose a suitable page to remove from the buffer
• Is needed when there are no empty buffer frames to host a retrieved page
• Multiple policies to decide on which page to get out of the buffer

Walid G. Aref

Handling a Request for Disk Page p

1. Use the Mapping Table to check if p is in the buffers 2. If yes (A Buffer Hit), then return the Frame id of the frame in the Buffer Pool that contains the requested page 3. Else, need to retrieve p from the disk 4. Find an empty buffer frame, say b, to store in it the retrieved page 5. If found, store p into b and insert the entry (p,b) into the Mapping Table for future use 6. If the buffer is entirely full, we need to find a disk page in the buffer to replace by p 7. Need to adopt some replacement policy to decide which page to preempt from the buffer pool

Walid G. Aref

Disk Empty buffer pages Buffer pages filled with disk pages Disk Page Buffer Frame Number p1 b7 p12 b3 p4 b0 Buffer Mapping Table Buffer pool

Buffer Replacement

• Assume that the page to exit the buffer as

decided by the buffer replacement policy is Page q in buffer frame c

• If q is not dirty (i.e., has not been modified

since being read from disk), then

• Discard q
• Free the buffer page that q occupies
• Load p from disk into c
• Else /* q is dirty */
• Store q to disk
• Free the buffer page that q occupies
• Load p from disk into c
• Reset c’s dirty bit
• Reset c’s pin counter to 0 /* not pinned */

Walid G. Aref

Disk Buffer pages filled with disk pages Disk Page Buffer Frame Number p1 b7 p12 b3 p4 b0 Buffer Mapping Table Buffer pool No empty buffer pages

Buffer Replacement Policies

• Given access patterns of Relational Algebra operations
• The page that is likely not to be visited soon is the page that

was just being processed

• E.g., in Table scan, in Joins, in selects, in projects.
• Makes the Most Recently Used (MRU) buffer eviction policy

the most attractive.

• Thus, the least recently used page is the most likely to be used

next.

• Clear from the join pattern, e.g., consider the inner table of the join
• In contrast to the OS’s Least Recently Used (LRU)

Walid G. Aref

LRU page Current page (MRU) Outer Table Inner Table