Leveraging Lock Contention to Improve Transaction Applications - - PowerPoint PPT Presentation

▶
leveraging lock contention to improve transaction
SMART_READER_LITE
LIVE PREVIEW

Leveraging Lock Contention to Improve Transaction Applications - - PowerPoint PPT Presentation

May 03, 2023 234 likes •660 views

Leveraging Lock Contention to Improve Transaction Applications Cong Yan Alvin Cheung University of Washington 1 Background Database transactions Airline ticket reservation, banking, online shopping... 2 Background Database

slide-1
SLIDE 1

Leveraging Lock Contention to Improve Transaction Applications

Cong Yan Alvin Cheung University of Washington

1

slide-2
SLIDE 2

Background

  • Database transactions
  • Airline ticket reservation, banking, online

shopping...

2

slide-3
SLIDE 3

Background

  • Database transactions
  • Airline ticket reservation, banking, online

shopping...

3

slide-4
SLIDE 4

Background

  • Database transactions
  • Parallelism under high data contention?

atomicity and consistency

  • Concurrency protocols:

Time

4

slide-5
SLIDE 5

select(Echo) update(Echo.num) select(Alice) update(Alice.bal) select(Echo) update(Echo.num) select(Cong) update(Cong.bal)

Two-phase Locking

  • Example transaction: online shopping
  • Alice(A) and Cong(C) buying Echo at the same time

5

slide-6
SLIDE 6

select(Echo) update(Echo.num) select(Alice) update(Alice.bal) select(Echo) update(Echo.num) select(Cong) update(Cong.bal)

Two-phase Locking

  • Example transaction: online shopping
  • Alice(A) and Cong(C) buying Echo at the same time

6

slide-7
SLIDE 7
  • -(T1)--

select(Echo)

  • -(T2)--

Execution time

Two-phase Locking

7

slide-8
SLIDE 8
  • -(T1)--
  • -(T2)--

select(Echo) update(Echo.num) select(Echo)

Execution time

Two-phase Locking

8

slide-9
SLIDE 9
  • -(T1)--
  • -(T2)--

select(Echo) select(Echo) select(Alice) (Waiting for lock) update(Echo.num)

Execution time

Two-phase Locking

9

slide-10
SLIDE 10
  • -(T1)--
  • -(T2)--

select(Echo) update(Echo.num) select(Echo) select(Alice) (Waiting for lock) update(Alice.bal)

Execution time

Two-phase Locking

10

slide-11
SLIDE 11
  • -(T1)--
  • -(T2)--

Execution time

select(Echo) update(Echo.num) select(Echo) select(Alice) (Waiting for lock) update(Alice.bal)

Two-phase Locking

11

slide-12
SLIDE 12
  • -(T1)--
  • -(T2)--

Execution time

select(Echo) update(Echo.num) select(Echo) select(Alice) (Waiting for lock) update(Alice.bal)

Two-phase Locking

12

slide-13
SLIDE 13
  • -(T1)--
  • -(T2)--

Execution time

select(Echo) update(Echo.num) select(Echo) select(Alice) (Waiting for lock) update(Alice.bal) update(Echo.num)

Two-phase Locking

13

slide-14
SLIDE 14
  • -(T1)--
  • -(T2)--

Execution time

select(Echo) update(Echo.num) select(Echo) select(Alice) (Waiting for lock) update(Alice.bal) update(Echo.num) select(Cong)

Two-phase Locking

14

slide-15
SLIDE 15
  • -(T1)--
  • -(T2)--

Execution time

select(Echo) update(Echo.num) select(Echo) select(Alice) (Waiting for lock) update(Alice.bal) update(Echo.num) select(Cong) update(Cong.bal)

Two-phase Locking

15

slide-16
SLIDE 16

Two-phase Locking

*: “Staring into the abyss, An Evaluation of Concurrency Control with One-thousand Cores”, VLDB-14

  • Problem: serialization of all transactions
  • Changing the order of queries within

transactions shortens lock waiting time

  • Other concurrency control protocols:

OCC, MVCC

  • 2PL is more efficient under high data

contention(*)

16

slide-17
SLIDE 17

Shortening Lock Waiting

  • -(T1)--
  • -(T2)--

Execution time

select(Echo) update(Echo.num) select(Echo) select(Alice) (Waiting for lock) update(Alice.bal) update(Echo.num) select(Cong) update(Cong.bal)

  • -(T1)--

select(Alice)

  • -(T2)--

17

slide-18
SLIDE 18

Shortening Lock Waiting

Execution time

  • -(T1)--

select(Alice) select(Cong)

  • -(T2)--
  • -(T1)--
  • -(T2)--

select(Echo) update(Echo.num) select(Echo) select(Alice) (Waiting for lock) update(Alice.bal) update(Echo.num) select(Cong) update(Cong.bal)

18

slide-19
SLIDE 19

Shortening Lock Waiting

  • -(T1)--
  • -(T2)--

Execution time

select(Echo) update(Echo.num) select(Echo) select(Alice) (Waiting for lock) update(Alice.bal) update(Echo.num) select(Cong) update(Cong.bal)

  • -(T1)--

select(Alice) update(Alice.bal) select(Cong)

  • -(T2)--

19

slide-20
SLIDE 20

Shortening Lock Waiting

  • -(T1)--
  • -(T2)--

Execution time

select(Echo) update(Echo.num) select(Echo) select(Alice) (Waiting for lock) update(Alice.bal) update(Echo.num) select(Cong) update(Cong.bal)

  • -(T1)--

select(Alice) update(Alice.bal) select(Echo) update(Cong.bal) select(Cong)

  • -(T2)--

20

slide-21
SLIDE 21

Shortening Lock Waiting

  • -(T1)--
  • -(T2)--

Execution time

select(Echo) update(Echo.num) select(Echo) select(Alice) (Waiting for lock) update(Alice.bal) update(Echo.num) select(Cong) update(Cong.bal)

  • -(T1)--

select(Alice) update(Alice.bal) select(Echo) update(Echo.num) select(Echo) update(Cong.bal) select(Cong)

  • -(T2)--

21

slide-22
SLIDE 22

Shortening Lock Waiting

  • -(T1)--
  • -(T2)--

Execution time

select(Echo) update(Echo.num) select(Echo) select(Alice) (Waiting for lock) update(Alice.bal) update(Echo.num) select(Cong) update(Cong.bal)

  • -(T1)--

select(Alice) update(Alice.bal) select(Echo) update(Echo.num) select(Echo) update(Cong.bal) select(Cong) (Waiting for lock)

  • -(T2)--

22

slide-23
SLIDE 23

Shortening Lock Waiting

  • -(T1)--
  • -(T2)--

Execution time

select(Echo) update(Echo.num) select(Echo) select(Alice) (Waiting for lock) update(Alice.bal) update(Echo.num) select(Cong) update(Cong.bal)

  • -(T1)--

select(Alice) update(Alice.bal) select(Echo) update(Echo.num) select(Echo) update(Cong.bal) select(Cong) (Waiting for lock) update(Echo.num)

  • -(T2)--

23

slide-24
SLIDE 24

2x! More threads...

Shortening Lock Waiting

24

slide-25
SLIDE 25

2x! More threads...

Shortening Lock Waiting

# Shorter latency # Higher throughput

25

slide-26
SLIDE 26
  • Manually reordering queries is hard
  • QURO: a query-aware compiler
  • Automatically reorders queries in

transactions based on data contention

  • Preserves original program semantics

26

slide-27
SLIDE 27

QURO

Profile the application Reorder queries Analyze application code Input: C++ transaction code with embedded SQL queries Output: C++ code with reordered SQL queries

27

slide-28
SLIDE 28
  • Profile the applications
  • Know which queries are likely to access

contentious data

  • Calculate the variance of running time for each query

QURO

28

slide-29
SLIDE 29

QURO

  • Analyze application code
  • Reordering preserves program semantics
  • Data dependency among program variables
  • Database constraints (same table, view, foreign key...)
  • 1. ¡ ¡ ¡ ¡v1 ¡= ¡select(“table1”);
  • 2. ¡ ¡ ¡ ¡v2 ¡= ¡select(“table2”, ¡v1);
  • 3. ¡ ¡ ¡ ¡update(“table1”, ¡input);

Statement ¡1 ¡should ¡appear ¡ before ¡statement ¡2

  • 1. ¡ ¡ ¡ ¡v1 ¡= ¡select(“table1”);
  • 2. ¡ ¡ ¡ ¡v2 ¡= ¡select(“table2”, ¡v1);
  • 3. ¡ ¡ ¡ ¡update(“table1”, ¡input);

Statement ¡1 ¡should ¡appear ¡ before ¡statement ¡3

29

slide-30
SLIDE 30

QURO

  • Goal: contentious queries appear as late as possible

in transactions

  • Constraint: data dependencies & database constraints

30

slide-31
SLIDE 31

QURO

Optimization problem!

  • Goal: contentious queries appear as late as possible

in transactions

  • Constraint: data dependencies & database constraints

31

slide-32
SLIDE 32

QURO

Optimization problem!

  • Formalize into ILP
  • Optimizations: reordering long transactions within

seconds

  • Goal: contentious queries appear as late as possible

in transactions

  • Constraint: data dependencies & database constraints

32

slide-33
SLIDE 33
  • Experiment overview:
  • Benchmarks: TPC-C, TPC-E
  • Throughput:
  • riginal
  • Vs. reordered implementation (by QURO)
  • Increasing data contention
  • smaller data size
  • more threads

Evaluation

33

slide-34
SLIDE 34
  • Benchmark: TPC-C payment transaction
  • changing data size scaling to more threads

Evaluation

contention contention

latency: -83% latency: -70%

34

slide-35
SLIDE 35
  • Benchmark: TPC-E trade update transaction
  • changing data size scaling to more threads

Evaluation

contention contention

latency: -75% latency: -66%

35

slide-36
SLIDE 36
  • Mix of different transactions
  • TPC-C standard mix: 5 types of transactions

Experiments

36

slide-37
SLIDE 37
  • More complicated transactions
  • TPC-E trade order and result
  • Each >500 lines of code, >20 queries, complicated logic

Experiments

37

slide-38
SLIDE 38

38

slide-39
SLIDE 39

39

slide-40
SLIDE 40

Better black friday!

40

slide-41
SLIDE 41

Conclusion

  • The order of query has large impact on

transaction performance.

  • QURO leverages information about query

contention, and automatically reorders the queries.

  • Reordered code generated by QURO can improve

throughput up to 6.53x, and can be applied to a wide range of applications.

  • We are in the process of releasing code

(congy@cs.washington.edu).

41