Cs 347 parallel and distributed data management notes02 distributed db design
This presentation is the property of its rightful owner.
Sponsored Links
1 / 80

CS 347: Parallel and Distributed Data Management Notes02: Distributed DB Design PowerPoint PPT Presentation


  • 95 Views
  • Uploaded on
  • Presentation posted in: General

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

Download Presentation

CS 347: Parallel and Distributed Data Management Notes02: Distributed DB Design

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

Presentation Transcript


Cs 347 parallel and distributed data management notes02 distributed db design

CS 347: Parallel and DistributedData ManagementNotes02: Distributed DB Design

Hector Garcia-Molina

Notes 02


Distributed db design

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

Two issues in DDB design:

  • Fragmentation

  • Allocation

    Note: issues not independent,

    but will cover separately

Notes 02


Example

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


Example1

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


Cs 347 parallel and distributed data management notes02 distributed db design

  • It does not take a rocket scientist to figure out fragmentation...

Notes 02


Cs 347 parallel and distributed data management notes02 distributed db design

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


Cs 347 parallel and distributed data management notes02 distributed db design

F = { F1, F2 }

F1 =  loc=SaE F2 =  loc=SbE

Notes 02


Cs 347 parallel and distributed data management notes02 distributed db design

F = { F1, F2 }

F1 =  loc=SaE F2 =  loc=SbE

 called primary horizontal fragmentation

Notes 02


Fragmentation

Fragmentation

  • Horizontal Primary

    depends on local attributes

    RDerived

    depends on foreign relation

  • Vertical

    R

Notes 02


Fragmentation1

Fragmentation

  • Horizontal Primary

    depends on local attributes

    RDerived

    depends on foreign relation

  • Vertical

    R

Fragmentation also called

Sharding

Notes 02


Three common horizontal partitioning techniques

Three common horizontal partitioning techniques

  • Round robin

  • Hash partitioning

  • Range partitioning

Notes 02


Round robin

Round robin

RD0D1D2

t1t1

t2t2

t3t3

t4t4

...t5

  • Evenly distributes data

  • Good for scanning full relation

  • Not good for point or range queries

Notes 02


Hash partitioning

Hash partitioning

RD0D1D2

t1h(k1)=2t1

t2h(k2)=0t2

t3h(k3)=0t3

t4h(k4)=1t4

...

  • 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

Range partitioning

RD0D1D2

t1: A=5t1

t2: A=8t2

t3: A=2t3

t4: A=3t4

...

  • 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

Which are good fragmentations?

Example:

F = { F1, F2 }

F1 = sal<10 E F2 = sal>20 E

Notes 02


Which are good fragmentations1

Which are good fragmentations?

Example:

F = { F1, F2 }

F1 = sal<10 E F2 = sal>20 E

 Problem: Some tuples lost!

Notes 02


Which are good fragmentations2

Which are good fragmentations?

Second example:

F = { F3, F4 }

F3 = sal<10 E F4 = sal>5 E

Notes 02


Which are good fragmentations3

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


Cs 347 parallel and distributed data management notes02 distributed db design

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


Desired properties for horizontal fragmentation

Desired properties for horizontal fragmentation

R F ={ F1, F2, … }

(1) Completeness

t  R,  Fi  Fsuch that t Fi

Notes 02


Cs 347 parallel and distributed data management notes02 distributed db design

(2) Disjointness

t  Fi,  Fj such that tFj, i  j, Fi, FjF

(3) Reconstruction - ignore

Notes 02


How do we get completeness and disjointness

How do we get completeness and disjointness?

(1) Check it “manually”!

e.g., F1 =  sal<10 E ; F2 =  sal10 E

Notes 02


How do we get completeness and disjointness1

How do we get completeness and disjointness?

(2) “Automatically” generate fragments

with these properties

Desired simple predicates  Fragments

Notes 02


Example of generation

Example of generation

  • Say queries use predicates:

    A<10, A>5, Loc = SA, Loc = SB

  • Next: - generate “minterm” predicates

    - eliminate useless ones

Notes 02


Minterm predicates part i

Minterm predicates (part I)

(1) A<10  A>5  Loc=SA Loc=SB

(2) A<10  A>5  Loc=SA ¬(Loc=SB)

(3) A<10  A>5  ¬(Loc=SA)  Loc=SB

(4) A<10  A>5  ¬(Loc=SA)  ¬(Loc=SB)

(5) A<10  ¬(A>5)  Loc=SA Loc=SB

(6) A<10  ¬(A>5)  Loc=SA ¬(Loc=SB)

(7) A<10  ¬(A>5)  ¬(Loc=SA)  Loc=SB

(8) A<10  ¬(A>5)  ¬(Loc=SA)  ¬(Loc=SB)

Notes 02


Minterm predicates part i1

Minterm predicates (part I)

(1) A<10  A>5  Loc=SA Loc=SB

(2) A<10  A>5  Loc=SA ¬(Loc=SB)

(3) A<10  A>5  ¬(Loc=SA)  Loc=SB

(4) A<10  A>5  ¬(Loc=SA)  ¬(Loc=SB)

(5) A<10  ¬(A>5)  Loc=SA Loc=SB

(6) A<10  ¬(A>5)  Loc=SA ¬(Loc=SB)

(7) A<10  ¬(A>5)  ¬(Loc=SA)  Loc=SB

(8) A<10  ¬(A>5)  ¬(Loc=SA)  ¬(Loc=SB)

Notes 02


Minterm predicates part i2

5 < A < 10

A  5

Minterm predicates (part I)

(1) A<10  A>5  Loc=SA Loc=SB

(2) A<10  A>5  Loc=SA ¬(Loc=SB)

(3) A<10  A>5  ¬(Loc=SA)  Loc=SB

(4) A<10  A>5  ¬(Loc=SA)  ¬(Loc=SB)

(5) A<10  ¬(A>5)  Loc=SA Loc=SB

(6) A<10  ¬(A>5)  Loc=SA ¬(Loc=SB)

(7) A<10  ¬(A>5)  ¬(Loc=SA)  Loc=SB

(8) A<10  ¬(A>5)  ¬(Loc=SA)  ¬(Loc=SB)

Notes 02


Minterm predicates part ii

Minterm predicates (part II)

(9) ¬(A<10)  A>5  Loc=SA Loc=SB

(10) ¬(A<10)  A>5  Loc=SA¬(Loc=SB)

(11) ¬(A<10)  A>5 ¬(Loc=SA)  Loc=SB

(12) ¬(A<10)  A>5 ¬(Loc=SA) ¬(Loc=SB)

(13) ¬(A<10) ¬(A>5)  Loc=SA Loc=SB

(14) ¬(A<10) ¬(A>5)  Loc=SA¬(Loc=SB)

(15) ¬(A<10) ¬(A>5) ¬(Loc=SA)  Loc=SB

(16) ¬(A<10) ¬(A>5) ¬(Loc=SA) ¬(Loc=SB)

Notes 02


Minterm predicates part ii1

A  10

Minterm predicates (part II)

(9) ¬(A<10)  A>5  Loc=SA Loc=SB

(10) ¬(A<10)  A>5  Loc=SA¬(Loc=SB)

(11) ¬(A<10)  A>5 ¬(Loc=SA)  Loc=SB

(12) ¬(A<10)  A>5 ¬(Loc=SA) ¬(Loc=SB)

(13) ¬(A<10) ¬(A>5)  Loc=SA Loc=SB

(14) ¬(A<10) ¬(A>5)  Loc=SA¬(Loc=SB)

(15) ¬(A<10) ¬(A>5) ¬(Loc=SA)  Loc=SB

(16) ¬(A<10) ¬(A>5) ¬(Loc=SA) ¬(Loc=SB)

Notes 02


Final fragments

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

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


Why does this work

Why does this work?

Predicates: p1  p2  p3  p4

p1  p2  p3  ¬p4

¬ p1  ¬p2  ¬p3  ¬p4

...

Notes 02


Cs 347 parallel and distributed data management notes02 distributed db design

(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


Cs 347 parallel and distributed data management notes02 distributed db design

(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

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

pkPr

  • Fragments m R for all m  M are

  • complete and disjoint

Notes 02


Another desired fragmentation property match access patterns

Another Desired Fragmentation Property:Match Access Patterns

data A

try to

place in

same

fragment

frequently

accessed

together

data B

data C

Notes 02


Return to example

Return to example:

E(#, NM, LOC, SAL,…)

Common queries:

Qa: select *Qb: select *

from Efrom E

where LOC=Sawhere LOC=Sb

and …and ...

Notes 02


Three choices

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  sal10 E,

loc=Sb  sal<10E, loc=Sb  sal10 E }

Notes 02


In other words

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


In other words1

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

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


Cs 347 parallel and distributed data management notes02 distributed db design

E2

E1

(at Sa)

(at Sb)

J

Notes 02


Cs 347 parallel and distributed data management notes02 distributed db design

J1 = J E1J2 = J E2

E2

E1

(at Sa)

(at Sb)

J2

J1

Notes 02


Derived horizontal fragmentation1

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


Checking completeness and disjointness of derived fragmentation

Checking completeness and disjointness of derived fragmentation

Example: Say J is:

 But no #= 33in E1 nor in E2!

This J tuple will not be in J1 nor J2

Fragmentation not complete

Notes 02


Cs 347 parallel and distributed data management notes02 distributed db design

To get

completeness

Need to enforce

referential integrity constraint:

join attr(#) of member relation

joint attr(#) of owner relation

Notes 02


Cs 347 parallel and distributed data management notes02 distributed db design

Example:

E2

E1

Fragmentation

is not

disjoint!

J

J1

J2

Notes 02


Cs 347 parallel and distributed data management notes02 distributed db design

To get

disjointness

Join attribute(#) should be

key of owner relation

Notes 02


Summary horizontal fragmentation

Summary: horizontal fragmentation

  • Type: primary, derived

  • Properties: completeness, disjointness

Notes 02


Vertical fragmentation

Vertical fragmentation

E

Example:

E2

E1

Notes 02


Cs 347 parallel and distributed data management notes02 distributed db design

...

R[T] R1[T1] Ti  T

Rn[Tn]

 Just like normalization of relations

Notes 02


Properties r t r i t i

Properties: R[T] Ri[Ti]

(1) Completeness

U Ti = T

all i

Notes 02


Cs 347 parallel and distributed data management notes02 distributed db design

(2) Disjointness

Ti  Tj =  for all i,j ij

E(#,LOC,SAL)

E1(#,LOC)

E2(SAL)

Notes 02


Cs 347 parallel and distributed data management notes02 distributed db design

Not a desirable property!!

(could not reconstruct R!)

(2) Disjointness

Ti  Tj =  for all i,j ij

E(#,LOC,SAL)

E1(#,LOC)

E2(SAL)

Notes 02


Cs 347 parallel and distributed data management notes02 distributed db design

(3) Lossless join

Ri = R

all i

 One way to achieve lossless join:

Repeat key in all fragments, i.e.,

Key  Ti for all i

Notes 02


How do we decide what attributes are grouped with which

 How do we decide what attributes are grouped with which?

E1(#,NM,LOC)

E2(#,SAL)

Example:

E(#,NM,LOC,SAL)E1(#,NM)

E2(#,LOC)

E3(#,SAL)

?

Notes 02


Attribute affinity matrix

Attribute affinity matrix

A1 A2 A3 A4 A5

A1-----

A250----

A34548---

A4120--

A500475-

Notes 02


Attribute affinity matrix1

R1[K,A1,A2,A3] R2[K,A4,A5]

Attribute affinity matrix

A1 A2 A3 A4 A5

A1-----

A250----

A34548---

A4120--

A500475-

Notes 02


Cs 347 parallel and distributed data management notes02 distributed db design

  • Textbook (Ozsu & Valduriez) discusses

    • How to build affinity matrix

    • How to identify attribute clusters

    • How to partition relation

  • You are not responsible for

    • Clustering and partitioning algorithms

      (i.e., Skip pages 135-145)

Notes 02


Allocation

Allocation

Example: E(#,NM,LOC,SAL) 

F1 = loc=Sa E ; F2 = loc=Sb E

Qa: select … where loc=Sa...

Qb: select … where loc=Sb…

Where do

F1,F2 go?

Site b

Site a

?

Notes 02


Issues

Issues

  • Where do queries originate

  • What is communication cost?and size of answers, relations,…

  • What is storage capacity, cost at sites?and size of fragments?

  • What is processing power at sites?

Notes 02


More issues

More Issues

  • What is query processing strategy?

    • How are joins done?

    • Where are answers collected?

Notes 02


Do we replicate fragments

Do we replicate fragments?

  • Cost of updating copies?

  • Writes and concurrency control?

  • ...

Notes 02


Optimization problem

Optimization problem:

  • What is best placement of fragments and/or best number of copies to:

    • minimize query response time

    • maximize throughput

    • minimize “some cost”

    • ...

  • Subject to constraints?

    • Available storage

    • Available bandwidth, power,…

    • Keep 90% of response time below X

    • ...

Notes 02


Optimization problem1

This is an incredibly hard problem

Optimization problem:

  • What is best placement of fragments and/or best number of copies to:

    • minimize query response time

    • maximize throughput

    • minimize “some cost”

    • ...

  • Subject to constraints?

    • Available storage

    • Available bandwidth, power,…

    • Keep 90% of response time below X

    • ...

Notes 02


Example single fragment f

Example: Single fragment F

m

Read cost:  [ti MIN Cij]

i:Originating site of request

ti:Read traffic at Si

Cij:Retrieval cost

Accessing fragment F at Sj from Si

i=1

j

Notes 02


Scenario read cost

Scenario - Read cost

F

F

.

.

12

.

..

ci,1

ci,2

C=inf

3

ci,3

F

.

i

Stream of read

requests for F

ti REQ/SEC

C=inf

C=inf

Notes 02


Write cost

Write cost

m

m

i=1

j=1

  Xj ui C’ij

i: Originating site of request

j: Site being updated

Xj: 0 if F not stored at Sj

1 if F stored at Sj

ui: Write traffic at Si

C’ij: Write cost

Updating F at Sj fromSi

Notes 02


Scenario write cost

Scenario - write cost

Updates

ui updates/sec

..

F

F

.

.

.

F

.

i

Notes 02


Storage cost

Storage cost:

m

 Xi di

Xi:0 if F not stored at Si

1 if F stored at Si

di:storage cost at Si

i=1

Notes 02


Target function

Target function:

min  [ti MIN Cij+  Xj  ui  C’ij ]

+  Xi  di

m

m

j

i=1

j=1

m

i=1

Notes 02


Can add more complications

Can add more complications:

Examples:

- Multiple fragments

- Fragment sizes

- Concurrency control cost

Notes 02


Case study pnuts

Case Study: PNUTS

  • Where in the World is My Data?SudarshanKadambi, JianjunChen, Brian F. Cooper, David Lomax, Raghu Ramakrishnan, Adam Silberstein, Erwin Tam, Hector Garcia-Molina; VLDB 2011

  • Distributed object/tuple store for Yahoo!

Notes 02


Case study pnuts1

Case Study: PNUTS

  • Issue: Where to locate data

  • Issue: What and where to replicate

Notes 02


Pnuts discussion

PNUTS Discussion

  • Dynamic vs Static fragment placement

  • Caching vs Replication

Notes 02


Policy constraints

Policy Constraints

  • MIN_COPIES: The minimum number of full replicas of the record that must exist.

  • INCL_LIST: An inclusion list -- the locations where a full replica of the record must exist.

  • EXCL_LIST: An exclusion list -- the locations where a full replica of the record cannot exist.

Notes 02


Example rule

Example Rule

  • Rule 1:

  • IF TABLE_NAME = "Users“THENSET 'MIN_COPIES' = 2CONSTRAINT_PRI = 0

Notes 02


Another example rule

Another Example Rule

  • Rule 2:

  • IF TABLE_NAME = "Users" ANDFIELD STR('home location') = 'France‘THENSET 'MIN_COPIES' = 3 ANDSET 'EXCL LIST' = 'USWest, USEast‘CONSTRAINT PRI = 1

Notes 02


Summary1

Summary

  • Description of fragmentation

  • Good fragmentations

  • Design of fragmentation

  • Allocation

Notes 02


  • Login