23
play

23 Databases Intro to Database Systems Andy Pavlo AP AP - PowerPoint PPT Presentation

Jul 15, 2023 •340 likes •1.21k views

Distributed OLTP 23 Databases Intro to Database Systems Andy Pavlo AP AP 15-445/15-645 Computer Science Fall 2019 Carnegie Mellon University 2 ADM INISTRIVIA Homework #5 : Monday Dec 3 rd @ 11:59pm Project #4 : Monday Dec 10 th @ 11:59pm

  1. Distributed OLTP 23 Databases Intro to Database Systems Andy Pavlo AP AP 15-445/15-645 Computer Science Fall 2019 Carnegie Mellon University

  2. 2 ADM INISTRIVIA Homework #5 : Monday Dec 3 rd @ 11:59pm Project #4 : Monday Dec 10 th @ 11:59pm Extra Credit : Wednesday Dec 10 th @ 11:59pm Final Exam : Monday Dec 9 th @ 5:30pm CMU 15-445/645 (Fall 2019)

  3. 3 LAST CLASS System Architectures → Shared-Memory, Shared-Disk, Shared-Nothing Partitioning/Sharding → Hash, Range, Round Robin Transaction Coordination → Centralized vs. Decentralized CMU 15-445/645 (Fall 2019)

  4. 4 OLTP VS. OLAP On-line Transaction Processing (OLTP): → Short-lived read/write txns. → Small footprint. → Repetitive operations. On-line Analytical Processing (OLAP): → Long-running, read-only queries. → Complex joins. → Exploratory queries. CMU 15-445/645 (Fall 2019)

  5. 5 DECENTRALIZED COORDINATO R Partitions Begin Request P1 P2 Application Server P3 P4 CMU 15-445/645 (Fall 2019)

  6. 5 DECENTRALIZED COORDINATO R Partitions Query P1 P2 Query Application Server P3 P4 Query CMU 15-445/645 (Fall 2019)

  7. 5 DECENTRALIZED COORDINATO R Partitions Commit Request P1 P2 Safe to commit? Application Server P3 P4 CMU 15-445/645 (Fall 2019)

  8. 6 OBSERVATION We have not discussed how to ensure that all nodes agree to commit a txn and then to make sure it does commit if we decide that it should. → What happens if a node fails? → What happens if our messages show up late? → What happens if we don't wait for every node to agree? CMU 15-445/645 (Fall 2019)

  9. 7 IM PORTAN T ASSUM PTIO N We can assume that all nodes in a distributed DBMS are well-behaved and under the same administrative domain. → If we tell a node to commit a txn, then it will commit the txn (if there is not a failure). If you do not trust the other nodes in a distributed DBMS, then you need to use a Byzantine Fault Tolerant protocol for txns (blockchain). CMU 15-445/645 (Fall 2019)

  10. 8 TODAY'S AGENDA Atomic Commit Protocols Replication Consistency Issues (CAP) Federated Databases CMU 15-445/645 (Fall 2019)

  11. 9 ATOM IC COM M IT PROTO CO L When a multi-node txn finishes, the DBMS needs to ask all the nodes involved whether it is safe to commit. Examples: → Two-Phase Commit → Three-Phase Commit (not used) → Paxos → Raft → ZAB (Apache Zookeeper) → Viewstamped Replication CMU 15-445/645 (Fall 2019)

  12. 10 TWO- PH ASE COM M IT (SUCCESS) Commit Request Participant Application Server Node 2 Coordinator Participant Node 1 Node 3 CMU 15-445/645 (Fall 2019)

  13. 10 TWO- PH ASE COM M IT (SUCCESS) Commit Request Participant Application Server Node 2 Phase1: Prepare Coordinator Participant Node 1 Node 3 CMU 15-445/645 (Fall 2019)

  14. 10 TWO- PH ASE COM M IT (SUCCESS) Commit Request Participant OK Application Server Node 2 Phase1: Prepare OK Coordinator Participant Node 1 Node 3 CMU 15-445/645 (Fall 2019)

  15. 10 TWO- PH ASE COM M IT (SUCCESS) Commit Request Participant OK Application Server Node 2 Phase1: Prepare OK Coordinator Participant Phase2: Commit Node 1 Node 3 CMU 15-445/645 (Fall 2019)

  16. 10 TWO- PH ASE COM M IT (SUCCESS) Commit Request Participant OK OK Application Server Node 2 Phase1: Prepare OK Coordinator Participant Phase2: Commit OK Node 1 Node 3 CMU 15-445/645 (Fall 2019)

  17. 10 TWO- PH ASE COM M IT (SUCCESS) Success! Participant Application Server Node 2 Coordinator Participant Node 1 Node 3 CMU 15-445/645 (Fall 2019)

  18. 11 TWO- PH ASE COM M IT (ABORT) Commit Request Participant Application Server Node 2 Coordinator Participant Node 1 Node 3 CMU 15-445/645 (Fall 2019)

  19. 11 TWO- PH ASE COM M IT (ABORT) Commit Request Participant Application Server Node 2 Phase1: Prepare Coordinator Participant Node 1 Node 3 CMU 15-445/645 (Fall 2019)

  20. 11 TWO- PH ASE COM M IT (ABORT) Commit Request Participant Application Server Node 2 Phase1: Prepare ABORT! Coordinator Participant Node 1 Node 3 CMU 15-445/645 (Fall 2019)

  21. 11 TWO- PH ASE COM M IT (ABORT) Aborted Participant Application Server Node 2 ABORT! Coordinator Participant Node 1 Node 3 CMU 15-445/645 (Fall 2019)

  22. 11 TWO- PH ASE COM M IT (ABORT) Aborted Participant Application Server Node 2 ABORT! Coordinator Participant Phase2: Abort Node 1 Node 3 CMU 15-445/645 (Fall 2019)

  23. 11 TWO- PH ASE COM M IT (ABORT) Aborted Participant OK Application Server Node 2 ABORT! Coordinator Participant Phase2: Abort OK Node 1 Node 3 CMU 15-445/645 (Fall 2019)

  24. 12 2PC OPTIM IZATION S Early Prepare Voting → If you send a query to a remote node that you know will be the last one you execute there, then that node will also return their vote for the prepare phase with the query result. Early Acknowledgement After Prepare → If all nodes vote to commit a txn, the coordinator can send the client an acknowledgement that their txn was successful before the commit phase finishes. CMU 15-445/645 (Fall 2019)

  25. 13 EARLY ACKNOWLEDGEM EN T Commit Request Participant Application Server Node 2 Coordinator Participant Node 1 Node 3 CMU 15-445/645 (Fall 2019)

  26. 13 EARLY ACKNOWLEDGEM EN T Commit Request Participant Application Server Node 2 Phase1: Prepare Coordinator Participant Node 1 Node 3 CMU 15-445/645 (Fall 2019)

  27. 13 EARLY ACKNOWLEDGEM EN T Commit Request Participant OK Application Server Node 2 Phase1: Prepare OK Coordinator Participant Node 1 Node 3 CMU 15-445/645 (Fall 2019)

  28. 13 EARLY ACKNOWLEDGEM EN T Success! Participant OK Application Server Node 2 Phase1: Prepare OK Coordinator Participant Node 1 Node 3 CMU 15-445/645 (Fall 2019)

  29. 13 EARLY ACKNOWLEDGEM EN T Success! Participant OK Application Server Node 2 Phase1: Prepare OK Coordinator Participant Phase2: Commit Node 1 Node 3 CMU 15-445/645 (Fall 2019)

  30. 13 EARLY ACKNOWLEDGEM EN T Success! Participant OK OK Application Server Node 2 Phase1: Prepare OK Coordinator Participant Phase2: Commit OK Node 1 Node 3 CMU 15-445/645 (Fall 2019)

  31. 14 TWO- PH ASE COM M IT Each node records the outcome of each phase in a non-volatile storage log. What happens if coordinator crashes? → Participants must decide what to do. What happens if participant crashes? → Coordinator assumes that it responded with an abort if it hasn't sent an acknowledgement yet. CMU 15-445/645 (Fall 2019)

  32. 15 PAXOS Consensus protocol where a coordinator proposes an outcome (e.g., commit or abort) and then the participants vote on whether that outcome should succeed. Does not block if a majority of participants are available and has provably minimal message delays in the best case. CMU 15-445/645 (Fall 2019)

  33. 16 PAXOS Acceptor Commit Request Node 2 Acceptor Application Server Node 3 Proposer Acceptor Node 1 Node 4 CMU 15-445/645 (Fall 2019)

  34. 16 PAXOS Acceptor Commit Request Node 2 Acceptor Application Server Propose Node 3 Proposer Acceptor Node 1 Node 4 CMU 15-445/645 (Fall 2019)

  35. 16 PAXOS Acceptor Commit Request Node 2 X Acceptor Application Server Propose Node 3 Proposer Acceptor Node 1 Node 4 CMU 15-445/645 (Fall 2019)

  36. 16 PAXOS Acceptor Agree Commit Request Node 2 X Acceptor Application Server Propose Node 3 Agree Proposer Acceptor Node 1 Node 4 CMU 15-445/645 (Fall 2019)

  37. 16 PAXOS Acceptor Agree Commit Request Node 2 X Acceptor Application Server Propose Node 3 Commit Agree Proposer Acceptor Node 1 Node 4 CMU 15-445/645 (Fall 2019)

  38. 16 PAXOS Acceptor Agree Commit Request Accept Node 2 X Acceptor Application Server Propose Node 3 Commit Agree Proposer Acceptor Accept Node 1 Node 4 CMU 15-445/645 (Fall 2019)

  39. 16 PAXOS Acceptor Success! Node 2 X Acceptor Application Server Node 3 Proposer Acceptor Node 1 Node 4 CMU 15-445/645 (Fall 2019)

  40. 17 PAXOS Proposer Acceptors Proposer TIM E CMU 15-445/645 (Fall 2019)

  41. 17 PAXOS Proposer Acceptors Proposer Propose(n) TIM E CMU 15-445/645 (Fall 2019)

  42. 17 PAXOS Proposer Acceptors Proposer Propose(n) Agree(n) TIM E CMU 15-445/645 (Fall 2019)

  43. 17 PAXOS Proposer Acceptors Proposer Propose(n) Agree(n) Propose(n+1) TIM E CMU 15-445/645 (Fall 2019)

  44. 17 PAXOS Proposer Acceptors Proposer Propose(n) Agree(n) Propose(n+1) Commit(n) TIM E CMU 15-445/645 (Fall 2019)

  45. 17 PAXOS Proposer Acceptors Proposer Propose(n) Agree(n) Propose(n+1) Commit(n) TIM E Reject(n,n+1) CMU 15-445/645 (Fall 2019)

  46. 17 PAXOS Proposer Acceptors Proposer Propose(n) Agree(n) Propose(n+1) Commit(n) TIM E Reject(n,n+1) Agree(n+1) CMU 15-445/645 (Fall 2019)

  47. 17 PAXOS Proposer Acceptors Proposer Propose(n) Agree(n) Propose(n+1) Commit(n) TIM E Reject(n,n+1) Agree(n+1) Commit(n+1) CMU 15-445/645 (Fall 2019)

  48. 17 PAXOS Proposer Acceptors Proposer Propose(n) Agree(n) Propose(n+1) Commit(n) TIM E Reject(n,n+1) Agree(n+1) Commit(n+1) Accept(n+1) CMU 15-445/645 (Fall 2019)

Recommend

More recommend