5 Distributed Database Design Chapter 3 Distributed Database

5. Distributed Database Design Chapter 3 Distributed Database Design

Outline v Introduction v Fragmentation （片段划分） w Horizontal fragmentation （水平划分） w Derived horizontal fragmentation （导出式水平划分） w Vertical fragmentation （垂直划分） v Allocation （片段分配） 2

Outline F Introduction v Fragmentation （片段划分） w Horizontal fragmentation （水平划分） w Derived horizontal fragmentation （导出式水平划分） w Vertical fragmentation （垂直划分） v Allocation （片段分配） 3

Distributed Database Design The design of a distributed computer system involves making decisions on the placement of data and programs across the sites of a computer network. v In distributed DBMSs, such placement involves two things: v w Placement of the DDBMS software w Placement of the applications that run on the database v The course concentrates on distribution of data. w The distribution of DDBMS and applications are given a priori. 4

Framework of Distribution Behavior of access pattern Static Dynamic Complete Information No sharing Data + program sharing Level of knowledge on access pattern behavior Partial Information 5

Design Strategies v Top Down w Mostly in designing systems from scratch w Mostly in designing homogeneous systems v Bottom up w When the databases already exist at a number of sites v Combining both 6

Top Down Design Process User Input Requirement Analysis User Input Objectives Conceptual Design GCS View Integration View Design Access Information Distribution Design ES’s User Input LCS’s Physical Design LIS’s 7

Distribution Design Issues v Fragmentation w Why fragmentation at all? w How should we fragment? w How much should we fragment? w How to test correctness of decomposition? v Allocation v Necessary information required for fragmentation and allocation 8

Why Fragment? v Can’t we just distribute relations? v What is a reasonable unit of distribution? w Relation – Views are subsets of relations locality – Extra communication cost w Fragments of relations (sub relations) 9

Fragmentation v Unit of distribution = unit of data application accesses J Reduce irrelevant data access J Facilitate intra query concurrency over different fragments J Can be used with other performance enhancing methods (e. g. , indexing and clustering) L Applications have conflicting requirements, making disjoint fragmentation a very hard problem L Multiple fragment access requires join or union L Semantic data control (integrity enforcement) could be very costly 10

About fragmentation v How should we fragment? w Vertical Fragments sub grouping of attributes w Horizontal Fragments sub grouping oftuples w Mixed/Hybrid Fragments combination of above two v How much to fragment? w Too little too much of irrelevant data access w Too much too much processing cost w Need to find suitable level of fragmentation 11

Correctness Criteria v Completeness no loss of data w Decomposition of relation R into fragments R 1, R 2, …, Rn is complete if and only if each data item in R can also be found in some Ri. v Reconstruction w If relation R is decomposed into fragments R 1, R 2, …, Rn there should exist some relational operator such that R = 1 i n Ri v Disjointness w If relation R is decomposed into fragments R 1, R 2, …, Rn and data item di is in Rj, then di should not be in any other fragment Rk (k!=j). 12

Allocation Alternatives v Full Replication w Each fragment resides at each site v Partial Replication w Each fragment resides at some of the sites v Not replicated (Partitioned) w Each fragment resides at only one site Q & A: What are the advantages and disadvantages? 13

Allocation Alternatives 14

Rule of Thumb for Allocation If number of read only queries is more than number of update queries, then replication is advantageous, otherwise replication may cause problems. 15

Information Requirements v Four categories w Database information w Application information w Communication network information w Computer system information 16

Outline v Introduction F Fragmentation （片段划分） w Horizontal fragmentation （水平划分） w Derived horizontal fragmentation （导出式水平划分） w Vertical fragmentation （垂直划分） v Allocation （片段分配） 17

Fragmentation v Horizontal fragmentation (HF) w Primary horizontal fragmentation (PHF) – based on predicates accessing the relation w Derived horizontal fragmentation (DHF) – based on predicates being defined on another logically related relation We shall first study algorithm for horizontal fragmentation, and then study issues related to derived horizontal fragmentation. Vertical fragmentation (VF) v Hybrid fragmentation (HVF) v 18

PHF Information Requirements v Database Information > links PAY Title, Sal EMP L 1 PROJ Eno, Ename, Title L 2 ASG Jno, Jname, Budget. Loc L 3 Eno, Jno, Resp, Dur > Cardinality of each relation: card (R) 19

PHF Information Requirements(cont. ) v Application Information 1 w Simple predicate: Given R(A 1 , A 2 , . . . , An ), with each Ai having domain of values dom(Ai), a simple predicate pj is pj : Ai Value where {<, >, , , } and Value dom(Ai). Example Jname=“maintenance” Budget 200000 Follow ``80/20'' rule 20

PHF Information Requirements(cont. ) v Application Information 2 w minterm predicate: Given R and a set of simple predicates Pr = {p 1, p 2, . . . , pm} on R, the set of minterm predicates M = {m 1, m 2 , . . . , m z } is defined as M = {mi | mi = Pj Pr p*j }, 1 i z, 1 j z where p*j = pj or ¬pj Example m 1: m 2: m 3: m 4: (Jname=“maintenance”) (Budget 200000) ¬ (Jname=“maintenance”) (Budget 200000) (Jname=“maintenance”) ¬ (Budget 200000) ¬ (Jname=“maintenance”) ¬ (Budget 200000) 21

PHF Information Requirements(cont. ) v Application Information 3 w Minterm selectivity: sel (mi) – number of tuples of the relation that would be accessed by a user query specified according to a given minterm predicate. w Access frequency: acc (qi) – frequency with which user applications access data. If Q = {q 1 , q 2 , . . . , qn } is the set of queries, acc (qi ) indicates access frequency of query qi in a given period. 22

Primary Horizontal Fragmentation v Each horizontal fragment Ri of relation R is defined by Ri = Fi (R), 1 i w, where Fi is a selection formula, which is (preferably) a minterm predicate. w A horizontal fragment Ri of relation R consists of all the tuples of R which satisfy a minterm predicate mi. v Given a set of minterm predicates M, there as many as horizontal fragments of relation R as there are minterm predicates. v Set of horizontal fragments also referred to as minterm fragments 23

PHF Algorithm Input: A relation R, a set of simple predicates Pr Output: The set of fragments of R = {R 1 , R 2 , . . . , Rw }, which obey the fragmentation rules. Preliminaries: Ø Pr should be complete Ø Pr should be minimal 24

Completeness of Simple Predicates v A set of simple predicates Pr is said to be complete if and only if the accesses to the tuples of the minterm fragments defined on Pr requires that two tuples of the same minterm fragment have the same probability of being accessed by any application. 25

Completeness of Simple Predicates v Example: Applications: Q 1: Find the projects at each location Q 2: Find projects with budget less than $200, 000 incomplete Predicates: Pr={ LOC=“Montreal”, LOC=“New York”, LOC=“Paris”} Pr={ LOC=“Montreal”, LOC=“New York”, LOC=“Paris”, BUDGET 2000000, BUDGET >200000} 26

Minimality of Simple Predicates v If a predicate influences how fragmentation is performed, (i. e. , causes a fragment f to be further fragmented into, say, fi and fj), then there should be at least one application that accesses fi and fj differently. v In other words, the simple predicate should be relevant in determining a fragmentation. v If all the predicates of a set Pr are relevant, then Pr is minimal. 27

Minimality of Simple Predicates Example Applications: Q 1: Find the projects at each location Q 2: Find projects with budget less than $200, 000 Pr={ LOC=“Montreal”, LOC=“New York”, LOC=“Paris”, BUDGET 2000000, BUDGET >200000} minimal Pr={ LOC=“Montreal”, LOC=“New York”, LOC=“Paris”, BUDGET 2000000, BUDGET >200000, Minimal? ? PNAME=“Instrumentation”} 28

COM_MIN Algorithm Input: A relation R and a set of simple predicates Pr Output: A complete and minimal set of simple predicates Pr ’ for Pr Rule 1: A relation or fragment is partitioned into at least two parts which are accessed differently by at least one application. 29

COM_MIN Algorithm (cont. ) 1. Initialization • Find a pi Pr such that pi partitions R according to Rule 1 • Set Pr’ = pi ; Pr Pr pi ; F fi 2. Iteratively add predicates to Pr ’ until it is complete • Find a pj Pr such that pj partitions some fk defined according to minterm predicate over Pr’ according to Rule 1 • P r’ p j ; P r = P r p j ; F f j • If pk Pr ’ which is nonrelevant, then P r ’ = P r ’ pk ; F F f k 30

PHORIZONTAL Algorithm Make use of COM_MIN to perform fragmentation Input: A relation R and a set of simple predicates Pr Output: A set of minterm predicates M according to which relation R is to be fragmented Steps: w Pr ’ COM_MIN(R, Pr) w Determine the set M of minterm predicates w Determine the set I of implications among pi Pr ’ Eliminate the contradictory minterms from M 31

Contradictory Minterms v Given a minimal and complete set of simple predicates, containing n simple predicates v Not all the minterm fragments derived are valid w A fragment can be self contradictory because of implications among simple predicates. Example: Dom(Sal): [10000, 200000]; Dom(Loc) = {HK, SF} p 1 : sal < 50000; p 2 : Loc = HK; p 3 : Loc = SF Note: p 2 (¬ p 3 ); p 3 (¬ p 2 ) the minterm p 1 p 2 p 3 is self contradictory. 32

PHORIZONTAL Algorithm (cont. ) Input: relation R and a set of simple predicates Pr Output: a set of minterm fragments M begin Pr’ = COM MIN(R, Pr ); M = set of minterm predicates from Pr’ I = set of implications among pi Pr’ for each mi M if mi is contradictory according to I then M = M mi end 33

PHF Example: PAY Application: Check the salary info and determine raise Employee records kept at two sites ==> application runs at two sites Simple predicates: P 1: SAL 30000, P 2: SAL > 30000 Pr = {P 1, P 2}, which is complete and minimal Pr’ = Pr Minterm predicates: m 1: (SAL 30000); m 2: (SAL > 30000) PAY 1 PAY 2 34

PHF Example: PROJ Applications w Find the name and budget of projects given their locations – Issued at three sites w Access project information according to budget – One site access 200000; other accesses >200000 m 1: LOC = “Montreal” BUDGET 200000 m 2 : LOC = “Montreal” BUDGET > 200000 m 3 : LOC = “New York” BUDGET 200000 m 4 : LOC = “New York” BUDGET > 200000 m 5 : LOC = “Paris” BUDGET 200000 M 6 : LOC = “Paris” BUDGET > 200000 p 1: LOC = “Montreal” p 2 : LOC = “New York” p 3 : LOC = “Paris” p 4: BUDGET 200000 p 5 : BUDGET > 200000 35

PHF Example: PROJ Result PROJ 1 PROJ 2 m 1: LOC = “Montreal” BUDGET 200000 m 2 : LOC = “Montreal” BUDGET > 200000 m 3 : LOC = “New York” BUDGET 200000 m 4 : LOC = “New York” BUDGET > 200000 m 5 : LOC = “Paris” BUDGET 200000 M 6 : LOC = “Paris” BUDGET > 200000 PROJ 4 PROJ 6 36

PHF Correctness v Completeness w Since Pr is complete and minimal, the selection predicates are complete v Reconstruction w If relation R is fragmented into FR = {R 1, R 2, …, Rr} R = Ri FR Ri v Disjointness w Minterm predicates that form the basis of fragmentation should be mutually exclusive 37

DHF: Derived Horizontal Fragmentation Owner relation PAY Title, Sal EMP Each link is an equijoin L 1 PROJ Eno, Ename, Title L 2 Member relation DHF: Defined on a member relation according to a selection operation on its owner ASG Jno, Jname, Budget. Loc L 3 Eno, Jno, Resp, Dur 38

PAY Title, Sal DHF – Example PAY 1= SAL 30000 PAY 2= SAL> 30000 PAY EMP Eno, Ename, Title EMP 1 = EMP L 1 PAY 1 DHF EMP 2 = EMP PAY 2 39

DHF: Derived Horizontal Fragmentation Let S be horizontally fragmented and let there be a link L with owner(L) = S, and member(L) = R, the derived horizontal fragments of R are defined as Ri = R Si , 1 i w where Si is the horizontal fragment of S, is the semi join operator, andw is the maximum number of fragments v Inputs to derived horizontal fragmentation: v w partitions of owner relation w member relation w the semi join condition v The algorithm is straight forward. 40

DHF: Correctness v Completeness w primary horizontal fragmentation based on completeness of selection predicates. For derived horizontal fragmentation based on referential integrity v Reconstruction w Same as primary horizontal fragmentation (via union) v Disjointedness w Simple join graphs between the owner and the member fragments. 41

DHF: Issues v Multiple owners for a member relation; how should we derived horizontally fragments of a member relation? v There could be a chain of derived horizontal fragmentation. 42

Vertical Fragmentation (VF) Has been studied within the centralized context v Vertical partitioning of a relation R produces fragments R 1, R 2, . . . , Rm, each of which contains a subset of R's attributes as well as the primary key of R v The object of vertical fragmentation is to reduce irrelevant attribute access, and thus irrelevant data access v “Optimal” vertical fragmentation is one that minimizes the irrelevant data access for user applications v 43

VF Two Approaches v Grouping: each individual attribute one fragment, at each step join some of the fragments until some criteria being satisfied w Attributes to fragments v Splitting: start with global relation, and generate beneficial partitions based on access behavior of the applications w Relations to fragments 44

Vertical Fragmentation v Overlapping fragments w grouping v Non overlapping fragments w Splitting v We do not consider the replicated key attributes to be overlapping w Advantage – easier to enforce functional dependencies (for integrity checking) 45

VF Information Requirements v Application Information w Attribute affinities – A measure that indicates how closely related the attributes are – This is obtained from more primitive usage data w Attribute usage values – Given a set of queries Q = {q 1 , q 2, . . . , qm} that will run on relation R (A 1, A 2, . . . , An) use (qi, Aj ) = 1 if attribute Aj is referenced by query qi 0 otherwise use (qi, . ) can be defined accordingly. 46

VF Definition of use(qi, Aj) v Consider the following 4 queries for relation PROJ q 1: SELECT BUDGET FROM PROJ WHERE PNO = val; q 2: SELECT PNAME, BUDGET FROM PROJ; q 3: SELECT PNAME FROM PROJ WHERE LOC = val; q 4: SELECT SUM(BUDGET) FROM PROJ WHERE LOC=val; Let A 1= PNO, A 2= PNAME, A 3 = BUDGET, A 4 = LOC A 1 A 2 A 3 A 4 q 1 q 2 q 3 q 4 47

VF Affinity Measure aff(Ai, Aj) v The attribute affinity measure between two attributes Ai and Aj of a relation R (A 1, A 2, . . . , An) with respect to the set of applications Q = {q 1 , q 2, . . . , qm} is defined as: Aff (Ai, Aj) = { k | use (qk, Ai)=1 use (qk, Aj)=1} l refl (qk) * accl (qk) where refl (qk) is the number of accesses to attributes for each execution of application qk at site l; accl (qk) is the access frequency of query qk at site l. 48

VF Affinity Measure aff(Ai, Aj) q 1 S 2 S 3 q 2 q 3 q 4 Assume each query in the previous example accesses the attribu Also assume the access frequency of query k at different sites is then Aff (A 1, A 3) = 15*1 + 20*1 + 10*1 = 45 and the. Aattribute affinity matrix AA is: 1 A 2 A 3 A 4 q 1 q 2 A 1 A 2 A 3 A 4 q 3 q 4 49

AA Matrix for Vertical Fragmentation v This affinity matrix will be used to guide the fragmentation effort. The process involves first clustering together the attributes with high affinity for each other, and then splitting the relation accordingly. 50

VF Correctness A relation R, defined over attribute set A and key K, generates vertical partitioning FR = {R 1 , R 2 , … , Rr} v Completeness v Reconstruction R = v Disjointness k Ri FR w Duplicate keys are not considered to be overlapping 51

Hybrid Fragmentation R HF R 1 R 2 VF VF R 11 R 12 R 21 R 22 R 23 52

Outline v Introduction v Fragmentation （片段划分） w Horizontal fragmentation （水平划分） w Derived horizontal fragmentation （导出式水平划分） w Vertical fragmentation （垂直划分） F Allocation （片段分配） 53

Allocation v File Allocation vs. Database Allocation w Fragments are not individual files – Relationships have to be maintained w Access to databases is more complicated – Remote file access model not applicable – Relationship between allocation and query processing w Cost of integrity enforcement should be considered w Cost of concurrency control should be considered 54

Allocation Problem v Assuming: A set of fragments A set of sites A set of applications Problem Find the optimal distribution of F over S 55

Optimality with Two Aspects v Minimal cost w Storing Fi at Sj w Querying Fi at Sj w Updating Fi at all Sj 's with a copy of Fi w Communication v Performance w Response time w Throughput w …… Separate the two issues to reduce its complexity. 56

A Simple Formulation of the Cost Problem 1 v For a single fragment Fi w R = {r 1, r 2, …, rm} rj : read only traffic generated at Sj for Fi w U = {u 1, u 2, …, um} uj : update traffic generated at Sj for Fi F, S, Q are defined as before 57

A Simple Formulation of the Cost Problem (cont. ) v Assume the communication cost between any pair of sites Si and Sj is fixed w C(T ) = {c 11, c 12, c 13, …, c 1, m, …, cm-1, m} cij : retrieval communication cost w C’(U ) = {c’ 11, c’ 12, c’ 13, …, c’ 1, m, …, c’m-1, m} c’ij : update communication cost 58

A Simple Formulation of the Cost Problem (cont. ) 3 D = {d 1, d 2, …, dm} cost for storing Fi at Sj No capacity constraints for sites and communication links 59

A Simple Formulation of the Cost Problem (cont. ) Let The allocation problem is a cost minimization problem for finding the set I i. e. , the sites to store the fragment Fi, where 60

A Simple Formulation of the Cost Problem (cont. ) For queries from site i Reads : Updates : Storage : Total cost This formulation only considers one fragment Fi at site Sj. It is NP complete. 61

A Precise Formulation of the Cost Problem v A precise formulation must consider: w All fragments together w How query is processed w The enforcement of integrity constraint w The cost of concurrency control and transaction control 62

Allocation Model in General v Allocation Model min (total Cost) subject to – response time constraint – storage constraint – processing constraint w Decision variable if fragment Fi is stored at site Sj otherwise 63

Allocation Model (cont. ) v Total Cost ∑all queries query processing cost + ∑all sites ∑all fragments cost of storing a fragment at a site v Storage Cost (on fragment Fj at site Sk) (unit storage cost at Sk) * (size of Fj) * xjk v Query processing Cost (for one query) processing component + transmission component 64

Allocation Model (cont. ) v Query Processing Cost Processing component access cost + integrity enforcement cost + concurrency control cost Ø access cost: ∑all sites ∑all fragments (number of update accesses + number of read accesses) * xjk * local processing cost at site Ø integrity enforcement and concurrency control costs can be similarly calculated. 65

Allocation Model (cont. ) v Query Processing Cost Transmission component cost of processing updates + cost of processing retrievals Ø Cost of updates ∑all sites ∑all fragments update message cost + ∑all sites ∑all fragments acknowledgement cost Ø Retrieval costs ∑all fragments minall sites (cost of retrieval command + cost of sending back the result) 66

Allocation Model (cont. ) v Constraints w Response time for each query not longer than maximally allowed response time for that query w Storage constraint: The total size of all fragments allocated at a site must be less than the storage capacity at that site. w Processing constraint: The total processing load because of all queries at a site must be less than the processing capacity at that site. 67

Solution Methods v NP complete v Heuristics approaches w Exploring techniques developed in operational research (运筹学) v Reduce problem complexity w ignore replication first, and then improve with a greedy algorithm 68

Question & Answer