Slide 1

Slide 1 text

Data Management Availability, Consistency, Peter Bailis! UC Berkeley, AMPLab! @pbailis with Alan Fekete, Mike Franklin, Ali Ghodsi, Ion Stoica, Joe Hellerstein and Horizontally Scalable

Slide 2

Slide 2 text

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

Slide 3

Slide 3 text

A portrait of big services

Slide 4

Slide 4 text

A portrait of big services

Slide 5

Slide 5 text

A portrait of big services

Slide 6

Slide 6 text

A portrait of big services

Slide 7

Slide 7 text

A portrait of big services

Slide 8

Slide 8 text

A portrait of big services

Slide 9

Slide 9 text

A portrait of big services

Slide 10

Slide 10 text

A portrait of big services

Slide 11

Slide 11 text

stateless! horizontally scalable A portrait of big services

Slide 12

Slide 12 text

stateless! horizontally scalable concurrent,! stateful! durable A portrait of big services

Slide 13

Slide 13 text

No content

Slide 14

Slide 14 text

Users care about the correctness of their applications

Slide 15

Slide 15 text

Users care about the correctness of their applications “usernames should be unique”

Slide 16

Slide 16 text

Users care about the correctness of their applications “usernames should be unique” “each patient should have a attending doctor”

Slide 17

Slide 17 text

Users care about the correctness of their applications “usernames should be unique” “each patient should have a attending doctor” “account balances should be positive”

Slide 18

Slide 18 text

No content

Slide 19

Slide 19 text

Classic answer: use ACID transactions

Slide 20

Slide 20 text

Classic answer: use ACID transactions Equivalent Serial Execution

Slide 21

Slide 21 text

Classic answer: use ACID transactions Equivalent Serial Execution

Slide 22

Slide 22 text

Classic answer: use ACID transactions Equivalent Serial Execution isolation provides correctness

Slide 23

Slide 23 text

Classic answer: use ACID transactions Equivalent Serial Execution isolation provides correctness ACID

Slide 24

Slide 24 text

Classic answer: use ACID transactions Equivalent Serial Execution isolation provides correctness ACID ACID

Slide 25

Slide 25 text

Classic answer: use ACID transactions Equivalent Serial Execution isolation provides correctness ACID ACID

Slide 26

Slide 26 text

Under the hood: ACID (conflict serializability) reasons about low-level read/write traces

Slide 27

Slide 27 text

Under the hood: ACID (conflict serializability) reasons about low-level read/write traces

Slide 28

Slide 28 text

Under the hood: ACID (conflict serializability) reasons about low-level read/write traces

Slide 29

Slide 29 text

Under the hood: ACID (conflict serializability) reasons about low-level read/write traces

Slide 30

Slide 30 text

Under the hood: ACID (conflict serializability) reasons about low-level read/write traces

Slide 31

Slide 31 text

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

Slide 32

Slide 32 text

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

Slide 33

Slide 33 text

END RESULT:! A MYOPIC APPROACH TO CORRECTNESS

Slide 34

Slide 34 text

END RESULT:! A MYOPIC APPROACH TO CORRECTNESS COST:! SERIALIZABILITY REQUIRES COORDINATION

Slide 35

Slide 35 text

END RESULT:! A MYOPIC APPROACH TO CORRECTNESS COST:! SERIALIZABILITY REQUIRES COORDINATION synchronous coordination =

Slide 36

Slide 36 text

END RESULT:! A MYOPIC APPROACH TO CORRECTNESS COST:! SERIALIZABILITY REQUIRES COORDINATION synchronous coordination = stalls during network partitions =

Slide 37

Slide 37 text

END RESULT:! A MYOPIC APPROACH TO CORRECTNESS COST:! SERIALIZABILITY REQUIRES COORDINATION synchronous coordination = stalls during network partitions = RTT latency during operations =

Slide 38

Slide 38 text

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

Slide 39

Slide 39 text

THE ACID SCALABILITY WALL

Slide 40

Slide 40 text

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

Slide 41

Slide 41 text

+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

Slide 42

Slide 42 text

+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

Slide 43

Slide 43 text

No content

Slide 44

Slide 44 text

HANA

Slide 45

Slide 45 text

do not support! SSI/serializability HANA

Slide 46

Slide 46 text

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

Slide 47

Slide 47 text

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

Slide 48

Slide 48 text

No content

Slide 49

Slide 49 text

synchronous coordination =! stalls during network partitions = RTT latency during operations = possible stall during concurrent access

Slide 50

Slide 50 text

no (or asynchronous) coordination = synchronous coordination =! stalls during network partitions = RTT latency during operations = possible stall during concurrent access

Slide 51

Slide 51 text

no (or asynchronous) coordination = Gilbert and Lynch “High Availability” = synchronous coordination =! stalls during network partitions = RTT latency during operations = possible stall during concurrent access

Slide 52

Slide 52 text

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

Slide 53

Slide 53 text

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

Slide 54

Slide 54 text

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

Slide 55

Slide 55 text

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

Slide 56

Slide 56 text

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

Slide 57

Slide 57 text

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!!

Slide 58

Slide 58 text

CORRECTNESS vs. SCALABILITY

Slide 59

Slide 59 text

CORRECTNESS vs. SCALABILITY SERIALIZABILITY

Slide 60

Slide 60 text

CORRECTNESS vs. SCALABILITY SERIALIZABILITY Our insight: serializability is sufficient for correctness! ! ! ! ! but is not necessary! ! ! !

Slide 61

Slide 61 text

CORRECTNESS vs. SCALABILITY SERIALIZABILITY Our insight: serializability is sufficient for correctness! ! ! ! ! but is not necessary! ! ! !

Slide 62

Slide 62 text

CORRECTNESS vs. SCALABILITY SERIALIZABILITY Our solution: coordination avoidance Our insight: serializability is sufficient for correctness! ! ! ! ! but is not necessary! ! ! !

Slide 63

Slide 63 text

CORRECTNESS vs. SCALABILITY SERIALIZABILITY Our solution: coordination avoidance Our insight: serializability is sufficient for correctness! ! ! ! ! but is not necessary! ! ! ! Only coordinate when necessary

Slide 64

Slide 64 text

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

Slide 65

Slide 65 text

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

Slide 66

Slide 66 text

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

Slide 67

Slide 67 text

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?! !

Slide 68

Slide 68 text

Ask applications for invariants

Slide 69

Slide 69 text

Invariant:! user IDs are unique Ask applications for invariants

Slide 70

Slide 70 text

Invariant:! user IDs are unique Ask applications for invariants

Slide 71

Slide 71 text

Invariant:! user IDs are unique Ask applications for invariants

Slide 72

Slide 72 text

Invariant:! user IDs are unique Ask applications for invariants

Slide 73

Slide 73 text

Invariant:! user IDs are unique Ask applications for invariants

Slide 74

Slide 74 text

No content

Slide 75

Slide 75 text

Invariant: each employee is in a department Operations: add employees

Slide 76

Slide 76 text

Invariant: each employee is in a department Operations: add employees Anomaly (to avoid):

Slide 77

Slide 77 text

Invariant: each employee is in a department Operations: add employees l_emp = employees.find(id=“louise”) ! Anomaly (to avoid):

Slide 78

Slide 78 text

Invariant: each employee is in a department Operations: add employees l_emp = employees.find(id=“louise”) ! Anomaly (to avoid):

Slide 79

Slide 79 text

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):

Slide 80

Slide 80 text

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):

Slide 81

Slide 81 text

Invariant: each employee is in a department Operations: add employees

Slide 82

Slide 82 text

Invariant: each employee is in a department Operations: add employees

Slide 83

Slide 83 text

Invariant: each employee is in a department Operations: add employees

Slide 84

Slide 84 text

employees = {} dept = {{“ops”:1}, {“dev”:2}} Invariant: each employee is in a department Operations: add employees

Slide 85

Slide 85 text

employees = {} dept = {{“ops”:1}, {“dev”:2}} Invariant: each employee is in a department Operations: add employees

Slide 86

Slide 86 text

employees = {} dept = {{“ops”:1}, {“dev”:2}} Invariant: each employee is in a department Operations: add employees d1 = dept.find(“ops”) employees.add({“Harry”:d1})

Slide 87

Slide 87 text

employees = {} dept = {{“ops”:1}, {“dev”:2}} Invariant: each employee is in a department Operations: add employees d1 = dept.find(“ops”) employees.add({“Harry”:d1})

Slide 88

Slide 88 text

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})

Slide 89

Slide 89 text

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})

Slide 90

Slide 90 text

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})

Slide 91

Slide 91 text

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!

Slide 92

Slide 92 text

No content

Slide 93

Slide 93 text

Invariant: only one ops on staff at a time Operations: change staffing

Slide 94

Slide 94 text

Anomaly (to avoid): Invariant: only one ops on staff at a time Operations: change staffing

Slide 95

Slide 95 text

on_duty = employees.find(staffed=”T”) ! Anomaly (to avoid): Invariant: only one ops on staff at a time Operations: change staffing

Slide 96

Slide 96 text

on_duty = employees.find(staffed=”T”) ! Anomaly (to avoid): Invariant: only one ops on staff at a time Operations: change staffing

Slide 97

Slide 97 text

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

Slide 98

Slide 98 text

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

Slide 99

Slide 99 text

Invariant: only one ops on staff at a time Operations: change staffing

Slide 100

Slide 100 text

staff = {“Laura”:T, “Harry”:F, “Gary”:F} Invariant: only one ops on staff at a time Operations: change staffing

Slide 101

Slide 101 text

staff = {“Laura”:T, “Harry”:F, “Gary”:F} Invariant: only one ops on staff at a time Operations: change staffing

Slide 102

Slide 102 text

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

Slide 103

Slide 103 text

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

Slide 104

Slide 104 text

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

Slide 105

Slide 105 text

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

Slide 106

Slide 106 text

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

Slide 107

Slide 107 text

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

Slide 108

Slide 108 text

No content

Slide 109

Slide 109 text

No content

Slide 110

Slide 110 text

No content

Slide 111

Slide 111 text

No content

Slide 112

Slide 112 text

No content

Slide 113

Slide 113 text

No content

Slide 114

Slide 114 text

No content

Slide 115

Slide 115 text

SAFETY correctness always guaranteed

Slide 116

Slide 116 text

SAFETY correctness always guaranteed LIVENESS database states agree (converge)

Slide 117

Slide 117 text

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

Slide 118

Slide 118 text

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

Slide 119

Slide 119 text

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

Slide 120

Slide 120 text

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

Slide 121

Slide 121 text

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

Slide 122

Slide 122 text

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

Slide 123

Slide 123 text

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

Slide 124

Slide 124 text

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

Slide 125

Slide 125 text

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

Slide 126

Slide 126 text

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

Slide 127

Slide 127 text

Formal framework for reasoning about application coordination requirements

Slide 128

Slide 128 text

Formal framework for reasoning about application coordination requirements Coordination depends on combination of:! - expressiveness of operations! - strength of invariants

Slide 129

Slide 129 text

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...

Slide 130

Slide 130 text

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

Slide 131

Slide 131 text

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

Slide 132

Slide 132 text

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

Slide 133

Slide 133 text

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

Slide 134

Slide 134 text

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 ???

Slide 135

Slide 135 text

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 ???

Slide 136

Slide 136 text

Constraint: record IDs are unique

Slide 137

Slide 137 text

Constraint: record IDs are unique DECLARE TABLE users (! ID int UNIQUE,! FirstName string,! LastName string )

Slide 138

Slide 138 text

Constraint: record IDs are unique DECLARE TABLE users (! ID int UNIQUE,! FirstName string,! LastName string ) Anomaly! (to avoid):

Slide 139

Slide 139 text

Constraint: record IDs are unique DECLARE TABLE users (! ID int UNIQUE,! FirstName string,! LastName string ) Anomaly! (to avoid):

Slide 140

Slide 140 text

Constraint: record IDs are unique DECLARE TABLE users (! ID int UNIQUE,! FirstName string,! LastName string ) Anomaly! (to avoid):

Slide 141

Slide 141 text

Constraint: record IDs are unique DECLARE TABLE users (! ID int UNIQUE,! FirstName string,! LastName string )

Slide 142

Slide 142 text

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 )

Slide 143

Slide 143 text

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 )

Slide 144

Slide 144 text

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 )

Slide 145

Slide 145 text

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 )

Slide 146

Slide 146 text

Foreign key constraints

Slide 147

Slide 147 text

Foreign key constraints DECLARE TABLE users (! U_ID int UNIQUE,! D_ID int! UserName string ! FOREIGN KEY (D_ID)! REFERENCES department(D_ID) )

Slide 148

Slide 148 text

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 )

Slide 149

Slide 149 text

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 )

Slide 150

Slide 150 text

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”);!

Slide 151

Slide 151 text

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”);!

Slide 152

Slide 152 text

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):

Slide 153

Slide 153 text

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

Slide 154

Slide 154 text

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!

Slide 155

Slide 155 text

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

Slide 156

Slide 156 text

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”);!

Slide 157

Slide 157 text

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”);!

Slide 158

Slide 158 text

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”);!

Slide 159

Slide 159 text

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

Slide 160

Slide 160 text

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

Slide 161

Slide 161 text

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

Slide 162

Slide 162 text

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

Slide 163

Slide 163 text

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

Slide 164

Slide 164 text

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”)

Slide 165

Slide 165 text

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”)

Slide 166

Slide 166 text

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”)

Slide 167

Slide 167 text

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”)

Slide 168

Slide 168 text

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”)

Slide 169

Slide 169 text

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”)

Slide 170

Slide 170 text

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

Slide 171

Slide 171 text

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

Slide 172

Slide 172 text

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

Slide 173

Slide 173 text

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

Slide 174

Slide 174 text

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

Slide 175

Slide 175 text

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

Slide 176

Slide 176 text

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

Slide 177

Slide 177 text

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

Slide 178

Slide 178 text

Standard SQL with extensions and ! analysis!

Slide 179

Slide 179 text

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!

Slide 180

Slide 180 text

> 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!

Slide 181

Slide 181 text

No content

Slide 182

Slide 182 text

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] )

Slide 183

Slide 183 text

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

Slide 184

Slide 184 text

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

Slide 185

Slide 185 text

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

Slide 186

Slide 186 text

I-confluence in real programs?

Slide 187

Slide 187 text

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

Slide 188

Slide 188 text

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

Slide 189

Slide 189 text

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

Slide 190

Slide 190 text

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

Slide 191

Slide 191 text

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

Slide 192

Slide 192 text

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

Slide 193

Slide 193 text

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

Slide 194

Slide 194 text

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

Slide 195

Slide 195 text

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

Slide 196

Slide 196 text

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

Slide 197

Slide 197 text

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

Slide 198

Slide 198 text

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

Slide 199

Slide 199 text

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

Slide 200

Slide 200 text

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

Slide 201

Slide 201 text

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

Slide 202

Slide 202 text

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

Slide 203

Slide 203 text

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

Slide 204

Slide 204 text

No content

Slide 205

Slide 205 text

TPC-C New-Order

Slide 206

Slide 206 text

TPC-C New-Order warehouse district orders neworders

Slide 207

Slide 207 text

TPC-C New-Order Pre-materialized aggregates (e.g., W_YTD=SUM(orders for warehouse)) warehouse district orders neworders

Slide 208

Slide 208 text

TPC-C New-Order Pre-materialized aggregates (e.g., W_YTD=SUM(orders for warehouse)) warehouse district orders neworders insert! 100

Slide 209

Slide 209 text

TPC-C New-Order Pre-materialized aggregates (e.g., W_YTD=SUM(orders for warehouse)) warehouse district orders neworders +100 insert! 100

Slide 210

Slide 210 text

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

Slide 211

Slide 211 text

TPC-C New-Order Pre-materialized aggregates (e.g., W_YTD=SUM(orders for warehouse)) RAMP transaction on counter CRDT warehouse district orders neworders

Slide 212

Slide 212 text

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

Slide 213

Slide 213 text

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

Slide 214

Slide 214 text

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

Slide 215

Slide 215 text

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

Slide 216

Slide 216 text

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

Slide 217

Slide 217 text

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

Slide 218

Slide 218 text

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

Slide 219

Slide 219 text

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

Slide 220

Slide 220 text

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

Slide 221

Slide 221 text

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

Slide 222

Slide 222 text

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

Slide 223

Slide 223 text

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

Slide 224

Slide 224 text

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

Slide 225

Slide 225 text

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

Slide 226

Slide 226 text

TPCC Combine fkeys with sequence number insert on commit...

Slide 227

Slide 227 text

TPCC Combine fkeys with sequence number insert on commit... 500K txns/s

Slide 228

Slide 228 text

No content

Slide 229

Slide 229 text

No content

Slide 230

Slide 230 text

No content

Slide 231

Slide 231 text

No content

Slide 232

Slide 232 text

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)

Slide 233

Slide 233 text

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)

Slide 234

Slide 234 text

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

Slide 235

Slide 235 text

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!! ! [email protected]! @pbailis! http://bailis.org/ http://amplab.cs.berkeley.edu/!

Slide 236

Slide 236 text

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!! ! [email protected]! @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

Slide 237

Slide 237 text

http://pbs.cs.berkeley.edu/#demo