TRUST @ANDY_PAVLO Thirty Years Ago 2 I NTERACTIVE T RANSACTIONS S - - PowerPoint PPT Presentation

▶
trust
SMART_READER_LITE
LIVE PREVIEW

TRUST @ANDY_PAVLO Thirty Years Ago 2 I NTERACTIVE T RANSACTIONS S - - PowerPoint PPT Presentation

Jan 26, 2023 479 likes •1.12k views

MY DATABASE SYSTEM IS THE ONLY THING I CAN TRUST @ANDY_PAVLO Thirty Years Ago 2 I NTERACTIVE T RANSACTIONS S MALL # OF CPU C ORES S MALL M EMORY S IZES TPC-C BENCHMARK APPLICATION NewOrder Transaction 4 TPC-C BENCHMARK 20,000 MySQL

slide-1
SLIDE 1

MY DATABASE SYSTEM IS THE ONLY THING I CAN

TRUST

@ANDY_PAVLO

slide-2
SLIDE 2

Thirty Years Ago…

2
slide-3
SLIDE 3

SMALL # OF CPU CORES SMALL MEMORY SIZES INTERACTIVE TRANSACTIONS

slide-4
SLIDE 4

NewOrder Transaction

TPC-C BENCHMARK

4

APPLICATION

slide-5
SLIDE 5

5,000 10,000 15,000 20,000 1 2 3 4 5 6 7 8 9 10 11 12

5

TPC-C BENCHMARK

MySQL Postgres

TXN/SEC CPU CORES

slide-6
SLIDE 6

BUFFER POOL LOCKING RECOVERY REAL WORK

28% 30% 30%

12%

TRADITIONAL DBMS

OLTP THROUGH THE LOOKING GLASS, AND WHAT WE FOUND THERE SIGMOD, pp. 981-992, 2008. 6
slide-7
SLIDE 7

NOSQL SHARDING MIDDLEWARE HARDWARE UPGRADE REPLICATION DISTRIBUTED CACHE

slide-8
SLIDE 8

HOW TO SCALE UP WITHOUT GIVING UP TRANSACTIONS?

slide-9
SLIDE 9 H-STORE: A HIGH-PERFORMANCE, DISTRIBUTED MAIN MEMORY TRANSACTION PROCESSING SYSTEM
  • Proc. VLDB Endow., vol. 1, iss. 2, pp. 1496-1499, 2008.

Distributed Main Memory Transaction Processing System

slide-10
SLIDE 10

DISK ORIENTED CONCURRENT EXECUTION HEAVYWEIGHT RECOVERY

x

i

/

MAIN MEMORY STORAGE SERIAL EXECUTION COMPACT LOGGING

slide-11
SLIDE 11

PARTITIONS SINGLE-THREADED EXECUTION ENGINES

11
slide-12
SLIDE 12

Procedure Name Input Parameters

12
slide-13
SLIDE 13

Transaction Execution

PARTITIONS SINGLE-THREADED EXECUTION ENGINES

Transaction Result

CMD LOG SNAPSHOTS

run(phoneNum, contestantId, currentTime) { result = execute(VoteCount, phoneNum); if (result > MAX_VOTES) { return (ERROR); } execute(InsertVote, phoneNum, contestantId, currentTime); return (SUCCESS); }

VoteCount:

SELECT COUNT(*) FROM votes WHERE phone_num = ?;

InsertVote:

INSERT INTO votes VALUES (?, ?, ?);

STORED PROCEDURE

13
slide-14
SLIDE 14

Transaction Execution

CMD LOG

14
slide-15
SLIDE 15

SNAPSHOTS

Transaction Result

15
slide-16
SLIDE 16

5,000 10,000 15,000 20,000 1 2 3 4 5 6 7 8 9 10 11 12

16

TPC-C BENCHMARK

MySQL Postgres H-Store

TXN/SEC CPU CORES

slide-17
SLIDE 17

DISTRIBUTED TRANSACTIONS

slide-18
SLIDE 18 18

10,000 20,000 30,000 40,000 1 2 3 4

TPC-C BENCHMARK

H-Store

TXN/SEC NODES

slide-19
SLIDE 19 19

DI DISTR STRIBU IBUTED TRA TED TRANSAC NSACTIONS TIONS

slide-20
SLIDE 20 20

DI DISTR STRIBU IBUTED TRA TED TRANSAC NSACTIONS TIONS

slide-21
SLIDE 21

Query Count

21

DI DISTR STRIBU IBUTED TRA TED TRANSAC NSACTIONS TIONS

slide-22
SLIDE 22

Query Count

22

DI DISTR STRIBU IBUTED TRA TED TRANSAC NSACTIONS TIONS

slide-23
SLIDE 23

KNOW WHAT TRANSACTIONS WILL DO BEFORE THEY START

slide-24
SLIDE 24

BUT PEOPLE ALWAYS GIVE ME BAD ADVICE

slide-25
SLIDE 25

DON’T GET INVOLVED WITH COMPUTERS. YOU’LL NEVER MAKE ANY MONEY.

slide-26
SLIDE 26

DON’T GET A PHD. EVERYONE WILL THINK YOU ARE A JERK.

slide-27
SLIDE 27

THE DATABASE SYSTEM ALWAYS HAS MORE

INFORMATION

slide-28
SLIDE 28

DO USE MACHINE LEARNING

TO PREDICT

TRANSACTION BEHAVIOR.

ON PREDICTIVE MODELING FOR OPTIMIZING TRANSACTION EXECUTION IN PARALLEL OLTP SYSTEMS
  • Proc. VLDB Endow., Vol 5, Iss. 2, pp. 85-96, 2011
slide-29
SLIDE 29

PREDICTIVE MODELS

29
slide-30
SLIDE 30

Model Generator Classifier Feature Clusterer

SELECT * FROM WAREHO EHOUSE WHERE W_ID = 10; INSERT INTO ORDERS RS (O_W_ID, O_D_ID, O_C_ID) VALUES (10, 9, 12345); ⋮ SELECT * FROM WAREHO EHOUSE WHERE W_ID = 10; INSERT INTO ORDERS RS (O_W_ID, O_D_ID, O_C_ID) VALUES (10, 9, 12345); ⋮ SELECT * FROM WAREHO EHOUSE WHERE W_ID = 10; SELECT * FROM DISTR TRIC ICT D_W_ID = 10 AND D_ID =9; INSERT INTO ORDERS RS (O_W_ID, O_D_ID, O_C_ID) VALUES (10, 9, 12345); ⋮ SELECT * FROM WAREHOUSE WHERE W_ID = 10; SELECT * FROM DISTRICT WHERE D_W_ID = 10 AND D_ID =9; INSERT INTO ORDERS (O_W_ID, O_D_ID, O_C_ID,…) VALUES (10, 9, 12345,…); ⋮

Decision Tree

________ ________ ________ ________ ________ ________ ______ ________ ________ ________ ________ ________ ________ ______ ________ ________ ________ ________ ________ ________ ______ ________ ________ ________ ________ ________ ________ ______

Markov Models

30
slide-31
SLIDE 31

DI DISTR STRIBU IBUTED TRA TED TRANSAC NSACTIONS TIONS

31
slide-32
SLIDE 32

DI DISTR STRIBU IBUTED TRA TED TRANSAC NSACTIONS TIONS

32
slide-33
SLIDE 33 33

5,000 10,000 15,000 20,000 25,000 1 2 3 4 Houdini Naïve

OPTIMAL

TPC-C BENCHMARK

TXN/SEC NODES

slide-34
SLIDE 34 34

15,000 30,000 45,000 60,000 1 2 3 4 Houdini Naïve

TPC-C BENCHMARK

TXN/SEC NODES

slide-35
SLIDE 35

DI DISTR STRIBU IBUTED TRA TED TRANSAC NSACTIONS TIONS

SP1 - Waiting for Query Result

35
slide-36
SLIDE 36

DI DISTR STRIBU IBUTED TRA TED TRANSAC NSACTIONS TIONS

SP1 - Waiting for Query Result SP2 - Waiting for Query Request

36
slide-37
SLIDE 37

DI DISTR STRIBU IBUTED TRA TED TRANSAC NSACTIONS TIONS

SP1 - Waiting for Query Result SP2 - Waiting for Query Request SP3 - Two-Phase Commit

37
slide-38
SLIDE 38

TR TRAN ANSACTI SACTION ON STA STALL LL POIN POINTS TS

38

BASE PARTITION REMOTE PARTITION SP1 - Waiting for Query Result SP2 - Waiting for Query Request Real Work

45% 18% 37% 73% 22% 5%

SP3 - Two-Phase Commit

slide-39
SLIDE 39

DO SOMETHING USEFUL WHEN STALLED

slide-40
SLIDE 40

DON’T BE SURPRISED IF YOU & KB DON’T LAST THROUGH GRAD SCHOOL.

slide-41
SLIDE 41

DON’T BE STAN’S STUDENT IF YOU GO TO BROWN.

slide-42
SLIDE 42

DO USE MACHINE LEARNING

TO SCHEDULE

SPECULATIVE TASKS.

THE ART OF SPECULATIVE EXECUTION In Progress (August 2013)
slide-43
SLIDE 43 43

Distributed Transaction

Zzzz…

Single-Partition Transaction Single-Partition Transaction

SERIALIZABLE SCHEDULE

slide-44
SLIDE 44 44

Distributed Transaction Speculative Transaction Speculative Transaction

Zzzz…

VERIFY

SERIALIZABLE SCHEDULE

slide-45
SLIDE 45

SPECULATIVE TRANSACTIONS

Transaction Queue

Distributed Transaction: Speculation Candidate: READ X READ X WRITE X

45
slide-46
SLIDE 46

SPECULATIVE TRANSACTIONS

Transaction Queue

Distributed Transaction: Speculation Candidate:

46
slide-47
SLIDE 47

SPECULATIVE QUERIES

Distributed Transaction:

47
slide-48
SLIDE 48

SPECULATIVE QUERIES

Distributed Transaction:

48
slide-49
SLIDE 49

SPECULATIVE QUERIES

Distributed Transaction:

49

SELECT S_QTY FROM STOCK WHERE S_W_ID = ? AND S_I_ID = ?;

QueryY:

slide-50
SLIDE 50

SPECULATIVE QUERIES

Distributed Transaction:

50

SELECT S_QTY FROM STOCK WHERE S_W_ID = ? AND S_I_ID = ?;

QueryY:

slide-51
SLIDE 51

SELECT * FROM WAREHOUSE WHERE W_ID = ? w_id=0 i_w_ids=[1,0] i_ids=[1001,1002]

GetWarehouse: Transaction Parameters:

51
slide-52
SLIDE 52

w_id=0 i_w_ids=[1,0] i_ids=[1001,1002]

Transaction Parameters:

SELECT S_QTY FROM STOCK WHERE S_W_ID = ? AND S_I_ID = ?;

CheckStock:

52
slide-53
SLIDE 53

w_id=0 i_w_ids=[1,0] i_ids=[1001,1002]

Transaction Parameters:

SELECT S_QTY FROM STOCK WHERE S_W_ID = ? AND S_I_ID = ?;

CheckStock:

53
slide-54
SLIDE 54

Distributed Transaction Speculative Transactions

Query1 Query2 Query3 Query1 Query3 Query3 Query1 Query2 Query3 Query3

VERIFICATION

54
slide-55
SLIDE 55

10,000 20,000 30,000 40,000 50,000 1 2 3 4 Spec Queries None All

55

TPC-C BENCHMARK

Spec Txns TXN/SEC NODES

slide-56
SLIDE 56

Optimize Single-Partition Execution

H-STORE: A HIGH-PERFORMANCE, DISTRIBUTED MAIN MEMORY TRANSACTION PROCESSING SYSTEM
  • Proc. VLDB Endow., vol. 1, iss. 2, pp. 1496-1499, 2008.

Minimize Distributed Transactions

SKEW-AWARE AUTOMATIC DATABASE PARTITIONING IN SHARED-NOTHING, PARALLEL OLTP SYSTEMS Proceedings of SIGMOD, 2012.

Identify Distributed Transactions

ON PREDICTIVE MODELING FOR OPTIMIZING TRANSACTION EXECUTION IN PARALLEL OLTP SYSTEMS
  • Proc. VLDB Endow., vol. 5, pp. 85-96, 2011.

Utilize Transaction Stalls

THE ART OF SPECULATIVE EXECUTION In Progress (August 2013)
slide-57
SLIDE 57

FUTURE WORK

slide-58
SLIDE 58
slide-59
SLIDE 59

H-STORE N-STORE

N

S-STORE

slide-60
SLIDE 60
slide-61
SLIDE 61
slide-62
SLIDE 62

DON’T MESS IT UP WITH KB.

slide-63
SLIDE 63