![Page 1: 05 Buffer Pools · 2020. 9. 16. · exact copy is placed into one of these frames. 8 Buffer Pool frame1 frame2 frame3 frame4 page1 On-Disk File page1 page2 page3 page4. 15-445/645](https://reader034.vdocuments.us/reader034/viewer/2022051408/5ffd1e73c4092502e8480d22/html5/thumbnails/1.jpg)
Intro to Database Systems
15-445/15-645
Fall 2020
Andy PavloComputer Science Carnegie Mellon UniversityAP
05 Buffer Pools
![Page 2: 05 Buffer Pools · 2020. 9. 16. · exact copy is placed into one of these frames. 8 Buffer Pool frame1 frame2 frame3 frame4 page1 On-Disk File page1 page2 page3 page4. 15-445/645](https://reader034.vdocuments.us/reader034/viewer/2022051408/5ffd1e73c4092502e8480d22/html5/thumbnails/2.jpg)
15-445/645 (Fall 2020)
ADMINISTRIV IA
Project #1 is due Sunday Sept 27th
Q&A Session about the project on Monday Sept 21st @ 8:00pm ET.→ In-Person: GHC 4401→ https://cmu.zoom.us/j/98100285498?pwd=a011L0E2eW
FwTndKMG9KNVhzb2tDdz09
2
![Page 3: 05 Buffer Pools · 2020. 9. 16. · exact copy is placed into one of these frames. 8 Buffer Pool frame1 frame2 frame3 frame4 page1 On-Disk File page1 page2 page3 page4. 15-445/645](https://reader034.vdocuments.us/reader034/viewer/2022051408/5ffd1e73c4092502e8480d22/html5/thumbnails/3.jpg)
15-445/645 (Fall 2020)
OFFICE HOURS
New Times:→ Ian: TBA→ Ricky Z: Mondays @ 8:00pm ET
Saturday Office Hours @ 5:00pm ET→ At least three TAs will be available during this time.→ https://cmu.zoom.us/j/96219031967?pwd=NjI0SlZaeklr
UnhtUkt5ai9SelJIUT09
Andy "After Dark" Office Hours→ Mon/Wed @ 10:00pm ET→ https://calendly.com/andy-pavlo/f20-andy-after-dark
3
![Page 4: 05 Buffer Pools · 2020. 9. 16. · exact copy is placed into one of these frames. 8 Buffer Pool frame1 frame2 frame3 frame4 page1 On-Disk File page1 page2 page3 page4. 15-445/645](https://reader034.vdocuments.us/reader034/viewer/2022051408/5ffd1e73c4092502e8480d22/html5/thumbnails/4.jpg)
15-445/645 (Fall 2020)
DATABASE STORAGE
Problem #1: How the DBMS represents the database in files on disk.
Problem #2: How the DBMS manages its memory and move data back-and-forth from disk.
4
![Page 5: 05 Buffer Pools · 2020. 9. 16. · exact copy is placed into one of these frames. 8 Buffer Pool frame1 frame2 frame3 frame4 page1 On-Disk File page1 page2 page3 page4. 15-445/645](https://reader034.vdocuments.us/reader034/viewer/2022051408/5ffd1e73c4092502e8480d22/html5/thumbnails/5.jpg)
15-445/645 (Fall 2020)
DATABASE STORAGE
Spatial Control:→ Where to write pages on disk.→ The goal is to keep pages that are used together often as
physically close together as possible on disk.
Temporal Control:→ When to read pages into memory, and when to write
them to disk.→ The goal is minimize the number of stalls from having to
read data from disk.
5
![Page 6: 05 Buffer Pools · 2020. 9. 16. · exact copy is placed into one of these frames. 8 Buffer Pool frame1 frame2 frame3 frame4 page1 On-Disk File page1 page2 page3 page4. 15-445/645](https://reader034.vdocuments.us/reader034/viewer/2022051408/5ffd1e73c4092502e8480d22/html5/thumbnails/6.jpg)
15-445/645 (Fall 2020)
DISK-ORIENTED DBMS
6
Disk
Memory
Da
tab
ase
File
1HeaderDirectory
2Header
3Header
… Pages
Bu
ffe
r P
oo
l
2Header
4Header
5Header
ExecutionEngine
Get page #2
Directory
Pointer to page #2
![Page 7: 05 Buffer Pools · 2020. 9. 16. · exact copy is placed into one of these frames. 8 Buffer Pool frame1 frame2 frame3 frame4 page1 On-Disk File page1 page2 page3 page4. 15-445/645](https://reader034.vdocuments.us/reader034/viewer/2022051408/5ffd1e73c4092502e8480d22/html5/thumbnails/7.jpg)
15-445/645 (Fall 2020)
TODAY'S AGENDA
Buffer Pool Manager
Replacement Policies
Other Memory Pools
7
![Page 8: 05 Buffer Pools · 2020. 9. 16. · exact copy is placed into one of these frames. 8 Buffer Pool frame1 frame2 frame3 frame4 page1 On-Disk File page1 page2 page3 page4. 15-445/645](https://reader034.vdocuments.us/reader034/viewer/2022051408/5ffd1e73c4092502e8480d22/html5/thumbnails/8.jpg)
15-445/645 (Fall 2020)
BUFFER POOL ORGANIZATION
Memory region organized as an array of fixed-size pages.An array entry is called a frame.
When the DBMS requests a page, an exact copy is placed into one of these frames.
8
BufferPool
frame1
frame2
frame3
frame4
On-Disk File
page1 page2 page3 page4
![Page 9: 05 Buffer Pools · 2020. 9. 16. · exact copy is placed into one of these frames. 8 Buffer Pool frame1 frame2 frame3 frame4 page1 On-Disk File page1 page2 page3 page4. 15-445/645](https://reader034.vdocuments.us/reader034/viewer/2022051408/5ffd1e73c4092502e8480d22/html5/thumbnails/9.jpg)
15-445/645 (Fall 2020)
BUFFER POOL ORGANIZATION
Memory region organized as an array of fixed-size pages.An array entry is called a frame.
When the DBMS requests a page, an exact copy is placed into one of these frames.
8
BufferPool
frame1
frame2
frame3
frame4
page1
On-Disk File
page1 page2 page3 page4
![Page 10: 05 Buffer Pools · 2020. 9. 16. · exact copy is placed into one of these frames. 8 Buffer Pool frame1 frame2 frame3 frame4 page1 On-Disk File page1 page2 page3 page4. 15-445/645](https://reader034.vdocuments.us/reader034/viewer/2022051408/5ffd1e73c4092502e8480d22/html5/thumbnails/10.jpg)
15-445/645 (Fall 2020)
BUFFER POOL ORGANIZATION
Memory region organized as an array of fixed-size pages.An array entry is called a frame.
When the DBMS requests a page, an exact copy is placed into one of these frames.
8
BufferPool
frame1
frame2
frame3
frame4
page1
page3
On-Disk File
page1 page2 page3 page4
![Page 11: 05 Buffer Pools · 2020. 9. 16. · exact copy is placed into one of these frames. 8 Buffer Pool frame1 frame2 frame3 frame4 page1 On-Disk File page1 page2 page3 page4. 15-445/645](https://reader034.vdocuments.us/reader034/viewer/2022051408/5ffd1e73c4092502e8480d22/html5/thumbnails/11.jpg)
15-445/645 (Fall 2020)
BUFFER POOL META-DATA
The page table keeps track of pages that are currently in memory.
Also maintains additional meta-data per page:→ Dirty Flag→ Pin/Reference Counter
9
page1 page2 page3 page4
On-Disk File
BufferPool
frame1
frame2
frame3
frame4
page1
page3
PageTable
page1
page3
![Page 12: 05 Buffer Pools · 2020. 9. 16. · exact copy is placed into one of these frames. 8 Buffer Pool frame1 frame2 frame3 frame4 page1 On-Disk File page1 page2 page3 page4. 15-445/645](https://reader034.vdocuments.us/reader034/viewer/2022051408/5ffd1e73c4092502e8480d22/html5/thumbnails/12.jpg)
15-445/645 (Fall 2020)
BUFFER POOL META-DATA
The page table keeps track of pages that are currently in memory.
Also maintains additional meta-data per page:→ Dirty Flag→ Pin/Reference Counter
9
page1 page2 page3 page4
On-Disk File
BufferPool
frame1
frame2
frame3
frame4
page1
page3
PageTable
page1
page3
![Page 13: 05 Buffer Pools · 2020. 9. 16. · exact copy is placed into one of these frames. 8 Buffer Pool frame1 frame2 frame3 frame4 page1 On-Disk File page1 page2 page3 page4. 15-445/645](https://reader034.vdocuments.us/reader034/viewer/2022051408/5ffd1e73c4092502e8480d22/html5/thumbnails/13.jpg)
15-445/645 (Fall 2020)
BUFFER POOL META-DATA
The page table keeps track of pages that are currently in memory.
Also maintains additional meta-data per page:→ Dirty Flag→ Pin/Reference Counter
9
page1 page2 page3 page4
On-Disk File
BufferPool
frame1
frame2
frame3
frame4
page1
page3
PageTable
page1
page3
page2
![Page 14: 05 Buffer Pools · 2020. 9. 16. · exact copy is placed into one of these frames. 8 Buffer Pool frame1 frame2 frame3 frame4 page1 On-Disk File page1 page2 page3 page4. 15-445/645](https://reader034.vdocuments.us/reader034/viewer/2022051408/5ffd1e73c4092502e8480d22/html5/thumbnails/14.jpg)
15-445/645 (Fall 2020)
BUFFER POOL META-DATA
The page table keeps track of pages that are currently in memory.
Also maintains additional meta-data per page:→ Dirty Flag→ Pin/Reference Counter
9
page1 page2 page3 page4
On-Disk File
BufferPool
frame1
frame2
frame3
frame4
page1
page3
PageTable
page1
page3
page2
page2
![Page 15: 05 Buffer Pools · 2020. 9. 16. · exact copy is placed into one of these frames. 8 Buffer Pool frame1 frame2 frame3 frame4 page1 On-Disk File page1 page2 page3 page4. 15-445/645](https://reader034.vdocuments.us/reader034/viewer/2022051408/5ffd1e73c4092502e8480d22/html5/thumbnails/15.jpg)
15-445/645 (Fall 2020)
LOCKS VS. L ATCHES
Locks:→ Protects the database's logical contents from other
transactions.→ Held for transaction duration.→ Need to be able to rollback changes.
Latches:→ Protects the critical sections of the DBMS's internal data
structure from other threads.→ Held for operation duration.→ Do not need to be able to rollback changes.
10
←Mutex
![Page 16: 05 Buffer Pools · 2020. 9. 16. · exact copy is placed into one of these frames. 8 Buffer Pool frame1 frame2 frame3 frame4 page1 On-Disk File page1 page2 page3 page4. 15-445/645](https://reader034.vdocuments.us/reader034/viewer/2022051408/5ffd1e73c4092502e8480d22/html5/thumbnails/16.jpg)
15-445/645 (Fall 2020)
PAGE TABLE VS. PAGE DIRECTORY
The page directory is the mapping from page ids to page locations in the database files.→ All changes must be recorded on disk to allow the DBMS
to find on restart.
The page table is the mapping from page ids to a copy of the page in buffer pool frames.→ This is an in-memory data structure that does not need to
be stored on disk.
11
![Page 17: 05 Buffer Pools · 2020. 9. 16. · exact copy is placed into one of these frames. 8 Buffer Pool frame1 frame2 frame3 frame4 page1 On-Disk File page1 page2 page3 page4. 15-445/645](https://reader034.vdocuments.us/reader034/viewer/2022051408/5ffd1e73c4092502e8480d22/html5/thumbnails/17.jpg)
15-445/645 (Fall 2020)
ALLOCATION POLICIES
Global Policies:→ Make decisions for all active txns.
Local Policies:→ Allocate frames to a specific txn without considering the
behavior of concurrent txns.→ Still need to support sharing pages.
12
![Page 18: 05 Buffer Pools · 2020. 9. 16. · exact copy is placed into one of these frames. 8 Buffer Pool frame1 frame2 frame3 frame4 page1 On-Disk File page1 page2 page3 page4. 15-445/645](https://reader034.vdocuments.us/reader034/viewer/2022051408/5ffd1e73c4092502e8480d22/html5/thumbnails/18.jpg)
15-445/645 (Fall 2020)
BUFFER POOL OPTIMIZATIONS
Multiple Buffer Pools
Pre-Fetching
Scan Sharing
Buffer Pool Bypass
13
![Page 19: 05 Buffer Pools · 2020. 9. 16. · exact copy is placed into one of these frames. 8 Buffer Pool frame1 frame2 frame3 frame4 page1 On-Disk File page1 page2 page3 page4. 15-445/645](https://reader034.vdocuments.us/reader034/viewer/2022051408/5ffd1e73c4092502e8480d22/html5/thumbnails/19.jpg)
15-445/645 (Fall 2020)
MULTIPLE BUFFER POOLS
The DBMS does not always have a single buffer pool for the entire system.→ Multiple buffer pool instances→ Per-database buffer pool→ Per-page type buffer pool
Helps reduce latch contention and improve locality.
14
![Page 20: 05 Buffer Pools · 2020. 9. 16. · exact copy is placed into one of these frames. 8 Buffer Pool frame1 frame2 frame3 frame4 page1 On-Disk File page1 page2 page3 page4. 15-445/645](https://reader034.vdocuments.us/reader034/viewer/2022051408/5ffd1e73c4092502e8480d22/html5/thumbnails/20.jpg)
15-445/645 (Fall 2020)
MULTIPLE BUFFER POOLS
Approach #1: Object Id→ Embed an object identifier in record ids
and then maintain a mapping from objects to specific buffer pools.
Approach #2: Hashing→ Hash the page id to select which
buffer pool to access.
15
Buffer Pool #1 Buffer Pool #2
![Page 21: 05 Buffer Pools · 2020. 9. 16. · exact copy is placed into one of these frames. 8 Buffer Pool frame1 frame2 frame3 frame4 page1 On-Disk File page1 page2 page3 page4. 15-445/645](https://reader034.vdocuments.us/reader034/viewer/2022051408/5ffd1e73c4092502e8480d22/html5/thumbnails/21.jpg)
15-445/645 (Fall 2020)
GET RECORD 123Q1
MULTIPLE BUFFER POOLS
Approach #1: Object Id→ Embed an object identifier in record ids
and then maintain a mapping from objects to specific buffer pools.
Approach #2: Hashing→ Hash the page id to select which
buffer pool to access.
15
Buffer Pool #1 Buffer Pool #2
<ObjectId, PageId, SlotNum>
![Page 22: 05 Buffer Pools · 2020. 9. 16. · exact copy is placed into one of these frames. 8 Buffer Pool frame1 frame2 frame3 frame4 page1 On-Disk File page1 page2 page3 page4. 15-445/645](https://reader034.vdocuments.us/reader034/viewer/2022051408/5ffd1e73c4092502e8480d22/html5/thumbnails/22.jpg)
15-445/645 (Fall 2020)
GET RECORD 123Q1
MULTIPLE BUFFER POOLS
Approach #1: Object Id→ Embed an object identifier in record ids
and then maintain a mapping from objects to specific buffer pools.
Approach #2: Hashing→ Hash the page id to select which
buffer pool to access.
15
Buffer Pool #1 Buffer Pool #2
<ObjectId, PageId, SlotNum>
![Page 23: 05 Buffer Pools · 2020. 9. 16. · exact copy is placed into one of these frames. 8 Buffer Pool frame1 frame2 frame3 frame4 page1 On-Disk File page1 page2 page3 page4. 15-445/645](https://reader034.vdocuments.us/reader034/viewer/2022051408/5ffd1e73c4092502e8480d22/html5/thumbnails/23.jpg)
15-445/645 (Fall 2020)
GET RECORD 123Q1
MULTIPLE BUFFER POOLS
Approach #1: Object Id→ Embed an object identifier in record ids
and then maintain a mapping from objects to specific buffer pools.
Approach #2: Hashing→ Hash the page id to select which
buffer pool to access.
15
Buffer Pool #1 Buffer Pool #2
HASH(123)%n
![Page 24: 05 Buffer Pools · 2020. 9. 16. · exact copy is placed into one of these frames. 8 Buffer Pool frame1 frame2 frame3 frame4 page1 On-Disk File page1 page2 page3 page4. 15-445/645](https://reader034.vdocuments.us/reader034/viewer/2022051408/5ffd1e73c4092502e8480d22/html5/thumbnails/24.jpg)
15-445/645 (Fall 2020)
PRE-FETCHING
The DBMS can also prefetch pages based on a query plan.→ Sequential Scans→ Index Scans
16
Buffer Pool
Disk Pages
page0
page1
page2
page3
page4
page5
Q1
![Page 25: 05 Buffer Pools · 2020. 9. 16. · exact copy is placed into one of these frames. 8 Buffer Pool frame1 frame2 frame3 frame4 page1 On-Disk File page1 page2 page3 page4. 15-445/645](https://reader034.vdocuments.us/reader034/viewer/2022051408/5ffd1e73c4092502e8480d22/html5/thumbnails/25.jpg)
15-445/645 (Fall 2020)
PRE-FETCHING
The DBMS can also prefetch pages based on a query plan.→ Sequential Scans→ Index Scans
16
Buffer Pool
page0
Disk Pages
page0
page1
page2
page3
page4
page5
Q1
![Page 26: 05 Buffer Pools · 2020. 9. 16. · exact copy is placed into one of these frames. 8 Buffer Pool frame1 frame2 frame3 frame4 page1 On-Disk File page1 page2 page3 page4. 15-445/645](https://reader034.vdocuments.us/reader034/viewer/2022051408/5ffd1e73c4092502e8480d22/html5/thumbnails/26.jpg)
15-445/645 (Fall 2020)
PRE-FETCHING
The DBMS can also prefetch pages based on a query plan.→ Sequential Scans→ Index Scans
16
Buffer Pool
page0
page1
Disk Pages
page0
page1
page2
page3
page4
page5
Q1
![Page 27: 05 Buffer Pools · 2020. 9. 16. · exact copy is placed into one of these frames. 8 Buffer Pool frame1 frame2 frame3 frame4 page1 On-Disk File page1 page2 page3 page4. 15-445/645](https://reader034.vdocuments.us/reader034/viewer/2022051408/5ffd1e73c4092502e8480d22/html5/thumbnails/27.jpg)
15-445/645 (Fall 2020)
PRE-FETCHING
The DBMS can also prefetch pages based on a query plan.→ Sequential Scans→ Index Scans
16
Buffer Pool
page0
page1
Disk Pages
page0
page1
page2
page3
page4
page5
Q1
![Page 28: 05 Buffer Pools · 2020. 9. 16. · exact copy is placed into one of these frames. 8 Buffer Pool frame1 frame2 frame3 frame4 page1 On-Disk File page1 page2 page3 page4. 15-445/645](https://reader034.vdocuments.us/reader034/viewer/2022051408/5ffd1e73c4092502e8480d22/html5/thumbnails/28.jpg)
15-445/645 (Fall 2020)
PRE-FETCHING
The DBMS can also prefetch pages based on a query plan.→ Sequential Scans→ Index Scans
16
Buffer Pool
page1
page2
Disk Pages
page0
page1
page2
page3
page4
page5
Q1
page3
![Page 29: 05 Buffer Pools · 2020. 9. 16. · exact copy is placed into one of these frames. 8 Buffer Pool frame1 frame2 frame3 frame4 page1 On-Disk File page1 page2 page3 page4. 15-445/645](https://reader034.vdocuments.us/reader034/viewer/2022051408/5ffd1e73c4092502e8480d22/html5/thumbnails/29.jpg)
15-445/645 (Fall 2020)
PRE-FETCHING
The DBMS can also prefetch pages based on a query plan.→ Sequential Scans→ Index Scans
16
Buffer Pool
page1
page2
Disk Pages
page0
page1
page2
page3
page4
page5
Q1
page3
![Page 30: 05 Buffer Pools · 2020. 9. 16. · exact copy is placed into one of these frames. 8 Buffer Pool frame1 frame2 frame3 frame4 page1 On-Disk File page1 page2 page3 page4. 15-445/645](https://reader034.vdocuments.us/reader034/viewer/2022051408/5ffd1e73c4092502e8480d22/html5/thumbnails/30.jpg)
15-445/645 (Fall 2020)
PRE-FETCHING
The DBMS can also prefetch pages based on a query plan.→ Sequential Scans→ Index Scans
16
Buffer Pool
Disk Pages
page0
page1
page2
page3
page4
page5Q1
page3
page4
page5
![Page 31: 05 Buffer Pools · 2020. 9. 16. · exact copy is placed into one of these frames. 8 Buffer Pool frame1 frame2 frame3 frame4 page1 On-Disk File page1 page2 page3 page4. 15-445/645](https://reader034.vdocuments.us/reader034/viewer/2022051408/5ffd1e73c4092502e8480d22/html5/thumbnails/31.jpg)
15-445/645 (Fall 2020)
PRE-FETCHING
17
Buffer Pool
Disk Pages
index-page0
index-page1
index-page2
index-page3
index-page4
index-page5
SELECT * FROM AWHERE val BETWEEN 100 AND 250
Q1
![Page 32: 05 Buffer Pools · 2020. 9. 16. · exact copy is placed into one of these frames. 8 Buffer Pool frame1 frame2 frame3 frame4 page1 On-Disk File page1 page2 page3 page4. 15-445/645](https://reader034.vdocuments.us/reader034/viewer/2022051408/5ffd1e73c4092502e8480d22/html5/thumbnails/32.jpg)
15-445/645 (Fall 2020)
PRE-FETCHING
17
Buffer Pool
Disk Pages
index-page0
index-page1
index-page2
index-page3
index-page4
index-page5
index-page0
index-page4index-page1
index-page2 index-page5index-page3 index-page6
0 99 100 199 200 299 300 399
![Page 33: 05 Buffer Pools · 2020. 9. 16. · exact copy is placed into one of these frames. 8 Buffer Pool frame1 frame2 frame3 frame4 page1 On-Disk File page1 page2 page3 page4. 15-445/645](https://reader034.vdocuments.us/reader034/viewer/2022051408/5ffd1e73c4092502e8480d22/html5/thumbnails/33.jpg)
15-445/645 (Fall 2020)
PRE-FETCHING
17
Buffer Pool
index-page0
Disk Pages
index-page0
index-page1
index-page2
index-page3
index-page4
index-page5
Q1
index-page0
index-page4index-page1
index-page2 index-page5index-page3 index-page6
0 99 100 199 200 299 300 399
![Page 34: 05 Buffer Pools · 2020. 9. 16. · exact copy is placed into one of these frames. 8 Buffer Pool frame1 frame2 frame3 frame4 page1 On-Disk File page1 page2 page3 page4. 15-445/645](https://reader034.vdocuments.us/reader034/viewer/2022051408/5ffd1e73c4092502e8480d22/html5/thumbnails/34.jpg)
15-445/645 (Fall 2020)
PRE-FETCHING
17
Buffer Pool
index-page0
index-page1
Disk Pages
index-page0
index-page1
index-page2
index-page3
index-page4
index-page5
Q1
index-page0
index-page4index-page1
index-page2 index-page5index-page3 index-page6
0 99 100 199 200 299 300 399
![Page 35: 05 Buffer Pools · 2020. 9. 16. · exact copy is placed into one of these frames. 8 Buffer Pool frame1 frame2 frame3 frame4 page1 On-Disk File page1 page2 page3 page4. 15-445/645](https://reader034.vdocuments.us/reader034/viewer/2022051408/5ffd1e73c4092502e8480d22/html5/thumbnails/35.jpg)
15-445/645 (Fall 2020)
PRE-FETCHING
17
Buffer Pool
index-page0
index-page1
Disk Pages
index-page0
index-page1
index-page2
index-page3
index-page4
index-page5
Q1
index-page0
index-page4index-page1
index-page2 index-page5index-page3 index-page6
0 99 100 199 200 299 300 399
![Page 36: 05 Buffer Pools · 2020. 9. 16. · exact copy is placed into one of these frames. 8 Buffer Pool frame1 frame2 frame3 frame4 page1 On-Disk File page1 page2 page3 page4. 15-445/645](https://reader034.vdocuments.us/reader034/viewer/2022051408/5ffd1e73c4092502e8480d22/html5/thumbnails/36.jpg)
15-445/645 (Fall 2020)
SCAN SHARING
Queries can reuse data retrieved from storage or operator computations.→ Also called synchronized scans.→ This is different from result caching.
Allow multiple queries to attach to a single cursor that scans a table.→ Queries do not have to be the same.→ Can also share intermediate results.
18
![Page 37: 05 Buffer Pools · 2020. 9. 16. · exact copy is placed into one of these frames. 8 Buffer Pool frame1 frame2 frame3 frame4 page1 On-Disk File page1 page2 page3 page4. 15-445/645](https://reader034.vdocuments.us/reader034/viewer/2022051408/5ffd1e73c4092502e8480d22/html5/thumbnails/37.jpg)
15-445/645 (Fall 2020)
SCAN SHARING
If a query wants to scan a table and another query is already doing this, then the DBMS will attach the second query's cursor to the existing cursor.
Examples:→ Fully supported in IBM DB2, MSSQL, and Postgres.→ Oracle only supports cursor sharing for identical queries.
19
![Page 38: 05 Buffer Pools · 2020. 9. 16. · exact copy is placed into one of these frames. 8 Buffer Pool frame1 frame2 frame3 frame4 page1 On-Disk File page1 page2 page3 page4. 15-445/645](https://reader034.vdocuments.us/reader034/viewer/2022051408/5ffd1e73c4092502e8480d22/html5/thumbnails/38.jpg)
15-445/645 (Fall 2020)
SCAN SHARING
If a query wants to scan a table and another query is already doing this, then the DBMS will attach the second query's cursor to the existing cursor.
Examples:→ Fully supported in IBM DB2, MSSQL, and Postgres.→ Oracle only supports cursor sharing for identical queries.
19
![Page 39: 05 Buffer Pools · 2020. 9. 16. · exact copy is placed into one of these frames. 8 Buffer Pool frame1 frame2 frame3 frame4 page1 On-Disk File page1 page2 page3 page4. 15-445/645](https://reader034.vdocuments.us/reader034/viewer/2022051408/5ffd1e73c4092502e8480d22/html5/thumbnails/39.jpg)
15-445/645 (Fall 2020)
SCAN SHARING
If a query wants to scan a table and another query is already doing this, then the DBMS will attach the second query's cursor to the existing cursor.
Examples:→ Fully supported in IBM DB2, MSSQL, and Postgres.→ Oracle only supports cursor sharing for identical queries.
19
![Page 40: 05 Buffer Pools · 2020. 9. 16. · exact copy is placed into one of these frames. 8 Buffer Pool frame1 frame2 frame3 frame4 page1 On-Disk File page1 page2 page3 page4. 15-445/645](https://reader034.vdocuments.us/reader034/viewer/2022051408/5ffd1e73c4092502e8480d22/html5/thumbnails/40.jpg)
15-445/645 (Fall 2020)
Buffer Pool
SCAN SHARING
20
Disk Pages
page0
page1
page2
page3
page4
page5
SELECT SUM(val) FROM AQ1Q1
![Page 41: 05 Buffer Pools · 2020. 9. 16. · exact copy is placed into one of these frames. 8 Buffer Pool frame1 frame2 frame3 frame4 page1 On-Disk File page1 page2 page3 page4. 15-445/645](https://reader034.vdocuments.us/reader034/viewer/2022051408/5ffd1e73c4092502e8480d22/html5/thumbnails/41.jpg)
15-445/645 (Fall 2020)
Buffer Pool
page0
SCAN SHARING
20
Disk Pages
page0
page1
page2
page3
page4
page5
SELECT SUM(val) FROM AQ1Q1
![Page 42: 05 Buffer Pools · 2020. 9. 16. · exact copy is placed into one of these frames. 8 Buffer Pool frame1 frame2 frame3 frame4 page1 On-Disk File page1 page2 page3 page4. 15-445/645](https://reader034.vdocuments.us/reader034/viewer/2022051408/5ffd1e73c4092502e8480d22/html5/thumbnails/42.jpg)
15-445/645 (Fall 2020)
Buffer Pool
page0
page1
page2
SCAN SHARING
20
Disk Pages
page0
page1
page2
page3
page4
page5
SELECT SUM(val) FROM AQ1
Q1
![Page 43: 05 Buffer Pools · 2020. 9. 16. · exact copy is placed into one of these frames. 8 Buffer Pool frame1 frame2 frame3 frame4 page1 On-Disk File page1 page2 page3 page4. 15-445/645](https://reader034.vdocuments.us/reader034/viewer/2022051408/5ffd1e73c4092502e8480d22/html5/thumbnails/43.jpg)
15-445/645 (Fall 2020)
Buffer Pool
page0
page1
page2
SCAN SHARING
20
Disk Pages
page0
page1
page2
page3
page4
page5
SELECT SUM(val) FROM AQ1
Q1
![Page 44: 05 Buffer Pools · 2020. 9. 16. · exact copy is placed into one of these frames. 8 Buffer Pool frame1 frame2 frame3 frame4 page1 On-Disk File page1 page2 page3 page4. 15-445/645](https://reader034.vdocuments.us/reader034/viewer/2022051408/5ffd1e73c4092502e8480d22/html5/thumbnails/44.jpg)
15-445/645 (Fall 2020)
Buffer Pool
page1
page2
SCAN SHARING
20
Disk Pages
page0
page1
page2
page3
page4
page5
SELECT SUM(val) FROM AQ1
Q1page3
![Page 45: 05 Buffer Pools · 2020. 9. 16. · exact copy is placed into one of these frames. 8 Buffer Pool frame1 frame2 frame3 frame4 page1 On-Disk File page1 page2 page3 page4. 15-445/645](https://reader034.vdocuments.us/reader034/viewer/2022051408/5ffd1e73c4092502e8480d22/html5/thumbnails/45.jpg)
15-445/645 (Fall 2020)
Buffer Pool
page1
page2
SCAN SHARING
20
Disk Pages
page0
page1
page2
page3
page4
page5
SELECT SUM(val) FROM AQ1
SELECT AVG(val) FROM AQ2
Q1page3
Q2
![Page 46: 05 Buffer Pools · 2020. 9. 16. · exact copy is placed into one of these frames. 8 Buffer Pool frame1 frame2 frame3 frame4 page1 On-Disk File page1 page2 page3 page4. 15-445/645](https://reader034.vdocuments.us/reader034/viewer/2022051408/5ffd1e73c4092502e8480d22/html5/thumbnails/46.jpg)
15-445/645 (Fall 2020)
Buffer Pool
page1
page2
SCAN SHARING
20
Disk Pages
page0
page1
page2
page3
page4
page5
SELECT SUM(val) FROM AQ1
SELECT AVG(val) FROM AQ2
Q1page3 Q2
![Page 47: 05 Buffer Pools · 2020. 9. 16. · exact copy is placed into one of these frames. 8 Buffer Pool frame1 frame2 frame3 frame4 page1 On-Disk File page1 page2 page3 page4. 15-445/645](https://reader034.vdocuments.us/reader034/viewer/2022051408/5ffd1e73c4092502e8480d22/html5/thumbnails/47.jpg)
15-445/645 (Fall 2020)
Buffer Pool
SCAN SHARING
20
Disk Pages
page0
page1
page2
page3
page4
page5
SELECT SUM(val) FROM AQ1
SELECT AVG(val) FROM AQ2
Q1
page3
Q2
page4
page5
![Page 48: 05 Buffer Pools · 2020. 9. 16. · exact copy is placed into one of these frames. 8 Buffer Pool frame1 frame2 frame3 frame4 page1 On-Disk File page1 page2 page3 page4. 15-445/645](https://reader034.vdocuments.us/reader034/viewer/2022051408/5ffd1e73c4092502e8480d22/html5/thumbnails/48.jpg)
15-445/645 (Fall 2020)
Buffer Pool
SCAN SHARING
20
Disk Pages
page0
page1
page2
page3
page4
page5
SELECT SUM(val) FROM AQ1
SELECT AVG(val) FROM AQ2
page3
Q2
page4
page5
![Page 49: 05 Buffer Pools · 2020. 9. 16. · exact copy is placed into one of these frames. 8 Buffer Pool frame1 frame2 frame3 frame4 page1 On-Disk File page1 page2 page3 page4. 15-445/645](https://reader034.vdocuments.us/reader034/viewer/2022051408/5ffd1e73c4092502e8480d22/html5/thumbnails/49.jpg)
15-445/645 (Fall 2020)
Buffer Pool
page0
page1
page2
SCAN SHARING
20
Disk Pages
page0
page1
page2
page3
page4
page5
SELECT SUM(val) FROM AQ1
SELECT AVG(val) FROM AQ2
Q2
![Page 50: 05 Buffer Pools · 2020. 9. 16. · exact copy is placed into one of these frames. 8 Buffer Pool frame1 frame2 frame3 frame4 page1 On-Disk File page1 page2 page3 page4. 15-445/645](https://reader034.vdocuments.us/reader034/viewer/2022051408/5ffd1e73c4092502e8480d22/html5/thumbnails/50.jpg)
15-445/645 (Fall 2020)
Buffer Pool
page0
page1
page2
SCAN SHARING
20
Disk Pages
page0
page1
page2
page3
page4
page5
SELECT SUM(val) FROM AQ1
Q2
SELECT AVG(val) FROM A LIMIT 100Q2
![Page 51: 05 Buffer Pools · 2020. 9. 16. · exact copy is placed into one of these frames. 8 Buffer Pool frame1 frame2 frame3 frame4 page1 On-Disk File page1 page2 page3 page4. 15-445/645](https://reader034.vdocuments.us/reader034/viewer/2022051408/5ffd1e73c4092502e8480d22/html5/thumbnails/51.jpg)
15-445/645 (Fall 2020)
BUFFER POOL BYPASS
The sequential scan operator will not store fetched pages in the buffer pool to avoid overhead.→ Memory is local to running query.→ Works well if operator needs to read a large sequence of
pages that are contiguous on disk.→ Can also be used for temporary data (sorting, joins).
Called "Light Scans" in Informix.
21
![Page 52: 05 Buffer Pools · 2020. 9. 16. · exact copy is placed into one of these frames. 8 Buffer Pool frame1 frame2 frame3 frame4 page1 On-Disk File page1 page2 page3 page4. 15-445/645](https://reader034.vdocuments.us/reader034/viewer/2022051408/5ffd1e73c4092502e8480d22/html5/thumbnails/52.jpg)
15-445/645 (Fall 2020)
OS PAGE CACHE
Most disk operations go through the OS API.
Unless you tell it not to, the OS maintains its own filesystem cache.
Most DBMSs use direct I/O (O_DIRECT)to bypass the OS's cache.→ Redundant copies of pages.→ Different eviction policies.
Demo: Postgres
22
![Page 53: 05 Buffer Pools · 2020. 9. 16. · exact copy is placed into one of these frames. 8 Buffer Pool frame1 frame2 frame3 frame4 page1 On-Disk File page1 page2 page3 page4. 15-445/645](https://reader034.vdocuments.us/reader034/viewer/2022051408/5ffd1e73c4092502e8480d22/html5/thumbnails/53.jpg)
15-445/645 (Fall 2020)
BUFFER REPL ACEMENT POLICIES
When the DBMS needs to free up a frame to make room for a new page, it must decide which page to evict from the buffer pool.
Goals:→ Correctness→ Accuracy→ Speed→ Meta-data overhead
23
![Page 54: 05 Buffer Pools · 2020. 9. 16. · exact copy is placed into one of these frames. 8 Buffer Pool frame1 frame2 frame3 frame4 page1 On-Disk File page1 page2 page3 page4. 15-445/645](https://reader034.vdocuments.us/reader034/viewer/2022051408/5ffd1e73c4092502e8480d22/html5/thumbnails/54.jpg)
15-445/645 (Fall 2020)
LEAST-RECENTLY USED
Maintain a single timestamp of when each page was last accessed.
When the DBMS needs to evict a page, select the one with the oldest timestamp.→ Keep the pages in sorted order to reduce the search time
on eviction.
24
![Page 55: 05 Buffer Pools · 2020. 9. 16. · exact copy is placed into one of these frames. 8 Buffer Pool frame1 frame2 frame3 frame4 page1 On-Disk File page1 page2 page3 page4. 15-445/645](https://reader034.vdocuments.us/reader034/viewer/2022051408/5ffd1e73c4092502e8480d22/html5/thumbnails/55.jpg)
15-445/645 (Fall 2020)
CLOCK
Approximation of LRU that does not need a separate timestamp per page.→ Each page has a reference bit.→ When a page is accessed, set to 1.
Organize the pages in a circular buffer with a "clock hand":→ Upon sweeping, check if a page's bit is set
to 1.→ If yes, set to zero. If no, then evict.
25
page1
page3
page4 page2
ref=0
ref=0
ref=0
ref=0
![Page 56: 05 Buffer Pools · 2020. 9. 16. · exact copy is placed into one of these frames. 8 Buffer Pool frame1 frame2 frame3 frame4 page1 On-Disk File page1 page2 page3 page4. 15-445/645](https://reader034.vdocuments.us/reader034/viewer/2022051408/5ffd1e73c4092502e8480d22/html5/thumbnails/56.jpg)
15-445/645 (Fall 2020)
CLOCK
Approximation of LRU that does not need a separate timestamp per page.→ Each page has a reference bit.→ When a page is accessed, set to 1.
Organize the pages in a circular buffer with a "clock hand":→ Upon sweeping, check if a page's bit is set
to 1.→ If yes, set to zero. If no, then evict.
25
page1
page3
page4 page2
ref=1
ref=0
ref=0
ref=0
![Page 57: 05 Buffer Pools · 2020. 9. 16. · exact copy is placed into one of these frames. 8 Buffer Pool frame1 frame2 frame3 frame4 page1 On-Disk File page1 page2 page3 page4. 15-445/645](https://reader034.vdocuments.us/reader034/viewer/2022051408/5ffd1e73c4092502e8480d22/html5/thumbnails/57.jpg)
15-445/645 (Fall 2020)
CLOCK
Approximation of LRU that does not need a separate timestamp per page.→ Each page has a reference bit.→ When a page is accessed, set to 1.
Organize the pages in a circular buffer with a "clock hand":→ Upon sweeping, check if a page's bit is set
to 1.→ If yes, set to zero. If no, then evict.
25
page1
page3
page4 page2
ref=0
ref=0
ref=0
ref=0
![Page 58: 05 Buffer Pools · 2020. 9. 16. · exact copy is placed into one of these frames. 8 Buffer Pool frame1 frame2 frame3 frame4 page1 On-Disk File page1 page2 page3 page4. 15-445/645](https://reader034.vdocuments.us/reader034/viewer/2022051408/5ffd1e73c4092502e8480d22/html5/thumbnails/58.jpg)
15-445/645 (Fall 2020)
CLOCK
Approximation of LRU that does not need a separate timestamp per page.→ Each page has a reference bit.→ When a page is accessed, set to 1.
Organize the pages in a circular buffer with a "clock hand":→ Upon sweeping, check if a page's bit is set
to 1.→ If yes, set to zero. If no, then evict.
25
page1
page3
page4 page2
ref=0
ref=0
ref=0
ref=0
![Page 59: 05 Buffer Pools · 2020. 9. 16. · exact copy is placed into one of these frames. 8 Buffer Pool frame1 frame2 frame3 frame4 page1 On-Disk File page1 page2 page3 page4. 15-445/645](https://reader034.vdocuments.us/reader034/viewer/2022051408/5ffd1e73c4092502e8480d22/html5/thumbnails/59.jpg)
15-445/645 (Fall 2020)
CLOCK
Approximation of LRU that does not need a separate timestamp per page.→ Each page has a reference bit.→ When a page is accessed, set to 1.
Organize the pages in a circular buffer with a "clock hand":→ Upon sweeping, check if a page's bit is set
to 1.→ If yes, set to zero. If no, then evict.
25
page1
page3
page4 page2
ref=0
ref=0
ref=0
ref=0
![Page 60: 05 Buffer Pools · 2020. 9. 16. · exact copy is placed into one of these frames. 8 Buffer Pool frame1 frame2 frame3 frame4 page1 On-Disk File page1 page2 page3 page4. 15-445/645](https://reader034.vdocuments.us/reader034/viewer/2022051408/5ffd1e73c4092502e8480d22/html5/thumbnails/60.jpg)
15-445/645 (Fall 2020)
CLOCK
Approximation of LRU that does not need a separate timestamp per page.→ Each page has a reference bit.→ When a page is accessed, set to 1.
Organize the pages in a circular buffer with a "clock hand":→ Upon sweeping, check if a page's bit is set
to 1.→ If yes, set to zero. If no, then evict.
25
page1
page3
page4
ref=0
ref=0
ref=0
ref=0page5
![Page 61: 05 Buffer Pools · 2020. 9. 16. · exact copy is placed into one of these frames. 8 Buffer Pool frame1 frame2 frame3 frame4 page1 On-Disk File page1 page2 page3 page4. 15-445/645](https://reader034.vdocuments.us/reader034/viewer/2022051408/5ffd1e73c4092502e8480d22/html5/thumbnails/61.jpg)
15-445/645 (Fall 2020)
CLOCK
Approximation of LRU that does not need a separate timestamp per page.→ Each page has a reference bit.→ When a page is accessed, set to 1.
Organize the pages in a circular buffer with a "clock hand":→ Upon sweeping, check if a page's bit is set
to 1.→ If yes, set to zero. If no, then evict.
25
page1
page3
page4
ref=0
ref=0
ref=1
ref=1page5
![Page 62: 05 Buffer Pools · 2020. 9. 16. · exact copy is placed into one of these frames. 8 Buffer Pool frame1 frame2 frame3 frame4 page1 On-Disk File page1 page2 page3 page4. 15-445/645](https://reader034.vdocuments.us/reader034/viewer/2022051408/5ffd1e73c4092502e8480d22/html5/thumbnails/62.jpg)
15-445/645 (Fall 2020)
CLOCK
Approximation of LRU that does not need a separate timestamp per page.→ Each page has a reference bit.→ When a page is accessed, set to 1.
Organize the pages in a circular buffer with a "clock hand":→ Upon sweeping, check if a page's bit is set
to 1.→ If yes, set to zero. If no, then evict.
25
page1
page3
page4
ref=0
ref=0
ref=0
ref=0page5
![Page 63: 05 Buffer Pools · 2020. 9. 16. · exact copy is placed into one of these frames. 8 Buffer Pool frame1 frame2 frame3 frame4 page1 On-Disk File page1 page2 page3 page4. 15-445/645](https://reader034.vdocuments.us/reader034/viewer/2022051408/5ffd1e73c4092502e8480d22/html5/thumbnails/63.jpg)
15-445/645 (Fall 2020)
CLOCK
Approximation of LRU that does not need a separate timestamp per page.→ Each page has a reference bit.→ When a page is accessed, set to 1.
Organize the pages in a circular buffer with a "clock hand":→ Upon sweeping, check if a page's bit is set
to 1.→ If yes, set to zero. If no, then evict.
25
page1
page3
page4
ref=0
ref=0
ref=0
ref=0page5
![Page 64: 05 Buffer Pools · 2020. 9. 16. · exact copy is placed into one of these frames. 8 Buffer Pool frame1 frame2 frame3 frame4 page1 On-Disk File page1 page2 page3 page4. 15-445/645](https://reader034.vdocuments.us/reader034/viewer/2022051408/5ffd1e73c4092502e8480d22/html5/thumbnails/64.jpg)
15-445/645 (Fall 2020)
PROBLEMS
LRU and CLOCK replacement policies are susceptible to sequential flooding.→ A query performs a sequential scan that reads every page.→ This pollutes the buffer pool with pages that are read
once and then never again.
In some workloads the most recently used page is the most unneeded page.
26
![Page 65: 05 Buffer Pools · 2020. 9. 16. · exact copy is placed into one of these frames. 8 Buffer Pool frame1 frame2 frame3 frame4 page1 On-Disk File page1 page2 page3 page4. 15-445/645](https://reader034.vdocuments.us/reader034/viewer/2022051408/5ffd1e73c4092502e8480d22/html5/thumbnails/65.jpg)
15-445/645 (Fall 2020)
Buffer Pool
SEQUENTIAL FLOODING
27
Disk Pages
page0
page1
page2
page3
page4
page5
SELECT * FROM A WHERE id = 1Q1
![Page 66: 05 Buffer Pools · 2020. 9. 16. · exact copy is placed into one of these frames. 8 Buffer Pool frame1 frame2 frame3 frame4 page1 On-Disk File page1 page2 page3 page4. 15-445/645](https://reader034.vdocuments.us/reader034/viewer/2022051408/5ffd1e73c4092502e8480d22/html5/thumbnails/66.jpg)
15-445/645 (Fall 2020)
Buffer Pool
page0
SEQUENTIAL FLOODING
27
Disk Pages
page0
page1
page2
page3
page4
page5
SELECT * FROM A WHERE id = 1Q1
Q1
![Page 67: 05 Buffer Pools · 2020. 9. 16. · exact copy is placed into one of these frames. 8 Buffer Pool frame1 frame2 frame3 frame4 page1 On-Disk File page1 page2 page3 page4. 15-445/645](https://reader034.vdocuments.us/reader034/viewer/2022051408/5ffd1e73c4092502e8480d22/html5/thumbnails/67.jpg)
15-445/645 (Fall 2020)
Buffer Pool
page0
SEQUENTIAL FLOODING
27
Disk Pages
page0
page1
page2
page3
page4
page5
SELECT * FROM A WHERE id = 1Q1
SELECT AVG(val) FROM AQ2 Q2
![Page 68: 05 Buffer Pools · 2020. 9. 16. · exact copy is placed into one of these frames. 8 Buffer Pool frame1 frame2 frame3 frame4 page1 On-Disk File page1 page2 page3 page4. 15-445/645](https://reader034.vdocuments.us/reader034/viewer/2022051408/5ffd1e73c4092502e8480d22/html5/thumbnails/68.jpg)
15-445/645 (Fall 2020)
Buffer Pool
page0
page1
page2
SEQUENTIAL FLOODING
27
Disk Pages
page0
page1
page2
page3
page4
page5
SELECT * FROM A WHERE id = 1Q1
SELECT AVG(val) FROM AQ2
Q2
![Page 69: 05 Buffer Pools · 2020. 9. 16. · exact copy is placed into one of these frames. 8 Buffer Pool frame1 frame2 frame3 frame4 page1 On-Disk File page1 page2 page3 page4. 15-445/645](https://reader034.vdocuments.us/reader034/viewer/2022051408/5ffd1e73c4092502e8480d22/html5/thumbnails/69.jpg)
15-445/645 (Fall 2020)
Buffer Pool
page1
page2
SEQUENTIAL FLOODING
27
Disk Pages
page0
page1
page2
page3
page4
page5
SELECT * FROM A WHERE id = 1Q1
SELECT AVG(val) FROM AQ2
page3 Q2
![Page 70: 05 Buffer Pools · 2020. 9. 16. · exact copy is placed into one of these frames. 8 Buffer Pool frame1 frame2 frame3 frame4 page1 On-Disk File page1 page2 page3 page4. 15-445/645](https://reader034.vdocuments.us/reader034/viewer/2022051408/5ffd1e73c4092502e8480d22/html5/thumbnails/70.jpg)
15-445/645 (Fall 2020)
Buffer Pool
page1
page2
SEQUENTIAL FLOODING
27
Disk Pages
page0
page1
page2
page3
page4
page5
SELECT * FROM A WHERE id = 1Q1
SELECT AVG(val) FROM AQ2
page3 Q2
SELECT * FROM A WHERE id = 1Q3
Q2
![Page 71: 05 Buffer Pools · 2020. 9. 16. · exact copy is placed into one of these frames. 8 Buffer Pool frame1 frame2 frame3 frame4 page1 On-Disk File page1 page2 page3 page4. 15-445/645](https://reader034.vdocuments.us/reader034/viewer/2022051408/5ffd1e73c4092502e8480d22/html5/thumbnails/71.jpg)
15-445/645 (Fall 2020)
Buffer Pool
page1
page2
SEQUENTIAL FLOODING
27
Disk Pages
page0
page1
page2
page3
page4
page5
SELECT * FROM A WHERE id = 1Q1
SELECT AVG(val) FROM AQ2
page3 Q2
SELECT * FROM A WHERE id = 1Q3
Q2
![Page 72: 05 Buffer Pools · 2020. 9. 16. · exact copy is placed into one of these frames. 8 Buffer Pool frame1 frame2 frame3 frame4 page1 On-Disk File page1 page2 page3 page4. 15-445/645](https://reader034.vdocuments.us/reader034/viewer/2022051408/5ffd1e73c4092502e8480d22/html5/thumbnails/72.jpg)
15-445/645 (Fall 2020)
BET TER POLICIES: LRU-K
Track the history of last K references to each page as timestamps and compute the interval between subsequent accesses.
The DBMS then uses this history to estimate the next time that page is going to be accessed.
28
![Page 73: 05 Buffer Pools · 2020. 9. 16. · exact copy is placed into one of these frames. 8 Buffer Pool frame1 frame2 frame3 frame4 page1 On-Disk File page1 page2 page3 page4. 15-445/645](https://reader034.vdocuments.us/reader034/viewer/2022051408/5ffd1e73c4092502e8480d22/html5/thumbnails/73.jpg)
15-445/645 (Fall 2020)
BET TER POLICIES: LOCALIZATION
The DBMS chooses which pages to evict on a per txn/query basis. This minimizes the pollution of the buffer pool from each query.→ Keep track of the pages that a query has accessed.
Example: Postgres maintains a small ring buffer that is private to the query.
29
![Page 74: 05 Buffer Pools · 2020. 9. 16. · exact copy is placed into one of these frames. 8 Buffer Pool frame1 frame2 frame3 frame4 page1 On-Disk File page1 page2 page3 page4. 15-445/645](https://reader034.vdocuments.us/reader034/viewer/2022051408/5ffd1e73c4092502e8480d22/html5/thumbnails/74.jpg)
15-445/645 (Fall 2020)
BET TER POLICIES: PRIORIT Y HINTS
The DBMS knows what the context of each page during query execution.
It can provide hints to the buffer pool on whether a page is important or not.
30
index-page0
index-page4index-page1
index-page2 index-page5index-page3 index-page6
INSERT INTO A VALUES (id++)Q1
MIN MAXid
![Page 75: 05 Buffer Pools · 2020. 9. 16. · exact copy is placed into one of these frames. 8 Buffer Pool frame1 frame2 frame3 frame4 page1 On-Disk File page1 page2 page3 page4. 15-445/645](https://reader034.vdocuments.us/reader034/viewer/2022051408/5ffd1e73c4092502e8480d22/html5/thumbnails/75.jpg)
15-445/645 (Fall 2020)
BET TER POLICIES: PRIORIT Y HINTS
The DBMS knows what the context of each page during query execution.
It can provide hints to the buffer pool on whether a page is important or not.
30
index-page0
index-page4index-page1
index-page2 index-page5index-page3 index-page6
INSERT INTO A VALUES (id++)Q1
MIN MAXid
![Page 76: 05 Buffer Pools · 2020. 9. 16. · exact copy is placed into one of these frames. 8 Buffer Pool frame1 frame2 frame3 frame4 page1 On-Disk File page1 page2 page3 page4. 15-445/645](https://reader034.vdocuments.us/reader034/viewer/2022051408/5ffd1e73c4092502e8480d22/html5/thumbnails/76.jpg)
15-445/645 (Fall 2020)
BET TER POLICIES: PRIORIT Y HINTS
The DBMS knows what the context of each page during query execution.
It can provide hints to the buffer pool on whether a page is important or not.
30
index-page0
index-page4index-page1
index-page2 index-page5index-page3 index-page6
SELECT * FROM A WHERE id = ?Q2
INSERT INTO A VALUES (id++)Q1
MIN MAXid
![Page 77: 05 Buffer Pools · 2020. 9. 16. · exact copy is placed into one of these frames. 8 Buffer Pool frame1 frame2 frame3 frame4 page1 On-Disk File page1 page2 page3 page4. 15-445/645](https://reader034.vdocuments.us/reader034/viewer/2022051408/5ffd1e73c4092502e8480d22/html5/thumbnails/77.jpg)
15-445/645 (Fall 2020)
BET TER POLICIES: PRIORIT Y HINTS
The DBMS knows what the context of each page during query execution.
It can provide hints to the buffer pool on whether a page is important or not.
30
index-page0
index-page4index-page1
index-page2 index-page5index-page3 index-page6
SELECT * FROM A WHERE id = ?Q2
INSERT INTO A VALUES (id++)Q1
MIN MAXid
![Page 78: 05 Buffer Pools · 2020. 9. 16. · exact copy is placed into one of these frames. 8 Buffer Pool frame1 frame2 frame3 frame4 page1 On-Disk File page1 page2 page3 page4. 15-445/645](https://reader034.vdocuments.us/reader034/viewer/2022051408/5ffd1e73c4092502e8480d22/html5/thumbnails/78.jpg)
15-445/645 (Fall 2020)
DIRT Y PAGES
FAST: If a page in the buffer pool is not dirty, then the DBMS can simply "drop" it.
SLOW: If a page is dirty, then the DBMS must write back to disk to ensure that its changes are persisted.
Trade-off between fast evictions versus dirty writing pages that will not be read again in the future.
31
![Page 79: 05 Buffer Pools · 2020. 9. 16. · exact copy is placed into one of these frames. 8 Buffer Pool frame1 frame2 frame3 frame4 page1 On-Disk File page1 page2 page3 page4. 15-445/645](https://reader034.vdocuments.us/reader034/viewer/2022051408/5ffd1e73c4092502e8480d22/html5/thumbnails/79.jpg)
15-445/645 (Fall 2020)
BACKGROUND WRITING
The DBMS can periodically walk through the page table and write dirty pages to disk.
When a dirty page is safely written, the DBMS can either evict the page or just unset the dirty flag.
Need to be careful that we don’t write dirty pages before their log records have been written…
32
![Page 80: 05 Buffer Pools · 2020. 9. 16. · exact copy is placed into one of these frames. 8 Buffer Pool frame1 frame2 frame3 frame4 page1 On-Disk File page1 page2 page3 page4. 15-445/645](https://reader034.vdocuments.us/reader034/viewer/2022051408/5ffd1e73c4092502e8480d22/html5/thumbnails/80.jpg)
15-445/645 (Fall 2020)
OTHER MEMORY POOLS
The DBMS needs memory for things other than just tuples and indexes.
These other memory pools may not always backedby disk. Depends on implementation.→ Sorting + Join Buffers→ Query Caches→ Maintenance Buffers→ Log Buffers→ Dictionary Caches
33
![Page 81: 05 Buffer Pools · 2020. 9. 16. · exact copy is placed into one of these frames. 8 Buffer Pool frame1 frame2 frame3 frame4 page1 On-Disk File page1 page2 page3 page4. 15-445/645](https://reader034.vdocuments.us/reader034/viewer/2022051408/5ffd1e73c4092502e8480d22/html5/thumbnails/81.jpg)
15-445/645 (Fall 2020)
CONCLUSION
The DBMS can manage that sweet, sweet memory better than the OS.
Leverage the semantics about the query plan to make better decisions:→ Evictions→ Allocations→ Pre-fetching
34
![Page 82: 05 Buffer Pools · 2020. 9. 16. · exact copy is placed into one of these frames. 8 Buffer Pool frame1 frame2 frame3 frame4 page1 On-Disk File page1 page2 page3 page4. 15-445/645](https://reader034.vdocuments.us/reader034/viewer/2022051408/5ffd1e73c4092502e8480d22/html5/thumbnails/82.jpg)
15-445/645 (Fall 2020)
NEXT CL ASS
HASH TABLES!
35
![Page 83: 05 Buffer Pools · 2020. 9. 16. · exact copy is placed into one of these frames. 8 Buffer Pool frame1 frame2 frame3 frame4 page1 On-Disk File page1 page2 page3 page4. 15-445/645](https://reader034.vdocuments.us/reader034/viewer/2022051408/5ffd1e73c4092502e8480d22/html5/thumbnails/83.jpg)
15-445/645 (Fall 2020)
PROJECT #1
You will build the first component of your storage manager.→ LRU Replacement Policy→ Buffer Pool Manager
We will provide you with the disk manager and page layouts.
36
Due Date:Sunday Sept 27th @ 11:59pm
![Page 84: 05 Buffer Pools · 2020. 9. 16. · exact copy is placed into one of these frames. 8 Buffer Pool frame1 frame2 frame3 frame4 page1 On-Disk File page1 page2 page3 page4. 15-445/645](https://reader034.vdocuments.us/reader034/viewer/2022051408/5ffd1e73c4092502e8480d22/html5/thumbnails/84.jpg)
15-445/645 (Fall 2020)
TASK #1 LRU REPL ACEMENT POLICY
Build a data structure that tracks the usage of frame_ids using the LRU policy.
General Hints:→ Your LRUReplacer needs to check the "pinned" status of
a Page.→ If there are no pages touched since last sweep, then
return the lowest page id.
37
![Page 85: 05 Buffer Pools · 2020. 9. 16. · exact copy is placed into one of these frames. 8 Buffer Pool frame1 frame2 frame3 frame4 page1 On-Disk File page1 page2 page3 page4. 15-445/645](https://reader034.vdocuments.us/reader034/viewer/2022051408/5ffd1e73c4092502e8480d22/html5/thumbnails/85.jpg)
15-445/645 (Fall 2020)
TASK #2 BUFFER POOL MANAGER
Use your LRU replacer to manage the allocation of pages.→ Need to maintain an internal data
structures of allocated + free pages.→ We will provide you components to
read/write data from disk.→ Use whatever data structure you want for
the page table.
General Hints:→ Make sure you get the order of operations
correct when pinning.
38
Buffer Pool(In-Memory)
Page6
Page2
Page4
Database(On-Disk)
Page0
Page1
Page2
![Page 86: 05 Buffer Pools · 2020. 9. 16. · exact copy is placed into one of these frames. 8 Buffer Pool frame1 frame2 frame3 frame4 page1 On-Disk File page1 page2 page3 page4. 15-445/645](https://reader034.vdocuments.us/reader034/viewer/2022051408/5ffd1e73c4092502e8480d22/html5/thumbnails/86.jpg)
15-445/645 (Fall 2020)
GET TING STARTED
Download the source code from GitHub.
Make sure you can build it on your machine.→ We have tested Ubuntu, OSX, and Windows (WSL2).→ We are also providing a Docker and Vagrant file to setup
your environment.→ It does not compile on the Andrews machines. Please
contact me if this is a problem.
39
![Page 87: 05 Buffer Pools · 2020. 9. 16. · exact copy is placed into one of these frames. 8 Buffer Pool frame1 frame2 frame3 frame4 page1 On-Disk File page1 page2 page3 page4. 15-445/645](https://reader034.vdocuments.us/reader034/viewer/2022051408/5ffd1e73c4092502e8480d22/html5/thumbnails/87.jpg)
15-445/645 (Fall 2020)
THINGS TO NOTE
Do not change any file other than the four that you must hand in.
The projects are cumulative.
We will not be providing solutions.
Post your questions on Piazza or come to our office hours. We will not help you debug.
40
![Page 88: 05 Buffer Pools · 2020. 9. 16. · exact copy is placed into one of these frames. 8 Buffer Pool frame1 frame2 frame3 frame4 page1 On-Disk File page1 page2 page3 page4. 15-445/645](https://reader034.vdocuments.us/reader034/viewer/2022051408/5ffd1e73c4092502e8480d22/html5/thumbnails/88.jpg)
15-445/645 (Fall 2020)
CODE QUALIT Y
We will automatically check whether you are writing good code.→ Google C++ Style Guide→ Doxygen Javadoc Style
You need to run these targets before you submit your implementation to Gradescope.→ make format→ make check-lint→ make check-censored→ make check-clang-tidy
41
![Page 89: 05 Buffer Pools · 2020. 9. 16. · exact copy is placed into one of these frames. 8 Buffer Pool frame1 frame2 frame3 frame4 page1 On-Disk File page1 page2 page3 page4. 15-445/645](https://reader034.vdocuments.us/reader034/viewer/2022051408/5ffd1e73c4092502e8480d22/html5/thumbnails/89.jpg)
15-445/645 (Fall 2020)
EXTRA CREDIT
Gradescope Leaderboard runs your code with a specialized in-memory version of BusTub.
The top 20 fastest implementations in the class will receive extra credit for this assignment.→ #1: 50% bonus points→ #2–10: 25% bonus points→ #11–20: 10% bonus points
Student with the most bonus points at the end of the semester will receive a BusTub shirt!
42
![Page 90: 05 Buffer Pools · 2020. 9. 16. · exact copy is placed into one of these frames. 8 Buffer Pool frame1 frame2 frame3 frame4 page1 On-Disk File page1 page2 page3 page4. 15-445/645](https://reader034.vdocuments.us/reader034/viewer/2022051408/5ffd1e73c4092502e8480d22/html5/thumbnails/90.jpg)
15-445/645 (Fall 2020)
PL AGIARISM WARNING
Your project implementation must be your own work.→ You may not copy source code from other
groups or the web.→ Do not publish your implementation on
GitHub.
Plagiarism will not be tolerated.See CMU's Policy on Academic Integrity for additional information.
43