MAINTAINING SQL INVARIANTS IN WEAKLY CONSISTENT DATABASES Nuno - - PowerPoint PPT Presentation

▶
maintaining sql invariants in weakly consistent databases
SMART_READER_LITE
LIVE PREVIEW

MAINTAINING SQL INVARIANTS IN WEAKLY CONSISTENT DATABASES Nuno - - PowerPoint PPT Presentation

Feb 04, 2023 381 likes •895 views

MAINTAINING SQL INVARIANTS IN WEAKLY CONSISTENT DATABASES Nuno Preguia (NOVA LINCS, FCT/Universidade NOVA de Lisboa) Joint work with: Valter Balegas, Cheng Li (MPI, now Oracle), Joo Sousa, David Lopes, Srgio Duarte, Carla Ferreira, Joo

slide-1
SLIDE 1

MAINTAINING SQL INVARIANTS IN WEAKLY CONSISTENT DATABASES

Nuno Preguiça (NOVA LINCS, FCT/Universidade NOVA de Lisboa) Joint work with: Valter Balegas, Cheng Li (MPI, now Oracle), João Sousa, David Lopes, Sérgio Duarte, Carla Ferreira, João Leitão, Allen Clement (MPI, now Google), Viktor Vafeiadis (MPI), Rodrigo Rodrigues (now Inesc-Id/IST)

slide-2
SLIDE 2

INTERNET SERVICES NOWADAYS

  • Services operate on a global scale.
  • An unprecedented number of people are using

Internet services.

  • Systems use geo-replication for low latency

and high availability.

2

slide-3
SLIDE 3

GEO-REPLICATION

3

20 ms 150 ms 20 ms SYNC DC2 DC1

slide-4
SLIDE 4

GEO-REPLICATION

4

DC2 DC1

slide-5
SLIDE 5

GEO-REPLICATION

5

Tournament A B Player Sonic Pacman Mario Tournament A B Player Sonic Pacman Mario

DC2 DC1

PT Sonic, A Sonic, B PT Sonic, A Sonic, B

create table player( id varchar(20), primary key id) create table tournament( id varchar(20), primary key id) create table pt( p varchar(20), t varchar(20), foreign key (p) REFERENCES player (id), foreign key (t) REFERENCES tournament (id))

slide-6
SLIDE 6

PT Sonic, A Sonic, B

GEO-REPLICATION

6

Tournament A B Player Sonic Pacman Mario Tournament A B Player Sonic Pacman Mario

DC2 DC1

PT Sonic, A Sonic, B PT Sonic, A Sonic, B Pacman, A

enroll(Pacman, A): insert into PT values(‘Pacman’,’A’)

slide-7
SLIDE 7

PT Sonic, A Sonic, B

GEO-REPLICATION

7

Tournament A B Player Sonic Pacman Mario Tournament A B Player Sonic Pacman Mario

DC2 DC1

PT Sonic, A Sonic, B PT Sonic, A Sonic, B Pacman, A

enroll(Mario, A): insert into PT values(‘Mario’,’A’)

PT Sonic, A Sonic, B Mario, A

slide-8
SLIDE 8

PT Sonic, A Sonic, B Pacman, A PT Sonic, A Sonic, B Mario, A

GEO-REPLICATION

8

Tournament A B Player Sonic Pacman Mario Tournament A B Player Sonic Pacman Mario

DC2 DC1 removeTournament(A): delete from tournament where id = ’A’ delete from PT where t = ’A’

slide-9
SLIDE 9

PT Sonic, A Sonic, B Pacman, A PT Sonic, A Sonic, B Mario, A

GEO-REPLICATION

9

Tournament A B Player Sonic Pacman Mario Tournament A B Player Sonic Pacman Mario

DC2 DC1 SYNC

slide-10
SLIDE 10

PT Sonic, A Sonic, B Pacman, A Mario, A PT Sonic, A Sonic, B Pacman, A Mario, A

GEO-REPLICATION

10

Tournament A B Player Sonic Pacman Mario Tournament A B Player Sonic Pacman Mario

DC2 DC1 SYNC

Mario is enrolled in tournament that was concurrently removed. Referential integrity violation.

slide-11
SLIDE 11

OUTLINE

  • Context / problem
  • First take: Sieve
  • Second take: SQL IPA
  • Final remarks

11

slide-12
SLIDE 12

RedBlue Consistency

Builds replicated systems that are fast and correct

slide-13
SLIDE 13

RedBlue Consistency

Builds replicated systems that are fast and correct

Blue ops: local, fast, weakly consistent

slide-14
SLIDE 14

RedBlue Consistency

Builds replicated systems that are fast and correct

State convergence Invariant preservation

Blue ops: local, fast, weakly consistent

slide-15
SLIDE 15

RedBlue Consistency

Builds replicated systems that are fast and correct

State convergence Invariant preservation

Blue ops: local, fast, weakly consistent Red ops: global, slow, strongly consistent

slide-16
SLIDE 16

Choosing between Blue or Red

  • peration u

commutative ? Red No

Ensuring state convergence

slide-17
SLIDE 17

Choosing between Blue or Red

  • peration u

commutative ? breaks invariants? Red Blue No Yes Yes No

Ensuring state convergence

Ensuring invariant preservation

slide-18
SLIDE 18

Choosing between Blue or Red

  • peration u

commutative ? breaks invariants? Red Blue No Yes Yes No

Ensuring state convergence

Ensuring invariant preservation

Good performance obtained if blue ops dominate op space

slide-19
SLIDE 19

SIEVE

Transforming

Operation stream Slow, Strongly consistent Fast, Weakly consistent Commutative shadow operations

Classifying

slide-20
SLIDE 20

SIEVE

Transforming

Operation stream Commutative shadow operations

slide-21
SLIDE 21

SIEVE

Transforming

Operation stream Commutative shadow operations

Challenges:

  • Making arbitrary side effects commute
  • Minimizing human intervention
slide-22
SLIDE 22

SIEVE

Transforming

Operation stream Commutative shadow operations

Challenges:

  • Making arbitrary side effects commute
  • Minimizing human intervention
slide-23
SLIDE 23

CRDT Annotation Example

@AUSET CREATE TABLE BankAccount( id INT(11) NOT NULL, @NUMDELTA balance INT(11) default 0, @LWW name char(60) default NULL, PRIMARY KEY (id) ) ENGINE=InnoDB

slide-24
SLIDE 24

CRDT Annotation Example

@AUSET CREATE TABLE BankAccount( id INT(11) NOT NULL, @NUMDELTA balance INT(11) default 0, @LWW name char(60) default NULL, PRIMARY KEY (id) ) ENGINE=InnoDB

slide-25
SLIDE 25

SIEVE

Transforming

Operation stream Slow, Strongly consistent Fast, Weakly consistent Commutative shadow operations

Classifying

slide-26
SLIDE 26

SIEVE

Slow, Strongly consistent Fast, Weakly consistent Commutative shadow operations

Classifying

slide-27
SLIDE 27

SIEVE

Slow, Strongly consistent Fast, Weakly consistent Commutative shadow operations

Classifying

Challenge:

  • How to classify accurately and efficiently?
slide-28
SLIDE 28

SIEVE

Slow, Strongly consistent Fast, Weakly consistent Commutative shadow operations

Classifying

Challenge:

  • How to classify accurately and efficiently?
slide-29
SLIDE 29

OUTLINE

  • Context / problem
  • First take: Sieve
  • Second take: SQL IPA
  • Final remarks

29

slide-30
SLIDE 30

Limitations of Sieve

  • Operations that may violate the invariant need

to be red/coordinated => slow

– Acquiring reservation/token (Indigo/CISE)

  • Static analysis of complete application(s)

– Changes in applications require rerunning the analysis process

30

slide-31
SLIDE 31

Limitations of Sieve

  • Operations that may violate the invariant need

to be blue/coordinated => slow

– Acquiring reservation/token (Indigo/CISE)

  • Static analysis of complete application(s)

– Changes in applications require rerunning the analysis process

31

Goal: maintain invariants while avoiding coordination

slide-32
SLIDE 32

Tournament A B PT Sonic, A Sonic, B PT Sonic, A Sonic, B PT Sonic, A Sonic, B

GEO-REPLICATION

32

Tournament A B Player Sonic Pacman Mario Tournament A B Player Sonic Pacman Mario

DC2 DC1 removeTournament(A): delete from tournament where id = ’A’ delete from PT where t = ’A’

slide-33
SLIDE 33

PT Sonic, A Sonic, B PT Sonic, A Sonic, B PT Sonic, A Sonic, B Mario, A

GEO-REPLICATION

33

Tournament A B Player Sonic Pacman Mario Tournament A B Player Sonic Pacman Mario

DC2 DC1 enroll(Mario, A): insert into PT values(‘Mario’,’A’)

slide-34
SLIDE 34

PT Sonic, A Sonic, B PT Sonic, A Sonic, B PT Sonic, A Sonic, B Mario, A

GEO-REPLICATION

34

Tournament A B Player Sonic Pacman Mario Tournament A B Player Sonic Pacman Mario

DC2 DC1 enroll(Mario, A): insert into PT values(‘Mario’,’A’) touch tournament where id = ‘A’

slide-35
SLIDE 35

PT Sonic, A Sonic, B PT Sonic, A Sonic, B PT Sonic, A Sonic, B Mario, A

GEO-REPLICATION

35

Tournament A B Player Sonic Pacman Mario Tournament A B Player Sonic Pacman Mario

DC2 DC1 enroll(Mario, A): insert into PT values(‘Mario’,’A’) touch tournament where id = ‘A’ touch cascade PT where t=’A’

slide-36
SLIDE 36

PT Sonic, A Sonic, B PT Sonic, A Sonic, B PT Sonic, A Sonic, B Mario, A

GEO-REPLICATION

36

Tournament A B Player Sonic Pacman Mario Tournament A B Player Sonic Pacman Mario

DC2 DC1 enroll(Mario, A): insert into PT values(‘Mario’,’A’) touch tournament where id = ‘A’ touch cascade PT where t=’A’ removeTournament(A): delete from tournament where id = ’A’ delete cascade from PT where t = ’A’

slide-37
SLIDE 37

PT Sonic, A Sonic, B PT Sonic, A Sonic, B PT Sonic, A Sonic, B Mario, A

GEO-REPLICATION

37

Tournament A B Player Sonic Pacman Mario Tournament A B Player Sonic Pacman Mario

DC2 DC1 SYNC

Rules add-wins:

  • 1. || =>
  • 2. || =>
slide-38
SLIDE 38

PT Sonic, A Sonic, B PT Sonic, A Sonic, B PT Sonic, A Sonic, B Mario, A

GEO-REPLICATION

38

Tournament A B Player Sonic Pacman Mario Tournament A B Player Sonic Pacman Mario

DC2 DC1 SYNC

Rules add-wins:

  • 1. || =>
  • 2. || =>
slide-39
SLIDE 39

Other invariants

  • Primary key (uniqueness)

– Split keyspace

  • Check constraint

– E.g. stock int CHECK (stock >= 0) – Solved using bounded counter (escrow)

39

slide-40
SLIDE 40

Limitations of Sieve

  • Operations that may violate the invariant need

to be blue/coordinated => slow

– Acquiring reservation/token (Indigo/CISE)

  • Static analysis of complete application(s)

– Changes in applications require rerunning the analysis process

40

Goal: “modify” operations in runtime. Use schema definition.

slide-41
SLIDE 41

PT Sonic, A Sonic, B PT Sonic, A Sonic, B PT Sonic, A Sonic, B Mario, A

GEO-REPLICATION

41

Tournament A B Player Sonic Pacman Mario Tournament A B Player Sonic Pacman Mario

DC2 DC1 enroll(Mario, A): insert into PT values(‘Mario’,’A’) create table player( id varchar(20), primary key id) create table tournament( id varchar(20), primary key id) create table pt( p varchar(20), t varchar(20), foreign key (p) REFERENCES player (id), foreign key (t) REFERENCES tournament (id))

slide-42
SLIDE 42

PT Sonic, A Sonic, B PT Sonic, A Sonic, B PT Sonic, A Sonic, B Mario, A

GEO-REPLICATION

42

Tournament A B Player Sonic Pacman Mario Tournament A B Player Sonic Pacman Mario

DC2 DC1 enroll(Mario, A): insert into PT values(‘Mario’,’A’) touch tournament where id = ‘A’ touch player where id = ‘Mario’ create table player( id varchar(20), primary key id) create table tournament( id varchar(20), primary key id) create table pt( p varchar(20), t varchar(20), AW foreign key (p) REFERENCES player (id), AW foreign key (t) REFERENCES tournament (id))

slide-43
SLIDE 43

OUTLINE

  • Context / problem
  • First take: Sieve
  • Second take: SQL IPA
  • Final remarks

43

slide-44
SLIDE 44

Status

  • Implementing prototype on top of Antidote

database

  • Runtime solution equivalent to static solution

implemented in IPA

44

slide-45
SLIDE 45

Impact of additional updates

10 20 30 40 1 2 4 8 16 32 64

Speed-up Number of updated Keys

IPA/Strong

45

slide-46
SLIDE 46

TOURNAMENT

46

50 100 150 200 250 300 350 50 100 150 200 250 300 350 400 Latency [ms] Throughput [TP/s]

Strong Indigo IPA Causal

slide-47
SLIDE 47

TOURNAMENT: OPERATIONS LATENCY

47

50 100 150 200 250 300

Begin Finish Remove DoMatch Enroll Disenroll Status Latency [ms]

Indigo IPA Causal

slide-48
SLIDE 48

Final remarks

  • SQL schema allows to define constraints
  • First approach

– Coordinate on operations that may break invariants

  • Second approach

– Maintain invariants without coordination (or minimizing coordination)

48

slide-49
SLIDE 49

QUESTIONS?

Valter Balegas – NOVA LINCS, FCT-UNL - Putting Consistency Back Into Eventual Consistency @ Eurosys'15 49