Back to index

An Evaluation of Buffer Management Strategies for Relational Database Systems

Hong-Tai Chou, David J. DeWitt.
University of Wisconsin
Scribe by: Zuyu Zhang

One-line Summary

Overview/Main Points

Background
- Observations
  - DBMSs [sometimes] know access pattern (regular and predictable) due to a limited set of operations.
  - Can sometimes figure out how much memory it needs for an operation as a reference pattern of a database operation can be decomposed into a number of simple reference patterns.
    - For an index join with an index on the inner relation, two locality sets: one for the sequential scan of the outer relation and the other for the index and data pages of the inner relation.
- Domain Separation (DS) Buffer Replacement Algorithm with a B-tree index (aka page-type-oriented buffer allocation scheme)
  - Pages classified into types with an associated domain of buffers.
  - Buffers inside each domain managed by LRU
  - One domain to each non-leaf level of the B-tree structure, and one to the leaf level together with the data.
  - provided 8-10% improvement in throughput comparing with an LRU algo.
  - Limitations
    - Static domains fail to reflect the dynamics of page references in different queries.
    - Every type of pages have the same importance; an idnex page will be overwritten by another index page, instead of a less important data page in another domain.
    - Memory partitioning according to domains, rather than queries, does not prevent interference among competing users.
    - No built-in mechansim for load control to prevent thrashing.
  - Extensions
    - The group LRU (GLRU) algo: a fixed priority ranking among different groups (domains)
    - Dynamically vary the size of each domain using a working-set-like partitioning scheme.
- “ New ” algo for INGRES
  - Two observations
    - The priority for a page is a property of the relation to which it belongs rather than the page itself.
    - Each relation needs a “ working set ”.
  - The buffer pool is subdivided and allocated on a per-relation basis.
  - Each resident set associated with an active relation
    - initially empty
    - linked in a priority list with a global free list on the top and the resident set whose pages are unlikedly to be reused placed near the top.
    - replace with MRU
    - Each relation is entitled to one active buffer which is exempt from replacement consideration.
  - Pros
    - Track the locality of a query through relations.
  - Cons
    - MRU is justifiable in limited cases.
    - The rules for arranging the order of relations in the priority list were based solely on intuition.
    - Finding a free buffer in the priority list can be expensive under high memory contention.
    - It is hard to extend the algo for multi-users with respect to priority assignment for relations from different concurrent queries.
- Hot Set (HOT) algo
  - A query behavior model for relational database systems that integrates advance knowledge on reference patterns into the model.
  - A hot set is a set of pages over which there is a looping behavior.
  - A hot point is a discontinuity around the buffer size in the curve where the number of page faults is a function of buffer size.
    - For a nested loops join, the number of pages in the inner relation plus one for the outer relation. If the scan on the outer relation is an index scan, an additional buffer is required for the leaf pages of the index.
  - Each query is provided a separate list of buffers managed by an LRU discipline. The number of buffers equals to the query's hot set size. A new query whose hot set size exceed the available buffer space will not be allowed to enter the system.
  - Cons
    - The derivation of the hot set model is based partially on LRU, which is inappropriate for certain looping behavior. In fact, MRU, which retains older data, is more suitable for cyclic access patterns, because the the most-recently-used page in a loop is the one that will not be re-accessed for the longest period of time. Thus, the hot set model does not truly reflect the inherent behavior of some reference patterns, but rather the behavior under an LRU algo.
    - Low memory utilization due to over-allocation for that a query referencing to different data structures will not interfere with another.

Query Locality Set Model (QLSM)

Predict future reference behavior so that has better performance than the stochastic models.
Separate the modeling of reference behavior from any particular buffer management algo, which is the potential problems of the hot set model.

Sequential patterns

Straight Sequential (SS)
- ex: file scan for select operations
- one page is enough; do not have to kill others
- replace with next
- Prefetching: cache in disk controller, good for SS

Clustered Sequential (CS)

ex: inner relation for sort-merge join
# pages in the largest cluster (statistical result).

LRU (or FIFO?)

references: ABCABCDEF
LRU

iteration	cache 1	cache 2	cache 3
1	A
2	A	B
3	A	B	C
4	D	B	C
5	D	E	C
6	D	E	F

FIFO

iteration	cache 1	cache 2	cache 3
1	A
2	A	B
3	A	B	C
4	D	B	C
5	D	E	C
6	D	E	F

Looping Sequential (LS)

ex: inner relation in Nested Loops Join (not required for sort)

for each r in R
  for each s in S
    if r.a = s.b
      output <r, s>.

use MRU & allocate as many pages as possible (up to the inner file size)

references: ABCDABCDABCD
LRU

page fault	page 1	page 2	page 3
1	A
2	A	B
3	A	B	C
4	D	B	C
5	D	A	C
6	D	A	B
7	C	A	B
8	C	D	B
9	C	D	A
10	B	D	A
11	B	C	A
12	B	C	D

page miss	page 1	page 2	page 3
1	A
2	A	B
3	A	B	C
4	A	B	D
5	A	C	D
6	B	C	D

Random References
- Independent Random (IR)
  - a series of independent random accesses
  - ex: an index scan through a non-clustered index
  - ex: scan through a hash table
  - one page if page references are sparse, or b pages if a series of k random record accesses [Yao77].
  - the choice of a replacement algo is immaterial since all perform equally well [King71]
  - wired definition for r = ^{k b}⁄_b as the residual value of a page
- Clustered Random (CR)
  - a series of “ random ” accesses in a “ cluster ” that are not physically adjacent but randomly distributed over the file.
  - ex: a join where the inner relation uses a non-clustered and non-unique index while the outer relation is a clustered file with non-unique keys.
  - # pages containing a record in a cluster
  - LRU or FIFO?
Hierarchical References
- a sequence of page accesses that form a traversal path from the root down to the leaves of an index
- Straight hierarchical (SH)
  - traverse the index only once
  - ex: retrieving a single tuple
  - one page is enough for buffering all the index pages
- Hierarchical with straight sequential (H/SS)
  - a tree traversal followed by a Straight Sequential scan through the leaves
  - one page.
- Hierarchical with clustered sequential (H/CS)
  - a tree traversal followed by a Clustered Sequential scan through the leaves
  - # pages in the largest cluster (a key-pointer pair rather than a data record).
- Looping Hierarchical (LH)
  - ex: repeated accesses to the index pages in a join where the inner relation is indexed on the join field
  - Pages closer to the root are more likely to be accessed than those closer to the leaves.
  - A priority list where pages closer to the root have higher priority than those closer to the leaves. In many cases, the root is perhaps the only page worth keeping in memory due to high fan-out of an index page.
  - In practice, use LIFO & 3-4 pages with the root is always kept in memory

DBMIN
- buffers allocated on a per file instance basis. Active instances of the same file are given different buffer pools with independent replacement policy, but all the file instances share the same copy of a buffered page wheneven possible through a global table mechanism.
- each file instance has a local, separately managed buffer pool to hold its locality set, the size of which is determined in advance and needs not be re-calculated during the query. And the file instance is considered the owner of all the pages in its locality set.
- a global, shared table of buffers, with LRU policy, for allowing data sharing among concurrent queries.
- parameters
  - N - total # of buffers.
  - L_ij - max buffers for file instance j of query i (desired size)
  - R_ij - the number of actually allocated buffers (actual size).
- When a query requests a page, search global table
  - found in global table & locality set, update usage stats.
  - in memory (global table), not in locality set
    - if already owned, return it. (NOT good if the use pattern is SS. One looping gets page, another does not by kicking out.)
    - else, put in locality set and increment R_ij. If R_ij > L_ij, release a page to ''free list''.
  - Not in memory, get a frame, schedule a read, go to “in memory” case.
- Issues
  - thrashing: locality set larger than buffer pool.
  - starvation: on file open, if ∑_i ∑_j L_ij < N, proceed; else block. May need to allow the query that is larger than N.
  - Size of locality size is logical.

Throughput Evaluation

Query #	Query Operators	Selectivity	Access Path of Selection	Join Method	Access Path of Join	CPU Demand	Disk	Memory
Ⅰ	select(A)	1%	clustered index	-	-	Low	Low	Low
Ⅱ	select(B)	2%	non-clustered index	-	-	Low	High	Low
Ⅲ	select(A) join B	2%	clustered index	index join	clustered index on B	High	Low	Low
Ⅳ	select(A′) join B	10%	sequential scan	index join	non-clustered index on B	High	High	Low
Ⅴ	select(A) join B′	3%	clustered index	nested loops	sequential scan over B′	High	Low	High
Ⅵ	select(A) join A′	4%	clustered index	hash join	hash on result of select(A)	High	High	High

A, B: 10K tuples; A′: 1K tuples; B′: 300 tuples; 182 bytes per tuple.
The number of concurrent queries, varied from 1 to 32.
The degree of data sharing with 32 replications of database.
- full sharing: all queries access the same database
- half sharing: every two queries share a copy o the database
- no sharing: every query has its own copy

Composition of Query Mixes

Query Mix	Type Ⅰ	Type Ⅱ	Type Ⅲ	Type Ⅳ	Type Ⅴ	Type Ⅵ
M1	16.67	16.67	16.67	16.67	16.67	16.67
M2	25.00	25.00	12.50	12.50	12.50	12.50
M3	37.50	37.50	6.25	6.25	6.25	6.25

M1: all six query types are equally likely to be requested.
M2: two simple queries (Ⅰ and Ⅱ) are chosen half the time with the same probability.
M3: two simple queries have a compbined probability of 75%.

Locks in a DBMS
- record lock
- latch data structures (index locking)
- page pin count: # users of the page in the frame of the buffer pool.
DB2's buffer mgmt (Love-hate Hits)
- Protocol
  - When an operation requests a page, it also says whether it ''loves'' or ''hates'' the page.
  - Replace hated pages before loved ones.
- By “hate”, we mean the page does not expect to access again, which is decided by access pattern.
- Do not have to remember history decisions.

An Evaluation of Buffer Management Strategies for Relational Database Systems

One-line Summary

Overview/Main Points

Relevance

Flaws