Availability, Consistency, and Horizontally Scalable Data Management (SF Bay Area ACM)

19 February 2014 SF Bay Area ACM Meetup
http://www.meetup.com/SF-Bay-ACM/events/158437632/

Video available at: http://www.youtube.com/watch?v=JVEwJyTIjcE

Transcript

1. Data Management Availability, Consistency, Peter Bailis! UC Berkeley, AMPLab! @pbailis with Alan Fekete, Mike Franklin, Ali Ghodsi, Ion Stoica, Joe Hellerstein and Horizontally Scalable
2. DISTRIBUTED DATABASES THE END OF THE END OF SCALABLE AND CORRECT Peter Bailis! UC Berkeley, AMPLab! @pbailis with Alan Fekete, Mike Franklin, Ali Ghodsi, Ion Stoica, Joe Hellerstein
3. A portrait of big services
4. A portrait of big services
5. A portrait of big services
6. A portrait of big services
7. A portrait of big services
8. A portrait of big services
9. A portrait of big services
10. A portrait of big services
11. stateless! horizontally scalable A portrait of big services
12. stateless! horizontally scalable concurrent,! stateful! durable A portrait of big services
14. Users care about the correctness of their applications
15. Users care about the correctness of their applications “usernames should be unique”
16. Users care about the correctness of their applications “usernames should be unique” “each patient should have a attending doctor”
17. Users care about the correctness of their applications “usernames should be unique” “each patient should have a attending doctor” “account balances should be positive”
19. Classic answer: use ACID transactions
20. Classic answer: use ACID transactions Equivalent
    Serial
    Execution
21. Classic answer: use ACID transactions Equivalent
    Serial
    Execution
22. Classic answer: use ACID transactions Equivalent
    Serial
    Execution isolation provides correctness
23. Classic answer: use ACID transactions Equivalent
    Serial
    Execution isolation provides correctness ACID
24. Classic answer: use ACID transactions Equivalent
    Serial
    Execution isolation provides correctness ACID ACID
25. Classic answer: use ACID transactions Equivalent
    Serial
    Execution isolation provides correctness ACID ACID
26. Under the hood: ACID (conflict serializability) reasons about low-level read/write traces
27. Under the hood: ACID (conflict serializability) reasons about low-level read/write traces
28. Under the hood: ACID (conflict serializability) reasons about low-level read/write traces
29. Under the hood: ACID (conflict serializability) reasons about low-level read/write traces
30. Under the hood: ACID (conflict serializability) reasons about low-level read/write traces
31. For any two operations to the same data item, if at least one is a write, operations might conflict T2 T1 T3 ww(c) rw(a) rw(c) rw(b) T3 T1 T2 Under the hood: ACID (conflict serializability) reasons about low-level read/write traces
32. For any two operations to the same data item, if at least one is a write, operations might conflict T2 T1 T3 ww(c) rw(a) rw(c) rw(b) T3 T1 T2 Under the hood: ACID (conflict serializability) reasons about low-level read/write traces END RESULT:! A MYOPIC APPROACH TO CORRECTNESS
33. END RESULT:! A MYOPIC APPROACH TO CORRECTNESS
34. END RESULT:! A MYOPIC APPROACH TO CORRECTNESS COST:! SERIALIZABILITY REQUIRES COORDINATION
35. END RESULT:! A MYOPIC APPROACH TO CORRECTNESS COST:! SERIALIZABILITY REQUIRES COORDINATION synchronous coordination =
36. END RESULT:! A MYOPIC APPROACH TO CORRECTNESS COST:! SERIALIZABILITY REQUIRES COORDINATION synchronous coordination = stalls during network partitions =
37. END RESULT:! A MYOPIC APPROACH TO CORRECTNESS COST:! SERIALIZABILITY REQUIRES COORDINATION synchronous coordination = stalls during network partitions = RTT latency during operations =
38. END RESULT:! A MYOPIC APPROACH TO CORRECTNESS COST:! SERIALIZABILITY REQUIRES COORDINATION synchronous coordination = stalls during network partitions = RTT latency during operations = possible stall during concurrent access
39. THE ACID SCALABILITY WALL
40. 2 4 6 8 10 12 14 16 18 20 Number of Servers in 2PC 0 200 400 600 800 1000 1200 Maximum Throughput (txns/s) LOCAL! DATACENTER max 1200 txn/s THE ACID SCALABILITY WALL
41. +OR +CA +IR +SP +TO +SI +SY Participating Datacenters (+VA) 2 4 6 8 10 12 Maximum Throughput (txn/s) 2 4 6 8 10 12 14 16 18 20 Number of Servers in 2PC 0 200 400 600 800 1000 1200 Maximum Throughput (txns/s) LOCAL! DATACENTER MULTI-! DATACENTER! max 1200 txn/s max 12 txn/s THE ACID SCALABILITY WALL
42. +OR +CA +IR +SP +TO +SI +SY Participating Datacenters (+VA) 2 4 6 8 10 12 Maximum Throughput (txn/s) 2 4 6 8 10 12 14 16 18 20 Number of Servers in 2PC 0 200 400 600 800 1000 1200 Maximum Throughput (txns/s) LOCAL! DATACENTER MULTI-! DATACENTER! max 1200 txn/s max 12 txn/s THE ACID SCALABILITY WALL decentralized (optimized) 2PC SERIALIZABILITY REQUIRES COORDINATION decentralized (optimized) 2PC
44. HANA
45. do not support! SSI/serializability HANA
46. do not support! SSI/serializability HANA Actian Ingres YES Aerospike NO Persistit NO Clustrix NO Greenplum YES IBM DB2 YES IBM Informix YES MySQL YES MemSQL NO MS SQL Server YES NuoDB NO Oracle 11G NO Oracle BDB YES Oracle BDB JE YES Postgres 9.2.2 YES SAP Hana NO ScaleDB NO VoltDB YES 8/18 databases! surveyed did not 15/18 used! weaker models! by default “Highly Available Transactions: Virtues and Limitations,” VLDB 2014
47. do not support! SSI/serializability HANA Actian Ingres YES Aerospike NO Persistit NO Clustrix NO Greenplum YES IBM DB2 YES IBM Informix YES MySQL YES MemSQL NO MS SQL Server YES NuoDB NO Oracle 11G NO Oracle BDB YES Oracle BDB JE YES Postgres 9.2.2 YES SAP Hana NO ScaleDB NO VoltDB YES 8/18 databases! surveyed did not 15/18 used! weaker models! by default “Highly Available Transactions: Virtues and Limitations,” VLDB 2014
49. synchronous coordination =! stalls during network partitions = RTT latency during operations = possible stall during concurrent access
50. no (or asynchronous) coordination = synchronous coordination =! stalls during network partitions = RTT latency during operations = possible stall during concurrent access
51. no (or asynchronous) coordination = Gilbert and Lynch “High Availability” = synchronous coordination =! stalls during network partitions = RTT latency during operations = possible stall during concurrent access
52. no (or asynchronous) coordination = Gilbert and Lynch “High Availability” = low latency (no RTT) = synchronous coordination =! stalls during network partitions = RTT latency during operations = possible stall during concurrent access
53. no (or asynchronous) coordination = Gilbert and Lynch “High Availability” = low latency (no RTT) = indefinite horizontal scaling synchronous coordination =! stalls during network partitions = RTT latency during operations = possible stall during concurrent access
54. no (or asynchronous) coordination = Gilbert and Lynch “High Availability” = low latency (no RTT) = indefinite horizontal scaling (even for a single record; true scalability) synchronous coordination =! stalls during network partitions = RTT latency during operations = possible stall during concurrent access
55. no (or asynchronous) coordination = Gilbert and Lynch “High Availability” = low latency (no RTT) = indefinite horizontal scaling (even for a single record; true scalability) synchronous coordination =! stalls during network partitions = RTT latency during operations = possible stall during concurrent access
56. no (or asynchronous) coordination = Gilbert and Lynch “High Availability” = low latency (no RTT) = indefinite horizontal scaling (even for a single record; true scalability) benefits also apply to concurrent access in single-node systems synchronous coordination =! stalls during network partitions = RTT latency during operations = possible stall during concurrent access
57. no (or asynchronous) coordination = Gilbert and Lynch “High Availability” = low latency (no RTT) = indefinite horizontal scaling (even for a single record; true scalability) benefits also apply to concurrent access in single-node systems synchronous coordination =! stalls during network partitions = RTT latency during operations = possible stall during concurrent access BUT OFTEN GIVE UP CORRECTNESS!!
58. CORRECTNESS vs. SCALABILITY
59. CORRECTNESS vs. SCALABILITY SERIALIZABILITY
60. CORRECTNESS vs. SCALABILITY SERIALIZABILITY Our insight: serializability is sufficient for correctness! ! ! ! ! but is not necessary! ! ! !
61. CORRECTNESS vs. SCALABILITY SERIALIZABILITY Our insight: serializability is sufficient for correctness! ! ! ! ! but is not necessary! ! ! !
62. CORRECTNESS vs. SCALABILITY SERIALIZABILITY Our solution: coordination avoidance Our insight: serializability is sufficient for correctness! ! ! ! ! but is not necessary! ! ! !
63. CORRECTNESS vs. SCALABILITY SERIALIZABILITY Our solution: coordination avoidance Our insight: serializability is sufficient for correctness! ! ! ! ! but is not necessary! ! ! ! Only coordinate when necessary
64. CORRECTNESS vs. SCALABILITY SERIALIZABILITY Our solution: coordination avoidance CORRECTNESS and SCALABILITY Our insight: serializability is sufficient for correctness! ! ! ! ! but is not necessary! ! ! ! Only coordinate when necessary
65. CORRECTNESS vs. SCALABILITY SERIALIZABILITY Our solution: coordination avoidance CORRECTNESS and SCALABILITY Our insight: serializability is sufficient for correctness! ! ! ! ! but is not necessary! ! ! ! Only coordinate when necessary
66. CORRECTNESS vs. SCALABILITY SERIALIZABILITY Our solution: coordination avoidance CORRECTNESS and SCALABILITY Our insight: serializability is sufficient for correctness! ! ! ! ! but is not necessary! ! ! ! Only coordinate when necessary Ask applications for invariants
67. CORRECTNESS vs. SCALABILITY SERIALIZABILITY Our solution: coordination avoidance CORRECTNESS and SCALABILITY Our insight: serializability is sufficient for correctness! ! ! ! ! but is not necessary! ! ! ! Only coordinate when necessary Ask applications for invariants Invariants determine:! ! When is coordination needed?! ! How much coordination is required?! !
68. Ask applications for invariants
69. Invariant:! user IDs are unique Ask applications for invariants
70. Invariant:! user IDs are unique Ask applications for invariants
71. Invariant:! user IDs are unique Ask applications for invariants
72. Invariant:! user IDs are unique Ask applications for invariants
73. Invariant:! user IDs are unique Ask applications for invariants
75. Invariant: each employee is in a department Operations: add employees
76. Invariant: each employee is in a department Operations: add employees Anomaly (to avoid):
77. Invariant: each employee is in a department Operations: add employees l_emp = employees.find(id=“louise”) ! Anomaly (to avoid):
78. Invariant: each employee is in a department Operations: add employees l_emp = employees.find(id=“louise”) ! Anomaly (to avoid):
79. Invariant: each employee is in a department Operations: add employees l_emp = employees.find(id=“louise”) ! l_dept = dept.find(l_emp.dept) ! Anomaly (to avoid):
80. Invariant: each employee is in a department Operations: add employees l_emp = employees.find(id=“louise”) ! l_dept = dept.find(l_emp.dept) ! ENORECORD Anomaly (to avoid):
81. Invariant: each employee is in a department Operations: add employees
82. Invariant: each employee is in a department Operations: add employees
83. Invariant: each employee is in a department Operations: add employees
84. employees = {} dept = {{“ops”:1}, {“dev”:2}} Invariant: each employee is in a department Operations: add employees
85. employees = {} dept = {{“ops”:1}, {“dev”:2}} Invariant: each employee is in a department Operations: add employees
86. employees = {} dept = {{“ops”:1}, {“dev”:2}} Invariant: each employee is in a department Operations: add employees d1 = dept.find(“ops”) employees.add({“Harry”:d1})
87. employees = {} dept = {{“ops”:1}, {“dev”:2}} Invariant: each employee is in a department Operations: add employees d1 = dept.find(“ops”) employees.add({“Harry”:d1})
88. employees = {} dept = {{“ops”:1}, {“dev”:2}} Invariant: each employee is in a department Operations: add employees d2 = dept.find(“dev”) employees.add({“Sue”:d2}) d1 = dept.find(“ops”) employees.add({“Harry”:d1})
89. employees = {} dept = {{“ops”:1}, {“dev”:2}} Invariant: each employee is in a department Operations: add employees d2 = dept.find(“dev”) employees.add({“Sue”:d2}) d1 = dept.find(“ops”) employees.add({“Harry”:d1})
90. employees = {{“Harry”:1}, {“Sue”:2}} dept = {{“ops”:1}, {“dev”:2}} employees = {} dept = {{“ops”:1}, {“dev”:2}} Invariant: each employee is in a department Operations: add employees d2 = dept.find(“dev”) employees.add({“Sue”:d2}) d1 = dept.find(“ops”) employees.add({“Harry”:d1})
91. employees = {{“Harry”:1}, {“Sue”:2}} dept = {{“ops”:1}, {“dev”:2}} employees = {} dept = {{“ops”:1}, {“dev”:2}} Invariant: each employee is in a department Operations: add employees d2 = dept.find(“dev”) employees.add({“Sue”:d2}) d1 = dept.find(“ops”) employees.add({“Harry”:d1}) Invariant holds!
93. Invariant: only one ops on staff at a time Operations: change staffing
94. Anomaly (to avoid): Invariant: only one ops on staff at a time Operations: change staffing
95. on_duty = employees.find(staffed=”T”) ! Anomaly (to avoid): Invariant: only one ops on staff at a time Operations: change staffing
96. on_duty = employees.find(staffed=”T”) ! Anomaly (to avoid): Invariant: only one ops on staff at a time Operations: change staffing
97. on_duty = employees.find(staffed=”T”) ! assert(len(on_duty) == 1) ! Anomaly (to avoid): Invariant: only one ops on staff at a time Operations: change staffing
98. on_duty = employees.find(staffed=”T”) ! assert(len(on_duty) == 1) ! ASSERTION FAILS Anomaly (to avoid): Invariant: only one ops on staff at a time Operations: change staffing
99. Invariant: only one ops on staff at a time Operations: change staffing
100. staff = {“Laura”:T, “Harry”:F, “Gary”:F} Invariant: only one ops on staff at a time Operations: change staffing
101. staff = {“Laura”:T, “Harry”:F, “Gary”:F} Invariant: only one ops on staff at a time Operations: change staffing
102. staff = {“Laura”:T, “Harry”:F, “Gary”:F} staff.set({“Laura”:F}, {“Harry”:T}) Invariant: only one ops on staff at a time Operations: change staffing
103. staff = {“Laura”:T, “Harry”:F, “Gary”:F} staff.set({“Laura”:F}, {“Harry”:T}) Invariant: only one ops on staff at a time Operations: change staffing
104. staff = {“Laura”:T, “Harry”:F, “Gary”:F} staff.set({“Laura”:F}, “Gary”:T}) staff.set({“Laura”:F}, {“Harry”:T}) Invariant: only one ops on staff at a time Operations: change staffing
105. staff = {“Laura”:T, “Harry”:F, “Gary”:F} staff.set({“Laura”:F}, “Gary”:T}) staff.set({“Laura”:F}, {“Harry”:T}) Invariant: only one ops on staff at a time Operations: change staffing
106. staff = {“Laura”:T, “Harry”:F, “Gary”:F} staff.set({“Laura”:F}, “Gary”:T}) staff.set({“Laura”:F}, {“Harry”:T}) Invariant: only one ops on staff at a time Operations: change staffing
107. staff = {“Laura”:T, “Harry”:F, “Gary”:F} staff.set({“Laura”:F}, “Gary”:T}) staff.set({“Laura”:F}, {“Harry”:T}) Invariant violated! staff = {“Laura”:F, “Harry”:T, “Gary”:T} Invariant: only one ops on staff at a time Operations: change staffing
115. SAFETY correctness always guaranteed
116. SAFETY correctness always guaranteed LIVENESS database states agree (converge)
117. I-confluence is necessary and sufficient for simultaneously maintaining application-level consistency, availability, convergence, and coordination-freedom Invariant confluence: formal characterization of safe, coordination-free execution
118. To maintain consistency... I-confluence is necessary and sufficient for simultaneously maintaining application-level consistency, availability, convergence, and coordination-freedom Invariant confluence: formal characterization of safe, coordination-free execution
119. Sufficient? Necessary? App-Level? Conflict Serializability Yes No No Invariant Confluence Yes Yes Yes State-based Commutativity Yes* No Depends To maintain consistency... I-confluence is necessary and sufficient for simultaneously maintaining application-level consistency, availability, convergence, and coordination-freedom Invariant confluence: formal characterization of safe, coordination-free execution
120. Sufficient? Necessary? App-Level? Conflict Serializability Yes No No Invariant Confluence Yes Yes Yes State-based Commutativity Yes* No Depends To maintain consistency... I-confluence is necessary and sufficient for simultaneously maintaining application-level consistency, availability, convergence, and coordination-freedom Invariant confluence: formal characterization of safe, coordination-free execution
121. Sufficient? Necessary? App-Level? Conflict Serializability Yes No No Invariant Confluence Yes Yes Yes State-based Commutativity Yes* No Depends To maintain consistency... I-confluence is necessary and sufficient for simultaneously maintaining application-level consistency, availability, convergence, and coordination-freedom Invariant confluence: formal characterization of safe, coordination-free execution
122. Sufficient? Necessary? App-Level? Conflict Serializability Yes No No Invariant Confluence Yes Yes Yes State-based Commutativity Yes* No Depends To maintain consistency... I-confluence is necessary and sufficient for simultaneously maintaining application-level consistency, availability, convergence, and coordination-freedom Invariant confluence: formal characterization of safe, coordination-free execution
123. Sufficient? Necessary? App-Level? Conflict Serializability Yes No No Invariant Confluence Yes Yes Yes State-based Commutativity Yes* No Depends To maintain consistency... I-confluence is necessary and sufficient for simultaneously maintaining application-level consistency, availability, convergence, and coordination-freedom Invariant confluence: formal characterization of safe, coordination-free execution
124. Sufficient? Necessary? App-Level? Conflict Serializability Yes No No Invariant Confluence Yes Yes Yes State-based Commutativity Yes* No Depends To maintain consistency... I-confluence is necessary and sufficient for simultaneously maintaining application-level consistency, availability, convergence, and coordination-freedom Invariant confluence: formal characterization of safe, coordination-free execution
125. Sufficient? Necessary? App-Level? Conflict Serializability Yes No No Invariant Confluence Yes Yes Yes State-based Commutativity Yes* No Depends To maintain consistency... I-confluence is necessary and sufficient for simultaneously maintaining application-level consistency, availability, convergence, and coordination-freedom Invariant confluence: formal characterization of safe, coordination-free execution
126. Sufficient? Necessary? App-Level? Conflict Serializability Yes No No Invariant Confluence Yes Yes Yes State-based Commutativity Yes* No Depends To maintain consistency... I-confluence is necessary and sufficient for simultaneously maintaining application-level consistency, availability, convergence, and coordination-freedom Invariant confluence: formal characterization of safe, coordination-free execution
127. Formal framework for reasoning about application coordination requirements
128. Formal framework for reasoning about application coordination requirements Coordination depends on combination of:! - expressiveness of operations! - strength of invariants
129. Formal framework for reasoning about application coordination requirements Coordination depends on combination of:! - expressiveness of operations! - strength of invariants STRENGTH OF INVARIANTS EXPRESSIVENESS OF OPERATIONS *Okay, so this is simplified, and there isn’t really a linear order on either axis (rather, it’s more about equivalence classes), but humor me here...
130. Formal framework for reasoning about application coordination requirements Coordination depends on combination of:! - expressiveness of operations! - strength of invariants STRENGTH OF INVARIANTS EXPRESSIVENESS OF OPERATIONS *Okay, so this is simplified, and there isn’t really a linear order on either axis (rather, it’s more about equivalence classes), but humor me here... COORDINATION! REQUIRED! COORDINATION-FREE
131. Formal framework for reasoning about application coordination requirements Coordination depends on combination of:! - expressiveness of operations! - strength of invariants STRENGTH OF INVARIANTS EXPRESSIVENESS OF OPERATIONS *Okay, so this is simplified, and there isn’t really a linear order on either axis (rather, it’s more about equivalence classes), but humor me here... COORDINATION! REQUIRED! COORDINATION-FREE
132. Formal framework for reasoning about application coordination requirements Coordination depends on combination of:! - expressiveness of operations! - strength of invariants STRENGTH OF INVARIANTS EXPRESSIVENESS OF OPERATIONS *Okay, so this is simplified, and there isn’t really a linear order on either axis (rather, it’s more about equivalence classes), but humor me here... COORDINATION! REQUIRED! COORDINATION-FREE
133. Formal framework for reasoning about application coordination requirements Coordination depends on combination of:! - expressiveness of operations! - strength of invariants STRENGTH OF INVARIANTS EXPRESSIVENESS OF OPERATIONS *Okay, so this is simplified, and there isn’t really a linear order on either axis (rather, it’s more about equivalence classes), but humor me here... COORDINATION! REQUIRED! COORDINATION-FREE
134. Can apply the I-confluence test to! standard SQL for program analysis Invariant Operation C.F. ? Equality, Inequality Any ??? Generate unique ID Any ??? Specify unique ID Insert ??? >! Increment ??? >! Decrement ??? < Decrement ??? < Increment ??? Foreign Key Insert ??? Foreign Key Delete ??? Secondary Indexing Any ??? Materialized Views Any ??? AUTO_INCREMENT Insert ???
135. Can apply the I-confluence test to! standard SQL for program analysis Invariant Operation C.F. ? Equality, Inequality Any ??? Generate unique ID Any ??? Specify unique ID Insert ??? >! Increment ??? >! Decrement ??? < Decrement ??? < Increment ??? Foreign Key Insert ??? Foreign Key Delete ??? Secondary Indexing Any ??? Materialized Views Any ??? AUTO_INCREMENT Insert ???
136. Constraint: record IDs are unique
137. Constraint: record IDs are unique DECLARE TABLE users (! ID int UNIQUE,! FirstName string,! LastName string )
138. Constraint: record IDs are unique DECLARE TABLE users (! ID int UNIQUE,! FirstName string,! LastName string ) Anomaly! (to avoid):
139. Constraint: record IDs are unique DECLARE TABLE users (! ID int UNIQUE,! FirstName string,! LastName string ) Anomaly! (to avoid):
140. Constraint: record IDs are unique DECLARE TABLE users (! ID int UNIQUE,! FirstName string,! LastName string ) Anomaly! (to avoid):
141. Constraint: record IDs are unique DECLARE TABLE users (! ID int UNIQUE,! FirstName string,! LastName string )
142. Operation: insert record with specific ID INSERT INTO users (ID, firstname, lastname)! VALUES (1, “Leslie”, “Lamport”) Constraint: record IDs are unique DECLARE TABLE users (! ID int UNIQUE,! FirstName string,! LastName string )
143. NOT C-FREE Operation: insert record with specific ID INSERT INTO users (ID, firstname, lastname)! VALUES (1, “Leslie”, “Lamport”) Constraint: record IDs are unique DECLARE TABLE users (! ID int UNIQUE,! FirstName string,! LastName string )
144. Operation: insert record INSERT INTO users (firstname, lastname)! VALUES (“Leslie”, “Lamport”) NOT C-FREE Operation: insert record with specific ID INSERT INTO users (ID, firstname, lastname)! VALUES (1, “Leslie”, “Lamport”) Constraint: record IDs are unique DECLARE TABLE users (! ID int UNIQUE,! FirstName string,! LastName string )
145. let the DB decide the ID; use node ID or UUID C-FREE! Operation: insert record INSERT INTO users (firstname, lastname)! VALUES (“Leslie”, “Lamport”) NOT C-FREE Operation: insert record with specific ID INSERT INTO users (ID, firstname, lastname)! VALUES (1, “Leslie”, “Lamport”) Constraint: record IDs are unique DECLARE TABLE users (! ID int UNIQUE,! FirstName string,! LastName string )
146. Foreign key constraints
147. Foreign key constraints DECLARE TABLE users (! U_ID int UNIQUE,! D_ID int! UserName string ! FOREIGN KEY (D_ID)! REFERENCES department(D_ID) )
148. Foreign key constraints DECLARE TABLE users (! U_ID int UNIQUE,! D_ID int! UserName string ! FOREIGN KEY (D_ID)! REFERENCES department(D_ID) ) DECLARE TABLE department (! D_ID int UNIQUE,! DeptName string )
149. Foreign key constraints DECLARE TABLE users (! U_ID int UNIQUE,! D_ID int! UserName string ! FOREIGN KEY (D_ID)! REFERENCES department(D_ID) ) DECLARE TABLE department (! D_ID int UNIQUE,! DeptName string )
150. Foreign key constraints DECLARE TABLE users (! U_ID int UNIQUE,! D_ID int! UserName string ! FOREIGN KEY (D_ID)! REFERENCES department(D_ID) ) DECLARE TABLE department (! D_ID int UNIQUE,! DeptName string ) NEW_D_ID = INSERT INTO department VALUES (“awesome division”);! INSERT INTO users (D_ID, UserName) VALUES (NEW_D_ID, “lamport”);!
151. Foreign key constraints DECLARE TABLE users (! U_ID int UNIQUE,! D_ID int! UserName string ! FOREIGN KEY (D_ID)! REFERENCES department(D_ID) ) DECLARE TABLE department (! D_ID int UNIQUE,! DeptName string ) NEW_D_ID = INSERT INTO department VALUES (“awesome division”);! INSERT INTO users (D_ID, UserName) VALUES (NEW_D_ID, “lamport”);!
152. Foreign key constraints DECLARE TABLE users (! U_ID int UNIQUE,! D_ID int! UserName string ! FOREIGN KEY (D_ID)! REFERENCES department(D_ID) ) DECLARE TABLE department (! D_ID int UNIQUE,! DeptName string ) NEW_D_ID = INSERT INTO department VALUES (“awesome division”);! INSERT INTO users (D_ID, UserName) VALUES (NEW_D_ID, “lamport”);! Anomaly (to avoid):
153. Foreign key constraints DECLARE TABLE users (! U_ID int UNIQUE,! D_ID int! UserName string ! FOREIGN KEY (D_ID)! REFERENCES department(D_ID) ) DECLARE TABLE department (! D_ID int UNIQUE,! DeptName string ) NEW_D_ID = INSERT INTO department VALUES (“awesome division”);! INSERT INTO users (D_ID, UserName) VALUES (NEW_D_ID, “lamport”);! Anomaly (to avoid): “lamport” has no department! read lamport record; lookup lamport.D_ID returns NULL
154. Foreign key constraints DECLARE TABLE users (! U_ID int UNIQUE,! D_ID int! UserName string ! FOREIGN KEY (D_ID)! REFERENCES department(D_ID) ) DECLARE TABLE department (! D_ID int UNIQUE,! DeptName string ) NEW_D_ID = INSERT INTO department VALUES (“awesome division”);! INSERT INTO users (D_ID, UserName) VALUES (NEW_D_ID, “lamport”);! Anomaly (to avoid): “lamport” has no department! read lamport record; lookup lamport.D_ID returns NULL I-confluence insight:! cannot be violated by inserts!
155. Foreign key constraints DECLARE TABLE users (! U_ID int UNIQUE,! D_ID int! UserName string ! FOREIGN KEY (D_ID)! REFERENCES department(D_ID) ) DECLARE TABLE department (! D_ID int UNIQUE,! DeptName string ) NEW_D_ID = INSERT INTO department VALUES (“awesome division”);! INSERT INTO users (D_ID, UserName) VALUES (NEW_D_ID, “lamport”);! Anomaly (to avoid): “lamport” has no department! read lamport record; lookup lamport.D_ID returns NULL I-confluence insight:! cannot be violated by inserts! …but be careful about implementation! many ways to use coordination to enforce coordination-free semantics
156. Foreign key constraints DECLARE TABLE users (! U_ID int UNIQUE,! D_ID int! UserName string ! FOREIGN KEY (D_ID)! REFERENCES department(D_ID) ) DECLARE TABLE department (! D_ID int UNIQUE,! DeptName string ) NEW_D_ID = INSERT INTO department VALUES (“awesome division”);! INSERT INTO users (D_ID, UserName) VALUES (NEW_D_ID, “lamport”);!
157. users shard department shard Foreign key constraints DECLARE TABLE users (! U_ID int UNIQUE,! D_ID int! UserName string ! FOREIGN KEY (D_ID)! REFERENCES department(D_ID) ) DECLARE TABLE department (! D_ID int UNIQUE,! DeptName string ) NEW_D_ID = INSERT INTO department VALUES (“awesome division”);! INSERT INTO users (D_ID, UserName) VALUES (NEW_D_ID, “lamport”);!
158. users shard department shard Foreign key constraints DECLARE TABLE users (! U_ID int UNIQUE,! D_ID int! UserName string ! FOREIGN KEY (D_ID)! REFERENCES department(D_ID) ) DECLARE TABLE department (! D_ID int UNIQUE,! DeptName string ) NEW_D_ID = INSERT INTO department VALUES (“awesome division”);! INSERT INTO users (D_ID, UserName) VALUES (NEW_D_ID, “lamport”);!
159. users shard department shard Foreign key constraints DECLARE TABLE users (! U_ID int UNIQUE,! D_ID int! UserName string ! FOREIGN KEY (D_ID)! REFERENCES department(D_ID) ) DECLARE TABLE department (! D_ID int UNIQUE,! DeptName string ) NEW_D_ID = INSERT INTO department VALUES (“awesome division”);! INSERT INTO users (D_ID, UserName) VALUES (NEW_D_ID, “lamport”);! Visible to all readers Visible to all readers
160. users shard department shard Foreign key constraints DECLARE TABLE users (! U_ID int UNIQUE,! D_ID int! UserName string ! FOREIGN KEY (D_ID)! REFERENCES department(D_ID) ) DECLARE TABLE department (! D_ID int UNIQUE,! DeptName string ) NEW_D_ID = INSERT INTO department VALUES (“awesome division”);! INSERT INTO users (D_ID, UserName) VALUES (NEW_D_ID, “lamport”);! Visible to all readers Visible to all readers Not yet visible to all readers Not yet visible to all readers
161. users shard department shard Foreign key constraints DECLARE TABLE users (! U_ID int UNIQUE,! D_ID int! UserName string ! FOREIGN KEY (D_ID)! REFERENCES department(D_ID) ) DECLARE TABLE department (! D_ID int UNIQUE,! DeptName string ) NEW_D_ID = INSERT INTO department VALUES (“awesome division”);! INSERT INTO users (D_ID, UserName) VALUES (NEW_D_ID, “lamport”);! Visible to all readers Visible to all readers Not yet visible to all readers Not yet visible to all readers
162. users shard department shard (342, “awesome division”) Foreign key constraints DECLARE TABLE users (! U_ID int UNIQUE,! D_ID int! UserName string ! FOREIGN KEY (D_ID)! REFERENCES department(D_ID) ) DECLARE TABLE department (! D_ID int UNIQUE,! DeptName string ) NEW_D_ID = INSERT INTO department VALUES (“awesome division”);! INSERT INTO users (D_ID, UserName) VALUES (NEW_D_ID, “lamport”);! Visible to all readers Visible to all readers Not yet visible to all readers Not yet visible to all readers
163. users shard department shard (342, “awesome division”) Foreign key constraints DECLARE TABLE users (! U_ID int UNIQUE,! D_ID int! UserName string ! FOREIGN KEY (D_ID)! REFERENCES department(D_ID) ) DECLARE TABLE department (! D_ID int UNIQUE,! DeptName string ) NEW_D_ID = INSERT INTO department VALUES (“awesome division”);! INSERT INTO users (D_ID, UserName) VALUES (NEW_D_ID, “lamport”);! Visible to all readers Visible to all readers Not yet visible to all readers Not yet visible to all readers
164. users shard department shard (342, “awesome division”) Foreign key constraints DECLARE TABLE users (! U_ID int UNIQUE,! D_ID int! UserName string ! FOREIGN KEY (D_ID)! REFERENCES department(D_ID) ) DECLARE TABLE department (! D_ID int UNIQUE,! DeptName string ) NEW_D_ID = INSERT INTO department VALUES (“awesome division”);! INSERT INTO users (D_ID, UserName) VALUES (NEW_D_ID, “lamport”);! Visible to all readers Visible to all readers Not yet visible to all readers Not yet visible to all readers (???, 342, “lamport”)
165. users shard department shard (342, “awesome division”) Foreign key constraints DECLARE TABLE users (! U_ID int UNIQUE,! D_ID int! UserName string ! FOREIGN KEY (D_ID)! REFERENCES department(D_ID) ) DECLARE TABLE department (! D_ID int UNIQUE,! DeptName string ) NEW_D_ID = INSERT INTO department VALUES (“awesome division”);! INSERT INTO users (D_ID, UserName) VALUES (NEW_D_ID, “lamport”);! Visible to all readers Visible to all readers Not yet visible to all readers Not yet visible to all readers (402, 342, “lamport”)
166. users shard department shard (342, “awesome division”) Foreign key constraints DECLARE TABLE users (! U_ID int UNIQUE,! D_ID int! UserName string ! FOREIGN KEY (D_ID)! REFERENCES department(D_ID) ) DECLARE TABLE department (! D_ID int UNIQUE,! DeptName string ) NEW_D_ID = INSERT INTO department VALUES (“awesome division”);! INSERT INTO users (D_ID, UserName) VALUES (NEW_D_ID, “lamport”);! Visible to all readers Visible to all readers Not yet visible to all readers Not yet visible to all readers (402, 342, “lamport”)
167. users shard department shard (342, “awesome division”) Foreign key constraints DECLARE TABLE users (! U_ID int UNIQUE,! D_ID int! UserName string ! FOREIGN KEY (D_ID)! REFERENCES department(D_ID) ) DECLARE TABLE department (! D_ID int UNIQUE,! DeptName string ) NEW_D_ID = INSERT INTO department VALUES (“awesome division”);! INSERT INTO users (D_ID, UserName) VALUES (NEW_D_ID, “lamport”);! Visible to all readers Visible to all readers Not yet visible to all readers Not yet visible to all readers (402, 342, “lamport”)
168. users shard department shard (342, “awesome division”) Foreign key constraints DECLARE TABLE users (! U_ID int UNIQUE,! D_ID int! UserName string ! FOREIGN KEY (D_ID)! REFERENCES department(D_ID) ) DECLARE TABLE department (! D_ID int UNIQUE,! DeptName string ) NEW_D_ID = INSERT INTO department VALUES (“awesome division”);! INSERT INTO users (D_ID, UserName) VALUES (NEW_D_ID, “lamport”);! Visible to all readers Visible to all readers Not yet visible to all readers Not yet visible to all readers (402, 342, “lamport”)
169. users shard department shard (342, “awesome division”) Foreign key constraints DECLARE TABLE users (! U_ID int UNIQUE,! D_ID int! UserName string ! FOREIGN KEY (D_ID)! REFERENCES department(D_ID) ) DECLARE TABLE department (! D_ID int UNIQUE,! DeptName string ) NEW_D_ID = INSERT INTO department VALUES (“awesome division”);! INSERT INTO users (D_ID, UserName) VALUES (NEW_D_ID, “lamport”);! Visible to all readers Visible to all readers Not yet visible to all readers Not yet visible to all readers (402, 342, “lamport”)
170. users shard department shard (342, “awesome division”) Foreign key constraints DECLARE TABLE users (! U_ID int UNIQUE,! D_ID int! UserName string ! FOREIGN KEY (D_ID)! REFERENCES department(D_ID) ) DECLARE TABLE department (! D_ID int UNIQUE,! DeptName string ) NEW_D_ID = INSERT INTO department VALUES (“awesome division”);! INSERT INTO users (D_ID, UserName) VALUES (NEW_D_ID, “lamport”);! Visible to all readers Visible to all readers Not yet visible to all readers Not yet visible to all readers (402, 342, “lamport”) 2 RTT writes (prepare and make visible)! Between 1-2 RTTs for reads! Basic idea: store metadata to record sibling writes
171. users shard department shard (342, “awesome division”) Foreign key constraints DECLARE TABLE users (! U_ID int UNIQUE,! D_ID int! UserName string ! FOREIGN KEY (D_ID)! REFERENCES department(D_ID) ) DECLARE TABLE department (! D_ID int UNIQUE,! DeptName string ) NEW_D_ID = INSERT INTO department VALUES (“awesome division”);! INSERT INTO users (D_ID, UserName) VALUES (NEW_D_ID, “lamport”);! Visible to all readers Visible to all readers Not yet visible to all readers Not yet visible to all readers (402, 342, “lamport”) 2 RTT writes (prepare and make visible)! Between 1-2 RTTs for reads! Basic idea: store metadata to record sibling writes Read Atomic Multi-Partition ! Transactions, SIGMOD 2014
172. Invariant Operation C.F. ? Equality, Inequality Any ??? Generate unique ID Any ??? Specify unique ID Insert ??? >! Increment ??? >! Decrement ??? < Decrement ??? < Increment ??? Foreign Key Insert ??? Foreign Key Delete ??? Secondary Indexing Any ??? Materialized Views Any ??? AUTO_INCREMENT Insert ??? Can apply the I-confluence test to! standard SQL for program analysis
173. Invariant Operation C.F. ? Equality, Inequality Any Y Generate unique ID Any Y Specify unique ID Insert N >! Increment Y >! Decrement N < Decrement Y < Increment N Foreign Key Insert Y Foreign Key Delete Y* Secondary Indexing Any Y Materialized Views Any Y! AUTO_INCREMENT Insert N Can apply the I-confluence test to! standard SQL for program analysis
174. Eventual consistency Invariant Operation C.F. ? Equality, Inequality Any Y Generate unique ID Any Y Specify unique ID Insert N >! Increment Y >! Decrement N < Decrement Y < Increment N Foreign Key Insert Y Foreign Key Delete Y* Secondary Indexing Any Y Materialized Views Any Y! AUTO_INCREMENT Insert N Can apply the I-confluence test to! standard SQL for program analysis
175. Eventual consistency Invariant Operation C.F. ? Equality, Inequality Any Y Generate unique ID Any Y Specify unique ID Insert N >! Increment Y >! Decrement N < Decrement Y < Increment N Foreign Key Insert Y Foreign Key Delete Y* Secondary Indexing Any Y Materialized Views Any Y! AUTO_INCREMENT Insert N Abstract data types Can apply the I-confluence test to! standard SQL for program analysis
176. Eventual consistency Invariant Operation C.F. ? Equality, Inequality Any Y Generate unique ID Any Y Specify unique ID Insert N >! Increment Y >! Decrement N < Decrement Y < Increment N Foreign Key Insert Y Foreign Key Delete Y* Secondary Indexing Any Y Materialized Views Any Y! AUTO_INCREMENT Insert N RAMP Transaction Abstract data types Can apply the I-confluence test to! standard SQL for program analysis
177. Remainder: Cannot avoid coordination Eventual consistency Invariant Operation C.F. ? Equality, Inequality Any Y Generate unique ID Any Y Specify unique ID Insert N >! Increment Y >! Decrement N < Decrement Y < Increment N Foreign Key Insert Y Foreign Key Delete Y* Secondary Indexing Any Y Materialized Views Any Y! AUTO_INCREMENT Insert N RAMP Transaction Abstract data types Can apply the I-confluence test to! standard SQL for program analysis
178. Standard SQL with extensions and ! analysis!
179. CREATE TABLE Orders! (! O_ID int AUTO_INCREMENT,! C_ID int,! O_QTY int,! DATE datetime NOT NULL! ! PRIMARY KEY (OrderID),! FOREIGN KEY (CustomerID) REFERENCES Customers(C_ID),! CONSTRAINT [O_QTY > 0]! ) CREATE PROCEDURE CreateOrder(@C_ID int, @O_QTY int)! AS! INSERT INTO Orders (C_ID, O_QTY, DATE) VALUES! (C_ID, O_QTY, NOW());! GO Standard SQL with extensions and ! analysis!
180. > WARNING: Orders.O_ID requires coordination!! INSERT found in CreateOrder! > WARNING: CreateOrder requires remote check for @C_ID! CREATE TABLE Orders! (! O_ID int AUTO_INCREMENT,! C_ID int,! O_QTY int,! DATE datetime NOT NULL! ! PRIMARY KEY (OrderID),! FOREIGN KEY (CustomerID) REFERENCES Customers(C_ID),! CONSTRAINT [O_QTY > 0]! ) CREATE PROCEDURE CreateOrder(@C_ID int, @O_QTY int)! AS! INSERT INTO Orders (C_ID, O_QTY, DATE) VALUES! (C_ID, O_QTY, NOW());! GO Standard SQL with extensions and ! analysis!
182. DDL with invariants DDL with invariants CREATE TABLE Orders ( O_ID int AUTO_INCREMENT, C_ID int, O_QTY int, DATE datetime NOT NULL PRIMARY KEY (OrderID), FOREIGN KEY (CustomerID) REFERENCES Customers(C_ID), CONSTRAINT [O_QTY > 0] )
183. DDL with invariants DDL with invariants CREATE TABLE Orders ( O_ID int AUTO_INCREMENT, C_ID int, O_QTY int, DATE datetime NOT NULL PRIMARY KEY (OrderID), FOREIGN KEY (CustomerID) REFERENCES Customers(C_ID), CONSTRAINT [O_QTY > 0] ) I-confluence! analysis
184. DDL with invariants DDL with invariants CREATE TABLE Orders ( O_ID int AUTO_INCREMENT, C_ID int, O_QTY int, DATE datetime NOT NULL PRIMARY KEY (OrderID), FOREIGN KEY (CustomerID) REFERENCES Customers(C_ID), CONSTRAINT [O_QTY > 0] ) I-confluence! analysis COORDINATION COST
185. DDL with invariants DDL with invariants CREATE TABLE Orders ( O_ID int AUTO_INCREMENT, C_ID int, O_QTY int, DATE datetime NOT NULL PRIMARY KEY (OrderID), FOREIGN KEY (CustomerID) REFERENCES Customers(C_ID), CONSTRAINT [O_QTY > 0] ) I-confluence! analysis COORDINATION COST
186. I-confluence in real programs?
187. I-confluence in real programs? Benchmark I-confluent invariants TPC-C! 10 of 12 TPC-E 4 of 4 AuctionMark all but 1 SEATS all but 1 JPAB all TATP all
188. I-confluence in real programs? Benchmark I-confluent invariants TPC-C! 10 of 12 TPC-E 4 of 4 AuctionMark all but 1 SEATS all but 1 JPAB all TATP all TRADITIONAL! OLTP! APPLICATIONS! ARE ACHIEVABLE! WITHOUT! (MUCH)! COORDINATION
189. I-confluence in real programs? Benchmark I-confluent invariants TPC-C! 10 of 12 TPC-E 4 of 4 AuctionMark all but 1 SEATS all but 1 JPAB all TATP all Still requires coordination-avoiding query plans!! • Appropriate merge (e.g., counter datatype) • Atomic multi-put (e.g., RAMP) • Nested atomic transactions (e.g., New-Order ID assignment) TRADITIONAL! OLTP! APPLICATIONS! ARE ACHIEVABLE! WITHOUT! (MUCH)! COORDINATION
190. DDL with invariants DDL with invariants CREATE TABLE Orders ( O_ID int AUTO_INCREMENT, C_ID int, O_QTY int, DATE datetime NOT NULL PRIMARY KEY (OrderID), FOREIGN KEY (CustomerID) REFERENCES Customers(C_ID), CONSTRAINT [O_QTY > 0] ) I-confluence! analysis COORDINATION COST
191. DDL with invariants DDL with invariants CREATE TABLE Orders ( O_ID int AUTO_INCREMENT, C_ID int, O_QTY int, DATE datetime NOT NULL PRIMARY KEY (OrderID), FOREIGN KEY (CustomerID) REFERENCES Customers(C_ID), CONSTRAINT [O_QTY > 0] ) I-confluence! analysis COORDINATION COST
192. DDL with invariants DDL with invariants CREATE TABLE Orders ( O_ID int AUTO_INCREMENT, C_ID int, O_QTY int, DATE datetime NOT NULL PRIMARY KEY (OrderID), FOREIGN KEY (CustomerID) REFERENCES Customers(C_ID), CONSTRAINT [O_QTY > 0] ) Query! planner I-confluence! analysis COORDINATION COST
193. DDL with invariants DDL with invariants CREATE TABLE Orders ( O_ID int AUTO_INCREMENT, C_ID int, O_QTY int, DATE datetime NOT NULL PRIMARY KEY (OrderID), FOREIGN KEY (CustomerID) REFERENCES Customers(C_ID), CONSTRAINT [O_QTY > 0] ) Query! planner Application queries I-confluence! analysis COORDINATION COST
194. DDL with invariants DDL with invariants CREATE TABLE Orders ( O_ID int AUTO_INCREMENT, C_ID int, O_QTY int, DATE datetime NOT NULL PRIMARY KEY (OrderID), FOREIGN KEY (CustomerID) REFERENCES Customers(C_ID), CONSTRAINT [O_QTY > 0] ) Query! planner Application queries Query! executor I-confluence! analysis COORDINATION COST
195. DDL with invariants DDL with invariants CREATE TABLE Orders ( O_ID int AUTO_INCREMENT, C_ID int, O_QTY int, DATE datetime NOT NULL PRIMARY KEY (OrderID), FOREIGN KEY (CustomerID) REFERENCES Customers(C_ID), CONSTRAINT [O_QTY > 0] ) Query! planner Storage! manager Application queries Query! executor I-confluence! analysis COORDINATION COST
196. DDL with invariants DDL with invariants CREATE TABLE Orders ( O_ID int AUTO_INCREMENT, C_ID int, O_QTY int, DATE datetime NOT NULL PRIMARY KEY (OrderID), FOREIGN KEY (CustomerID) REFERENCES Customers(C_ID), CONSTRAINT [O_QTY > 0] ) Query! planner Storage! manager Lock! manager Application queries Query! executor I-confluence! analysis COORDINATION COST
197. DDL with invariants DDL with invariants CREATE TABLE Orders ( O_ID int AUTO_INCREMENT, C_ID int, O_QTY int, DATE datetime NOT NULL PRIMARY KEY (OrderID), FOREIGN KEY (CustomerID) REFERENCES Customers(C_ID), CONSTRAINT [O_QTY > 0] ) Query! planner Storage! manager Lock! manager Application queries Query! executor I-confluence! analysis STATISTICS COORDINATION COST
198. COORDINATION COST DDL with invariants DDL with invariants CREATE TABLE Orders ( O_ID int AUTO_INCREMENT, C_ID int, O_QTY int, DATE datetime NOT NULL PRIMARY KEY (OrderID), FOREIGN KEY (CustomerID) REFERENCES Customers(C_ID), CONSTRAINT [O_QTY > 0] ) Query! planner Storage! manager Application queries Query! executor I-confluence! analysis STATISTICS Lock! manager
199. COORDINATION COST DDL with invariants DDL with invariants CREATE TABLE Orders ( O_ID int AUTO_INCREMENT, C_ID int, O_QTY int, DATE datetime NOT NULL PRIMARY KEY (OrderID), FOREIGN KEY (CustomerID) REFERENCES Customers(C_ID), CONSTRAINT [O_QTY > 0] ) Query! planner Storage! manager Application queries Query! executor I-confluence! analysis STATISTICS Lock! manager
200. COORDINATION COST DDL with invariants DDL with invariants CREATE TABLE Orders ( O_ID int AUTO_INCREMENT, C_ID int, O_QTY int, DATE datetime NOT NULL PRIMARY KEY (OrderID), FOREIGN KEY (CustomerID) REFERENCES Customers(C_ID), CONSTRAINT [O_QTY > 0] ) Query! planner Storage! manager Application queries Query! executor I-confluence! analysis STATISTICS Lock! manager ONGOING! WORK
201. COORDINATION COST DDL with invariants DDL with invariants CREATE TABLE Orders ( O_ID int AUTO_INCREMENT, C_ID int, O_QTY int, DATE datetime NOT NULL PRIMARY KEY (OrderID), FOREIGN KEY (CustomerID) REFERENCES Customers(C_ID), CONSTRAINT [O_QTY > 0] ) STATIC! PLAN! (manual) Storage! manager Application queries Query! executor I-confluence! analysis STATISTICS Lock! manager
202. COORDINATION COST DDL with invariants DDL with invariants CREATE TABLE Orders ( O_ID int AUTO_INCREMENT, C_ID int, O_QTY int, DATE datetime NOT NULL PRIMARY KEY (OrderID), FOREIGN KEY (CustomerID) REFERENCES Customers(C_ID), CONSTRAINT [O_QTY > 0] ) STATIC! PLAN! (manual) Storage! manager Application queries Query! executor I-confluence! analysis Lock! manager
203. COORDINATION COST DDL with invariants DDL with invariants CREATE TABLE Orders ( O_ID int AUTO_INCREMENT, C_ID int, O_QTY int, DATE datetime NOT NULL PRIMARY KEY (OrderID), FOREIGN KEY (CustomerID) REFERENCES Customers(C_ID), CONSTRAINT [O_QTY > 0] ) STATIC! PLAN! (manual) Storage! manager Application queries Query! executor I-confluence! analysis Lock! manager
205. TPC-C New-Order
206. TPC-C New-Order warehouse district orders neworders
207. TPC-C New-Order Pre-materialized aggregates (e.g., W_YTD=SUM(orders for warehouse)) warehouse district orders neworders
208. TPC-C New-Order Pre-materialized aggregates (e.g., W_YTD=SUM(orders for warehouse)) warehouse district orders neworders insert! 100
209. TPC-C New-Order Pre-materialized aggregates (e.g., W_YTD=SUM(orders for warehouse)) warehouse district orders neworders +100 insert! 100
210. TPC-C New-Order Pre-materialized aggregates (e.g., W_YTD=SUM(orders for warehouse)) RAMP transaction on counter CRDT warehouse district orders neworders +100 insert! 100
211. TPC-C New-Order Pre-materialized aggregates (e.g., W_YTD=SUM(orders for warehouse)) RAMP transaction on counter CRDT warehouse district orders neworders
212. TPC-C New-Order Foreign key insert (e.g., NewOrder, Orders tables) Pre-materialized aggregates (e.g., W_YTD=SUM(orders for warehouse)) RAMP transaction on counter CRDT warehouse district orders neworders
213. TPC-C New-Order Foreign key insert (e.g., NewOrder, Orders tables) Pre-materialized aggregates (e.g., W_YTD=SUM(orders for warehouse)) RAMP transaction on counter CRDT insert! O_ID warehouse district orders neworders
214. TPC-C New-Order Foreign key insert (e.g., NewOrder, Orders tables) Pre-materialized aggregates (e.g., W_YTD=SUM(orders for warehouse)) RAMP transaction on counter CRDT insert! O_ID warehouse district orders neworders insert! O_ID
215. TPC-C New-Order Foreign key insert (e.g., NewOrder, Orders tables) Pre-materialized aggregates (e.g., W_YTD=SUM(orders for warehouse)) RAMP transaction on counter CRDT RAMP transaction across tables insert! O_ID warehouse district orders neworders insert! O_ID
216. TPC-C New-Order Foreign key insert (e.g., NewOrder, Orders tables) Pre-materialized aggregates (e.g., W_YTD=SUM(orders for warehouse)) Sequence number ID assignment (i.e., D_NEXT_O_ID) RAMP transaction on counter CRDT RAMP transaction across tables insert! O_ID warehouse district orders neworders insert! O_ID
217. TPC-C New-Order Foreign key insert (e.g., NewOrder, Orders tables) Pre-materialized aggregates (e.g., W_YTD=SUM(orders for warehouse)) Sequence number ID assignment (i.e., D_NEXT_O_ID) RAMP transaction on counter CRDT RAMP transaction across tables insert! O_ID warehouse district orders neworders insert! O_ID assign! new! O_ID
218. TPC-C New-Order Foreign key insert (e.g., NewOrder, Orders tables) Pre-materialized aggregates (e.g., W_YTD=SUM(orders for warehouse)) Sequence number ID assignment (i.e., D_NEXT_O_ID) RAMP transaction on counter CRDT RAMP transaction across tables insert! O_ID warehouse district orders neworders insert! O_ID assign! new! O_ID
219. TPC-C New-Order Foreign key insert (e.g., NewOrder, Orders tables) Pre-materialized aggregates (e.g., W_YTD=SUM(orders for warehouse)) Sequence number ID assignment (i.e., D_NEXT_O_ID) RAMP transaction on counter CRDT RAMP transaction across tables insert! O_ID warehouse district orders neworders insert! O_ID deferred atomic incrementAndGet() on commit! assign! new! O_ID
220. TPC-C New-Order Foreign key insert (e.g., NewOrder, Orders tables) Pre-materialized aggregates (e.g., W_YTD=SUM(orders for warehouse)) Sequence number ID assignment (i.e., D_NEXT_O_ID) RAMP transaction on counter CRDT RAMP transaction across tables insert! O_ID warehouse district orders neworders insert! O_ID deferred atomic incrementAndGet() on commit! assign! new! O_ID tmp ID
221. TPC-C New-Order Foreign key insert (e.g., NewOrder, Orders tables) Pre-materialized aggregates (e.g., W_YTD=SUM(orders for warehouse)) Sequence number ID assignment (i.e., D_NEXT_O_ID) RAMP transaction on counter CRDT RAMP transaction across tables rewrite FK references to point to temp unique ID! create local index from temp unique ID to sequence ID insert! O_ID warehouse district orders neworders insert! O_ID deferred atomic incrementAndGet() on commit! assign! new! O_ID tmp ID
222. TPC-C New-Order Foreign key insert (e.g., NewOrder, Orders tables) Pre-materialized aggregates (e.g., W_YTD=SUM(orders for warehouse)) Sequence number ID assignment (i.e., D_NEXT_O_ID) RAMP transaction on counter CRDT RAMP transaction across tables rewrite FK references to point to temp unique ID! create local index from temp unique ID to sequence ID insert! O_ID warehouse district orders neworders insert! O_ID deferred atomic incrementAndGet() on commit! assign! new! O_ID tmp ID
223. TPC-C New-Order Foreign key insert (e.g., NewOrder, Orders tables) Pre-materialized aggregates (e.g., W_YTD=SUM(orders for warehouse)) Sequence number ID assignment (i.e., D_NEXT_O_ID) RAMP transaction on counter CRDT RAMP transaction across tables rewrite FK references to point to temp unique ID! create local index from temp unique ID to sequence ID insert! O_ID warehouse district orders neworders insert! O_ID deferred atomic incrementAndGet() on commit! assign! new! O_ID tmp ID
224. TPC-C New-Order Foreign key insert (e.g., NewOrder, Orders tables) Pre-materialized aggregates (e.g., W_YTD=SUM(orders for warehouse)) Sequence number ID assignment (i.e., D_NEXT_O_ID) RAMP transaction on counter CRDT RAMP transaction across tables rewrite FK references to point to temp unique ID! create local index from temp unique ID to sequence ID insert! O_ID warehouse district orders neworders insert! O_ID deferred atomic incrementAndGet() on commit! assign! new! O_ID tmp ID
225. TPC-C New-Order Foreign key insert (e.g., NewOrder, Orders tables) Pre-materialized aggregates (e.g., W_YTD=SUM(orders for warehouse)) Sequence number ID assignment (i.e., D_NEXT_O_ID) RAMP transaction on counter CRDT RAMP transaction across tables rewrite FK references to point to temp unique ID! create local index from temp unique ID to sequence ID insert! O_ID warehouse district orders neworders insert! O_ID deferred atomic incrementAndGet() on commit! assign! new! O_ID tmp ID O NLY SYNCH CO O RDINATIO N! REQ UIRED
226. TPCC Combine fkeys with sequence number insert on commit...
227. TPCC Combine fkeys with sequence number insert on commit... 500K txns/s
232. Linear Scaling via Coordination Avoidance Coordination need not be a bottleneck (if implemented in a coordination-free manner): UC Berkeley database prototype, 100 EC2 CC2.8xlarge instances (thank you AWS folks! currently poor single-node performance, but unimportant if you can scale out [for the time being]), linearizable masters, only blocking coordination: incrementAndGet for “district next order ID” key, CPU-bound on in-memory data; ~2500 lines Java; 120 clients/warehouse, 5 warehouses/machine, no THINK TIME (i.e., more contention than stock configuration)
233. Linear Scaling via Coordination Avoidance Coordination need not be a bottleneck (if implemented in a coordination-free manner): UC Berkeley database prototype, 100 EC2 CC2.8xlarge instances (thank you AWS folks! currently poor single-node performance, but unimportant if you can scale out [for the time being]), linearizable masters, only blocking coordination: incrementAndGet for “district next order ID” key, CPU-bound on in-memory data; ~2500 lines Java; 120 clients/warehouse, 5 warehouses/machine, no THINK TIME (i.e., more contention than stock configuration)
234. Traditional database systems suffer from! coordination bottlenecks By understanding application requirements,! we can avoid coordination unless necessary We can build systems that actually scale! while providing correct behavior
235. Traditional database systems suffer from! coordination bottlenecks By understanding application requirements,! we can avoid coordination unless necessary We can build systems that actually scale! while providing correct behavior Thanks!! ! pbailis@cs.berkeley.edu! @pbailis! http://bailis.org/ http://amplab.cs.berkeley.edu/!
236. Traditional database systems suffer from! coordination bottlenecks By understanding application requirements,! we can avoid coordination unless necessary We can build systems that actually scale! while providing correct behavior Thanks!! ! pbailis@cs.berkeley.edu! @pbailis! http://bailis.org/ http://amplab.cs.berkeley.edu/! based on “Coordination-Avoiding Database Systems,”! Bailis, Fekete, Franklin, Ghodsi, Hellerstein, Stoica! arXiv:1402.2237 http://arxiv.org/abs/1402.2237
237. http://pbs.cs.berkeley.edu/#demo