distributed oltp databases part ii
play

Distributed OLTP Databases (Part II) Lecture # 23 Database Systems - PowerPoint PPT Presentation

Jan 31, 2024 •199 likes •1.14k views

Distributed OLTP Databases (Part II) Lecture # 23 Database Systems Andy Pavlo AP AP Computer Science 15-445/15-645 Carnegie Mellon Univ. Fall 2018 2 LAST CLASS System Architectures Shared-Memory, Shared-Disk, Shared-Nothing

  1. Distributed OLTP Databases (Part II) Lecture # 23 Database Systems Andy Pavlo AP AP Computer Science 15-445/15-645 Carnegie Mellon Univ. Fall 2018

  2. 2 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 2018)

  3. 3 DECEN TRALIZED CO O RDIN ATO R Partitions Begin Request P1 P2 Application Server P3 P4 CMU 15-445/645 (Fall 2018)

  4. 3 DECEN TRALIZED CO O RDIN ATO R Partitions Query P1 P2 Query Application Server P3 P4 Query CMU 15-445/645 (Fall 2018)

  5. 3 DECEN TRALIZED CO O RDIN ATO R Partitions Commit Request P1 P2 Safe to commit? Application Server P3 P4 CMU 15-445/645 (Fall 2018)

  6. 4 O BSERVATIO N 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 2018)

  7. 5 TO DAY'S AGEN DA Atomic Commit Protocols Replication Consistency Issues (CAP) Federated Databases CMU 15-445/645 (Fall 2018)

  8. 6 ATO M IC CO M M IT PROTO CO L When a multi-node txn finishes, the DBMS needs to ask all of 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 2018)

  9. 7 TWO - PH ASE CO M M IT (SUCCESS) Commit Request Participant Application Server Node 2 Coordinator Participant Node 1 Node 3 CMU 15-445/645 (Fall 2018)

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

  11. 7 TWO - PH ASE CO M M IT (SUCCESS) Commit Request Participant OK Application Server Phase1: Prepare Node 2 OK Coordinator Participant Node 1 Node 3 CMU 15-445/645 (Fall 2018)

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

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

  14. 7 TWO - PH ASE CO M M IT (SUCCESS) Success! Participant Application Server Node 2 Coordinator Participant Node 1 Node 3 CMU 15-445/645 (Fall 2018)

  15. 8 TWO - PH ASE CO M M IT (ABO RT) Commit Request Participant Application Server Node 2 Coordinator Participant Node 1 Node 3 CMU 15-445/645 (Fall 2018)

  16. 8 TWO - PH ASE CO M M IT (ABO RT) Commit Request Participant Application Server Phase1: Prepare Node 2 Coordinator Participant Node 1 Node 3 CMU 15-445/645 (Fall 2018)

  17. 8 TWO - PH ASE CO M M IT (ABO RT) Aborted Participant Application Server Node 2 ABORT! Coordinator Participant Node 1 Node 3 CMU 15-445/645 (Fall 2018)

  18. 8 TWO - PH ASE CO M M IT (ABO RT) Aborted Participant Application Server Node 2 ABORT! Coordinator Participant Phase2: Abort Node 1 Node 3 CMU 15-445/645 (Fall 2018)

  19. 8 TWO - PH ASE CO M M IT (ABO RT) Aborted Participant OK Application Server Node 2 ABORT! Coordinator Participant Phase2: Abort OK Node 1 Node 3 CMU 15-445/645 (Fall 2018)

  20. 9 2PC O PTIM IZATIO N 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 2018)

  21. 10 EARLY ACKN OWLEDGEM EN T Commit Request Participant Application Server Phase1: Prepare Node 2 Coordinator Participant Node 1 Node 3 CMU 15-445/645 (Fall 2018)

  22. 10 EARLY ACKN OWLEDGEM EN T Commit Request Participant OK Application Server Phase1: Prepare Node 2 OK Coordinator Participant Node 1 Node 3 CMU 15-445/645 (Fall 2018)

  23. 10 EARLY ACKN OWLEDGEM EN T Success! Participant OK Application Server Phase1: Prepare Node 2 OK Coordinator Participant Node 1 Node 3 CMU 15-445/645 (Fall 2018)

  24. 10 EARLY ACKN OWLEDGEM EN T Success! Participant OK Application Server Phase1: Prepare Node 2 OK Coordinator Participant Phase2: Commit Node 1 Node 3 CMU 15-445/645 (Fall 2018)

  25. 10 EARLY ACKN OWLEDGEM EN T Success! Participant OK OK Application Server Phase1: Prepare Node 2 OK Coordinator Participant Phase2: Commit OK Node 1 Node 3 CMU 15-445/645 (Fall 2018)

  26. 11 TWO - PH ASE CO M M IT Each node has to record the outcome of each phase in a stable storage log. What happens if coordinator crashes? → Participants have to 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 2018)

  27. 12 PAXO S 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. Lamport CMU 15-445/645 (Fall 2018)

  28. 12 PAXO S 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 2018)

  29. 13 PAXO S Acceptor Commit Request Node 2 Acceptor Application Server Node 3 Proposer Acceptor Node 1 Node 4 CMU 15-445/645 (Fall 2018)

  30. 13 PAXO S Acceptor Commit Request Node 2 Acceptor Application Server Propose Node 3 Proposer Acceptor Node 1 Node 4 CMU 15-445/645 (Fall 2018)

  31. 13 PAXO S Acceptor Commit Request Node 2 X Acceptor Application Server Propose Node 3 Proposer Acceptor Node 1 Node 4 CMU 15-445/645 (Fall 2018)

  32. 13 PAXO S 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 2018)

  33. 13 PAXO S 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 2018)

  34. 13 PAXO S 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 2018)

  35. 13 PAXO S Acceptor Success! Node 2 X Acceptor Application Server Node 3 Proposer Acceptor Node 1 Node 4 CMU 15-445/645 (Fall 2018)

  36. 14 PAXO S Proposer Acceptors Proposer TIM E CMU 15-445/645 (Fall 2018)

  37. 14 PAXO S Proposer Acceptors Proposer Propose(n) TIM E CMU 15-445/645 (Fall 2018)

  38. 14 PAXO S Proposer Acceptors Proposer Propose(n) Agree(n) TIM E CMU 15-445/645 (Fall 2018)

  39. 14 PAXO S Proposer Acceptors Proposer Propose(n) Agree(n) Propose(n+1) TIM E CMU 15-445/645 (Fall 2018)

  40. 14 PAXO S Proposer Acceptors Proposer Propose(n) Agree(n) Propose(n+1) Commit(n) TIM E CMU 15-445/645 (Fall 2018)

  41. 14 PAXO S Proposer Acceptors Proposer Propose(n) Agree(n) Propose(n+1) Commit(n) TIM E Reject(n,n+1) CMU 15-445/645 (Fall 2018)

  42. 14 PAXO S 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 2018)

  43. 14 PAXO S 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 2018)

  44. 14 PAXO S 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 2018)

  45. 15 M ULTI- PAXO S If the system elects a single leader that is in charge of proposing changes for some period of time, then it can skip the PREPARE phase. → Fall back to full Paxos whenever there is a failure. The system has to periodically renew who the leader is. CMU 15-445/645 (Fall 2018)

  46. 16 2PC VS. PAXO S Two-Phase Commit → Blocks if coordinator fails after the prepare message is sent, until coordinator recovers. Paxos → Non-blocking as long as a majority participants are alive, provided there is a sufficiently long period without further failures. CMU 15-445/645 (Fall 2018)

  47. 17 REPLICATIO N The DBMS can replicate data across redundant nodes to increase availability. Design Decisions: → Replica Configuration → Propagation Scheme → Propagation Timing CMU 15-445/645 (Fall 2018)

Recommend

Distributed OLTP Databases (Part I) Lecture # 22 Andy Pavlo Database Systems AP AP Computer

Distributed OLTP Databases (Part I) Lecture # 22 Andy Pavlo Database Systems AP AP Computer

Distributed OLTP Databases (Part I) Lecture # 22 Andy Pavlo Database Systems AP AP Computer Science 15-445/15-645 Carnegie Mellon Univ. Fall 2018 2 ADM IN ISTRIVIA Project #3 : TODAY @ 11:59am Homework #5 : Monday Dec 3 rd @ 11:59pm

1.66k views • 92 slides
Modern OLTP Indexes (Part 2) 1 / 43 Modern OLTP Indexes (Part 2) Recap Recap 2 / 43 Modern OLTP

Modern OLTP Indexes (Part 2) 1 / 43 Modern OLTP Indexes (Part 2) Recap Recap 2 / 43 Modern OLTP

Modern OLTP Indexes (Part 2) Modern OLTP Indexes (Part 2) 1 / 43 Modern OLTP Indexes (Part 2) Recap Recap 2 / 43 Modern OLTP Indexes (Part 2) Recap Versioned Latch Coupling Optimistic coupling scheme where writers are not blocked on

610 views • 43 slides
DISTRIBUTED DATABASES CHAPTER 25 LECTURE OVERVIEW What are distributed databases?

DISTRIBUTED DATABASES CHAPTER 25 LECTURE OVERVIEW What are distributed databases?

DISTRIBUTED DATABASES CHAPTER 25 LECTURE OVERVIEW What are distributed databases? Transparency and autonomy Fragmentation, allocation, replication Homogeneous vs. heterogeneous DDBMS Distributed transaction

1.27k views • 15 slides
CS377: Database Systems Distributed Databases Distributed Databases

CS377: Database Systems Distributed Databases Distributed Databases

CS377: Database Systems Distributed Databases Distributed Databases Department of Mathematics and Computer Science Emory University 1 Centralized DBMS on a Network

669 views • 29 slides
DAT ABASES IN THE CLOUD @andy_pavlo CMU-Q 15-440 December 3 rd , 2014 OLTP vs. OLAP databases .

DAT ABASES IN THE CLOUD @andy_pavlo CMU-Q 15-440 December 3 rd , 2014 OLTP vs. OLAP databases .

DAT ABASES IN THE CLOUD @andy_pavlo CMU-Q 15-440 December 3 rd , 2014 OLTP vs. OLAP databases . Source : https://www.flickr.com/photos/adesigna/3237575990 On-line Transaction Processing Fast operations that ingest new data and then update

572 views • 33 slides
Distributed Databases Chapter 22, Part B Database Management Systems, 2 nd Edition. R.

Distributed Databases Chapter 22, Part B Database Management Systems, 2 nd Edition. R.

Distributed Databases Chapter 22, Part B Database Management Systems, 2 nd Edition. R. Ramakrishnan and Johannes Gehrke 1 Introduction Data is stored at several sites, each managed by a DBMS that can run independently. Distributed Data

467 views • 11 slides
Distributed Databases Distributed database management system A distributed database (DDB) is

Distributed Databases Distributed database management system A distributed database (DDB) is

Distributed Databases Distributed database management system A distributed database (DDB) is a collection of multiple, logically interrelated databases distributed over a computer network. A distributed database management system

444 views • 10 slides
Distributed Databases 1 19.1 Distributed Database System A distributed database system

Distributed Databases 1 19.1 Distributed Database System A distributed database system

Distributed Databases 1 19.1 Distributed Database System A distributed database system consists of loosely coupled sites that share no physical component Database systems that run on each site are independent of each other

761 views • 42 slides
23 Databases Intro to Database Systems Andy Pavlo AP AP 15-445/15-645 Computer Science Fall

23 Databases Intro to Database Systems Andy Pavlo AP AP 15-445/15-645 Computer Science Fall

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.21k views • 86 slides
Introduction to Hadoop 1 Distributed Data Processing The idea of distributed databases is older

Introduction to Hadoop 1 Distributed Data Processing The idea of distributed databases is older

Introduction to Hadoop 1 Distributed Data Processing The idea of distributed databases is older than you might think Richard Peebles, Eric G. Manning: A Computer Architecture for Large (Distributed) Data Bases. VLDB 1975 : 405-427 Distributed

838 views • 10 slides
More Than A Network: Distributed OLTP on Clusters of Hardware Islands Danica Porobic , Pnar

More Than A Network: Distributed OLTP on Clusters of Hardware Islands Danica Porobic , Pnar

More Than A Network: Distributed OLTP on Clusters of Hardware Islands Danica Porobic , Pnar Tzn, Raja Appuswamy, Anastasia Ailamaki 1 Multisocket multicores 21 cycles 72 cycles 2x12-core 237 cycles Intel Xeon E5-2650L v3 threads

554 views • 14 slides
Ruby Monstas Session 29 Agenda Recap Databases Introduction to databases, Part 2 Exercises

Ruby Monstas Session 29 Agenda Recap Databases Introduction to databases, Part 2 Exercises

Ruby Monstas Session 29 Agenda Recap Databases Introduction to databases, Part 2 Exercises SQL Part 1 Recap Before SQL: CSV users.csv first_name,last_name,city,shoe_size Tatjana,Abt,Bern,42 Kasimir,Spitznogle,Luzern,46

743 views • 33 slides
YMMV The The Las Last Si t Six Mon x Months ths Prison Life GOOD EVIL NVM OLTP DRAM

YMMV The The Las Last Si t Six Mon x Months ths Prison Life GOOD EVIL NVM OLTP DRAM

OLTP on NVM: YMMV The The Las Last Si t Six Mon x Months ths Prison Life GOOD EVIL NVM OLTP DRAM SSD/HDD Ov Overv erview iew OL OLTP TP Wo Workl rkloads oads In Inte tel NV l NVM M Em Emul ulat

952 views • 44 slides
OLAP and Data Mining Chapter 17 OLTP Compared With OLAP On Line Transaction Processing

OLAP and Data Mining Chapter 17 OLTP Compared With OLAP On Line Transaction Processing

OLAP and Data Mining Chapter 17 OLTP Compared With OLAP On Line Transaction Processing OLTP OLTP Maintains a database that is an accurate model of some real- world enterprise. Supports day-to-day operations. Characteristics:

929 views • 46 slides
Making Non-Distributed Databases, Distributed Ioannis Papapanagiotou, PhD Shailesh Birari

Making Non-Distributed Databases, Distributed Ioannis Papapanagiotou, PhD Shailesh Birari

Making Non-Distributed Databases, Distributed Ioannis Papapanagiotou, PhD Shailesh Birari Dynomite Ecosystem Dynomite - Proxy layer Dyno - Client Dynomite-manager - Ecosystem orchestrator Dynomite-explorer - UI Problems &

577 views • 30 slides
Distributed databases I largely follow Silberschatz (not the latest edition) while adding info

Distributed databases I largely follow Silberschatz (not the latest edition) while adding info

Distributed databases I largely follow Silberschatz (not the latest edition) while adding info from Elmasri-Navathe, Connolly-Begg and (to a large extent) my own experience. A distributed database consists of loosely connected nodes in a

1.49k views • 47 slides
Ruby Monstas Session 28 Agenda Introduction to databases, Part 1 Exercises Introduction to

Ruby Monstas Session 28 Agenda Introduction to databases, Part 1 Exercises Introduction to

Ruby Monstas Session 28 Agenda Introduction to databases, Part 1 Exercises Introduction to databases Part 1 CSV Recap users.csv first_name,last_name,city,shoe_size Tatjana,Abt,Bern,42 Kasimir,Spitznogle,Luzern,46

433 views • 14 slides
2 Measured CPU Cycles 30% BUFFER POOL 28% LOCKING RECOVERY 30% 12% REAL WORK OLTP THROUGH

2 Measured CPU Cycles 30% BUFFER POOL 28% LOCKING RECOVERY 30% 12% REAL WORK OLTP THROUGH

2 Measured CPU Cycles 30% BUFFER POOL 28% LOCKING RECOVERY 30% 12% REAL WORK OLTP THROUGH THE LOOKING GLASS, AND WHAT WE FOUND THERE 3 SIGMOD, pp. 981-992, 2008. H-STORE: A HIGH-PERFORMANCE, DISTRIBUTED MAIN MEMORY TRANSACTION

137 views • 12 slides
Reducing Replication Bandwidth for Distributed Document Databases Lianghong Xu 1 , Andy Pavlo 1 ,

Reducing Replication Bandwidth for Distributed Document Databases Lianghong Xu 1 , Andy Pavlo 1 ,

Reducing Replication Bandwidth for Distributed Document Databases Lianghong Xu 1 , Andy Pavlo 1 , Sudipta Sengupta 2 Jin Li 2 , Greg Ganger 1 Carnegie Mellon University 1 , Microsoft Research 2 Document-oriented Databases { { " _id " :

563 views • 19 slides
Reducing Replication Bandwidth for Distributed Document Databases Lianghong Xu 1 , Andy Pavlo 1 ,

Reducing Replication Bandwidth for Distributed Document Databases Lianghong Xu 1 , Andy Pavlo 1 ,

Reducing Replication Bandwidth for Distributed Document Databases Lianghong Xu 1 , Andy Pavlo 1 , Sudipta Sengupta 2 Jin Li 2 , Greg Ganger 1 Carnegie Mellon University 1 , Microsoft Research 2 Document-oriented Databases { { " _id " :

488 views • 24 slides
15-721 DATABASE SYSTEMS Lecture #07 Latch-free OLTP Indexes (Part I) Andy Pavlo / /

15-721 DATABASE SYSTEMS Lecture #07 Latch-free OLTP Indexes (Part I) Andy Pavlo / /

15-721 DATABASE SYSTEMS Lecture #07 Latch-free OLTP Indexes (Part I) Andy Pavlo / / Carnegie Mellon University / / Spring 2016 @Andy_Pavlo // Carnegie Mellon University // Spring 2017 2 ADMINISTRIVIA Peloton master branch has been

1.11k views • 83 slides
2 Workloa d? 3 OLTP 4 OLAP OLTP 4 OLAP OLTP Streaming 4 Scan- OLAP OLTP Streaming

2 Workloa d? 3 OLTP 4 OLAP OLTP 4 OLAP OLTP Streaming 4 Scan- OLAP OLTP Streaming

2 Workloa d? 3 OLTP 4 OLAP OLTP 4 OLAP OLTP Streaming 4 Scan- OLAP OLTP Streaming oriented 4 Scan- OLAP OLTP Streaming Archiving oriented 4 Scan- Log- OLAP OLTP Streaming Archiving processing oriented 4 Scan- Log-

1.09k views • 105 slides
Hybrid Indexes D a v i d G . A n d e r s e n A n d r e w P a v l o M i

Hybrid Indexes D a v i d G . A n d e r s e n A n d r e w P a v l o M i

Reducing the Storage Overhead of Main-Memory OLTP Databases with C o - a u t h o r s : Hybrid Indexes D a v i d G . A n d e r s e n A n d r e w P a v l o M i c h a e l K a m i n s k y Huanchen

487 views • 33 slides
@andy_pavlo Part #1 Background Part # 2 Engineering Part # 3 Oracle Rant 3 AUTONOMOUS DBMSs

@andy_pavlo Part #1 Background Part # 2 Engineering Part # 3 Oracle Rant 3 AUTONOMOUS DBMSs

@andy_pavlo Part #1 Background Part # 2 Engineering Part # 3 Oracle Rant 3 AUTONOMOUS DBMSs SELF-ADAPTIVE DATABASES 1970-1990s Index Selection Self-Adaptive Partitioning / Sharding Databases Data Placement 3 AUTONOMOUS

960 views • 76 slides

More recommend