CS 347: Parallel and Distributed Data Management Notes02: Distributed DB Design. Hector Garcia-Molina. Chapter 5 Ozsu & Valduriez. Distributed DB Design. Top-down approach: - have DB… - how to split and allocate the sites
01 / 80CS 347: Parallel and Distributed Data Management Notes02: Distributed DB Design
1 / 17
Télécharger la présentation
CS 347: Parallel and Distributed Data Management Notes02: Distributed DB Design
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.
Chapter 5 Ozsu & Valduriez Distributed DB Design Top-down approach: - have DB… - how to split and allocate the sites Multi-DBs (or bottom-up): no design issues! Notes 02
Two issues in DDB design: • Fragmentation • Allocation Note: issues not independent, but will cover separately Notes 02
Example Employee relation E (#,name,loc,sal,…) 40% of queries: 40% of queries: Qa: select * Qb: select * from E from E where loc=Sa where loc=Sb and… and ... Notes 02
Motivation: Two sites: Sa, Sb Qa Qb Sa Sb Example Employee relation E (#,name,loc,sal,…) 40% of queries: 40% of queries: Qa: select * Qb: select * from E from E where loc=Sa where loc=Sb and… and ... Notes 02
5 Joe Sa 10 # NM Loc Sal E 7 Sally Sb 25 8 Tom Sa 15 .. .. F # NM Loc Sal # NM Loc Sal 5 Joe Sa 10 7 Sally Sb 25 .. 8 Tom Sa 15 .. At Sb At Sa Notes 02
F = { F1, F2 } F1 = loc=SaE F2 = loc=SbE called primary horizontal fragmentation Notes 02
Fragmentation • Horizontal Primary depends on local attributes R Derived depends on foreign relation • Vertical R Notes 02
Fragmentation • Horizontal Primary depends on local attributes R Derived depends on foreign relation • Vertical R Fragmentation also called Sharding Notes 02
Round robin R D0 D1 D2 t1 t1 t2 t2 t3 t3 t4 t4 ... t5 • Evenly distributes data • Good for scanning full relation • Not good for point or range queries Notes 02
Hash partitioning R D0 D1 D2 t1h(k1)=2 t1 t2h(k2)=0 t2 t3h(k3)=0 t3 t4h(k4)=1 t4 ... • Good for point queries on key; also for joins • Not good for range queries; point queries not on key • If hash function good, even distribution Notes 02
Range partitioning R D0 D1 D2 t1: A=5 t1 t2: A=8 t2 t3: A=2 t3 t4: A=3 t4 ... • Good for some range queries on A • Need to select good vector: else unbalance data skew execution skew partitioning vector 4 7 V0 V1 Notes 02
Which are good fragmentations? Second example: F = { F3, F4 } F3 = sal<10 E F4 = sal>5 E Tuples with 5 < sal < 10 are duplicated... Notes 02
Prefer to deal with replication explicitly Example: F = { F5, F6, F7 } F5 = sal 5 E F6 = 5< sal <10 EF7 = sal 10 E Then replicate F6 if convenient (part of allocation problem) Notes 02
Example of generation • Say queries use predicates: A<10, A>5, Loc = SA, Loc = SB • Next: - generate “minterm” predicates - eliminate useless ones Notes 02
Final fragments: F2: 5 < A < 10 Loc=SA F3: 5 < A < 10 Loc=SB F6: A 5 Loc=SA F7: A 5 Loc=SB F10: A 10 Loc=SA F11: A 10 Loc=SB Notes 02
Note: elimination of useless fragments depends on application semantics: e.g.: if LOC could be SA, SB, we need to add fragments F4: 5 <A <10 Loc SA Loc SB F8: A 5 Loc SA Loc SB F12: A 10 Loc SA Loc SB Notes 02
(1) Completeness: Take t R pi(t) must be T or F! Say p1(t) =T p2(t) = T p3(t) =F p4(t) =F Then t is in fragment with predicate p1 p2 ¬p3 ¬p4 Notes 02
(2) Disjointness Say t Fragment p1 p2 ¬p3 ¬p4 Then: p1(t) = T, p2(t) = T, p3(t) = F, p4(t)= F t cannot be in any other fragment! Notes 02
Summary • Given simple predicates Pr= { p1, p2,.. pm } minterm predicates are M={m | m = pk*, 1 k m } where pk* is pk or is ¬ pk pkPr • Fragments m R for all m M are • complete and disjoint Notes 02
Return to example: E(#, NM, LOC, SAL,…) Common queries: Qa: select * Qb: select * from E from E where LOC=Sa where LOC=Sb and … and ... Notes 02
Three choices: (1) Pr = { } F1 ={ E } (2) Pr = {LOC=Sa, LOC=Sb} F2={ loc=Sa E, loc=Sb E } (3) Pr = {LOC=Sa, LOC=Sb, Sal<10} F3={ loc=Sa sal<10 E, loc=Sa sal10 E, loc=Sb sal<10E, loc=Sb sal10 E } Notes 02
In other words: Qa: Select … loc = Sa ... Loc=Sa sal < 10 Qb: Select … loc = Sb ... Loc=Sa sal 10 F3 F1 F2 Loc=Sb sal < 10 Loc=Sb sal 10 Notes 02
F2 is good… (not F1 , F3 ) In other words: Qa: Select … loc = Sa ... Loc=Sa sal < 10 Qb: Select … loc = Sb ... Loc=Sa sal 10 F3 F1 F2 Loc=Sb sal < 10 Loc=Sb sal 10 Notes 02
Derived horizontal fragmentation Example: E(#, NM, SAL, LOC)F={ E1, E2} by LOC J(#, DES,…) Common query for project: [Given employee name, list projects (s)he works in] Notes 02
Derived horizontal fragmentation F could be primary or derived R, F = { F1, F2, ... Fn} S, D = {D1, D2, …Dn} where Di =S Fi Convention: R is owner S is member Notes 02
