Relaxed Currency and Consistency: How to Say

1
Relaxed Currency and Consistency How to Say
Good Enough in SQL

• Hongfei Guo University of Wisconsin
• Per-Åke Larson Microsoft Research
• Raghu Ramakrishnan University of Wisconsin
• Jonathan Goldstein Microsoft Research

2
Middle-tier Database Caching Scenario
Distributor (SQL Server Replication)
Log Reader
Backend Database
Replicate data from backend (SQL Server)
Update propagation
Remote queries updates
Caching Database
Submit queries updates to the cache
Caching Database
Application Server (IIS)
Application Server (IIS)
Application Server (IIS)
(Figure from LGZ, ICDE04)
3
Problem

• How to tell whether the cached data is good
  enough for an application?
• NO data freshness requirements from the apps!
• NO data freshness guarantees from the caching
  DBMS!

4
Big Picture
Application (e.g., CNN, eBay)
Replica
Application Specific Caching
Caching Middle-ware
5
Our Contributions

• Allow queries to specify relaxed currency and
  consistency (CC) constraints
• Extend SQL to support CC constraints
• Semantics of CC constraints
• Efficiently enforce CC constraints in caching
  DBMS
• Prototyped in SQL Server (MTCache)
• Experiments show only small overhead
• First query-centric approach in DBMS!

6
Terminologies (informal)

• Currency The elapsed time since this copy became
  stale
• Consistency A query result is (snapshot)
  consistent iff it is as if evaluated from a
  snapshot of the master database
• CC Currency Consistency

7
Roadmap

• Background
• Expressing CC constraints
• Enforcing CC constraints
• Experiments and analysis
• Conclusion future work

8
Currency Requirements

• Example 1 In mid-tier caching setting, the
  caching database keeps Books info
• Customer A is browsing it is ok if the data is
  no more than 3 days out of sync
• (Quick response time is preferred)
• Customer B is about to purchase he wants the
  data to be exactly current
• (High data quality is preferred)

9
Currency Requirements

• Example 1 In mid-tier caching setting, the
  caching database keeps Books info
• Customer A is browsing it is ok if the data is
  no more than 3 days out of sync
• (Quick response time is preferred)
• Customer B is about to purchase he wants the
  data to be exactly current
• (High data quality is preferred)

10
Currency Requirements

• Example 1 In mid-tier caching setting, the
  caching database keeps Books info
• Customer A is browsing it is ok if the data is
  no more than 3 days out of sync
• (Quick response time is preferred)
• Customer B is about to purchase he wants the
  data to be exactly current
• (High data quality is preferred)

Different apps may have different currency
requirements for the same query
11
Consistency Requirements
Example 2
SELECT FROM Books B, Reviews R WHERE B.bid
R.bid AND B.title Databases
Different apps may have different consistency
requirements for the same query
The whole query result be consistent
Books be consistent Reviews be consistent
Each book be consistent with its reviews
bid title author bid rid text
1 databases Raghu 1 1
1 databases Raghu 1 2
2 databases Ullman 2 3
12
Proposed SQL Syntax
SELECT FROM Books B, Reviews R WHERE B.bid
R.bid AND B.title Databases
Consistent class
Currency bound
Group by
CURRENCY BOUND 10 min ON (B, R) BY B.bid
CURRENCY BOUND 10 min ON (B),
30 min ON (R)
CURRENCY BOUND 10 min ON (B, R)
bid title author bid rid text
1 databases Raghu 1 1
1 databases Raghu 1 2
2 databases Ullman 2 3
13
Roadmap

• Background
• Expressing CC constraints
• Enforcing CC constraints
• Experiments and analysis
• Conclusion futurework

14
Queries
Queries with Relaxed CC Requirements
Query Optimizer
Execution Engine
Local Materialized Views
Caching DBMS
Results
Results
Extension to MTCache Framework
MTCache Framework LGZ04
15
Queries with Relaxed CC Requirements
Query Optimizer
Execution Engine
Local Materialized Views
Caching DBMS
Results
Results
Extension to MTCache Framework
16
CC Tracking Mechanism

• Consistency tracking ? currency region (CR)
• The unit of update propagation
• Data mutually consistent all the time
• Properties, e.g., est. delay, est. interval
• Currency tracking ? heartbeat table

V 1
V 3
V2
Backend
Cache
V 4
V 5
Cid Timestamp
1
2 12 00
CR1
12 10
12 20
12 30
12 30
12 00
1
12 00
2 12 00
CR2
17
Queries with Relaxed CC Requirements
Queries with Relaxed CC Requirements
Query Optimizer
Currency Region Metadata
Heartbeat Tables
Execution Engine
Local Materialized Views

• The best plan that
• Satisfies consistency requirements
• Includes run-time currency checking

Caching DBMS
Results
Results
Extension to MTCache Framework
18
Extension to the Optimizer

• Compile-time consistency checking
• Run-time currency checking
• Cost estimation

19
Consistency Checking

• Enforced at optimization time
• Immediately prune a sub-plan if it violates
  consistency constraints

Merge join
Q1 s( Books Reviews) CURRENCY 5
ON (Books, Reviews)
Local scanReviews
Remote queryon Books
20
Run-time Currency Checking

• When view V matches expression E

E
V
Currency guard Check if local view V satisfies
currency requirement
21
Cost Estimation

• Cost for the SwitchUnion operator
• C p Clocal (1- p) Cremote Ccg

p probability that the local branch will be
used Clocal cost of execution the local
branch Cremote cost of execution the remote
branch Ccg cost of currency checking
22
Estimating p

• Compute p from two variables
• f estimated refresh interval
• d estimated minimal delay

p 0 if B-d 0 p (B-d)/f if 0 lt
B-d f p 1 if B-d gt f
Given d delay f refresh interval B
currency bound
23
Roadmap

• Background
• Expressing CC constraints
• Enforcing CC constraints
• Experiments and analysis
• Conclusion future work

24
Experimental Setting

• Back-end hosts a TPCD database tpcd1gh with scale
  factor 1.0 (1GB)
• Cache server has a shadow of tpcd1gh
• Two local views cust_prj, order_prj
• Currency region setting

  cid interval delay views
CR1 1 15 5 cust_prj
CR2 2 10 5 orders_prj

25
Queries Used
26
Overhead of Currency Guards
  Local Local Local Remote Remote Remote
  Q1 Q2 Q3 Q1 Q2 Q3
cost (ms) 0.11 0.19 2.39 0.24 0.42 0.90
cost () 15.25 21.30 3.66 3.59 4.31 0.41
Rows 1 6 5975 1 6 5975

27
Overhead of Currency Guards
  Local Local Local Remote Remote Remote
  Q1 Q2 Q3 Q1 Q2 Q3
cost (ms) 0.11 0.19 2.39 0.24 0.42 0.90
cost () 15.25 21.30 3.66 3.59 4.31 0.41
Rows 1 6 5975 1 6 5975
28
Overhead Breakdown
  Setup Run Shutdown Total Total IdealTotal IdealTotal
  ms ms ms ms ms
Q1 0.04 0.06 0.01 0.11 15.25 0.07 11.51
Q2 0.06 0.09 0.01 0.19 21.30 0.10 14.32
Q3 0.01 1.99 0.04 2.39 3.66 0.10 0.16
Inherent Cost of CG crun cshutdown
29
Overhead Breakdown
  Setup Run Shutdown Total Total IdealTotal IdealTotal
  ms ms ms ms ms
Q1 0.04 0.06 0.01 0.11 15.25 0.07 11.51
Q2 0.06 0.09 0.01 0.19 21.30 0.10 14.32
Q3 0.01 1.99 0.04 2.39 3.66 0.10 0.16
Inherent Cost of CG crun cshutdown
30
Roadmap

• Background
• Expressing CC constraints
• Enforcing CC constraints
• Experiments and analysis
• Conclusion future work

31
Conclusion

• Goal provide query results with quality
  guarantees
• Allow queries with explicit CC constraints
• Enforce CC constraints in SQL Server (MTCache
  framework)

32
Future Work

• Improve current prototype
• timeline constraints
• Finer granularity CC constraints
• CC-aware cache management
• Does additional knowledge (CC reqts.) buy us
  something?

33
Questions ?
Thank You !
34
Workload Shifting
Local Workload ()
(a) With relaxed currency bound
(b) With increased refresh interval