SlideServe
Télécharger Obtenir Pro
Menu
Accueil Pro
Préférences
ApparenceLight Aide et commentaires
© SlideServe · v2026

Distributed Database Systems and Optimization Techniques

vic avatar
vic
59 diapositives · 746 Views · Aug 08, 2012

The fundamental concepts of distributed databases, including the relational model, SQL, functional dependencies, normalization, and physical design. It explores the benefits and drawbacks of distributed databases, such as data sharing and reliability versus increased costs and complexity. The document also delves into query optimization techniques, including semijoins, and the challenges inherent in distributed query processing, such as transmission costs and parallelism. Key design choices, including replication and fragmentation, are discussed to help in developing efficient distributed database management systems.

#distributed databases #query optimization #relational model #database design #sql #normalization #data sharing #semijoins
Distributed Database Systems and Optimization Techniques - slide 1
01 / 59Distributed Database Systems and Optimization Techniques
1 / 17

Télécharger la présentation

Distributed Database Systems and Optimization Techniques
Une image / un lien ci-dessous est fourni (tel quel) pour télécharger la présentation

Politique de téléchargement : le contenu du site vous est fourni TEL QUEL pour votre information et votre usage personnel ; il ne peut être vendu, concédé sous licence ni partagé sur d’autres sites sans l’accord de son auteur.Le contenu vous est fourni TEL QUEL, pour votre information et votre usage personnel uniquement. Téléchargez la présentation en cliquant sur ce lien. Lors du téléchargement, si vous ne parvenez pas à télécharger une présentation, l’éditeur a peut-être supprimé le fichier de son serveur.Pendant le téléchargement, si vous ne parvenez pas à obtenir une présentation, le fichier a peut-être été supprimé par l’éditeur.

F I N

Plus de contenu associé

Transcription de la présentation

  1. Carnegie Mellon Univ.Dept. of Computer Science15-415 - Database Applications C. Faloutsos Distributed DB

  2. General Overview - rel. model • Relational model - SQL • Functional Dependencies & Normalization • Physical Design; Indexing • Query optimization • Transaction processing • Advanced topics • Distributed Databases 15-415 - C. Faloutsos

  3. Problem – definition centralized DB: CHICAGO LA NY 15-415 - C. Faloutsos

  4. Problem – definition Distr. DB: DB stored in many places ... connected NY LA 15-415 - C. Faloutsos

  5. Problem – definition now: connect to LA; exec sql select * from EMP; ... connect to NY; exec sql select * from EMPLOYEE; ... NY LA EMPLOYEE DBMS2 EMP DBMS1 15-415 - C. Faloutsos

  6. Problem – definition ideally: connect to distr-LA; exec sql select * from EMPL; LA NY EMPLOYEE D-DBMS D-DBMS EMP DBMS2 DBMS1 15-415 - C. Faloutsos

  7. Pros + Cons • Pros • . • . • . • Cons • . • . • . 15-415 - C. Faloutsos

  8. Pros + Cons • Pros • Data sharing • reliability & availability • speed up of query processing • Cons • software development cost • more bugs • may increase processing overhead (msg) 15-415 - C. Faloutsos

  9. Overview • Problem – motivation • Design issues • Query optimization – semijoins • transactions (recovery, conc. control) 15-415 - C. Faloutsos

  10. Design of Distr. DBMS what are our choices of storing a table? 15-415 - C. Faloutsos

  11. Design of Distr. DBMS • replication • fragmentation (horizontal; vertical; hybrid) • both 15-415 - C. Faloutsos

  12. Design of Distr. DBMS vertical fragm. horiz. fragm. 15-415 - C. Faloutsos

  13. Transparency & autonomy Issues/goals: • naming and local autonomy • replication and fragmentation transp. • location transparency i.e.: 15-415 - C. Faloutsos

  14. Problem – definition ideally: connect to distr-LA; exec sql select * from EMPL; LA NY EMPLOYEE D-DBMS D-DBMS EMP DBMS2 DBMS1 15-415 - C. Faloutsos

  15. Overview • Problem – motivation • Design issues • Query optimization – semijoins • transactions (recovery, conc. control) 15-415 - C. Faloutsos

  16. Distributed Query processing • issues (additional to centralized q-opt) • cost of transmission • parallelism / overlap of delays • (cpu, disk, #bytes-transmitted, #messages-trasmitted) • minimize elapsed time? • or minimize resource consumption? 15-415 - C. Faloutsos

  17. Distr. Q-opt – semijoins S2 SUPPLIER SHIPMENT S1 S3 SUPPLIER Join SHIPMENT = ? 15-415 - C. Faloutsos

  18. semijoins • choice of plans? 15-415 - C. Faloutsos

  19. semijoins • choice of plans? • plan #1: ship SHIP -> S2; join; ship -> S3 • plan #2: ship SHIP->S3; ship SUP->S3; join • ... • others? 15-415 - C. Faloutsos

  20. Distr. Q-opt – semijoins S2 SUPPLIER SHIPMENT S1 S3 SUPPLIER Join SHIPMENT = ? 15-415 - C. Faloutsos

  21. Semijoins • Idea: reduce the tables before shipping SUPPLIER SHIPMENT S1 S3 SUPPLIER Join SHIPMENT = ? 15-415 - C. Faloutsos

  22. Semijoins • How to do the reduction, cheaply? • Eg., reduce ‘SHIPMENT’: 15-415 - C. Faloutsos

  23. Semijoins • Idea: reduce the tables before shipping SUPPLIER SHIPMENT (s1,s2,s5,s11) S1 S3 SUPPLIER Join SHIPMENT = ? 15-415 - C. Faloutsos

  24. Semijoins • Formally: • SHIPMENT’ = SHIPMENT SUPPLIER • express semijoin w/ rel. algebra 15-415 - C. Faloutsos

  25. Semijoins • Formally: • SHIPMENT’ = SHIPMENT SUPPLIER • express semijoin w/ rel. algebra 15-415 - C. Faloutsos

  26. Semijoins – eg: • suppose each attr. is 4 bytes • Q: transmission cost (#bytes) for semijoin SHIPMENT’ = SHIPMENT semijoin SUPPLIER 15-415 - C. Faloutsos

  27. Semijoins • Idea: reduce the tables before shipping SUPPLIER SHIPMENT (s1,s2,s5,s11) S1 S3 4 bytes SUPPLIER Join SHIPMENT = ? 15-415 - C. Faloutsos

  28. Semijoins – eg: • suppose each attr. is 4 bytes • Q: transmission cost (#bytes) for semijoin SHIPMENT’ = SHIPMENT semijoin SUPPLIER • A: 4*4 bytes 15-415 - C. Faloutsos

  29. Semijoins – eg: • suppose each attr. is 4 bytes • Q1: give a plan, with semijoin(s) • Q2: estimate its cost (#bytes shipped) 15-415 - C. Faloutsos

  30. Semijoins – eg: • A1: • reduce SHIPMENT to SHIPMENT’ • SHIPMENT’ -> S3 • SUPPLIER -> S3 • do join @ S3 • Q2: cost? 15-415 - C. Faloutsos

  31. 4 bytes 4 bytes 4 bytes 4 bytes Semijoins SUPPLIER SHIPMENT (s1,s2,s5,s11) S1 S3 15-415 - C. Faloutsos

  32. Semijoins – eg: • A2: • 4*4 bytes - reduce SHIPMENT to SHIPMENT’ • 3*8 bytes - SHIPMENT’ -> S3 • 4*8 bytes - SUPPLIER -> S3 • 0 bytes - do join @ S3 72 bytes TOTAL 15-415 - C. Faloutsos

  33. Other plans? 15-415 - C. Faloutsos

  34. Other plans? P2: • reduce SHIPMENT to SHIPMENT’ • reduce SUPPLIER to SUPPLIER’ • SHIPMENT’ -> S3 • SUPPLIER’ -> S3 15-415 - C. Faloutsos

  35. Other plans? P3: • reduce SUPPLIER to SUPPLIER’ • SUPPLIER’ -> S2 • do join @ S2 • ship results -> S3 15-415 - C. Faloutsos

  36. A brilliant idea: ‘Bloom-joins’ • (not in the book – not in the final exam) • how to ship the projection, say, of SUPPLIER.s#, even cheaper? • A: Bloom-filter [Lohman+] = • quick&dirty membership testing 15-415 - C. Faloutsos

  37. 4 bytes 4 bytes 4 bytes 4 bytes Semijoins SUPPLIER SHIPMENT (s1,s2,s5,s11) S1 S3 15-415 - C. Faloutsos

  38. Another brilliant idea: two-way semijoins • (not in book, not in final exam) • reduce both relations with one more exchange: [Kang, ’86] • ship back the list of keys that didn’t match • CAN NOT LOSE! (why?) • further improvement: • or the list of ones that matched – whatever is shorter! 15-415 - C. Faloutsos

  39. Two-way Semijoins S2 SUPPLIER SHIPMENT (s1,s2,s5,s11) S1 (s5,s11) S3 15-415 - C. Faloutsos

  40. Overview • Problem – motivation • Design issues • Query optimization – semijoins • transactions (recovery, conc. control) 15-415 - C. Faloutsos

  41. Transactions – recovery • Problem: eg., a transaction moves $100 from NY -> $50 to LA, $50 to Chicago • 3 sub-transactions, on 3 systems, with 3 W.A.L.s • how to guarantee atomicity (all-or-none)? • Observation: additional types of failures (links, servers, delays, time-outs ....) 15-415 - C. Faloutsos

  42. Transactions – recovery • Problem: eg., a transaction moves $100 from NY -> $50 to LA, $50 to Chicago 15-415 - C. Faloutsos

  43. Distributed recovery CHICAGO How? T1,2: +$50 NY LA NY T1,3: +$50 T1,1:-$100 15-415 - C. Faloutsos

  44. Distributed recovery CHICAGO Step1: choose coordinator T1,2: +$50 NY LA NY T1,3: +$50 T1,1:-$100 15-415 - C. Faloutsos

  45. Distributed recovery • Step 2: execute a protocol, eg., “2 phase commit” 15-415 - C. Faloutsos

  46. 2 phase commit T1,3 T1,1 (coord.) T1,2 prepare to commit time 15-415 - C. Faloutsos

  47. 2 phase commit T1,3 T1,1 (coord.) T1,2 prepare to commit Y Y time 15-415 - C. Faloutsos

  48. 2 phase commit T1,3 T1,1 (coord.) T1,2 prepare to commit Y Y commit time 15-415 - C. Faloutsos

  49. 2 phase commit (eg., failure) T1,3 T1,1 (coord.) T1,2 prepare to commit time 15-415 - C. Faloutsos

  50. 2 phase commit T1,3 T1,1 (coord.) T1,2 prepare to commit Y N time 15-415 - C. Faloutsos