parallel & distributed databases
DESCRIPTION
Parallel & distributed databases. WPI, Mohamed eltabakh. Introduction. In centralized database: Data is located in one place (one server) All DBMS functionalities are done by that server Enforcing ACID properties of transactions Concurrency control, recovery mechanisms Answering queries - PowerPoint PPT PresentationTRANSCRIPT
![Page 1: Parallel & distributed databases](https://reader035.vdocuments.us/reader035/viewer/2022082201/5681637d550346895dd45dce/html5/thumbnails/1.jpg)
W P I , M O H A M E D E LTA B A K H
PARALLEL & DISTRIBUTED DATABASES
1
![Page 2: Parallel & distributed databases](https://reader035.vdocuments.us/reader035/viewer/2022082201/5681637d550346895dd45dce/html5/thumbnails/2.jpg)
INTRODUCTION
• In centralized database:• Data is located in one place (one server)• All DBMS functionalities are done by that server• Enforcing ACID properties of transactions• Concurrency control, recovery mechanisms • Answering queries
• In Distributed databases:• Data is stored in multiple places (each is running a DBMS)• New notion of distributed transactions• DBMS functionalities are now distributed over many machines• Revisit how these functionalities work in distributed environment
2
![Page 3: Parallel & distributed databases](https://reader035.vdocuments.us/reader035/viewer/2022082201/5681637d550346895dd45dce/html5/thumbnails/3.jpg)
WHY DISTRIBUTED DATABASES
• Data is too large
• Applications are by nature distributed• Bank with many branches• Chain of retail stores with many locations• Library with many branches
• Get benefit of distributed and parallel processing• Faster response time for queries
3
![Page 4: Parallel & distributed databases](https://reader035.vdocuments.us/reader035/viewer/2022082201/5681637d550346895dd45dce/html5/thumbnails/4.jpg)
PARALLEL VS. DISTRIBUTED DATABASES
• Distributed processing usually imply parallel processing (not vise versa)
• Can have parallel processing on a single machine
4
Similar but different concepts
![Page 5: Parallel & distributed databases](https://reader035.vdocuments.us/reader035/viewer/2022082201/5681637d550346895dd45dce/html5/thumbnails/5.jpg)
PARALLEL VS. DISTRIBUTED DATABASES
• Parallel Databases: Assumptions about architecture• Machines are physically close to each other, e.g., same
server room• Machines connects with dedicated high-speed LANs and
switches• Communication cost is assumed to be small• Can shared-memory, shared-disk, or shared-nothing
architecture
5
![Page 6: Parallel & distributed databases](https://reader035.vdocuments.us/reader035/viewer/2022082201/5681637d550346895dd45dce/html5/thumbnails/6.jpg)
PARALLEL VS. DISTRIBUTED DATABASES
• Distributed Databases: Assumptions about architecture• Machines can far from each other, e.g., in different
continent • Can be connected using public-purpose network, e.g.,
Internet• Communication cost and problems cannot be ignored• Usually shared-nothing architecture
6
![Page 7: Parallel & distributed databases](https://reader035.vdocuments.us/reader035/viewer/2022082201/5681637d550346895dd45dce/html5/thumbnails/7.jpg)
PARALLEL DATABASE PROCESSING
7
![Page 8: Parallel & distributed databases](https://reader035.vdocuments.us/reader035/viewer/2022082201/5681637d550346895dd45dce/html5/thumbnails/8.jpg)
DIFFERENT ARCHITECTURE
• Three possible architectures for passing information
8
Shared-memory Shared-diskShared-nothing
![Page 9: Parallel & distributed databases](https://reader035.vdocuments.us/reader035/viewer/2022082201/5681637d550346895dd45dce/html5/thumbnails/9.jpg)
1- SHARED-MEMORY ARCHITECTURE
• Every processor has its own disk
• Single memory address-space for all processors• Reading or writing to far memory can
be slightly more expensive
• Every processor can have its own local memory and cache as well
9
![Page 10: Parallel & distributed databases](https://reader035.vdocuments.us/reader035/viewer/2022082201/5681637d550346895dd45dce/html5/thumbnails/10.jpg)
2- SHARED-DISK ARCHITECTURE
• Every processor has its own memory (not accessible by others)
• All machines can access all disks in the system
• Number of disks does not necessarily match the number of processors
10
![Page 11: Parallel & distributed databases](https://reader035.vdocuments.us/reader035/viewer/2022082201/5681637d550346895dd45dce/html5/thumbnails/11.jpg)
3- SHARED-NOTHING ARCHITECTURE
• Most common architecture nowadays
• Every machine has its own memory and disk • Many cheap machines (commodity hardware)
• Communication is done through high-speed network and switches
• Usually machines can have a hierarchy• Machines on same rack• Then racks are connected through high-speed
switches
11
• Scales better• Easier to build• Cheaper cost
![Page 12: Parallel & distributed databases](https://reader035.vdocuments.us/reader035/viewer/2022082201/5681637d550346895dd45dce/html5/thumbnails/12.jpg)
PARTITIONING OF DATA
12
To partition a relation R over m machinesRange partitioning Hash-based partitioning Round-robin partitioning
• Shared-nothing architecture is sensitive to partitioning
• Good partitioning depends on what operations are common
![Page 13: Parallel & distributed databases](https://reader035.vdocuments.us/reader035/viewer/2022082201/5681637d550346895dd45dce/html5/thumbnails/13.jpg)
PARALLEL SCAN σc(R)• Relation R is partitioned over m machines
• Each partition of R is around |R|/m tuples
• Each machine scans its own partition and applies the selection condition c
• If data are partitioned using round robin or a hash function (over the entire tuple)• The resulted relation is expected to be well distributed over all nodes• All partitioned will be scanned
• If data are range partitioned or hash-based partitioned (on the selection column)• The resulted relation can be clustered on few nodes• Few partitions need to be touched
13
• Parallel Projection is also straightforward • All partitions will be touched• Not sensitive to how data is partitioned
![Page 14: Parallel & distributed databases](https://reader035.vdocuments.us/reader035/viewer/2022082201/5681637d550346895dd45dce/html5/thumbnails/14.jpg)
PARALLEL DUPLICATE ELIMINATION
• If relation is range or hash-based partitioned• Identical tuples are in the same partition• So, eliminate duplicates in each partition independently
• If relation is round-robin partitioned• Re-partition the relation using a hash function • So every machine creates m partitions and send the ith
partition to machine i• machine i can now perform the duplicate elimination
14
![Page 15: Parallel & distributed databases](https://reader035.vdocuments.us/reader035/viewer/2022082201/5681637d550346895dd45dce/html5/thumbnails/15.jpg)
PARALLEL SORTING• Range-based• Re-partition R based on ranges into m partitions• Machine i receives all ith partitions from all machines
and sort that partition• The entire R is now sorted • Skewed data is an issue• Apply sampling phase first• Ranges can be of different width
• Merge-based• Each node sorts its own data• All nodes start sending their sorted data (one block at
a time) to a single machine• This machine applies merge-sort technique as data
come15
![Page 16: Parallel & distributed databases](https://reader035.vdocuments.us/reader035/viewer/2022082201/5681637d550346895dd45dce/html5/thumbnails/16.jpg)
DISTRIBUTED DATABASE
16
![Page 17: Parallel & distributed databases](https://reader035.vdocuments.us/reader035/viewer/2022082201/5681637d550346895dd45dce/html5/thumbnails/17.jpg)
DEFINITIONS
A distributed database (DDB) is a collection of multiple, logically interrelated databases distributed over a computer network.
Distributed database system (DDBS) = DB + Communication
17
![Page 18: Parallel & distributed databases](https://reader035.vdocuments.us/reader035/viewer/2022082201/5681637d550346895dd45dce/html5/thumbnails/18.jpg)
DISTRIBUTED DATABASESMAIN CONCEPTS
• Data are stored at several locations• Each managed by a DBMS that can run autonomously
• Ideally, location of data is unknown to client• Distributed Data Independence
• Distributed Transactions• Clients can write Transactions regardless of where the
affected data are located• Big question: How to ensure the ACID properties
Distributed Transactions???
18
![Page 19: Parallel & distributed databases](https://reader035.vdocuments.us/reader035/viewer/2022082201/5681637d550346895dd45dce/html5/thumbnails/19.jpg)
• Transparent management of distributed, fragmented, and replicated data
• Improved reliability/availability through distributed transactions
• Improved performance
• Easier and more economical system expansion
DISTRIBUTED DBMS PROMISES
19
![Page 20: Parallel & distributed databases](https://reader035.vdocuments.us/reader035/viewer/2022082201/5681637d550346895dd45dce/html5/thumbnails/20.jpg)
TRANSPARENCY & DATA INDEPENDENCE
• Data distributed (with some replication)
• Transparently ask query:
20
SELECT ENAME,SALFROM EMP,ASG,PAYWHERE DUR > 12AND EMP.ENO = ASG.ENOAND PAY.TITLE = EMP.TITLE
![Page 21: Parallel & distributed databases](https://reader035.vdocuments.us/reader035/viewer/2022082201/5681637d550346895dd45dce/html5/thumbnails/21.jpg)
TYPES OF DISTRIBUTED DATABASES
• Homogeneous• Every site runs the same type
of DBMS
• Heterogeneous: • Different sites run different
DBMS (maybe even RDBMS and ODBMS)
21
Homogeneous DBs can communicate directly with each other
Heterogeneous DBs communicate through gateway interfaces
![Page 22: Parallel & distributed databases](https://reader035.vdocuments.us/reader035/viewer/2022082201/5681637d550346895dd45dce/html5/thumbnails/22.jpg)
MAIN ISSUES
• Data Layout Issues• Data partitioning and fragmentation• Data replication
• Query Processing and Distributed Transactions• Distributed join • Transaction atomicity using two-phase commit• Transaction serializability using distributed locking
22
![Page 23: Parallel & distributed databases](https://reader035.vdocuments.us/reader035/viewer/2022082201/5681637d550346895dd45dce/html5/thumbnails/23.jpg)
MAIN ISSUES
• Data Layout Issues• Data partitioning and fragmentation• Data replication
• Query Processing and Distributed Transactions• Distributed join • Transaction atomicity using two-phase commit• Transaction serializability using distributed locking
23
![Page 24: Parallel & distributed databases](https://reader035.vdocuments.us/reader035/viewer/2022082201/5681637d550346895dd45dce/html5/thumbnails/24.jpg)
FRAGMENTATION
• How to divide the data? Can't we just distribute relations?
• What is a reasonable unit of distribution?
24
![Page 25: Parallel & distributed databases](https://reader035.vdocuments.us/reader035/viewer/2022082201/5681637d550346895dd45dce/html5/thumbnails/25.jpg)
FRAGMENTATION ALTERNATIVES – HORIZONTAL
Stored in LondonStored in Boston
25
![Page 26: Parallel & distributed databases](https://reader035.vdocuments.us/reader035/viewer/2022082201/5681637d550346895dd45dce/html5/thumbnails/26.jpg)
FRAGMENTATION ALTERNATIVES – VERTICAL
Stored in London Stored in Boston
Horizontal partitioning is more common
26
![Page 27: Parallel & distributed databases](https://reader035.vdocuments.us/reader035/viewer/2022082201/5681637d550346895dd45dce/html5/thumbnails/27.jpg)
• Completeness• Decomposition of relation R into fragments R1, R2, ..., Rn is
complete if and only if each data item in R can also be found in some Ri
• Reconstruction (Lossless)• If relation R is decomposed into fragments R1, R2, ..., Rn,
then there should exist some relational operator such that
R = 1≤i≤nRi
• Disjointness (Non-overlapping)• If relation R is decomposed into fragments R1, R2, ..., Rn, and
data item di is in Rj, then di should not be in any other fragment Rk (k ≠ j ).
CORRECTNESS OF FRAGMENTATION
27
![Page 28: Parallel & distributed databases](https://reader035.vdocuments.us/reader035/viewer/2022082201/5681637d550346895dd45dce/html5/thumbnails/28.jpg)
REPLICATION ALTERNATIVES
28
![Page 29: Parallel & distributed databases](https://reader035.vdocuments.us/reader035/viewer/2022082201/5681637d550346895dd45dce/html5/thumbnails/29.jpg)
DATA REPLICATION• Pros: • Improves availability• Disconnected (mobile) operation• Distributes load• Reads are cheaper
• Cons:• N times more updates• N times more storage
• Synchronous vs. asynchronous• Synchronous: all replica are up-to-date• Asynchronous: cheaper but delay in synchronization
29
Catalog Management
• Catalog is needed to keep track of the location of each fragment & replica
• Catalog itself can be centralized or distributed
![Page 30: Parallel & distributed databases](https://reader035.vdocuments.us/reader035/viewer/2022082201/5681637d550346895dd45dce/html5/thumbnails/30.jpg)
MAIN ISSUES
• Data Layout Issues• Data partitioning and fragmentation• Data replication
• Query Processing and Distributed Transactions• Distributed join • Transaction atomicity using two-phase commit• Transaction serializability using distributed locking
30
![Page 31: Parallel & distributed databases](https://reader035.vdocuments.us/reader035/viewer/2022082201/5681637d550346895dd45dce/html5/thumbnails/31.jpg)
DISTRIBUTED JOIN R(X,Y) ⋈ S(Y,Z)
• Option 1: Send R to S’s location and join their• Option 2: Send S to R’s location and join their• Communication cost is expensive, too much data to
send
• Is there a better option ???• Semi Join• Bloom Join 31
R(X1,X2, …Xn, Y)
S(Y, Z1, Z2,…, Zm)
Stored in London Stored in Boston
Join based on R.Y = S.Y
![Page 32: Parallel & distributed databases](https://reader035.vdocuments.us/reader035/viewer/2022082201/5681637d550346895dd45dce/html5/thumbnails/32.jpg)
SEMI-JOIN
• Send only S.Y column to R’s location
• Do the join based on Y columns in R’s location (Semi Join)
• Send the records of R that will join (without duplicates) to S’s location
• Perform the final join in S’s location
32
R(X1,X2, …Xn, Y)
S(Y, Z1, Z2,…, Zm)
Stored in London Stored in Boston
![Page 33: Parallel & distributed databases](https://reader035.vdocuments.us/reader035/viewer/2022082201/5681637d550346895dd45dce/html5/thumbnails/33.jpg)
IS SEMI-JOIN EFFECTIVE
Depends on many factors:• If the size of Y attribute is small compared to the remaining
attributes in R and S
• If the join selectivity is high is small
• If there are many duplicates that can be eliminated
33
R(X1,X2, …Xn, Y)
S(Y, Z1, Z2,…, Zm)
Stored in London Stored in Boston
![Page 34: Parallel & distributed databases](https://reader035.vdocuments.us/reader035/viewer/2022082201/5681637d550346895dd45dce/html5/thumbnails/34.jpg)
BLOOM JOIN• Build a bit vector of size K in R’s location (all 0’s)
• For every record in R, use a hash function(s) based on Y value (return from 1 to K)• Each function hashes Y to a bit in the bit vector. Set this bit to 1
• Send the bit vector to S’s location
• S will use the same hash function(s) to hash its Y values• If the hashing matched with 1’s in all its hashing positions, then this Y is
candidate for Join• Otherwise, not candidate for join• Send S’s records having candidate Y’s to R’s location for join
34
0 0 1 1 … 0 0 1
![Page 35: Parallel & distributed databases](https://reader035.vdocuments.us/reader035/viewer/2022082201/5681637d550346895dd45dce/html5/thumbnails/35.jpg)
MAIN ISSUES
• Data Layout Issues• Data partitioning and fragmentation• Data replication
• Query Processing and Distributed Transactions• Distributed join • Transaction atomicity using two-phase commit• Transaction serializability using distributed locking
35
![Page 36: Parallel & distributed databases](https://reader035.vdocuments.us/reader035/viewer/2022082201/5681637d550346895dd45dce/html5/thumbnails/36.jpg)
TRANSACTIONS
• A Transaction is an atomic sequence of actions in the Database (reads and writes)
• Each Transaction has to be executed completely, and must leave the Database in a consistent state
• If the Transaction fails or aborts midway, then the Database is “rolled back” to its initial consistent state (before the Transaction began)
36
ACID Properties of Transactions
![Page 37: Parallel & distributed databases](https://reader035.vdocuments.us/reader035/viewer/2022082201/5681637d550346895dd45dce/html5/thumbnails/37.jpg)
ATOMICITY IN DISTRIBUTED DBS• One transaction T may touch many sites• T has several components T1, T2, …Tm• Each Tk is running (reading and writing) at site k• How to make T is atomic ????• Either T1, T2, …, Tm complete or None of them is executed
• Two-Phase Commit techniques is used
37
![Page 38: Parallel & distributed databases](https://reader035.vdocuments.us/reader035/viewer/2022082201/5681637d550346895dd45dce/html5/thumbnails/38.jpg)
TWO-PHASE COMMIT• Phase 1• Site that initiates T is the coordinator• When coordinator wants to commit (complete T), it sends a
“prepare T” msg to all participant sites• Every other site receiving “prepare T”, either sends “ready T”
or “don’t commit T”• A site can wait for a while until it reaches a decision (Coordinator will
wait reasonable time to hear from the others)
• These msgs are written to local logs
38
![Page 39: Parallel & distributed databases](https://reader035.vdocuments.us/reader035/viewer/2022082201/5681637d550346895dd45dce/html5/thumbnails/39.jpg)
TWO-PHASE COMMIT (CONT’D)• Phase 2• IF coordinator received all “ready T”
• Remember no one committed yet• Coordinator sends “commit T” to all participant sites• Every site receiving “commit T” commits its
transaction• IF coordinator received any “don’t commit
T”• Coordinator sends “abort T” to all participant sites• Every site receiving “abort T” commits its
transaction
• These msgs are written to local logs
39
• Straightforward if no failures happen• In case of failure logs are used to
ensure ALL are done or NONEExample 2: What if all sites in Phase 1 replied “ready T”, then one site crashed???
Example 1: What if one sites in Phase 1 replied “don’t commit T”, and then crashed???
![Page 40: Parallel & distributed databases](https://reader035.vdocuments.us/reader035/viewer/2022082201/5681637d550346895dd45dce/html5/thumbnails/40.jpg)
MAIN ISSUES
• Data Layout Issues• Data partitioning and fragmentation• Data replication
• Query Processing and Distributed Transactions• Distributed join • Transaction atomicity using two-phase commit• Transaction serializability using distributed locking
40
![Page 41: Parallel & distributed databases](https://reader035.vdocuments.us/reader035/viewer/2022082201/5681637d550346895dd45dce/html5/thumbnails/41.jpg)
DATABASE LOCKING• Locking mechanisms are used to prevent concurrent transactions from
updating the same data at the same time
• Reading(x) shared lock on x• Writing(x) exclusive lock on x• More types of locks exist for efficiency
41
Shared lock Exclusive lockShared lock Yes NoExclusive lock No No
What you have
What you request
In Distributed DBs:• x may be replicated in multiple sites (not one place)• The transactions reading or writing x may be running at different
sites
![Page 42: Parallel & distributed databases](https://reader035.vdocuments.us/reader035/viewer/2022082201/5681637d550346895dd45dce/html5/thumbnails/42.jpg)
DISTRIBUTED LOCKING• Centralized approach• One dedicated site managing all locks• Cons: bottleneck, not scalable, single point of failure
• Primary-Copy approach• Every item in the database, say x, has a primary site, say Px• Any transaction running any where, will ask Px for lock on x
• Fully Distributed approach• To read, lock any copy of x• To write, lock all copies of x• Variations exists to balance the cots of read and write op.
42
Deadlocks are very possible. How to resolve them???Using timeout: After waiting for a while for a lock, abort and start again
![Page 43: Parallel & distributed databases](https://reader035.vdocuments.us/reader035/viewer/2022082201/5681637d550346895dd45dce/html5/thumbnails/43.jpg)
• Promises of DDBMSs• Transparent management of distributed, fragmented,
and replicated data• Improved reliability/availability through distributed
transactions• Improved performance• Easier and more economical system expansion
• Classification of DDBMS• Homogeneous vs. Heterogeneous• Client-Sever vs. Collaborative Servers vs. Peer-to-Peer
SUMMARY OF DISTRIBUTED DBMS
43
![Page 44: Parallel & distributed databases](https://reader035.vdocuments.us/reader035/viewer/2022082201/5681637d550346895dd45dce/html5/thumbnails/44.jpg)
SUMMARY OF DISTRIBUTED DBMS (CONT’D)
• Data Layout Issues• Data partitioning and fragmentation• Data replication
• Query Processing and Distributed Transactions• Distributed join • Transaction atomicity using two-phase commit• Transaction serializability using distributed locking
44