PPT Distributed Database Design

Distributed Database Design

The world’s largest digital library

Try Scribd FREE for 30 days to access over 125 million titles without ads or interruptions! Start Free Trial Cancel Anytime.



















Objectives


•

Defi Defini niti tio on of of Di Distri stribu butted Datab atabas ase e

•

Mot Motivat ivatiion fo for Dist Distri ribu bute ted d Dat Data abase base

•

Adva Advant ntag age e of of Dis Distr triibute buted d Dat Datab abas ase e

•

Disa Disadv dvan anta tage ges s of Dist Distri ribu bute ted d Data Databa base se

•

Tran Transa sact ctio ions ns Mana Manage geme ment nt in a Dis Distr trib ibut uted ed Data Databa base se envi enviro ronm nmen entt

•

Desi Design gn of the the Dist Distri ribu bute ted d Data Databa base se envi enviro ronm nmen entt





















Definition

•


Dist Distri ribu bute ted d data databa base se man manag agem emen entt syst system em (DD (DDBM BMS) S) –

Distribut Distributed ed Database Database:: A logicall logically y interrela interrelated ted collec collection tion of of shared shared data, data, physicall physically y distribut distributed ed over a computer network

–

Distribut Distributed ed DBMS DBMS (DDBM (DDBMS): S): Softw Software are system system that permits permits the the manage management ment of the the distributed database and makes the distribution transparent to users •

Governs storage and processi processing ng of logically logically related related data over interconnected interconnected computer computer systems systems in which both data and processing functions are distributed among several sites





















Motivation for Distributed Database


•

The The deve develo lopm pmen entt of comp comput uter er net netwo work rk prom promot otes es dede-ce cent ntra rali liza zati tion on

•

In a comp company any,, the databa database se organ organiza izatio tion n might might refle reflect ct the the organi organizat zation ional al struc structur ture, e, which which is distributed into units. Each unit maintains its own database

•

Sharin Sharing g of data data can be achi achieve eved d by deve develop loping ing a distr distribu ibuted ted databa database se syst system em whic which: h: –

Make Makes s data data acc acces essi sibl ble e by all all uni units ts

–

Stores Stores data data close close to where where itit is is most most frequen frequently tly used used






















Distributed Processing Environment






















Distributed Database Management System























DDBMS Advantages

•

Data Data are are loc locat ate ed ne near “gre “grea atest test dema deman nd” site site

•

Faster da data access

•

Faster da data pr processing
























DDBMS Disadvantages

•

Com Comple plexity xity of mana manage geme men nt an and con contr tro ol

•

Security

•

Lack of standards

























Characteristics of Distributed Management Systems • • •

Coll Collec ecti tion on of of logi logica call llyy-re rela late ted d shar shared ed dat data a Data sp split in into fr fragments Fragments may be replicat cated Frag Fragme ment nts/ s/re repl plic icas as allo alloca cate ted d to site sites s























Distributed Database Design Characteristics of Distributed Management Systems

•

Must Must perf perfor orm m all all the the fun funct ctio ions ns of a cen centr tral aliz ized ed DBMS DBMS

•

Must Must handl handle e all all neces necessar sary y funct function ions s impos imposed ed by by the the distri distribut bution ion of data data and processing

•

Must Must perf perfor orm m thes these e addi additi tion onal al func functi tion ons s transparently to the end user























DDBMS Components

•


Must Must incl includ ude e (at (at lea least st)) the the foll follow owin ing g compo compone nent nts: s: – Computer workstations – Network hardware and software – Communications media Tra cti r (or, appl applica icatio tio

or tra

cti

er)

























Database Design Single-Site Processing, Single-Site Data Distributed (SPSD)























Distributed Database Design Multiple-Site Processing, Single-Site Data (MPSD)























Multiple-Site Processing,















































Multiple-Site Processing,






























































































































































































































































































Fragment Locations






















Why Fragment? •

Usage –

•

Data Data is stored stored close close to to where where it is is most most freq frequen uently tly used used Data Data that that is is not not neede needed d by loca locall appli applicat cation ions s is not not store stored d

Parallelism –

•

Applic Applicati ation ons s work work with with partit partition ion rathe ratherr than than entire entire rela relatio tions ns

Efficiency – –

•


With fragm fragments ents as as unit of distri distributi bution, on, transact transaction ion can can be divided divided into into several several sub-qu sub-queries eries that that operate operate on fragments

Security –

Data not required required by local local applicat applications ions is not not stored stored and so not not availabl available e to unauthori unauthorized zed users





















Design Consideration for FragmentationsDistributed Database Design •

Quan Quanti tita tati tive ve info inform rmat atio ion n may may inc inclu lude de:: – frequ frequen ency cy wit with h which which a tran transa sact ctio ion n is run run;; – site site from from which which a tra trans nsac acti tion on is run run;; – perf perform orman ance ce crit criter eria ia for for trans transac acti tion ons. s.

•

Qualit Qualitati ative ve info informa rmatio tion n may may inclu include de tran transact saction ions s that that are are exec execute uted d such such as: as: – type type of acce access ss (rea (read d or or writ write) e);; – pred predic icat ates es of read read opera operati tion ons. s.





















Fragmentation


•

A relati relation on R is is divid divided ed into into fragme fragments nts r1, r2, r2, …rn, …rn, whic which h conta contain in enou enough gh info informa rmatio tion n to allow reconstruction of R

•

Example: We have a relation Sells (pub, address, price, type) Type is “small” or “large”. We can split Sells into two different fragments:

•

• Sellssmall= σtype type = “small”( “small”(Sel Sells) ls)

•

• SellsLage= σtype type = “large”(S “large”(Sell ells) s)





















Comparison of Strategies for Data Distribution
























Types of Fragmentation •

Four type ypes of of fragmentation: – Horizontal – Vertical – Mixed – Derived

•

Othe Otherr pos possi sibi bili lity ty is no frag fragme ment ntat atio ion: n:
























Horizontal and Vertical Fragmentation
























Horizontal Fragmentation • • •


Each Each frag fragme ment nt cons consis ists ts of a sub subse sett of of the the tupl tuples es of a rel relat atio ion n R. R. Defi Define ned d usin using g Sele Select ctio ion n oper operat atio ion n of rel relat atio iona nall alge algebr bra: a: σp(R) Example: Relation: Sells(pub, address,price,type) Fragments: – SellsBitter= σtype type = “bitter”(S “bitter”(Sell ells) s)























Horizontal Fragmentation


•

This This strate strategy gy is determ determine ined d by lookin looking g at predic predicate ates s used used by transa transacti ctions ons..

•

Involves fifinding set of minimal (complete and relevant ) predicates.

•

Set of predicates is complete , if and only if, any two tuples tuples in same fragment fragment are referenc referenced ed with same same probability by any application.

•

Predicate is relevant if there is at least one application that accesses fragments























Vertical Fragmentation • • • • •

Each Each fra fragm gmen entt cons consis ists ts of of a sub subse sett of attr attrib ibut utes es of of a rela relati tion on R. R. Defi Define ned d usin using g pro proje ject ctio ion n oper operat atio ion n of rela relati tion onal al alg algeb ebra ra:: Πa1,…an(R) Determined by establishing affinity of one attribute to another. Example: Relation: –

•


Bars Bars (name, (name, addres address, s, licenc licence, e, employe employees, es, owner) owner)

Fragments: (Bars)

























Mixed Fragmentation
























Example Exam ple - Mixe Mixed d Fra Fragme gmentat ntation ion
























Derived Horizontal Fragmentation














































































































































































































































































































































Performance Transparency


•

DDBM DDBMS S mus mustt per perfo form rm as if it were were a cen centr tral aliz ized ed DBMS DBMS::

•

DDBMS DDBMS should should not suffer suffer any perfor performan mance ce degr degrada adatio tion n due due to distri distribut buted ed architecture.

•

DDBMS DDBMS should should determ determine ine most most cost-e cost-effe ffecti ctive ve strate strategy gy to execu execute te a reques request. t.





















Performance Transparency • • •


Dist Distri ribu bute ted d Quer Query y Proc Proces esso sorr (DQP (DQP)) maps maps dat data a requ reques estt into into ord order ered ed sequ sequen ence ce of operations on local databases. It must must cons conside iderr frag fragmen mentat tation ion,, repl replica icatio tion, n, and alloca allocatio tion n sche schemas mas.. DQP has to decide: – whic which h fra fragm gmen entt to to acce access ss;; – whic which h cop copy y of of a fra fragm gmen entt to use; use; – whic which h loc loca atio tion to to use use..





















Performance Transparency


•

DQP produc produces es execut execution ion strateg strategy y opti optimiz mized ed with with resp respect ect to some some cost cost function.

•

Typi Typica calllly, y, cost costs s assoc associa iate ted d with with a dis distr trib ibut uted ed req reque uest st incl includ ude: e: –

I/O cost;

–

CPU cost;

–

Communication co cost.





















Perform Per formanc ance e Transp Transpare arency ncy - Exa Exampl mple e • • •

Property(Pno, City) 10000 records in London Renter(Rno,Max_Price) Renter(Rno,Max_Price) 100000 records in Glasgow Viewing(Pno, Rno) 1000000 records in London

SELECT p.pno FROM property p INNER JOIN (rente (renter r r INNER INNER JOIN JOIN view viewing ing v ON r.rno r.rno = v.rno) v.rno) ON p. p.pno = v. v.pno WHE WHERE RE p.ci p.city ty=‘ =‘Ab Aber erde deen en’ ’ AND AND r.m r.max ax_p _pri rice ce > 200 20000 000; 0;






















Perform Per formanc ance e Transp Transpare arency ncy - Exa Exampl mple e


•

Assu Assume me:: Eac Each h tup tuple le in each each rela relati tion on is 100 100 cha chara ract cter ers s lon long g

•

10 rent renter ers s wit with h max maxim imum um pric price e gre great ater er than than £200 £200,0 ,000 00..

•

100 100 000 000 view viewin ings gs for for pro prope pert rtie ies s in in Abe Aberd rdee een. n.

•

Comp Comput utat atio ion n time time neg negliligi gibl ble e compa compare red d to comm commun unic icat atio ion n time time..























Perform Per formanc ance e Transp Transpare arency ncy - Exa Exampl mple e
























Distribution Transaction Management •


DDBMS must ensure: –

synchroniza synchronization tion of sub-transac sub-transactions tions with other other local local transac transactions tions executing executing concurr concurrently ently at a site; site;

–

synchr synchroni onizat zation ion of sub-tra sub-transa nsacti ctions ons with with global global

transactions running simultaneously at same of different sites.

•

Global Global tran transac sactio tion n manag manager er (trans (transact action ion coor coordin dinato ator) r) at each each site site,, to coor coordin dinate ate global global and local transactions initiated at that site.























Distribution Transaction Management


•

Techni Technique ques s for for Distri Distribut buted ed Concurr Concurrenc ency y Cont Control rol must must ensu ensure re distri distribut buted ed serializability

•

Lock Lockin ing g pro proto toco cols ls (2PL (2PL prot protoc ocol ol))

•

Time Timesta stamp mpin ing g meth method ods s (exte (extend nd the the def defin init itio ion n of tim times esta tamp mp so so that that it it incl includ udes es a site identifier)























Two-Phase Commit Protocol


•

Distri Distribut buted ed databa databases ses make make itit possi possible ble for a tran transact saction ion to access access data data at several sites

•

Final Final COMMIT COMMIT must must not not be issued issued until until all sites sites have have comm committ itted ed their their























Two-Phase Commit Protocol


•

All partic participa ipatin ting g nodes nodes in a distri distribut buted ed tran transac sactio tion n shoul should d perfo perform rm the the same same action:

•

They They shoul should d eithe eitherr all all commi committ or all perfor perform m a roll rollbac back k of the transa transacti ction. on. The database

•

Automa Automatic ticall ally y contr controls ols and monito monitors rs the the comm commit it or or rollb rollback ack of a distri distribut buted ed transaction and maintains the integrity of the global database (the collection of

























Two-phase commit
























Two-pha Twophase se comm commit it – Pre Prepar pare e Phase Phase
























Two-pha Twophase se com commit mit – Com Commit mit Pha Phase se
























Two-pha Twophase se comm commit it – Oth Other er possi possibil bility ity
























Two-pha Twophase se Comm Commit it – Act Actual ual com commit mit








































































































































































PPT Distributed Database Design

Recommend Documents