Abel Flórez, profile picture
Uploaded byAbel Flórez
PDF, PPTX916 views

Introduction to MySQL Cluster

The document provides an overview of MySQL Cluster, its history, architecture, features, and use cases. It emphasizes the design goals of high reliability, performance, and real-time capabilities, primarily for telecom applications. Additionally, it outlines conditions for employing MySQL Cluster and structural components including data nodes, management nodes, and various partitioning methods.

Download as PDF, PPTX
Copyright © 2015 Oracle and/or its affiliates. All rights reserved. | Introduction to MySQL Cluster Abel Flórez Technical Account Manager 2015
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | Safe Harbor Statement The following is intended to outline our general product direction. It is intended for information purposes only, and may not be incorporated into any contract. It is not a commitment to deliver any material, code, or functionality, and should not be relied upon in making purchasing decisions. The development, release, and timing of any features or functionality described for Oracle’s products remains at the sole discretion of Oracle. 3
Copyright © 2015 Oracle and/or its affiliates. All rights reserved. | History of MySQL Cluster ”NDB” • MySQL Cluster aka Network DataBase NDB • Designed/Developed at Ericsson in late 90’s • Original design paper: ”Design and Modeling of a Parallel Data Server for Telecom Applications” from 1997 by Mikael Ronström • Originally written in PLEX (Programming Language for EXchanges) but later converted to C++. • MySQL AB acquired Alzato (owned by Ericsson) late 2003. The Network DataBase NDB
Copyright © 2015 Oracle and/or its affiliates. All rights reserved. | History of MySQL Cluster ”NDB” • Databases services back then: – SCP/SDP (Service Control/Data Point) in Intelligent Networks. – HLR (Home Location Register) for keeping track of mobile phones/users. – Databases for network management especially real-time charging information. The Network DataBase NDB
Copyright © 2015 Oracle and/or its affiliates. All rights reserved. | History of MySQL Cluster ”NDB” • NDB was designed to: – Reliability, the availability class of the telecom databases should be 6 (99.9999%). This means that downtime must be less than 30 seconds per year: no planned down time of the system is allowed. – Performance, designed for high throughput, linear scalabillity when adding more servers (data nodes) for simple access patterns (PK lookups). – Real-time, data is kept in memory and system is designed for memory operations. The Network DataBase NDB
Copyright © 2015 Oracle and/or its affiliates. All rights reserved. | When to consider MySQL Cluster • What are the consequences of downtime or failing to meet performance requirements? • How much effort and $ is spent in developing and managing HA in your applications? • Are you considering sharding your database to scale write performance? How does that impact your application and developers? • Do your services need to be real-time? • Will your services have unpredictable scalability demands, especially for writes? • Do you want the flexibility to manage your data with more than just SQL? 7
Copyright © 2015 Oracle and/or its affiliates. All rights reserved. | When NOT to consider MySQL Cluster • Most 3rd party applications • Long running transactions • Geospatial indexes • Huge dataset (>2TB) • Complex access pattern to data and many full table scans • When you need a disk based database like InnoDB 8
Copyright © 2015 Oracle and/or its affiliates. All rights reserved. | Oracle MySQL HA & Scaling Solutions MySQL Replication MySQL Fabric Oracle VM Template Oracle Clusterware Solaris Cluster Windows Cluster DRBD MySQL Cluster App Auto-Failover ✖ ✔ ✔ ✔ ✔ ✔ ✔ ✔ Data Layer Auto-Failover ✖ ✔ ✔ ✔ ✔ ✔ ✔ ✔ Zero Data Loss MySQL 5.7 MySQL 5.7 ✔ ✔ ✔ ✔ ✔ ✔ Platform Support All All Linux Linux Solaris Windows Linux All Clustering Mode Master + Slaves Master + Slaves Active/ Passive Active/ Passive Active/ Passive Active/ Passive Active/ Passive Multi- Master Failover Time N/A Secs Secs + Secs + Secs + Secs + Secs + < 1 Sec Scale-out Reads ✔ ✖ ✖ ✖ ✖ ✖ ✔ Cross-shard operations N/A ✖ N/A N/A N/A N/A N/A ✔ Transparent routing ✖ For HA ✔ ✔ ✔ ✔ ✔ ✔ Shared Nothing ✔ ✔ ✖ ✖ ✖ ✖ ✔ ✔ Storage Engine InnoDB+ InnoDB+ InnoDB+ InnoDB+ InnoDB+ InnoDB+ InnoDB+ NDB Single Vendor Support ✔ ✔ ✔ ✔ ✔ ✖ ✔ ✔
Copyright © 2015 Oracle and/or its affiliates. All rights reserved. | MySQL Cluster overview
Copyright © 2015 Oracle and/or its affiliates. All rights reserved. | MySQL Cluster Components 11 NDB API (Applications) Data Node (Data Storage) MGM Node (Management) SQL Node (Applications) • Standard SQL interface • Scale out for performance • Enables Geo Replication • Real-time applications • C++/Java APIs • Automatic failover & load balancing • Data storage (Memory & Disk) • Automatic & User defined data partitioning • Scale out for capacity and performance • Management, Monitoring & Configuration • Arbitrator for split brain/network partitioning • Cluster logs
Copyright © 2015 Oracle and/or its affiliates. All rights reserved. | Data Nodes • Stores data and indexes – In memory – Non-indexed data possible on disk – Contains several blocks, most important, LQH, TUP, ACC and TC. • Data check pointed to disk “LCP” • Transaction coordination • Handling fail-over • Doing online backup • All connect to each other • Up to 48 – Typically 2, 4.
Copyright © 2015 Oracle and/or its affiliates. All rights reserved. | Management Nodes • Distributing configuration • Logging • Monitoring • Act as Arbitrator – Prevents split-brain • OK when not running – Need to start others • 1 is minimum, 3 too many, 2 is OK
Copyright © 2015 Oracle and/or its affiliates. All rights reserved. | API Nodes • Applications written using NDB API – C / C++ / Java • Fast – No SQL parsing • Examples: – NDBCluster storage engine – ndb_restore
Copyright © 2015 Oracle and/or its affiliates. All rights reserved. | SQL Nodes • MySQL using NDBCluster engine – Is also an API Node • Transparent for most applications • Used to create tables • Used for Geographical Replication – Binary logging all changes • Can act as Arbitrator • Connects to all Data Nodes
Copyright © 2015 Oracle and/or its affiliates. All rights reserved. | MySQL Cluster Architecture MySQL Cluster Data Nodes Clients Application Layer Data Layer Management
Copyright © 2015 Oracle and/or its affiliates. All rights reserved. | MySQL Cluster Scaling MySQL Cluster Data Nodes Clients Application Layer Data Layer Management
Copyright © 2015 Oracle and/or its affiliates. All rights reserved. | MySQL Cluster - Extreme Resilience MySQL Cluster Data Nodes Clients Application Layer Data Layer Management
Copyright © 2015 Oracle and/or its affiliates. All rights reserved. | Partitioning I • Vertical Partitioning - 1:1 tables to reduce the size of rows, tables and indexes • Horizontal Partitioning - 1 table split on multiple tables with different rows
Copyright © 2015 Oracle and/or its affiliates. All rights reserved. | p1 p2 p1 p2 p3 Data Partitioning II • Data is partitioned on primary key per default • HASH value of PK, only selective if you provide full PK not “left most” • Linear hashing, data is only moved away (low impact of reorganize)
Copyright © 2015 Oracle and/or its affiliates. All rights reserved. | Data Node 1 Data Node 2 Data Node 3 Data Node 4- A partition is a portion of a table - Number of partitions = number of data nodes - Horizontal partitioning Table T1 ID FirstName LastName Email Phone P2 P3 P4 Px Partition P1 Automatic Data Partitioning
Copyright © 2015 Oracle and/or its affiliates. All rights reserved. | Data Node 1 Data Node 2 Data Node 3 Data Node 4A fragment is a partition Number of fragments = # of partitions * # of replicas Table T1 ID FirstName LastName Email Phone P2 P3 P4 Px Partition P1 Automatic Data Partitioning
Copyright © 2015 Oracle and/or its affiliates. All rights reserved. | Data Node 1 Data Node 2 Data Node 3 Data Node 4A fragment can be primary or secondary/backup Number of fragments = # of partitions * # of replicas Table T1 ID FirstName LastName Email Phone P2 P3 P4 Px Partition 4 Partitions * 2 Replicas = 8 Fragments P1 Automatic Data Partitioning
Copyright © 2015 Oracle and/or its affiliates. All rights reserved. | Data Node 1 Data Node 2 F1 Primary Fragment Secondary Fragment Data Node 3 Data Node 4Fx Fx Table T1 ID FirstName LastName Email Phone P2 P3 P4 Px Partition 4 Partitions * 2 Replicas = 8 Fragments P1 Automatic Data Partitioning
Copyright © 2015 Oracle and/or its affiliates. All rights reserved. | Data Node 1 Data Node 2 F1 Primary Fragment Secondary Fragment F1 Data Node 3 Data Node 4Fx Fx Table T1 ID FirstName LastName Email Phone P2 P3 P4 Px Partition 4 Partitions * 2 Replicas = 8 Fragments P1 Automatic Data Partitioning
Copyright © 2015 Oracle and/or its affiliates. All rights reserved. | Data Node 1 Data Node 2 F1 Primary Fragment Secondary Fragment F3 F1 Data Node 3 Data Node 4Fx Fx Table T1 ID FirstName LastName Email Phone P2 P3 P4 Px Partition 4 Partitions * 2 Replicas = 8 Fragments P1 Automatic Data Partitioning
Copyright © 2015 Oracle and/or its affiliates. All rights reserved. | Data Node 1 Data Node 2 F1 F3 Primary Fragment Secondary Fragment F3 F1 Data Node 3 Data Node 4Fx Fx Table T1 ID FirstName LastName Email Phone P2 P3 P4 Px Partition 4 Partitions * 2 Replicas = 8 Fragments P1 Automatic Data Partitioning
Copyright © 2015 Oracle and/or its affiliates. All rights reserved. | Data Node 1 Data Node 2 F1 F3 Primary Fragment Secondary Fragment F3 F1 Data Node 3 Data Node 4 F2 Fx Fx Table T1 ID FirstName LastName Email Phone P2 P3 P4 Px Partition 4 Partitions * 2 Replicas = 8 Fragments P1 Automatic Data Partitioning
Copyright © 2015 Oracle and/or its affiliates. All rights reserved. | Data Node 1 Data Node 2 F1 F3 Primary Fragment Secondary Fragment F3 F1 Data Node 3 Data Node 4 F2 F2 Fx Fx Table T1 ID FirstName LastName Email Phone P2 P3 P4 Px Partition 4 Partitions * 2 Replicas = 8 Fragments P1 Automatic Data Partitioning
Copyright © 2015 Oracle and/or its affiliates. All rights reserved. | Data Node 1 Data Node 2 F1 F3 Primary Fragment Secondary Fragment F3 F1 Data Node 3 Data Node 4 F2 F4 F2 4 Partitions * 2 Replicas = 8 Fragments Fx Fx Table T1 ID FirstName LastName Email Phone P2 P3 P4 Px Partition P1 Automatic Data Partitioning
Copyright © 2015 Oracle and/or its affiliates. All rights reserved. | Data Node 1 Data Node 2 F1 F3 Primary Fragment Secondary Fragment F3 F1 Data Node 3 Data Node 4 F2 F4 F4 F2 Fx Fx Table T1 ID FirstName LastName Email Phone P2 P3 P4 Px Partition 4 Partitions * 2 Replicas = 8 Fragments P1 Automatic Data Partitioning
Copyright © 2015 Oracle and/or its affiliates. All rights reserved. | Data Node 1 Data Node 2 F3 Primary Fragment Secondary Fragment F1 Data Node 3 Data Node 4 F2 F4 F4 F2 Node Group 1 Fx Fx Table T1 ID FirstName LastName Email Phone P2 P3 P4 Px Partition 4 Partitions * 2 Replicas = 8 Fragments P1 F1 F3 Automatic Data Partitioning
Copyright © 2015 Oracle and/or its affiliates. All rights reserved. | Data Node 1 Data Node 2 F1 F3 Primary Fragment Secondary Fragment F3 F1 Data Node 3 Data Node 4 F2 F4 F4 F2 Node Group 1 Node Group 2 Fx Fx - Node groups are created automatically - # of groups = # of data nodes / # of replicas Table T1 ID FirstName LastName Email Phone P2 P3 P4 Px Partition 4 Partitions * 2 Replicas = 8 Fragments P1 Automatic Data Partitioning
Copyright © 2015 Oracle and/or its affiliates. All rights reserved. | Data Node 1 Data Node 2 F1 F3 Primary Fragment Secondary Fragment F3 F1 Data Node 3 Data Node 4 F2 F4 F4 F2 Node Group 1 Node Group 2 Fx Fx As long as one data node in each node group is running we have a complete copy of the data Table T1 ID FirstName LastName Email Phone P2 P3 P4 Px Partition 4 Partitions * 2 Replicas = 8 Fragments P1 Automatic Data Partitioning
Copyright © 2015 Oracle and/or its affiliates. All rights reserved. | Data Node 1 Data Node 2 F1 F3 Primary Fragment Secondary Fragment F3 F1 Data Node 3 Data Node 4 F2 F4 F4 F2 Node Group 1 Node Group 2 Fx Fx Table T1 ID FirstName LastName Email Phone P2 P3 P4 Px Partition 4 Partitions * 2 Replicas = 8 Fragments P1 Automatic Data Partitioning As long as one data node in each node group is running we have a complete copy of the data
Copyright © 2015 Oracle and/or its affiliates. All rights reserved. | Data Node 1 Data Node 2 F1 F3 Primary Fragment Secondary Fragment F3 F1 Data Node 3 Data Node 4 F2 F4 F4 F2 Node Group 1 Node Group 2 Fx Fx Table T1 ID FirstName LastName Email Phone P2 P3 P4 Px Partition 4 Partitions * 2 Replicas = 8 Fragments P1 Automatic Data Partitioning As long as one data node in each node group is running we have a complete copy of the data
Copyright © 2015 Oracle and/or its affiliates. All rights reserved. | Table T1 Data Node 1 Data Node 2 F1 F3 Primary Fragment Secondary Fragment ID FirstName LastName Email Phone P2 P3 P4 Px Partition F3 F1 Data Node 3 Data Node 4 F2 F4 F4 F2 Node Group 1 Node Group 2 4 Partitions * 2 Replicas = 8 Fragments Fx Fx - No complete copy of the data - Cluster shutdowns automatically P1 Automatic Data Partitioning
Copyright © 2015 Oracle and/or its affiliates. All rights reserved. | Data Partitioning III • Partition – Horizontal partitioning – A portion of a table, each partition contains a set of rows – Number of partitions == LQH • Replica – A complete copy of the data • Node Group – Created automatically – # of groups = # of data nodes / # of replicas – As long as there is one data node in each node group we have a complete copy of the data
Copyright © 2015 Oracle and/or its affiliates. All rights reserved. | Internal Replication “2-Phase Commit” 39
Copyright © 2015 Oracle and/or its affiliates. All rights reserved. | Simplistic view of two Data Nodes Internal Replication “2-Phase Commit”
Copyright © 2015 Oracle and/or its affiliates. All rights reserved. | Prepare Phase insert into T1 values (...) 1. Calc hash on PK 1 Internal Replication “2-Phase Commit”
Copyright © 2015 Oracle and/or its affiliates. All rights reserved. | Prepare Phase insert into T1 values (...) 1. Calc hash on PK 2. Forward request to LQH where primary fragment is 2 1 Internal Replication “2-Phase Commit“
Copyright © 2015 Oracle and/or its affiliates. All rights reserved. | Prepare Phase insert into T1 values (...) 1. Calc hash on PK 2. Forward request to LQH where primary fragment is 2 1 Internal Replication “2-Phase Commit”
Copyright © 2015 Oracle and/or its affiliates. All rights reserved. | Prepare Phase insert into T1 values (...) 1. Calc hash on PK 2. Forward request to LQH where primary fragment is 3. Prepare secondary fragment 2 1 3 Internal Replication “2-Phase Commit“
Copyright © 2015 Oracle and/or its affiliates. All rights reserved. | Prepare Phase insert into T1 values (...) 1. Calc hash on PK 2. Forward request to LQH where primary fragment is 3. Prepare secondary fragment 2 1 3 Internal Replication “2-Phase Commit”
Copyright © 2015 Oracle and/or its affiliates. All rights reserved. | Prepare Phase insert into T1 values (...) 1. Calc hash on PK 2. Forward request to LQH where primary fragment is 3. Prepare secondary fragment 4. Prepare phase done 2 1 3 4 Internal Replication “2-Phase Commit”
Copyright © 2015 Oracle and/or its affiliates. All rights reserved. | Commit Phase insert into T1 values (...) 1 Internal Replication “2-Phase Commit”
Copyright © 2015 Oracle and/or its affiliates. All rights reserved. | Commit Phase insert into T1 values (...) 2 1 Internal Replication “2-Phase Commit”
Copyright © 2015 Oracle and/or its affiliates. All rights reserved. | Commit Phase insert into T1 values (...) 3 2 1 Internal Replication “2-Phase Commit”
Copyright © 2015 Oracle and/or its affiliates. All rights reserved. | Commit Phase insert into T1 values (...) 3 4 2 1 Internal Replication “2-Phase Commit”
Copyright © 2015 Oracle and/or its affiliates. All rights reserved. | Accessing data • Four operation types, each accessing a single table or index: – Primary key operation. Hash key to determine node and 'bucket' in node. O(1) in rows and nodes. Batching gives intra-query parallelism. – Unique key operation. Two primary key operations back to back. O(1) in rows and nodes – Ordered index scan operation. In-memory tree traversal on one or all table fragments. Fragments can be scanned in parallel. O(log N) in rows, O(n) in nodes, unless pruned. – Table scan operation. In memory hash/page traversal on all table fragments. Fragments can be scanned in parallel. O(n) in rows, O(n) in nodes.
Copyright © 2015 Oracle and/or its affiliates. All rights reserved. | D1 D2 D3 D4 API ---------- ---------- ---------- TC TC TC TC 1 2 3 Accessing data: PK key lookup • You will have the same TC during all STMTS building up an transaction so after initial STMT the “distribution awareness” is gone. • First Statement decides TC • Keep transactions short!
Copyright © 2015 Oracle and/or its affiliates. All rights reserved. | D1 D2 D3 D4 API ---------- ---------- ---------- TC TC TC TC 1 2 3 Accessing data: Unique key lookup • Secondary keys implemented as hidden/system tables. • Hidden tables have new secondary key as PK and basetables PK as value. • Data may reside on same node or other node.
Copyright © 2015 Oracle and/or its affiliates. All rights reserved. | D1 D2 D3 D4 API ---------- ---------- ---------- TC TC TC TC 1 3 2 Accessing data: Table scan • TC is chosen using RR • Data nodes send data directly to API • Flow: – Choose data node – Send request to all LDM – Send data to API
Copyright © 2015 Oracle and/or its affiliates. All rights reserved. | Checkpoints and Logging Global • Global Checkpoint Protocol/Group Commit - GCP – REDO log, synchronized between the Data Nodes. – Writes transactions that have been recorded in the REDO log buffer to disk/REDO log – Frequency controlled by TimebetweenGlobalCheckpoints setting • Default is 2000ms – Size of the REDO log set by NumOfFragmentLogFiles 55
Copyright © 2015 Oracle and/or its affiliates. All rights reserved. | Checkpoints and Logging Local • Local Checkpoint Protocol - LCP – Flushes the Data Nodes’ data to disk. After 2 LCP the REDO log is cut – Frequency controlled by TimebetweenLocalCheckpoints setting • Specifies the amount of data that can change before flushing to disk • Not a time! Base-2 logarithm of the number of 4-byte words • Ex: Default value of 20 means 4*2^20 = 4MB of data changes, 56
Copyright © 2015 Oracle and/or its affiliates. All rights reserved. | Checkpoints and Logging Local & Redo • LCP and REDO Log are used to bring back the cluster online – System failure or planned shutdown – 1st Data Nodes are restored using the latest LCP – 2nd the REDO logs are applied until the latest GCP 57
Copyright © 2015 Oracle and/or its affiliates. All rights reserved. | Data Node 1 Data Node 2 Data Node 3 Data Node 4 ― Date Nodes are organized in a logical circle ― Heartbeat messages are sent to the next Data Node in the circle Failure Detection • Node Failure – Heartbeat • Each Node is responsible for performing periodic heartbeat checks of other nodes – Requests/Response – Node makes request and the response serves as an indicator, i.e., heartbeat • Failed heartbeat/response – The Node detecting the failed Node reports the failure to the rest of the cluster
Copyright © 2015 Oracle and/or its affiliates. All rights reserved. | Data Node A Data Node B MGM Node Arbitration I • What will happen: – NoOfReplicas==2? – NoOfReplicas==1?
Copyright © 2015 Oracle and/or its affiliates. All rights reserved. | Data Node A Data Node D MGM Node Data center I Data Node B Data Node C MGM Node Data center II Node group 1 Node group 2 Arbitration II • What will happen: – Which side will survive? – And why?
Copyright © 2015 Oracle and/or its affiliates. All rights reserved. | Data Node A Data Node D MGM Node Data center I Data Node B Data Node C MGM Node Data center II Node group 1 Node group 2 Arbitration II • What will happen: – New cluster with 3 nodes will continue!
Copyright © 2015 Oracle and/or its affiliates. All rights reserved. | Data Node A Data Node D MGM Node Data center I Data Node B Data Node C MGM Node Data center II Node group 1 Node group 2 Arbitration III • What will happen: – Which side will survive? – And why?
Copyright © 2015 Oracle and/or its affiliates. All rights reserved. | One or more data Nodes fails … Yes Yes Yes NoNo No Do we have data from each NG Do we have one full node group Survive Arbitration Shutdown Won Arbitration Arbitration flow chart 1. Check whether a data node from each node group is present. If that is not the case, the data nodes will have to shutdown. 2. Are all data nodes from one of the node groups present? If so it is guaranteed that this fragment is the only one that can survive. If no, continue to 3. 3. Contact the arbitrator. 4. If arbitration was won, continue. Otherwise shutdown.
Copyright © 2015 Oracle and/or its affiliates. All rights reserved. | Questions?
Introduction to MySQL Cluster

More Related Content

ODP
MySQL Cluster
byAbel Flórez
 
PPT
MySQL Cluster Basics
byWagner Bianchi
 
PDF
MySQL Cluster
byMario Beck
 
PDF
MySQL 5.7 - What's new, How to upgrade and Document Store
byAbel Flórez
 
PDF
Mysql tech day_paris_ps_and_sys
byMark Leith
 
PDF
MySQL 5.7: What's New, Nov. 2015
byMario Beck
 
ODP
MySQL Enterprise Portfolio
byAbel Flórez
 
PDF
20141011 my sql clusterv01pptx
byIvan Ma
 
MySQL Cluster
byAbel Flórez
 
MySQL Cluster Basics
byWagner Bianchi
 
MySQL Cluster
byMario Beck
 
MySQL 5.7 - What's new, How to upgrade and Document Store
byAbel Flórez
 
Mysql tech day_paris_ps_and_sys
byMark Leith
 
MySQL 5.7: What's New, Nov. 2015
byMario Beck
 
MySQL Enterprise Portfolio
byAbel Flórez
 
20141011 my sql clusterv01pptx
byIvan Ma
 

What's hot

PDF
MySQL 5.7: Focus on Replication
byMario Beck
 
PDF
My sql 5.6&MySQL Cluster 7.3
byOleksii(Alexey) Porytskyi
 
PDF
NoSQL no MySQL 5.7
byMySQL Brasil
 
PDF
MySQL Cluster overview + development slides (2014)
byFrazer Clement
 
PDF
Expert performance tuning tips for Oracle RAC
bySolarWinds
 
PPT
OSSCube MySQL Cluster Tutorial By Sonali At Osspac 09
byOSSCube
 
PDF
Oracle Database In-Memory Meets Oracle RAC
byMarkus Michalewicz
 
PDF
MySQL Fabric: Easy Management of MySQL Servers
byMats Kindahl
 
PDF
MySQL Security
byMario Beck
 
PDF
MySQL 5.7: Focus on InnoDB
byMario Beck
 
PDF
Cluster Health Advisor (CHA) Deep Dive by Mark Scardina
byMarkus Michalewicz
 
PDF
Oracle RAC BP for Upgrade & More by Anil Nair and Markus Michalewicz
byMarkus Michalewicz
 
PDF
MySQL 5.7 Replication News
byTed Wennmark
 
PDF
Oracle RAC 11g Release 2 Client Connections
byMarkus Michalewicz
 
PDF
Oracle RAC 12c Collaborate Best Practices - IOUG 2014 version
byMarkus Michalewicz
 
PDF
Oracle RAC 12c Rel. 2 Under the Hood and Best Practices
byMarkus Michalewicz
 
PDF
Understanding oracle rac internals part 1 - slides
byMohamed Farouk
 
PDF
Oracle Fleet Patching and Provisioning Deep Dive Webcast Slides
byLudovico Caldara
 
PPTX
MySQL High Availibility Solutions
byMark Swarbrick
 
PPTX
2015: Whats New in MySQL 5.7, At Oracle Open World, November 3rd, 2015
byGeir Høydalsvik
 
MySQL 5.7: Focus on Replication
byMario Beck
 
My sql 5.6&MySQL Cluster 7.3
byOleksii(Alexey) Porytskyi
 
NoSQL no MySQL 5.7
byMySQL Brasil
 
MySQL Cluster overview + development slides (2014)
byFrazer Clement
 
Expert performance tuning tips for Oracle RAC
bySolarWinds
 
OSSCube MySQL Cluster Tutorial By Sonali At Osspac 09
byOSSCube
 
Oracle Database In-Memory Meets Oracle RAC
byMarkus Michalewicz
 
MySQL Fabric: Easy Management of MySQL Servers
byMats Kindahl
 
MySQL Security
byMario Beck
 
MySQL 5.7: Focus on InnoDB
byMario Beck
 
Cluster Health Advisor (CHA) Deep Dive by Mark Scardina
byMarkus Michalewicz
 
Oracle RAC BP for Upgrade & More by Anil Nair and Markus Michalewicz
byMarkus Michalewicz
 
MySQL 5.7 Replication News
byTed Wennmark
 
Oracle RAC 11g Release 2 Client Connections
byMarkus Michalewicz
 
Oracle RAC 12c Collaborate Best Practices - IOUG 2014 version
byMarkus Michalewicz
 
Oracle RAC 12c Rel. 2 Under the Hood and Best Practices
byMarkus Michalewicz
 
Understanding oracle rac internals part 1 - slides
byMohamed Farouk
 
Oracle Fleet Patching and Provisioning Deep Dive Webcast Slides
byLudovico Caldara
 
MySQL High Availibility Solutions
byMark Swarbrick
 
2015: Whats New in MySQL 5.7, At Oracle Open World, November 3rd, 2015
byGeir Høydalsvik
 

Similar to Introduction to MySQL Cluster

PPTX
What's new in MySQL Cluster 7.4 webinar charts
byAndrew Morgan
 
PDF
Mysql NDB Cluster's Asynchronous Parallel Design for High Performance
byBernd Ocklin
 
ODP
MySQL HA
byKris Buytaert
 
PDF
200 million qps on commodity hardware : Getting started with MySQL Cluster 7.4
byFrazer Clement
 
PDF
Exploring mysql cluster 7.4
byIvan Ma
 
PDF
MySQL InnoDB Cluster - A complete High Availability solution for MySQL
byOlivier DASINI
 
PDF
Ramp-Tutorial for MYSQL Cluster - Scaling with Continuous Availability
byPythian
 
PDF
NoSQL and MySQL
byTed Wennmark
 
PPT
2010 12 mysql_clusteroverview
byDimas Prasetyo
 
PDF
MySQL NDB Cluster 101
byBernd Ocklin
 
PPTX
OUG Scotland 2014 - NoSQL and MySQL - The best of both worlds
byAndrew Morgan
 
PDF
MySQL 5.7 InnoDB Cluster (Jan 2018)
byOlivier DASINI
 
PPT
MYSQL
bygilashikwa
 
ODP
Barcamp MySQL
byKris Buytaert
 
PDF
Breakthrough performance with MySQL Cluster (2012)
byFrazer Clement
 
PDF
Introduction to MySQL
byTed Wennmark
 
PDF
MySQL Cluster Scaling to a Billion Queries
byBernd Ocklin
 
PDF
MySQL NDB Cluster 8.0
byTed Wennmark
 
PDF
2_MySQL_Cluster_Introduction.pdf
byHaiping Li
 
PDF
MySQL Day Paris 2016 - MySQL HA: InnoDB Cluster and NDB Cluster
byOlivier DASINI
 
What's new in MySQL Cluster 7.4 webinar charts
byAndrew Morgan
 
Mysql NDB Cluster's Asynchronous Parallel Design for High Performance
byBernd Ocklin
 
MySQL HA
byKris Buytaert
 
200 million qps on commodity hardware : Getting started with MySQL Cluster 7.4
byFrazer Clement
 
Exploring mysql cluster 7.4
byIvan Ma
 
MySQL InnoDB Cluster - A complete High Availability solution for MySQL
byOlivier DASINI
 
Ramp-Tutorial for MYSQL Cluster - Scaling with Continuous Availability
byPythian
 
NoSQL and MySQL
byTed Wennmark
 
2010 12 mysql_clusteroverview
byDimas Prasetyo
 
MySQL NDB Cluster 101
byBernd Ocklin
 
OUG Scotland 2014 - NoSQL and MySQL - The best of both worlds
byAndrew Morgan
 
MySQL 5.7 InnoDB Cluster (Jan 2018)
byOlivier DASINI
 
MYSQL
bygilashikwa
 
Barcamp MySQL
byKris Buytaert
 
Breakthrough performance with MySQL Cluster (2012)
byFrazer Clement
 
Introduction to MySQL
byTed Wennmark
 
MySQL Cluster Scaling to a Billion Queries
byBernd Ocklin
 
MySQL NDB Cluster 8.0
byTed Wennmark
 
2_MySQL_Cluster_Introduction.pdf
byHaiping Li
 
MySQL Day Paris 2016 - MySQL HA: InnoDB Cluster and NDB Cluster
byOlivier DASINI
 

Recently uploaded

PPTX
Empower Your Business with Busy Software from MovingCloud360.pptx
byMoving Cloud
 
PDF
Transform Salesforce Workflows: The Basics You Must Know to Deploy AI Agents
byCymetrix Software
 
DOCX
A A Comprehensive Guide on Unicode Text Converter
byJosh Bell
 
PDF
Workshop about mORMot 2 during EKON: write a clean-architecture TDD sample
byArnaud Bouchez
 
PDF
Do customers need Windows Server Device CALs for devices if they have User CA...
byQ-Advise
 
PDF
[GBGCPP] MISRA C++: Safer C++17 for critical systems
byDimitris Platis
 
PDF
End-to-End Payroll Processing Checklist for Growing Businesses.pdf
byCRMLeaf
 
PPTX
ETH Belgrade 2025 - AI Agent Custody and Fund Access
byDejan Radic
 
PDF
Improving Customer Experience with Einstein AI and Salesforce Data Cloud
byMinuscule Technologies
 
PPTX
Soft(ware) Stories: Tecnologia, Identità e Giustizia di genere nel mondo digi...
byLetizia Jaccheri
 
PPTX
Foundations of Marketo Engage - Professional Exam Preparation - October 2025
bybbedford2
 
PDF
ICTlecture1 for 1st Semester Students of BS(CS)
bySirRafiLectures
 
PDF
Claims Software Features That Improve Accuracy & Efficiency
byDataGenix
 
PDF
One HTTPS server in Modern Pascal to Rule Them All
byArnaud Bouchez
 
PDF
Insurance Underwriting Software for Smarter Operations
byInsurance Tech Services
 
PPTX
Trailblazer Community Session - Patterns & Anti-Patterns_ The Good, The Bad, ...
bymansoorahmad941764
 
DOCX
Digital Dominance_ Why You Need a Top Web Design Agency in Dubai.docx
bydigpal3
 
PDF
Top Tech Stacks for Developing Scalable Social Networking Apps.pdf
byOlivia Rose
 
PDF
Test Driven Development with mORMot 2 with Delphi and FPC
byArnaud Bouchez
 
PDF
Dr. Robert Krug - A Data Science Veteran
byDr. Robert Krug
 
Empower Your Business with Busy Software from MovingCloud360.pptx
byMoving Cloud
 
Transform Salesforce Workflows: The Basics You Must Know to Deploy AI Agents
byCymetrix Software
 
A A Comprehensive Guide on Unicode Text Converter
byJosh Bell
 
Workshop about mORMot 2 during EKON: write a clean-architecture TDD sample
byArnaud Bouchez
 
Do customers need Windows Server Device CALs for devices if they have User CA...
byQ-Advise
 
[GBGCPP] MISRA C++: Safer C++17 for critical systems
byDimitris Platis
 
End-to-End Payroll Processing Checklist for Growing Businesses.pdf
byCRMLeaf
 
ETH Belgrade 2025 - AI Agent Custody and Fund Access
byDejan Radic
 
Improving Customer Experience with Einstein AI and Salesforce Data Cloud
byMinuscule Technologies
 
Soft(ware) Stories: Tecnologia, Identità e Giustizia di genere nel mondo digi...
byLetizia Jaccheri
 
Foundations of Marketo Engage - Professional Exam Preparation - October 2025
bybbedford2
 
ICTlecture1 for 1st Semester Students of BS(CS)
bySirRafiLectures
 
Claims Software Features That Improve Accuracy & Efficiency
byDataGenix
 
One HTTPS server in Modern Pascal to Rule Them All
byArnaud Bouchez
 
Insurance Underwriting Software for Smarter Operations
byInsurance Tech Services
 
Trailblazer Community Session - Patterns & Anti-Patterns_ The Good, The Bad, ...
bymansoorahmad941764
 
Digital Dominance_ Why You Need a Top Web Design Agency in Dubai.docx
bydigpal3
 
Top Tech Stacks for Developing Scalable Social Networking Apps.pdf
byOlivia Rose
 
Test Driven Development with mORMot 2 with Delphi and FPC
byArnaud Bouchez
 
Dr. Robert Krug - A Data Science Veteran
byDr. Robert Krug
 

Introduction to MySQL Cluster

  • 2.
    Copyright © 2015Oracle and/or its affiliates. All rights reserved. | Introduction to MySQL Cluster Abel Flórez Technical Account Manager 2015
  • 3.
    Copyright © 2015,Oracle and/or its affiliates. All rights reserved. | Safe Harbor Statement The following is intended to outline our general product direction. It is intended for information purposes only, and may not be incorporated into any contract. It is not a commitment to deliver any material, code, or functionality, and should not be relied upon in making purchasing decisions. The development, release, and timing of any features or functionality described for Oracle’s products remains at the sole discretion of Oracle. 3
  • 4.
    Copyright © 2015Oracle and/or its affiliates. All rights reserved. | History of MySQL Cluster ”NDB” • MySQL Cluster aka Network DataBase NDB • Designed/Developed at Ericsson in late 90’s • Original design paper: ”Design and Modeling of a Parallel Data Server for Telecom Applications” from 1997 by Mikael Ronström • Originally written in PLEX (Programming Language for EXchanges) but later converted to C++. • MySQL AB acquired Alzato (owned by Ericsson) late 2003. The Network DataBase NDB
  • 5.
    Copyright © 2015Oracle and/or its affiliates. All rights reserved. | History of MySQL Cluster ”NDB” • Databases services back then: – SCP/SDP (Service Control/Data Point) in Intelligent Networks. – HLR (Home Location Register) for keeping track of mobile phones/users. – Databases for network management especially real-time charging information. The Network DataBase NDB
  • 6.
    Copyright © 2015Oracle and/or its affiliates. All rights reserved. | History of MySQL Cluster ”NDB” • NDB was designed to: – Reliability, the availability class of the telecom databases should be 6 (99.9999%). This means that downtime must be less than 30 seconds per year: no planned down time of the system is allowed. – Performance, designed for high throughput, linear scalabillity when adding more servers (data nodes) for simple access patterns (PK lookups). – Real-time, data is kept in memory and system is designed for memory operations. The Network DataBase NDB
  • 7.
    Copyright © 2015Oracle and/or its affiliates. All rights reserved. | When to consider MySQL Cluster • What are the consequences of downtime or failing to meet performance requirements? • How much effort and $ is spent in developing and managing HA in your applications? • Are you considering sharding your database to scale write performance? How does that impact your application and developers? • Do your services need to be real-time? • Will your services have unpredictable scalability demands, especially for writes? • Do you want the flexibility to manage your data with more than just SQL? 7
  • 8.
    Copyright © 2015Oracle and/or its affiliates. All rights reserved. | When NOT to consider MySQL Cluster • Most 3rd party applications • Long running transactions • Geospatial indexes • Huge dataset (>2TB) • Complex access pattern to data and many full table scans • When you need a disk based database like InnoDB 8
  • 9.
    Copyright © 2015Oracle and/or its affiliates. All rights reserved. | Oracle MySQL HA & Scaling Solutions MySQL Replication MySQL Fabric Oracle VM Template Oracle Clusterware Solaris Cluster Windows Cluster DRBD MySQL Cluster App Auto-Failover ✖ ✔ ✔ ✔ ✔ ✔ ✔ ✔ Data Layer Auto-Failover ✖ ✔ ✔ ✔ ✔ ✔ ✔ ✔ Zero Data Loss MySQL 5.7 MySQL 5.7 ✔ ✔ ✔ ✔ ✔ ✔ Platform Support All All Linux Linux Solaris Windows Linux All Clustering Mode Master + Slaves Master + Slaves Active/ Passive Active/ Passive Active/ Passive Active/ Passive Active/ Passive Multi- Master Failover Time N/A Secs Secs + Secs + Secs + Secs + Secs + < 1 Sec Scale-out Reads ✔ ✖ ✖ ✖ ✖ ✖ ✔ Cross-shard operations N/A ✖ N/A N/A N/A N/A N/A ✔ Transparent routing ✖ For HA ✔ ✔ ✔ ✔ ✔ ✔ Shared Nothing ✔ ✔ ✖ ✖ ✖ ✖ ✔ ✔ Storage Engine InnoDB+ InnoDB+ InnoDB+ InnoDB+ InnoDB+ InnoDB+ InnoDB+ NDB Single Vendor Support ✔ ✔ ✔ ✔ ✔ ✖ ✔ ✔
  • 10.
    Copyright © 2015Oracle and/or its affiliates. All rights reserved. | MySQL Cluster overview
  • 11.
    Copyright © 2015Oracle and/or its affiliates. All rights reserved. | MySQL Cluster Components 11 NDB API (Applications) Data Node (Data Storage) MGM Node (Management) SQL Node (Applications) • Standard SQL interface • Scale out for performance • Enables Geo Replication • Real-time applications • C++/Java APIs • Automatic failover & load balancing • Data storage (Memory & Disk) • Automatic & User defined data partitioning • Scale out for capacity and performance • Management, Monitoring & Configuration • Arbitrator for split brain/network partitioning • Cluster logs
  • 12.
    Copyright © 2015Oracle and/or its affiliates. All rights reserved. | Data Nodes • Stores data and indexes – In memory – Non-indexed data possible on disk – Contains several blocks, most important, LQH, TUP, ACC and TC. • Data check pointed to disk “LCP” • Transaction coordination • Handling fail-over • Doing online backup • All connect to each other • Up to 48 – Typically 2, 4.
  • 13.
    Copyright © 2015Oracle and/or its affiliates. All rights reserved. | Management Nodes • Distributing configuration • Logging • Monitoring • Act as Arbitrator – Prevents split-brain • OK when not running – Need to start others • 1 is minimum, 3 too many, 2 is OK
  • 14.
    Copyright © 2015Oracle and/or its affiliates. All rights reserved. | API Nodes • Applications written using NDB API – C / C++ / Java • Fast – No SQL parsing • Examples: – NDBCluster storage engine – ndb_restore
  • 15.
    Copyright © 2015Oracle and/or its affiliates. All rights reserved. | SQL Nodes • MySQL using NDBCluster engine – Is also an API Node • Transparent for most applications • Used to create tables • Used for Geographical Replication – Binary logging all changes • Can act as Arbitrator • Connects to all Data Nodes
  • 16.
    Copyright © 2015Oracle and/or its affiliates. All rights reserved. | MySQL Cluster Architecture MySQL Cluster Data Nodes Clients Application Layer Data Layer Management
  • 17.
    Copyright © 2015Oracle and/or its affiliates. All rights reserved. | MySQL Cluster Scaling MySQL Cluster Data Nodes Clients Application Layer Data Layer Management
  • 18.
    Copyright © 2015Oracle and/or its affiliates. All rights reserved. | MySQL Cluster - Extreme Resilience MySQL Cluster Data Nodes Clients Application Layer Data Layer Management
  • 19.
    Copyright © 2015Oracle and/or its affiliates. All rights reserved. | Partitioning I • Vertical Partitioning - 1:1 tables to reduce the size of rows, tables and indexes • Horizontal Partitioning - 1 table split on multiple tables with different rows
  • 20.
    Copyright © 2015Oracle and/or its affiliates. All rights reserved. | p1 p2 p1 p2 p3 Data Partitioning II • Data is partitioned on primary key per default • HASH value of PK, only selective if you provide full PK not “left most” • Linear hashing, data is only moved away (low impact of reorganize)
  • 21.
    Copyright © 2015Oracle and/or its affiliates. All rights reserved. | Data Node 1 Data Node 2 Data Node 3 Data Node 4- A partition is a portion of a table - Number of partitions = number of data nodes - Horizontal partitioning Table T1 ID FirstName LastName Email Phone P2 P3 P4 Px Partition P1 Automatic Data Partitioning
  • 22.
    Copyright © 2015Oracle and/or its affiliates. All rights reserved. | Data Node 1 Data Node 2 Data Node 3 Data Node 4A fragment is a partition Number of fragments = # of partitions * # of replicas Table T1 ID FirstName LastName Email Phone P2 P3 P4 Px Partition P1 Automatic Data Partitioning
  • 23.
    Copyright © 2015Oracle and/or its affiliates. All rights reserved. | Data Node 1 Data Node 2 Data Node 3 Data Node 4A fragment can be primary or secondary/backup Number of fragments = # of partitions * # of replicas Table T1 ID FirstName LastName Email Phone P2 P3 P4 Px Partition 4 Partitions * 2 Replicas = 8 Fragments P1 Automatic Data Partitioning
  • 24.
    Copyright © 2015Oracle and/or its affiliates. All rights reserved. | Data Node 1 Data Node 2 F1 Primary Fragment Secondary Fragment Data Node 3 Data Node 4Fx Fx Table T1 ID FirstName LastName Email Phone P2 P3 P4 Px Partition 4 Partitions * 2 Replicas = 8 Fragments P1 Automatic Data Partitioning
  • 25.
    Copyright © 2015Oracle and/or its affiliates. All rights reserved. | Data Node 1 Data Node 2 F1 Primary Fragment Secondary Fragment F1 Data Node 3 Data Node 4Fx Fx Table T1 ID FirstName LastName Email Phone P2 P3 P4 Px Partition 4 Partitions * 2 Replicas = 8 Fragments P1 Automatic Data Partitioning
  • 26.
    Copyright © 2015Oracle and/or its affiliates. All rights reserved. | Data Node 1 Data Node 2 F1 Primary Fragment Secondary Fragment F3 F1 Data Node 3 Data Node 4Fx Fx Table T1 ID FirstName LastName Email Phone P2 P3 P4 Px Partition 4 Partitions * 2 Replicas = 8 Fragments P1 Automatic Data Partitioning
  • 27.
    Copyright © 2015Oracle and/or its affiliates. All rights reserved. | Data Node 1 Data Node 2 F1 F3 Primary Fragment Secondary Fragment F3 F1 Data Node 3 Data Node 4Fx Fx Table T1 ID FirstName LastName Email Phone P2 P3 P4 Px Partition 4 Partitions * 2 Replicas = 8 Fragments P1 Automatic Data Partitioning
  • 28.
    Copyright © 2015Oracle and/or its affiliates. All rights reserved. | Data Node 1 Data Node 2 F1 F3 Primary Fragment Secondary Fragment F3 F1 Data Node 3 Data Node 4 F2 Fx Fx Table T1 ID FirstName LastName Email Phone P2 P3 P4 Px Partition 4 Partitions * 2 Replicas = 8 Fragments P1 Automatic Data Partitioning
  • 29.
    Copyright © 2015Oracle and/or its affiliates. All rights reserved. | Data Node 1 Data Node 2 F1 F3 Primary Fragment Secondary Fragment F3 F1 Data Node 3 Data Node 4 F2 F2 Fx Fx Table T1 ID FirstName LastName Email Phone P2 P3 P4 Px Partition 4 Partitions * 2 Replicas = 8 Fragments P1 Automatic Data Partitioning
  • 30.
    Copyright © 2015Oracle and/or its affiliates. All rights reserved. | Data Node 1 Data Node 2 F1 F3 Primary Fragment Secondary Fragment F3 F1 Data Node 3 Data Node 4 F2 F4 F2 4 Partitions * 2 Replicas = 8 Fragments Fx Fx Table T1 ID FirstName LastName Email Phone P2 P3 P4 Px Partition P1 Automatic Data Partitioning
  • 31.
    Copyright © 2015Oracle and/or its affiliates. All rights reserved. | Data Node 1 Data Node 2 F1 F3 Primary Fragment Secondary Fragment F3 F1 Data Node 3 Data Node 4 F2 F4 F4 F2 Fx Fx Table T1 ID FirstName LastName Email Phone P2 P3 P4 Px Partition 4 Partitions * 2 Replicas = 8 Fragments P1 Automatic Data Partitioning
  • 32.
    Copyright © 2015Oracle and/or its affiliates. All rights reserved. | Data Node 1 Data Node 2 F3 Primary Fragment Secondary Fragment F1 Data Node 3 Data Node 4 F2 F4 F4 F2 Node Group 1 Fx Fx Table T1 ID FirstName LastName Email Phone P2 P3 P4 Px Partition 4 Partitions * 2 Replicas = 8 Fragments P1 F1 F3 Automatic Data Partitioning
  • 33.
    Copyright © 2015Oracle and/or its affiliates. All rights reserved. | Data Node 1 Data Node 2 F1 F3 Primary Fragment Secondary Fragment F3 F1 Data Node 3 Data Node 4 F2 F4 F4 F2 Node Group 1 Node Group 2 Fx Fx - Node groups are created automatically - # of groups = # of data nodes / # of replicas Table T1 ID FirstName LastName Email Phone P2 P3 P4 Px Partition 4 Partitions * 2 Replicas = 8 Fragments P1 Automatic Data Partitioning
  • 34.
    Copyright © 2015Oracle and/or its affiliates. All rights reserved. | Data Node 1 Data Node 2 F1 F3 Primary Fragment Secondary Fragment F3 F1 Data Node 3 Data Node 4 F2 F4 F4 F2 Node Group 1 Node Group 2 Fx Fx As long as one data node in each node group is running we have a complete copy of the data Table T1 ID FirstName LastName Email Phone P2 P3 P4 Px Partition 4 Partitions * 2 Replicas = 8 Fragments P1 Automatic Data Partitioning
  • 35.
    Copyright © 2015Oracle and/or its affiliates. All rights reserved. | Data Node 1 Data Node 2 F1 F3 Primary Fragment Secondary Fragment F3 F1 Data Node 3 Data Node 4 F2 F4 F4 F2 Node Group 1 Node Group 2 Fx Fx Table T1 ID FirstName LastName Email Phone P2 P3 P4 Px Partition 4 Partitions * 2 Replicas = 8 Fragments P1 Automatic Data Partitioning As long as one data node in each node group is running we have a complete copy of the data
  • 36.
    Copyright © 2015Oracle and/or its affiliates. All rights reserved. | Data Node 1 Data Node 2 F1 F3 Primary Fragment Secondary Fragment F3 F1 Data Node 3 Data Node 4 F2 F4 F4 F2 Node Group 1 Node Group 2 Fx Fx Table T1 ID FirstName LastName Email Phone P2 P3 P4 Px Partition 4 Partitions * 2 Replicas = 8 Fragments P1 Automatic Data Partitioning As long as one data node in each node group is running we have a complete copy of the data
  • 37.
    Copyright © 2015Oracle and/or its affiliates. All rights reserved. | Table T1 Data Node 1 Data Node 2 F1 F3 Primary Fragment Secondary Fragment ID FirstName LastName Email Phone P2 P3 P4 Px Partition F3 F1 Data Node 3 Data Node 4 F2 F4 F4 F2 Node Group 1 Node Group 2 4 Partitions * 2 Replicas = 8 Fragments Fx Fx - No complete copy of the data - Cluster shutdowns automatically P1 Automatic Data Partitioning
  • 38.
    Copyright © 2015Oracle and/or its affiliates. All rights reserved. | Data Partitioning III • Partition – Horizontal partitioning – A portion of a table, each partition contains a set of rows – Number of partitions == LQH • Replica – A complete copy of the data • Node Group – Created automatically – # of groups = # of data nodes / # of replicas – As long as there is one data node in each node group we have a complete copy of the data
  • 39.
    Copyright © 2015Oracle and/or its affiliates. All rights reserved. | Internal Replication “2-Phase Commit” 39
  • 40.
    Copyright © 2015Oracle and/or its affiliates. All rights reserved. | Simplistic view of two Data Nodes Internal Replication “2-Phase Commit”
  • 41.
    Copyright © 2015Oracle and/or its affiliates. All rights reserved. | Prepare Phase insert into T1 values (...) 1. Calc hash on PK 1 Internal Replication “2-Phase Commit”
  • 42.
    Copyright © 2015Oracle and/or its affiliates. All rights reserved. | Prepare Phase insert into T1 values (...) 1. Calc hash on PK 2. Forward request to LQH where primary fragment is 2 1 Internal Replication “2-Phase Commit“
  • 43.
    Copyright © 2015Oracle and/or its affiliates. All rights reserved. | Prepare Phase insert into T1 values (...) 1. Calc hash on PK 2. Forward request to LQH where primary fragment is 2 1 Internal Replication “2-Phase Commit”
  • 44.
    Copyright © 2015Oracle and/or its affiliates. All rights reserved. | Prepare Phase insert into T1 values (...) 1. Calc hash on PK 2. Forward request to LQH where primary fragment is 3. Prepare secondary fragment 2 1 3 Internal Replication “2-Phase Commit“
  • 45.
    Copyright © 2015Oracle and/or its affiliates. All rights reserved. | Prepare Phase insert into T1 values (...) 1. Calc hash on PK 2. Forward request to LQH where primary fragment is 3. Prepare secondary fragment 2 1 3 Internal Replication “2-Phase Commit”
  • 46.
    Copyright © 2015Oracle and/or its affiliates. All rights reserved. | Prepare Phase insert into T1 values (...) 1. Calc hash on PK 2. Forward request to LQH where primary fragment is 3. Prepare secondary fragment 4. Prepare phase done 2 1 3 4 Internal Replication “2-Phase Commit”
  • 47.
    Copyright © 2015Oracle and/or its affiliates. All rights reserved. | Commit Phase insert into T1 values (...) 1 Internal Replication “2-Phase Commit”
  • 48.
    Copyright © 2015Oracle and/or its affiliates. All rights reserved. | Commit Phase insert into T1 values (...) 2 1 Internal Replication “2-Phase Commit”
  • 49.
    Copyright © 2015Oracle and/or its affiliates. All rights reserved. | Commit Phase insert into T1 values (...) 3 2 1 Internal Replication “2-Phase Commit”
  • 50.
    Copyright © 2015Oracle and/or its affiliates. All rights reserved. | Commit Phase insert into T1 values (...) 3 4 2 1 Internal Replication “2-Phase Commit”
  • 51.
    Copyright © 2015Oracle and/or its affiliates. All rights reserved. | Accessing data • Four operation types, each accessing a single table or index: – Primary key operation. Hash key to determine node and 'bucket' in node. O(1) in rows and nodes. Batching gives intra-query parallelism. – Unique key operation. Two primary key operations back to back. O(1) in rows and nodes – Ordered index scan operation. In-memory tree traversal on one or all table fragments. Fragments can be scanned in parallel. O(log N) in rows, O(n) in nodes, unless pruned. – Table scan operation. In memory hash/page traversal on all table fragments. Fragments can be scanned in parallel. O(n) in rows, O(n) in nodes.
  • 52.
    Copyright © 2015Oracle and/or its affiliates. All rights reserved. | D1 D2 D3 D4 API ---------- ---------- ---------- TC TC TC TC 1 2 3 Accessing data: PK key lookup • You will have the same TC during all STMTS building up an transaction so after initial STMT the “distribution awareness” is gone. • First Statement decides TC • Keep transactions short!
  • 53.
    Copyright © 2015Oracle and/or its affiliates. All rights reserved. | D1 D2 D3 D4 API ---------- ---------- ---------- TC TC TC TC 1 2 3 Accessing data: Unique key lookup • Secondary keys implemented as hidden/system tables. • Hidden tables have new secondary key as PK and basetables PK as value. • Data may reside on same node or other node.
  • 54.
    Copyright © 2015Oracle and/or its affiliates. All rights reserved. | D1 D2 D3 D4 API ---------- ---------- ---------- TC TC TC TC 1 3 2 Accessing data: Table scan • TC is chosen using RR • Data nodes send data directly to API • Flow: – Choose data node – Send request to all LDM – Send data to API
  • 55.
    Copyright © 2015Oracle and/or its affiliates. All rights reserved. | Checkpoints and Logging Global • Global Checkpoint Protocol/Group Commit - GCP – REDO log, synchronized between the Data Nodes. – Writes transactions that have been recorded in the REDO log buffer to disk/REDO log – Frequency controlled by TimebetweenGlobalCheckpoints setting • Default is 2000ms – Size of the REDO log set by NumOfFragmentLogFiles 55
  • 56.
    Copyright © 2015Oracle and/or its affiliates. All rights reserved. | Checkpoints and Logging Local • Local Checkpoint Protocol - LCP – Flushes the Data Nodes’ data to disk. After 2 LCP the REDO log is cut – Frequency controlled by TimebetweenLocalCheckpoints setting • Specifies the amount of data that can change before flushing to disk • Not a time! Base-2 logarithm of the number of 4-byte words • Ex: Default value of 20 means 4*2^20 = 4MB of data changes, 56
  • 57.
    Copyright © 2015Oracle and/or its affiliates. All rights reserved. | Checkpoints and Logging Local & Redo • LCP and REDO Log are used to bring back the cluster online – System failure or planned shutdown – 1st Data Nodes are restored using the latest LCP – 2nd the REDO logs are applied until the latest GCP 57
  • 58.
    Copyright © 2015Oracle and/or its affiliates. All rights reserved. | Data Node 1 Data Node 2 Data Node 3 Data Node 4 ― Date Nodes are organized in a logical circle ― Heartbeat messages are sent to the next Data Node in the circle Failure Detection • Node Failure – Heartbeat • Each Node is responsible for performing periodic heartbeat checks of other nodes – Requests/Response – Node makes request and the response serves as an indicator, i.e., heartbeat • Failed heartbeat/response – The Node detecting the failed Node reports the failure to the rest of the cluster
  • 59.
    Copyright © 2015Oracle and/or its affiliates. All rights reserved. | Data Node A Data Node B MGM Node Arbitration I • What will happen: – NoOfReplicas==2? – NoOfReplicas==1?
  • 60.
    Copyright © 2015Oracle and/or its affiliates. All rights reserved. | Data Node A Data Node D MGM Node Data center I Data Node B Data Node C MGM Node Data center II Node group 1 Node group 2 Arbitration II • What will happen: – Which side will survive? – And why?
  • 61.
    Copyright © 2015Oracle and/or its affiliates. All rights reserved. | Data Node A Data Node D MGM Node Data center I Data Node B Data Node C MGM Node Data center II Node group 1 Node group 2 Arbitration II • What will happen: – New cluster with 3 nodes will continue!
  • 62.
    Copyright © 2015Oracle and/or its affiliates. All rights reserved. | Data Node A Data Node D MGM Node Data center I Data Node B Data Node C MGM Node Data center II Node group 1 Node group 2 Arbitration III • What will happen: – Which side will survive? – And why?
  • 63.
    Copyright © 2015Oracle and/or its affiliates. All rights reserved. | One or more data Nodes fails … Yes Yes Yes NoNo No Do we have data from each NG Do we have one full node group Survive Arbitration Shutdown Won Arbitration Arbitration flow chart 1. Check whether a data node from each node group is present. If that is not the case, the data nodes will have to shutdown. 2. Are all data nodes from one of the node groups present? If so it is guaranteed that this fragment is the only one that can survive. If no, continue to 3. 3. Contact the arbitrator. 4. If arbitration was won, continue. Otherwise shutdown.
  • 64.
    Copyright © 2015Oracle and/or its affiliates. All rights reserved. | Questions?