Relational Algebra and Buffer Management

1
Relational Algebra and Buffer Management!
2
Banking Example

• branch (branch-name, branch-city, assets)
• customer (customer-name, customer-street,
  customer-only)
• account (account-number, branch-name, balance)
• loan (loan-number, branch-name, amount)
• depositor (customer-name, account-number)
• borrower (customer-name, loan-number)

3
Example Queries

• Find all loans of over 1200
• ?amount gt1200 (loan)
• Find the loan number for each loan of an amount
  greater than 1200
• ploan-number (?amount gt
  1200 (loan))

• loan (loan-number, branch-name, amount)

4
Example Queries

• Find the names of all customers who have a loan,
  an account, or both, from the bank
• pcustomer-name (borrower) ? pcustomer-name
  (depositor)
• Find the names of all customers who have a loan
  and an account at bank.
• pcustomer-name (borrower) ? pcustomer-name
  (depositor)

depositor (customer-name, account-number) borrower
(customer-name, loan-number)
5
Example Queries

• Find the names of all customers who have a loan
  at the Perryridge branch but do not have an
  account at any branch of the bank.
• pcustomer-name (?branch-name Perryridge
  (borrower loan))

• pcustomer-name (depositor)

• loan (loan-number, branch-name, amount)

depositor (customer-name, account-number) borrower
(customer-name, loan-number)
6
Example Queries

• Find the largest account balance
• Rename account relation as d
• The query is

• p balance(account) -

paccount.balance(

• ?account.balance lt d.balance (account x rd
  (account)))

account (account-number, branch-name, balance)
7
Example Queries

• Find all customers who have an account from at
  least the Downtown and the Uptown branches.
• Query 1
• pCN(?BNDowntown(depositor account)) ?
• pCN(?BNUptown(depositor account))
• where CN denotes customer-name and BN denotes
  branch-name.
• Query 2
• ?customer-name, branch-name (depositor
  account) ? ?temp(branch-name) ((Downtown),
  (Uptown))

8
Example Queries

• Find all customers who have an account at all
  branches located in Boston. pcustomer-name,
  branch-name (depositor account) ?
  pbranch-name (?branch-city Boston (branch))

9
Buffer manager- Context
10
Buffer Management in a DBMS
Page Requests from Higher Levels
BUFFER POOL
disk page
free frame
MAIN MEMORY
DISK
choice of frame dictated by replacement policy

• Data must be in RAM for DBMS to operate on it!
• Buffer Mgr hides the fact that not all data is in
  RAM

11
When a Page is Requested ...

• Buffer pool information table contains
  ltframe,
  pageid, pin_count, dirtygt
• If requested page is not in pool and no free
  frame
• Choose a frame for replacement.Only un-pinned
  pages are candidates!
• 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!

12
More on Buffer Management

• Requestor of page must eventually 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.
• To pin a page, pin_count
• A page is a candidate for replacement iff pin
  count 0 (unpinned)
• CC recovery may entail additional I/O when a
  frame is chosen for replacement.
• Write-Ahead Log protocol more later!

13
Buffer Replacement Policy

• Frame is chosen for replacement by a replacement
  policy
• Least-recently-used (LRU), MRU, Clock, etc.
• Policy can have big impact on of I/Os depends
  on the access pattern.

Why MRU? Consider the following access pattern
(page ids) 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,1
,2,3,4,5,6,7,8,.
Assume a buffer of 10 pages After the first 10
pages, MRU will replace 10 with 11, 11 with 12,
12 with 13, 13 with 14, 14 with 15. Next 9 pages
will be found in and we need no disk reads for
them. Compare this approach against LRU.
14
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 lt pages in file means each page
  request causes an I/O.

15
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

Questions How like LRU? Problems?
16
DBMS vs. OS File System

• OS does disk space buffer mgmt why not let
  OS manage these tasks?
• Some limitations, e.g., files cant span disks.
• Buffer management in DBMS requires ability to
• pin a page in buffer pool, force a page to disk
  order writes (important for implementing CC
  recovery)
• adjust replacement policy, and pre-fetch pages
  based on access patterns in typical DB operations.