AU
Uploaded byAli Usman
PPTX, PDF3,280 views

Database ,14 Parallel DBMS

This document discusses distributed database management systems (DBMS). It covers topics like distributed database design, distributed query processing, and transaction management in a distributed environment. It also discusses parallel DBMS architectures like shared memory, shared disk, and shared nothing and techniques for data partitioning, parallel query processing and optimization in a distributed parallel DBMS.

Technology◦
In this document
Powered by AI

Introduction to distributed DBMS, focusing on design, integration, processing, transaction management, and current issues.

Identifies the I/O bottleneck in databases, predicts growth in technology, and concludes worsening impact on performance.

Discusses increasing I/O bandwidth via data partitioning and parallel access, transitioning from hardware-focused to multiprocessor solutions.

Highlights goals for multiprocessors including high performance and effective load balancing in communication.

Illustrates the layered architecture of data servers, including client interfaces and communication channels.

Describes benefits of separating application and data management functions to improve efficiency.

Lists advantages like integrated data control and performance, and challenges such as communication overhead.

Explains methods for leveraging multiprocessor systems, discussing automatic detection of parallelism, language enhancements, and new language offerings.

Differentiates between inter-operation and intra-operation parallelism for improved query execution.

Defines parallel DBMS and contrasts between straightforward ports of relational DBMS and new hardware/software combinations.

Outlines goals for cost-performance, high throughput, low response time, and reliability through replication.

Discusses linear speed-up and scale-up strategies by increasing components or database size while maintaining performance.

Identifies barriers such as startup time, interference, and skew in parallel processing and management.

Presentation of the functional architecture with roles of session, request, and data managers.

Discusses shared-memory, shared-disk, shared-nothing architectures, and hybrid systems to achieve an optimal DBMS.

Describes the NUMA architecture, its advantages for application portability, and details on the Cache Coherent NUMA implementation.

Explains benefits of cluster architecture combining load balancing and extensibility while addressing cost performance.

Discusses trade-offs between SN and SD clusters regarding performance, simplicity, and management for OLTP and OLAP.

Outlines techniques for data placement, parallel processing, and transaction management in a distributed DBMS.

Explains methods for partitioning data, including round-robin, B-tree, and hash functions for efficient data management.

Details algorithms for parallel join operations including nested loop, associative, and hash joins.

Describes strategies for selecting optimal execution plans for parallel queries using operator trees and cost models.

Discusses issues caused by data skew in parallelism and strategies for efficient load distribution and failover.

Presents Oracle's transparent application failover solutions and the necessary network architecture.

Details the capabilities of Oracle's Exadata machine for handling various workloads and its unique architecture.

Downloaded 103 times
Distributed DBMS ©M. T. Özsu & P. Valduriez Ch.14/1 Outline • Introduction • Background • Distributed Database Design • Database Integration • Semantic Data Control • Distributed Query Processing • Multidatabase Query Processing • Distributed Transaction Management • Data Replication • Parallel Database Systems ➡ Data placement and query processing ➡ Load balancing ➡ Database clusters • Distributed Object DBMS • Peer-to-Peer Data Management • Web Data Management • Current Issues
Distributed DBMS ©M. T. Özsu & P. Valduriez Ch.14/2 The Database Problem • Large volume of data use disk and large main memory • I/O bottleneck (or memory access bottleneck) ➡ Speed(disk) << speed(RAM) << speed(microprocessor) • Predictions ➡ Moore’s law: processor speed growth (with multicore): 50 % per year ➡ DRAM capacity growth : 4 × every three years ➡ Disk throughput : 2 × in the last ten years • Conclusion : the I/O bottleneck worsens
Distributed DBMS ©M. T. Özsu & P. Valduriez Ch.14/3 The Solution • Increase the I/O bandwidth ➡ Data partitioning ➡ Parallel data access • Origins (1980's): database machines ➡ Hardware-oriented bad cost-performance failure ➡ Notable exception : ICL's CAFS Intelligent Search Processor • 1990's: same solution but using standard hardware components integrated in a multiprocessor ➡ Software-oriented ➡ Standard essential to exploit continuing technology improvements
Distributed DBMS ©M. T. Özsu & P. Valduriez Ch.14/4 Multiprocessor Objectives • High-performance with better cost-performance than mainframe or vector supercomputer • Use many nodes, each with good cost-performance, communicating through network ➡ Good cost via high-volume components ➡ Good performance via bandwidth • Trends ➡ Microprocessor and memory (DRAM): off-the-shelf ➡ Network (multiprocessor edge): custom • The real chalenge is to parallelize applications to run with good load balancing
Distributed DBMS ©M. T. Özsu & P. Valduriez Ch.14/5 Data Server Architecture client interface query parsing data server interface communication channel Application server Data server database application server interface database functions Client
Distributed DBMS ©M. T. Özsu & P. Valduriez Ch.14/6 Objectives of Data Servers • Avoid the shortcomings of the traditional DBMS approach ➡ Centralization of data and application management ➡ General-purpose OS (not DB-oriented) • By separating the functions between ➡ Application server (or host computer) ➡ Data server (or database computer or back-end computer)
Distributed DBMS ©M. T. Özsu & P. Valduriez Ch.14/7 Data Server Approach: Assessment • Advantages ➡ Integrated data control by the server (black box) ➡ Increased performance by dedicated system ➡ Can better exploit parallelism ➡ Fits well in distributed environments • Potential problems ➡ Communication overhead between application and data server ✦ High-level interface ➡ High cost with mainframe servers
Distributed DBMS ©M. T. Özsu & P. Valduriez Ch.14/8 Parallel Data Processing • Three ways of exploiting high-performance multiprocessor systems:  Automatically detect parallelism in sequential programs (e.g., Fortran, OPS5)  Augment an existing language with parallel constructs (e.g., C*, Fortran90)  Offer a new language in which parallelism can be expressed or automatically inferred • Critique  Hard to develop parallelizing compilers, limited resulting speed-up  Enables the programmer to express parallel computations but too low-level  Can combine the advantages of both (1) and (2)
Distributed DBMS ©M. T. Özsu & P. Valduriez Ch.14/9 Data-based Parallelism • Inter-operation ➡ p operations of the same query in parallel op.3 op.1 op.2 op. R op. R1 op. R2 op. R3 op. R4 • Intra-operation ➡ The same op in parallel
Distributed DBMS ©M. T. Özsu & P. Valduriez Ch.14/10 Parallel DBMS • Loose definition: a DBMS implemented on a tighly coupled multiprocessor • Alternative extremes ➡ Straighforward porting of relational DBMS (the software vendor edge) ➡ New hardware/software combination (the computer manufacturer edge) • Naturally extends to distributed databases with one server per site
Distributed DBMS ©M. T. Özsu & P. Valduriez Ch.14/11 Parallel DBMS - Objectives • Much better cost / performance than mainframe solution • High-performance through parallelism ➡ High throughput with inter-query parallelism ➡ Low response time with intra-operation parallelism • High availability and reliability by exploiting data replication • Extensibility with the ideal goals ➡ Linear speed-up ➡ Linear scale-up
Distributed DBMS ©M. T. Özsu & P. Valduriez Ch.14/12 Linear Speed-up Linear increase in performance for a constant DB size and proportional increase of the system components (processor, memory, disk) new perf. old perf. ideal components
Distributed DBMS ©M. T. Özsu & P. Valduriez Ch.14/13 Linear Scale-up Sustained performance for a linear increase of database size and proportional increase of the system components. components + database size new perf. old perf. ideal
Distributed DBMS ©M. T. Özsu & P. Valduriez Ch.14/14 Barriers to Parallelism • Startup ➡ The time needed to start a parallel operation may dominate the actual computation time • Interference ➡ When accessing shared resources, each new process slows down the others (hot spot problem) • Skew ➡ The response time of a set of parallel processes is the time of the slowest one • Parallel data management techniques intend to overcome these barriers
Distributed DBMS ©M. T. Özsu & P. Valduriez Ch.14/15 Parallel DBMS – Functional Architecture RM task n DM task 12 DM task n2 DM task n1 Data Mgr DM task 11 Request MgrRM task 1 Session Mgr User task 1 User task n
Distributed DBMS ©M. T. Özsu & P. Valduriez Ch.14/16 Parallel DBMS Functions • Session manager ➡ Host interface ➡ Transaction monitoring for OLTP • Request manager ➡ Compilation and optimization ➡ Data directory management ➡ Semantic data control ➡ Execution control • Data manager ➡ Execution of DB operations ➡ Transaction management support ➡ Data management
Distributed DBMS ©M. T. Özsu & P. Valduriez Ch.14/17 Parallel System Architectures • Multiprocessor architecture alternatives ➡ Shared memory (SM) ➡ Shared disk (SD) ➡ Shared nothing (SN) • Hybrid architectures ➡ Non-Uniform Memory Architecture (NUMA) ➡ Cluster
Distributed DBMS ©M. T. Özsu & P. Valduriez Ch.14/18 Shared-Memory DBMS on symmetric multiprocessors (SMP) Prototypes: XPRS, Volcano, DBS3 + Simplicity, load balancing, fast communication - Network cost, low extensibility
Distributed DBMS ©M. T. Özsu & P. Valduriez Ch.14/19 Shared-Disk Origins : DEC's VAXcluster, IBM's IMS/VS Data Sharing Used first by Oracle with its Distributed Lock Manager Now used by most DBMS vendors + network cost, extensibility, migration from uniprocessor - complexity, potential performance problem for cache coherency
Distributed DBMS ©M. T. Özsu & P. Valduriez Ch.14/20 Shared-Nothing Used by Teradata, IBM, Sybase, Microsoft for OLAP Prototypes: Gamma, Bubba, Grace, Prisma, EDS + Extensibility, availability - Complexity, difficult load balancing
Distributed DBMS ©M. T. Özsu & P. Valduriez Ch.14/21 Hybrid Architectures • Various possible combinations of the three basic architectures are possible to obtain different trade-offs between cost, performance, extensibility, availability, etc. • Hybrid architectures try to obtain the advantages of different architectures: ➡ efficiency and simplicity of shared-memory ➡ extensibility and cost of either shared disk or shared nothing • 2 main kinds: NUMA and cluster
Distributed DBMS ©M. T. Özsu & P. Valduriez Ch.14/22 NUMA • Shared-Memory vs. Distributed Memory ➡ Mixes two different aspects : addressing and memory ✦ Addressing: single address space vs multiple address spaces ✦ Physical memory: central vs distributed • NUMA = single address space on distributed physical memory ➡ Eases application portability ➡ Extensibility • The most successful NUMA is Cache Coherent NUMA (CC-NUMA)
Distributed DBMS ©M. T. Özsu & P. Valduriez Ch.14/23 CC-NUMA • Principle ➡ Main memory distributed as with shared-nothing ➡ However, any processor has access to all other processors’ memories • Similar to shared-disk, different processors can access the same data in a conflicting update mode, so global cache consistency protocols are needed. ➡ Cache consistency done in hardware through a special consistent cache interconnect ✦ Remote memory access very efficient, only a few times (typically between 2 and 3 times) the cost of local access
Distributed DBMS ©M. T. Özsu & P. Valduriez Ch.14/24 Cluster • Combines good load balancing of SM with extensibility of SN • Server nodes: off-the-shelf components ➡ From simple PC components to more powerful SMP ➡ Yields the best cost/performance ratio ➡ In its cheapest form, • Fast standard interconnect (e.g., Myrinet and Infiniband) with high bandwidth (Gigabits/sec) and low latency
Distributed DBMS ©M. T. Özsu & P. Valduriez Ch.14/25 SN cluster vs SD cluster • SN cluster can yield best cost/performance and extensibility ➡ But adding or replacing cluster nodes requires disk and data reorganization • SD cluster avoids such reorganization but requires disks to be globally accessible by the cluster nodes ➡ Network-attached storage (NAS) ✦ distributed file system protocol such as NFS, relatively slow and not appropriate for database management ➡ Storage-area network (SAN) ✦ Block-based protocol thus making it easier to manage cache consistency, efficient, but costlier
Distributed DBMS ©M. T. Özsu & P. Valduriez Ch.14/26 Discussion • For a small configuration (e.g., 8 processors), SM can provide the highest performance because of better load balancing • Some years ago, SN was the only choice for high-end systems. But SAN makes SN a viable alternative with the main advantage of simplicity (for transaction management) ➡ SD is now the preferred architecture for OLTP ➡ But for OLAP databases that are typically very large and mostly read-only, SN is used • Hybrid architectures, such as NUMA and cluster, can combine the efficiency and simplicity of SM and the extensibility and cost of either SD or SN
Distributed DBMS ©M. T. Özsu & P. Valduriez Ch.14/27 Parallel DBMS Techniques • Data placement ➡ Physical placement of the DB onto multiple nodes ➡ Static vs. Dynamic • Parallel data processing ➡ Select is easy ➡ Join (and all other non-select operations) is more difficult • Parallel query optimization ➡ Choice of the best parallel execution plans ➡ Automatic parallelization of the queries and load balancing • Transaction management ➡ Similar to distributed transaction management
Distributed DBMS ©M. T. Özsu & P. Valduriez Ch.14/28 Data Partitioning • Each relation is divided in n partitions (subrelations), where n is a function of relation size and access frequency • Implementation ➡ Round-robin ✦ Maps i-th element to node i mod n ✦ Simple but only exact-match queries ➡ B-tree index ✦ Supports range queries but large index ➡ Hash function ✦ Only exact-match queries but small index
Distributed DBMS ©M. T. Özsu & P. Valduriez Ch.14/29 Partitioning Schemes Round-Robin Hashing Interval ••• ••• ••• ••• ••• ••• •••a-g h-m u-z
Distributed DBMS ©M. T. Özsu & P. Valduriez Ch.14/30 Replicated Data Partitioning • High-availability requires data replication ➡ simple solution is mirrored disks ✦ hurts load balancing when one node fails ➡ more elaborate solutions achieve load balancing ✦ interleaved partitioning (Teradata) ✦ chained partitioning (Gamma)
Distributed DBMS ©M. T. Özsu & P. Valduriez Ch.14/31 Interleaved Partitioning Node Primary copy R1 R2 R3 R4 Backup copy r1.1 r1.2 r1.3 r2.3 r2.1 r2.2 r3.2 r3.2 r3.1 1 2 3 4
Distributed DBMS ©M. T. Özsu & P. Valduriez Ch.14/32 Chained Partitioning Node Primary copy R1 R2 R3 R4 Backup copy r4 r1 r2 r3 1 2 3 4
Distributed DBMS ©M. T. Özsu & P. Valduriez Ch.14/33 Placement Directory • Performs two functions ➡ F1 (relname, placement attval) = lognode-id ➡ F2 (lognode-id) = phynode-id • In either case, the data structure for f1 and f2 should be available when needed at each node
Distributed DBMS ©M. T. Özsu & P. Valduriez Ch.14/34 Join Processing • Three basic algorithms for intra-operator parallelism ➡ Parallel nested loop join: no special assumption ➡ Parallel associative join: one relation is declustered on join attribute and equi-join ➡ Parallel hash join: equi-join • They also apply to other complex operators such as duplicate elimination, union, intersection, etc. with minor adaptation
Distributed DBMS ©M. T. Özsu & P. Valduriez Ch.14/35 Parallel Nested Loop Join send partition node 3 node 4 node 1 node 2 R1: S1 S2 R2:
Distributed DBMS ©M. T. Özsu & P. Valduriez Ch.14/36 Parallel Associative Join node 1 node 3 node 4 node 2 R1: R2: S1 S2
Distributed DBMS ©M. T. Özsu & P. Valduriez Ch.14/37 Parallel Hash Join node node node node node 1 node 2 R1: R2: S1: S2:
Distributed DBMS ©M. T. Özsu & P. Valduriez Ch.14/38 Parallel Query Optimization • The objective is to select the “best” parallel execution plan for a query using the following components • Search space ➡ Models alternative execution plans as operator trees ➡ Left-deep vs. Right-deep vs. Bushy trees • Search strategy ➡ Dynamic programming for small search space ➡ Randomized for large search space • Cost model (abstraction of execution system) ➡ Physical schema info. (partitioning, indexes, etc.) ➡ Statistics and cost functions
Distributed DBMS ©M. T. Özsu & P. Valduriez Ch.14/39 Execution Plans as Operator Trees R2R1 R4 Result j2 j3 Left-deep Right-deep j1 R3 R2R1 R4 Result j5 j6 j4R3 R2R1 R3j7 R4 Result j9 Zig-zag Bushyj8 Result j10 j12 j11 R2R1 R4R3
Distributed DBMS ©M. T. Özsu & P. Valduriez Ch.14/40 Equivalent Hash-Join Trees with Different Scheduling R3 Probe3Build3 R4 Temp2 Temp1 Build3 R4 Temp2 Probe3Build3 Probe2Build2 Probe1Build1 R2R1 R3 Temp1 Probe2Build2 Probe1Build1 R2R1
Distributed DBMS ©M. T. Özsu & P. Valduriez Ch.14/41 Load Balancing • Problems arise for intra-operator parallelism with skewed data distributions ➡ attribute data skew (AVS) ➡ tuple placement skew (TPS) ➡ selectivity skew (SS) ➡ redistribution skew (RS) ➡ join product skew (JPS) • Solutions ➡ sophisticated parallel algorithms that deal with skew ➡ dynamic processor allocation (at execution time)
Distributed DBMS ©M. T. Özsu & P. Valduriez Ch.14/42 Data Skew Example Join1 Res1 Res2 Join2 AVS/TPS AVS/TPS AVS/TPS AVS/TPS JPS JPS RS/SS RS/SS Scan1 S2 R2 S1 R1 Scan2
Distributed DBMS ©M. T. Özsu & P. Valduriez Ch.14/43 Load Balancing in a DB Cluster • Choose the node to execute Q ➡ round robin ➡ The least loaded ✦ Need to get load information • Fail over ➡ In case a node N fails, N’s queries are taken over by another node ✦ Requires a copy of N’s data or SD • In case of interference ➡ Data of an overloaded node are replicated to another node Q1 Q2 Load balancing Q3 Q4 Q4 Q3 Q2 Q1
Distributed DBMS ©M. T. Özsu & P. Valduriez Ch.14/44 Oracle Transparent Application Failover Client Node 1 connect1 Node 2Ping
Distributed DBMS ©M. T. Özsu & P. Valduriez Ch.14/45 Client PCs Enterprise network Microsoft Failover Cluster Topology Internal network Fibre Channel
Distributed DBMS ©M. T. Özsu & P. Valduriez Ch.14/46 Main Products Vendor Product Architecture Platforms IBM DB2 Pure Scale DB2 Database Partitioning Feature (DPF) SD SN AIX on SP Linux on cluster Microsoft SQL Server SQL Server 2008 R2 Parallel Data Warehouse SD SN Windows on SMP and cluster Oracle Real Application Cluster Exadata Database Machine SD Windows, Unix, Linux on SMP and cluster NCR Teradata SN Bynet network NCR Unix and Windows Oracle MySQL SN Linux Cluster
Distributed DBMS ©M. T. Özsu & P. Valduriez Ch.14/47 The Exadata Database Machine • New machine from Oracle with Sun • Objectives ➡ OLTP, OLAP, mixed workloads • Oracle Real Application Cluster ➡ 8+ servers bi-pro Xeon, 72 GB RAM • Exadata storage server : intelligent cache ➡ 14+ cells, each with ✦ 2 processors, 24 Go RAM ✦ 385 GB of Flash memory (read is 10* faster than disk) ✦ 12+ SATA disks of 2 To or 12 SAS disks of 600 GB
Distributed DBMS ©M. T. Özsu & P. Valduriez Ch.14/48 Exadata Architecture Real Application Cluster Infiniband Switches Storage cells

More Related Content

PPTX
Deployment Models of Cloud Computing.pptx
byJaya Silwal
 
PPT
15. Transactions in DBMS
bykoolkampus
 
PPT
Memory management
byVishal Singh
 
PPTX
Transaction management in DBMS
byMegha Sharma
 
PPTX
Concurrency control!
byAshish K
 
PDF
management of distributed transactions
byNilu Desai
 
PDF
Cs8493 unit 4
byKathirvel Ayyaswamy
 
PPT
Chapter 13 - I/O Systems
byWayne Jones Jnr
 
Deployment Models of Cloud Computing.pptx
byJaya Silwal
 
15. Transactions in DBMS
bykoolkampus
 
Memory management
byVishal Singh
 
Transaction management in DBMS
byMegha Sharma
 
Concurrency control!
byAshish K
 
management of distributed transactions
byNilu Desai
 
Cs8493 unit 4
byKathirvel Ayyaswamy
 
Chapter 13 - I/O Systems
byWayne Jones Jnr
 

What's hot

PPT
Virtual memory - Demand Paging
byjeyaperumal
 
PPT
Distributed database management systems
byDhani Ahmad
 
PPTX
DBMS - RAID
byMythiliMurugan3
 
PDF
CS9222 ADVANCED OPERATING SYSTEMS
byKathirvel Ayyaswamy
 
PPTX
Replication in Distributed Database
byAbhilasha Lahigude
 
PPTX
Query processing and optimization (updated)
byRavinder Kamboj
 
PPT
Thrashing allocation frames.43
bymyrajendra
 
PPTX
Data decomposition techniques
byMohamed Ramadan
 
PPTX
Transactions and Concurrency Control
byDilum Bandara
 
DOCX
data replication
byHassanein Alwan
 
PPTX
Parallel processing
byPraveen Kumar
 
PPT
Memory Management in OS
byvampugani
 
PPT
Transactions in dbms
byNancy Gulati
 
PPT
11. Storage and File Structure in DBMS
bykoolkampus
 
PPT
Paging.ppt
byinfomerlin
 
PPTX
Virtual Memory
byArchith777
 
PPTX
Operating system - Process and its concepts
byKaran Thakkar
 
PPTX
Distributed database
byReachLocal Services India
 
PPTX
Concurrency control
bySoumyajit Dutta
 
PPTX
Limitations of memory system performance
bySyed Zaid Irshad
 
Virtual memory - Demand Paging
byjeyaperumal
 
Distributed database management systems
byDhani Ahmad
 
DBMS - RAID
byMythiliMurugan3
 
CS9222 ADVANCED OPERATING SYSTEMS
byKathirvel Ayyaswamy
 
Replication in Distributed Database
byAbhilasha Lahigude
 
Query processing and optimization (updated)
byRavinder Kamboj
 
Thrashing allocation frames.43
bymyrajendra
 
Data decomposition techniques
byMohamed Ramadan
 
Transactions and Concurrency Control
byDilum Bandara
 
data replication
byHassanein Alwan
 
Parallel processing
byPraveen Kumar
 
Memory Management in OS
byvampugani
 
Transactions in dbms
byNancy Gulati
 
11. Storage and File Structure in DBMS
bykoolkampus
 
Paging.ppt
byinfomerlin
 
Virtual Memory
byArchith777
 
Operating system - Process and its concepts
byKaran Thakkar
 
Distributed database
byReachLocal Services India
 
Concurrency control
bySoumyajit Dutta
 
Limitations of memory system performance
bySyed Zaid Irshad
 

Viewers also liked

PPTX
Load Balancing in Parallel and Distributed Database
byMd. Shamsur Rahim
 
PPSX
Parallel Database
byVESIT/University of Mumbai
 
PPTX
CS 542 Parallel DBs, NoSQL, MapReduce
byJ Singh
 
PDF
The DSP/BIOS Bridge - OMAP3
byvjaquez
 
PPTX
Database , 5 Semantic
byAli Usman
 
PPTX
PL/pgSQL - An Introduction on Using Imperative Programming in PostgreSQL
byReactive.IO
 
PPTX
Introduction to Parallel Processing Algorithms in Shared Nothing Databases
byOfir Manor
 
PDF
MySQL InnoDB 源码实现分析(一)
byfrogd
 
PPTX
Database ,16 P2P
byAli Usman
 
PPTX
Database , 12 Reliability
byAli Usman
 
PPTX
Database ,10 Transactions
byAli Usman
 
PPTX
Postgres MVCC - A Developer Centric View of Multi Version Concurrency Control
byReactive.IO
 
PDF
InnoDB Internal
bymysqlops
 
PPTX
Database , 13 Replication
byAli Usman
 
PPTX
Database ,7 query localization
byAli Usman
 
PPTX
Log based and Recovery with concurrent transaction
bynikunjandy
 
PPTX
Database, 3 Distribution Design
byAli Usman
 
PPTX
Database ,11 Concurrency Control
byAli Usman
 
PDF
Oracle rac资源管理算法与cache fusion实现浅析
byfrogd
 
PDF
Discrete Structures lecture 2
byAli Usman
 
Load Balancing in Parallel and Distributed Database
byMd. Shamsur Rahim
 
Parallel Database
byVESIT/University of Mumbai
 
CS 542 Parallel DBs, NoSQL, MapReduce
byJ Singh
 
The DSP/BIOS Bridge - OMAP3
byvjaquez
 
Database , 5 Semantic
byAli Usman
 
PL/pgSQL - An Introduction on Using Imperative Programming in PostgreSQL
byReactive.IO
 
Introduction to Parallel Processing Algorithms in Shared Nothing Databases
byOfir Manor
 
MySQL InnoDB 源码实现分析(一)
byfrogd
 
Database ,16 P2P
byAli Usman
 
Database , 12 Reliability
byAli Usman
 
Database ,10 Transactions
byAli Usman
 
Postgres MVCC - A Developer Centric View of Multi Version Concurrency Control
byReactive.IO
 
InnoDB Internal
bymysqlops
 
Database , 13 Replication
byAli Usman
 
Database ,7 query localization
byAli Usman
 
Log based and Recovery with concurrent transaction
bynikunjandy
 
Database, 3 Distribution Design
byAli Usman
 
Database ,11 Concurrency Control
byAli Usman
 
Oracle rac资源管理算法与cache fusion实现浅析
byfrogd
 
Discrete Structures lecture 2
byAli Usman
 

Similar to Database ,14 Parallel DBMS

PPTX
1 introduction
byAmrit Kaur
 
PPTX
DBMS Notes for BSC Students for all batch
bysudhanadar606
 
PPTX
1-Introduction<in data base and informentions.pptx
bypsychoguyyy
 
PPTX
1 introduction DDBS
bynaimanighat
 
PPTX
1- Introduction for software engineering
bymouath1424
 
PPT
Introduction to Distributed computing.ppt
bysinunambiar4
 
PPTX
Database , 1 Introduction
byAli Usman
 
PPTX
1 introduction ddbms
byamna izzat
 
PPT
Intro to Distributed Database Management System
byAli Raza
 
PPT
Ch07.ppt
byamgadmonir1
 
PDF
Lecture 1 DDBS.pdf basic introduction of distribution
byFatimaTariq87
 
PPTX
database Architecture and alternative distributed solutions
byNoureenZafar3
 
PPT
week1 lecture2 on DataBaseManagementSystem.ppt
byssuserf170c4
 
PPT
1.Beginning_Distributed Database Management Systems.ppt
bymanasmukul1
 
PPTX
Database , 15 Object DBMS
byAli Usman
 
PDF
SingleLecture.pdf
byMastroQUU
 
PPTX
Distributed Databases - Concepts & Architectures
byDaniel Marcous
 
PPT
Distributed Database System
bySulemang
 
PPTX
Pmit 6102-14-lec1-intro
byJesmin Rahaman
 
PPT
Lecture 10 distributed database management system
byemailharmeet
 
1 introduction
byAmrit Kaur
 
DBMS Notes for BSC Students for all batch
bysudhanadar606
 
1-Introduction<in data base and informentions.pptx
bypsychoguyyy
 
1 introduction DDBS
bynaimanighat
 
1- Introduction for software engineering
bymouath1424
 
Introduction to Distributed computing.ppt
bysinunambiar4
 
Database , 1 Introduction
byAli Usman
 
1 introduction ddbms
byamna izzat
 
Intro to Distributed Database Management System
byAli Raza
 
Ch07.ppt
byamgadmonir1
 
Lecture 1 DDBS.pdf basic introduction of distribution
byFatimaTariq87
 
database Architecture and alternative distributed solutions
byNoureenZafar3
 
week1 lecture2 on DataBaseManagementSystem.ppt
byssuserf170c4
 
1.Beginning_Distributed Database Management Systems.ppt
bymanasmukul1
 
Database , 15 Object DBMS
byAli Usman
 
SingleLecture.pdf
byMastroQUU
 
Distributed Databases - Concepts & Architectures
byDaniel Marcous
 
Distributed Database System
bySulemang
 
Pmit 6102-14-lec1-intro
byJesmin Rahaman
 
Lecture 10 distributed database management system
byemailharmeet
 

More from Ali Usman

PPT
Cisco Packet Tracer Overview
byAli Usman
 
PDF
Islamic Arts and Architecture
byAli Usman
 
PPTX
Database ,18 Current Issues
byAli Usman
 
PPTX
Database , 17 Web
byAli Usman
 
PPTX
Database , 8 Query Optimization
byAli Usman
 
PPTX
Database , 6 Query Introduction
byAli Usman
 
PPTX
Database , 4 Data Integration
byAli Usman
 
PPTX
Database ,2 Background
byAli Usman
 
DOCX
Processor Specifications
byAli Usman
 
PDF
Fifty Year Of Microprocessor
byAli Usman
 
PDF
Discrete Structures. Lecture 1
byAli Usman
 
PDF
Muslim Contributions in Medicine-Geography-Astronomy
byAli Usman
 
PDF
Muslim Contributions in Geography
byAli Usman
 
PDF
Muslim Contributions in Astronomy
byAli Usman
 
DOCX
Processor Specifications
byAli Usman
 
PDF
Ptcl modem (user manual)
byAli Usman
 
PDF
Nimat-ul-ALLAH shah wali
byAli Usman
 
PDF
Muslim Contributions in Mathematics
byAli Usman
 
PDF
Osi protocols
byAli Usman
 
Cisco Packet Tracer Overview
byAli Usman
 
Islamic Arts and Architecture
byAli Usman
 
Database ,18 Current Issues
byAli Usman
 
Database , 17 Web
byAli Usman
 
Database , 8 Query Optimization
byAli Usman
 
Database , 6 Query Introduction
byAli Usman
 
Database , 4 Data Integration
byAli Usman
 
Database ,2 Background
byAli Usman
 
Processor Specifications
byAli Usman
 
Fifty Year Of Microprocessor
byAli Usman
 
Discrete Structures. Lecture 1
byAli Usman
 
Muslim Contributions in Medicine-Geography-Astronomy
byAli Usman
 
Muslim Contributions in Geography
byAli Usman
 
Muslim Contributions in Astronomy
byAli Usman
 
Processor Specifications
byAli Usman
 
Ptcl modem (user manual)
byAli Usman
 
Nimat-ul-ALLAH shah wali
byAli Usman
 
Muslim Contributions in Mathematics
byAli Usman
 
Osi protocols
byAli Usman
 

Recently uploaded

PDF
APDCA Energy sustainability white paper.pdf
byflintglobalapac
 
PPTX
Stop Worrying, Start Wiring: Azure Serverless for the AI Era
byMuralidharan Deenathayalan
 
DOCX
Formula 2 - APIC HBEL general calculation.docx
byMarkus Janssen
 
PDF
Session 3 - UiPath Coded Agents & MCP Server in Action
byDianaGray10
 
PDF
ChatGPT Ain't Got $%@& On Me! by Andy Pavlo
byScyllaDB
 
PDF
Energia Nowa and byteLAKE Partner to Develop Intelligent Services for Energy ...
bybyteLAKE
 
PDF
ClipsField AI - Transform Static Images Into Cinematic Videos in Minutes.pdf
bySOFTTECHHUB
 
DOCX
Formula 1 - APIC general MACO calculation.docx
byMarkus Janssen
 
PPTX
Mosaic: The Agentic AI Video Editing Platform
byDaniel Gimness
 
PDF
CymruSec 2025 - Wales' Inaugural Cyber Summit
byRay Bugg
 
PDF
Session 7 Specialized AI Associate Series: UiPath Communications Mining Overview
byDianaGray10
 
PDF
ZGC: A Decade of Innovation by Stefan Johansson
byScyllaDB
 
PPTX
Session 5 - Specialized AI Associate Series: Build a Document Understanding ...
byDianaGray10
 
PDF
AI Enabled Task Automation Technology Trends in the IT Industry
byManish Chopra
 
PPTX
ScalaF: Functional Refactoring and Automated Code Transformations
byhelloshrikha
 
PDF
The State of Adventure Capital - Grant Gregory | Cantos
byRazin Mustafiz
 
PDF
Keynote: X-rAI Specs, Submarines and Juice
byAll Things Open
 
PDF
G.O.RT.No. 1173_LRS Extension of time limit
byRAGHU RAM
 
PPTX
Artificial Intelligence lecture slides.pptx
bymaimelabernard6
 
PPTX
Autonomous Convoy Routing via Drone Swarms and Multi-Modal Threat Detection
byIvan Ruchkin
 
APDCA Energy sustainability white paper.pdf
byflintglobalapac
 
Stop Worrying, Start Wiring: Azure Serverless for the AI Era
byMuralidharan Deenathayalan
 
Formula 2 - APIC HBEL general calculation.docx
byMarkus Janssen
 
Session 3 - UiPath Coded Agents & MCP Server in Action
byDianaGray10
 
ChatGPT Ain't Got $%@& On Me! by Andy Pavlo
byScyllaDB
 
Energia Nowa and byteLAKE Partner to Develop Intelligent Services for Energy ...
bybyteLAKE
 
ClipsField AI - Transform Static Images Into Cinematic Videos in Minutes.pdf
bySOFTTECHHUB
 
Formula 1 - APIC general MACO calculation.docx
byMarkus Janssen
 
Mosaic: The Agentic AI Video Editing Platform
byDaniel Gimness
 
CymruSec 2025 - Wales' Inaugural Cyber Summit
byRay Bugg
 
Session 7 Specialized AI Associate Series: UiPath Communications Mining Overview
byDianaGray10
 
ZGC: A Decade of Innovation by Stefan Johansson
byScyllaDB
 
Session 5 - Specialized AI Associate Series: Build a Document Understanding ...
byDianaGray10
 
AI Enabled Task Automation Technology Trends in the IT Industry
byManish Chopra
 
ScalaF: Functional Refactoring and Automated Code Transformations
byhelloshrikha
 
The State of Adventure Capital - Grant Gregory | Cantos
byRazin Mustafiz
 
Keynote: X-rAI Specs, Submarines and Juice
byAll Things Open
 
G.O.RT.No. 1173_LRS Extension of time limit
byRAGHU RAM
 
Artificial Intelligence lecture slides.pptx
bymaimelabernard6
 
Autonomous Convoy Routing via Drone Swarms and Multi-Modal Threat Detection
byIvan Ruchkin
 

Database ,14 Parallel DBMS

  • 1.
    Distributed DBMS ©M.T. Özsu & P. Valduriez Ch.14/1 Outline • Introduction • Background • Distributed Database Design • Database Integration • Semantic Data Control • Distributed Query Processing • Multidatabase Query Processing • Distributed Transaction Management • Data Replication • Parallel Database Systems ➡ Data placement and query processing ➡ Load balancing ➡ Database clusters • Distributed Object DBMS • Peer-to-Peer Data Management • Web Data Management • Current Issues
  • 2.
    Distributed DBMS ©M.T. Özsu & P. Valduriez Ch.14/2 The Database Problem • Large volume of data use disk and large main memory • I/O bottleneck (or memory access bottleneck) ➡ Speed(disk) << speed(RAM) << speed(microprocessor) • Predictions ➡ Moore’s law: processor speed growth (with multicore): 50 % per year ➡ DRAM capacity growth : 4 × every three years ➡ Disk throughput : 2 × in the last ten years • Conclusion : the I/O bottleneck worsens
  • 3.
    Distributed DBMS ©M.T. Özsu & P. Valduriez Ch.14/3 The Solution • Increase the I/O bandwidth ➡ Data partitioning ➡ Parallel data access • Origins (1980's): database machines ➡ Hardware-oriented bad cost-performance failure ➡ Notable exception : ICL's CAFS Intelligent Search Processor • 1990's: same solution but using standard hardware components integrated in a multiprocessor ➡ Software-oriented ➡ Standard essential to exploit continuing technology improvements
  • 4.
    Distributed DBMS ©M.T. Özsu & P. Valduriez Ch.14/4 Multiprocessor Objectives • High-performance with better cost-performance than mainframe or vector supercomputer • Use many nodes, each with good cost-performance, communicating through network ➡ Good cost via high-volume components ➡ Good performance via bandwidth • Trends ➡ Microprocessor and memory (DRAM): off-the-shelf ➡ Network (multiprocessor edge): custom • The real chalenge is to parallelize applications to run with good load balancing
  • 5.
    Distributed DBMS ©M.T. Özsu & P. Valduriez Ch.14/5 Data Server Architecture client interface query parsing data server interface communication channel Application server Data server database application server interface database functions Client
  • 6.
    Distributed DBMS ©M.T. Özsu & P. Valduriez Ch.14/6 Objectives of Data Servers • Avoid the shortcomings of the traditional DBMS approach ➡ Centralization of data and application management ➡ General-purpose OS (not DB-oriented) • By separating the functions between ➡ Application server (or host computer) ➡ Data server (or database computer or back-end computer)
  • 7.
    Distributed DBMS ©M.T. Özsu & P. Valduriez Ch.14/7 Data Server Approach: Assessment • Advantages ➡ Integrated data control by the server (black box) ➡ Increased performance by dedicated system ➡ Can better exploit parallelism ➡ Fits well in distributed environments • Potential problems ➡ Communication overhead between application and data server ✦ High-level interface ➡ High cost with mainframe servers
  • 8.
    Distributed DBMS ©M.T. Özsu & P. Valduriez Ch.14/8 Parallel Data Processing • Three ways of exploiting high-performance multiprocessor systems:  Automatically detect parallelism in sequential programs (e.g., Fortran, OPS5)  Augment an existing language with parallel constructs (e.g., C*, Fortran90)  Offer a new language in which parallelism can be expressed or automatically inferred • Critique  Hard to develop parallelizing compilers, limited resulting speed-up  Enables the programmer to express parallel computations but too low-level  Can combine the advantages of both (1) and (2)
  • 9.
    Distributed DBMS ©M.T. Özsu & P. Valduriez Ch.14/9 Data-based Parallelism • Inter-operation ➡ p operations of the same query in parallel op.3 op.1 op.2 op. R op. R1 op. R2 op. R3 op. R4 • Intra-operation ➡ The same op in parallel
  • 10.
    Distributed DBMS ©M.T. Özsu & P. Valduriez Ch.14/10 Parallel DBMS • Loose definition: a DBMS implemented on a tighly coupled multiprocessor • Alternative extremes ➡ Straighforward porting of relational DBMS (the software vendor edge) ➡ New hardware/software combination (the computer manufacturer edge) • Naturally extends to distributed databases with one server per site
  • 11.
    Distributed DBMS ©M.T. Özsu & P. Valduriez Ch.14/11 Parallel DBMS - Objectives • Much better cost / performance than mainframe solution • High-performance through parallelism ➡ High throughput with inter-query parallelism ➡ Low response time with intra-operation parallelism • High availability and reliability by exploiting data replication • Extensibility with the ideal goals ➡ Linear speed-up ➡ Linear scale-up
  • 12.
    Distributed DBMS ©M.T. Özsu & P. Valduriez Ch.14/12 Linear Speed-up Linear increase in performance for a constant DB size and proportional increase of the system components (processor, memory, disk) new perf. old perf. ideal components
  • 13.
    Distributed DBMS ©M.T. Özsu & P. Valduriez Ch.14/13 Linear Scale-up Sustained performance for a linear increase of database size and proportional increase of the system components. components + database size new perf. old perf. ideal
  • 14.
    Distributed DBMS ©M.T. Özsu & P. Valduriez Ch.14/14 Barriers to Parallelism • Startup ➡ The time needed to start a parallel operation may dominate the actual computation time • Interference ➡ When accessing shared resources, each new process slows down the others (hot spot problem) • Skew ➡ The response time of a set of parallel processes is the time of the slowest one • Parallel data management techniques intend to overcome these barriers
  • 15.
    Distributed DBMS ©M.T. Özsu & P. Valduriez Ch.14/15 Parallel DBMS – Functional Architecture RM task n DM task 12 DM task n2 DM task n1 Data Mgr DM task 11 Request MgrRM task 1 Session Mgr User task 1 User task n
  • 16.
    Distributed DBMS ©M.T. Özsu & P. Valduriez Ch.14/16 Parallel DBMS Functions • Session manager ➡ Host interface ➡ Transaction monitoring for OLTP • Request manager ➡ Compilation and optimization ➡ Data directory management ➡ Semantic data control ➡ Execution control • Data manager ➡ Execution of DB operations ➡ Transaction management support ➡ Data management
  • 17.
    Distributed DBMS ©M.T. Özsu & P. Valduriez Ch.14/17 Parallel System Architectures • Multiprocessor architecture alternatives ➡ Shared memory (SM) ➡ Shared disk (SD) ➡ Shared nothing (SN) • Hybrid architectures ➡ Non-Uniform Memory Architecture (NUMA) ➡ Cluster
  • 18.
    Distributed DBMS ©M.T. Özsu & P. Valduriez Ch.14/18 Shared-Memory DBMS on symmetric multiprocessors (SMP) Prototypes: XPRS, Volcano, DBS3 + Simplicity, load balancing, fast communication - Network cost, low extensibility
  • 19.
    Distributed DBMS ©M.T. Özsu & P. Valduriez Ch.14/19 Shared-Disk Origins : DEC's VAXcluster, IBM's IMS/VS Data Sharing Used first by Oracle with its Distributed Lock Manager Now used by most DBMS vendors + network cost, extensibility, migration from uniprocessor - complexity, potential performance problem for cache coherency
  • 20.
    Distributed DBMS ©M.T. Özsu & P. Valduriez Ch.14/20 Shared-Nothing Used by Teradata, IBM, Sybase, Microsoft for OLAP Prototypes: Gamma, Bubba, Grace, Prisma, EDS + Extensibility, availability - Complexity, difficult load balancing
  • 21.
    Distributed DBMS ©M.T. Özsu & P. Valduriez Ch.14/21 Hybrid Architectures • Various possible combinations of the three basic architectures are possible to obtain different trade-offs between cost, performance, extensibility, availability, etc. • Hybrid architectures try to obtain the advantages of different architectures: ➡ efficiency and simplicity of shared-memory ➡ extensibility and cost of either shared disk or shared nothing • 2 main kinds: NUMA and cluster
  • 22.
    Distributed DBMS ©M.T. Özsu & P. Valduriez Ch.14/22 NUMA • Shared-Memory vs. Distributed Memory ➡ Mixes two different aspects : addressing and memory ✦ Addressing: single address space vs multiple address spaces ✦ Physical memory: central vs distributed • NUMA = single address space on distributed physical memory ➡ Eases application portability ➡ Extensibility • The most successful NUMA is Cache Coherent NUMA (CC-NUMA)
  • 23.
    Distributed DBMS ©M.T. Özsu & P. Valduriez Ch.14/23 CC-NUMA • Principle ➡ Main memory distributed as with shared-nothing ➡ However, any processor has access to all other processors’ memories • Similar to shared-disk, different processors can access the same data in a conflicting update mode, so global cache consistency protocols are needed. ➡ Cache consistency done in hardware through a special consistent cache interconnect ✦ Remote memory access very efficient, only a few times (typically between 2 and 3 times) the cost of local access
  • 24.
    Distributed DBMS ©M.T. Özsu & P. Valduriez Ch.14/24 Cluster • Combines good load balancing of SM with extensibility of SN • Server nodes: off-the-shelf components ➡ From simple PC components to more powerful SMP ➡ Yields the best cost/performance ratio ➡ In its cheapest form, • Fast standard interconnect (e.g., Myrinet and Infiniband) with high bandwidth (Gigabits/sec) and low latency
  • 25.
    Distributed DBMS ©M.T. Özsu & P. Valduriez Ch.14/25 SN cluster vs SD cluster • SN cluster can yield best cost/performance and extensibility ➡ But adding or replacing cluster nodes requires disk and data reorganization • SD cluster avoids such reorganization but requires disks to be globally accessible by the cluster nodes ➡ Network-attached storage (NAS) ✦ distributed file system protocol such as NFS, relatively slow and not appropriate for database management ➡ Storage-area network (SAN) ✦ Block-based protocol thus making it easier to manage cache consistency, efficient, but costlier
  • 26.
    Distributed DBMS ©M.T. Özsu & P. Valduriez Ch.14/26 Discussion • For a small configuration (e.g., 8 processors), SM can provide the highest performance because of better load balancing • Some years ago, SN was the only choice for high-end systems. But SAN makes SN a viable alternative with the main advantage of simplicity (for transaction management) ➡ SD is now the preferred architecture for OLTP ➡ But for OLAP databases that are typically very large and mostly read-only, SN is used • Hybrid architectures, such as NUMA and cluster, can combine the efficiency and simplicity of SM and the extensibility and cost of either SD or SN
  • 27.
    Distributed DBMS ©M.T. Özsu & P. Valduriez Ch.14/27 Parallel DBMS Techniques • Data placement ➡ Physical placement of the DB onto multiple nodes ➡ Static vs. Dynamic • Parallel data processing ➡ Select is easy ➡ Join (and all other non-select operations) is more difficult • Parallel query optimization ➡ Choice of the best parallel execution plans ➡ Automatic parallelization of the queries and load balancing • Transaction management ➡ Similar to distributed transaction management
  • 28.
    Distributed DBMS ©M.T. Özsu & P. Valduriez Ch.14/28 Data Partitioning • Each relation is divided in n partitions (subrelations), where n is a function of relation size and access frequency • Implementation ➡ Round-robin ✦ Maps i-th element to node i mod n ✦ Simple but only exact-match queries ➡ B-tree index ✦ Supports range queries but large index ➡ Hash function ✦ Only exact-match queries but small index
  • 29.
    Distributed DBMS ©M.T. Özsu & P. Valduriez Ch.14/29 Partitioning Schemes Round-Robin Hashing Interval ••• ••• ••• ••• ••• ••• •••a-g h-m u-z
  • 30.
    Distributed DBMS ©M.T. Özsu & P. Valduriez Ch.14/30 Replicated Data Partitioning • High-availability requires data replication ➡ simple solution is mirrored disks ✦ hurts load balancing when one node fails ➡ more elaborate solutions achieve load balancing ✦ interleaved partitioning (Teradata) ✦ chained partitioning (Gamma)
  • 31.
    Distributed DBMS ©M.T. Özsu & P. Valduriez Ch.14/31 Interleaved Partitioning Node Primary copy R1 R2 R3 R4 Backup copy r1.1 r1.2 r1.3 r2.3 r2.1 r2.2 r3.2 r3.2 r3.1 1 2 3 4
  • 32.
    Distributed DBMS ©M.T. Özsu & P. Valduriez Ch.14/32 Chained Partitioning Node Primary copy R1 R2 R3 R4 Backup copy r4 r1 r2 r3 1 2 3 4
  • 33.
    Distributed DBMS ©M.T. Özsu & P. Valduriez Ch.14/33 Placement Directory • Performs two functions ➡ F1 (relname, placement attval) = lognode-id ➡ F2 (lognode-id) = phynode-id • In either case, the data structure for f1 and f2 should be available when needed at each node
  • 34.
    Distributed DBMS ©M.T. Özsu & P. Valduriez Ch.14/34 Join Processing • Three basic algorithms for intra-operator parallelism ➡ Parallel nested loop join: no special assumption ➡ Parallel associative join: one relation is declustered on join attribute and equi-join ➡ Parallel hash join: equi-join • They also apply to other complex operators such as duplicate elimination, union, intersection, etc. with minor adaptation
  • 35.
    Distributed DBMS ©M.T. Özsu & P. Valduriez Ch.14/35 Parallel Nested Loop Join send partition node 3 node 4 node 1 node 2 R1: S1 S2 R2:
  • 36.
    Distributed DBMS ©M.T. Özsu & P. Valduriez Ch.14/36 Parallel Associative Join node 1 node 3 node 4 node 2 R1: R2: S1 S2
  • 37.
    Distributed DBMS ©M.T. Özsu & P. Valduriez Ch.14/37 Parallel Hash Join node node node node node 1 node 2 R1: R2: S1: S2:
  • 38.
    Distributed DBMS ©M.T. Özsu & P. Valduriez Ch.14/38 Parallel Query Optimization • The objective is to select the “best” parallel execution plan for a query using the following components • Search space ➡ Models alternative execution plans as operator trees ➡ Left-deep vs. Right-deep vs. Bushy trees • Search strategy ➡ Dynamic programming for small search space ➡ Randomized for large search space • Cost model (abstraction of execution system) ➡ Physical schema info. (partitioning, indexes, etc.) ➡ Statistics and cost functions
  • 39.
    Distributed DBMS ©M.T. Özsu & P. Valduriez Ch.14/39 Execution Plans as Operator Trees R2R1 R4 Result j2 j3 Left-deep Right-deep j1 R3 R2R1 R4 Result j5 j6 j4R3 R2R1 R3j7 R4 Result j9 Zig-zag Bushyj8 Result j10 j12 j11 R2R1 R4R3
  • 40.
    Distributed DBMS ©M.T. Özsu & P. Valduriez Ch.14/40 Equivalent Hash-Join Trees with Different Scheduling R3 Probe3Build3 R4 Temp2 Temp1 Build3 R4 Temp2 Probe3Build3 Probe2Build2 Probe1Build1 R2R1 R3 Temp1 Probe2Build2 Probe1Build1 R2R1
  • 41.
    Distributed DBMS ©M.T. Özsu & P. Valduriez Ch.14/41 Load Balancing • Problems arise for intra-operator parallelism with skewed data distributions ➡ attribute data skew (AVS) ➡ tuple placement skew (TPS) ➡ selectivity skew (SS) ➡ redistribution skew (RS) ➡ join product skew (JPS) • Solutions ➡ sophisticated parallel algorithms that deal with skew ➡ dynamic processor allocation (at execution time)
  • 42.
    Distributed DBMS ©M.T. Özsu & P. Valduriez Ch.14/42 Data Skew Example Join1 Res1 Res2 Join2 AVS/TPS AVS/TPS AVS/TPS AVS/TPS JPS JPS RS/SS RS/SS Scan1 S2 R2 S1 R1 Scan2
  • 43.
    Distributed DBMS ©M.T. Özsu & P. Valduriez Ch.14/43 Load Balancing in a DB Cluster • Choose the node to execute Q ➡ round robin ➡ The least loaded ✦ Need to get load information • Fail over ➡ In case a node N fails, N’s queries are taken over by another node ✦ Requires a copy of N’s data or SD • In case of interference ➡ Data of an overloaded node are replicated to another node Q1 Q2 Load balancing Q3 Q4 Q4 Q3 Q2 Q1
  • 44.
    Distributed DBMS ©M.T. Özsu & P. Valduriez Ch.14/44 Oracle Transparent Application Failover Client Node 1 connect1 Node 2Ping
  • 45.
    Distributed DBMS ©M.T. Özsu & P. Valduriez Ch.14/45 Client PCs Enterprise network Microsoft Failover Cluster Topology Internal network Fibre Channel
  • 46.
    Distributed DBMS ©M.T. Özsu & P. Valduriez Ch.14/46 Main Products Vendor Product Architecture Platforms IBM DB2 Pure Scale DB2 Database Partitioning Feature (DPF) SD SN AIX on SP Linux on cluster Microsoft SQL Server SQL Server 2008 R2 Parallel Data Warehouse SD SN Windows on SMP and cluster Oracle Real Application Cluster Exadata Database Machine SD Windows, Unix, Linux on SMP and cluster NCR Teradata SN Bynet network NCR Unix and Windows Oracle MySQL SN Linux Cluster
  • 47.
    Distributed DBMS ©M.T. Özsu & P. Valduriez Ch.14/47 The Exadata Database Machine • New machine from Oracle with Sun • Objectives ➡ OLTP, OLAP, mixed workloads • Oracle Real Application Cluster ➡ 8+ servers bi-pro Xeon, 72 GB RAM • Exadata storage server : intelligent cache ➡ 14+ cells, each with ✦ 2 processors, 24 Go RAM ✦ 385 GB of Flash memory (read is 10* faster than disk) ✦ 12+ SATA disks of 2 To or 12 SAS disks of 600 GB
  • 48.
    Distributed DBMS ©M.T. Özsu & P. Valduriez Ch.14/48 Exadata Architecture Real Application Cluster Infiniband Switches Storage cells