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

Download Presentation

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

Loading in 2 Seconds...

- 105 Views
- Uploaded on
- Presentation posted in: General

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

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 - - - - - - - - - - - - - - - - - - - - - - - - - -

CS 347: Parallel and DistributedData ManagementNotes02: Distributed DB Design

Hector Garcia-Molina

Notes 02

Chapter 5

Ozsu & Valduriez

Top-down approach: - have DB…

- how to split and

allocate the sites

Multi-DBs (or bottom-up): no design issues!

Notes 02

- Fragmentation
- Allocation
Note: issues not independent,

but will cover separately

Notes 02

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

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

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

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

Notes 02

F = { F1, F2 }

F1 = loc=SaE F2 = loc=SbE

called primary horizontal fragmentation

Notes 02

- Horizontal Primary
depends on local attributes

RDerived

depends on foreign relation

- Vertical
R

Notes 02

- Horizontal Primary
depends on local attributes

RDerived

depends on foreign relation

- Vertical
R

Fragmentation also called

Sharding

Notes 02

- Round robin
- Hash partitioning
- Range partitioning

Notes 02

RD0D1D2

t1t1

t2t2

t3t3

t4t4

...t5

- Evenly distributes data
- Good for scanning full relation
- Not good for point or range queries

Notes 02

RD0D1D2

t1h(k1)=2t1

t2h(k2)=0t2

t3h(k3)=0t3

t4h(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

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

Example:

F = { F1, F2 }

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

Notes 02

Example:

F = { F1, F2 }

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

Problem: Some tuples lost!

Notes 02

Second example:

F = { F3, F4 }

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

Notes 02

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

R F ={ F1, F2, … }

(1) Completeness

t R, Fi Fsuch that t Fi

Notes 02

(2) Disjointness

t Fi, Fj such that tFj, i j, Fi, FjF

(3) Reconstruction - ignore

Notes 02

(1) Check it “manually”!

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

Notes 02

(2) “Automatically” generate fragments

with these properties

Desired simple predicates Fragments

Notes 02

- Say queries use predicates:
A<10, A>5, Loc = SA, Loc = SB

- Next: - generate “minterm” predicates
- eliminate useless ones

Notes 02

(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

(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

5 < A < 10

A 5

(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

(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

A 10

(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

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

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

Predicates: p1 p2 p3 p4

p1 p2 p3 ¬p4

¬ p1 ¬p2 ¬p3 ¬p4

...

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

- 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

data A

try to

place in

same

fragment

frequently

accessed

together

data B

data C

Notes 02

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

Common queries:

Qa: select *Qb: select *

from Efrom E

where LOC=Sawhere LOC=Sb

and …and ...

Notes 02

(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

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 )

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

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

E2

E1

(at Sa)

(at Sb)

J

Notes 02

J1 = J E1J2 = J E2

E2

E1

(at Sa)

(at Sb)

J2

J1

Notes 02

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

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

To get

completeness

Need to enforce

referential integrity constraint:

join attr(#) of member relation

joint attr(#) of owner relation

Notes 02

Example:

E2

E1

Fragmentation

is not

disjoint!

J

J1

J2

Notes 02

To get

disjointness

Join attribute(#) should be

key of owner relation

Notes 02

- Type: primary, derived
- Properties: completeness, disjointness

Notes 02

E

Example:

E2

E1

Notes 02

...

R[T] R1[T1] Ti T

Rn[Tn]

Just like normalization of relations

Notes 02

(1) Completeness

U Ti = T

all i

Notes 02

(2) Disjointness

Ti Tj = for all i,j ij

E(#,LOC,SAL)

E1(#,LOC)

E2(SAL)

Notes 02

Not a desirable property!!

(could not reconstruct R!)

(2) Disjointness

Ti Tj = for all i,j ij

E(#,LOC,SAL)

E1(#,LOC)

E2(SAL)

Notes 02

(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

E1(#,NM,LOC)

E2(#,SAL)

Example:

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

E2(#,LOC)

E3(#,SAL)

?

Notes 02

A1 A2 A3 A4 A5

A1-----

A250----

A34548---

A4120--

A500475-

Notes 02

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

A1 A2 A3 A4 A5

A1-----

A250----

A34548---

A4120--

A500475-

Notes 02

- 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)

- Clustering and partitioning algorithms

Notes 02

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

- 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

- What is query processing strategy?
- How are joins done?
- Where are answers collected?

Notes 02

- Cost of updating copies?
- Writes and concurrency control?
- ...

Notes 02

- 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

This is an incredibly hard 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

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

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

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

Updates

ui updates/sec

..

F

F

.

.

.

F

.

i

Notes 02

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

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

+ Xi di

m

m

j

i=1

j=1

m

i=1

Notes 02

Examples:

- Multiple fragments

- Fragment sizes

- Concurrency control cost

Notes 02

- 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

- Issue: Where to locate data
- Issue: What and where to replicate

Notes 02

- Dynamic vs Static fragment placement
- Caching vs Replication

Notes 02

- 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

- Rule 1:
- IF TABLE_NAME = "Users“THENSET 'MIN_COPIES' = 2CONSTRAINT_PRI = 0

Notes 02

- 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

- Description of fragmentation
- Good fragmentations
- Design of fragmentation
- Allocation

Notes 02