MySQL Cluster

Presentation by

Krishna

What is MySQL Cluster ?

Just like Hadoop, it is a technology/framework for distributed databases.

ACID compliant.

Horizontal scaling of (MySQL server + storage engine).

In a nut shell it provides clustering for the MySQL DBMS.

MySQL Cluster

Design principles

High write scalability- auto sharding.

99.99 % availability- Shared nothing design.

Real-Time responsiveness- in memory data base system.

Low TCO and Open platform.

MySQL Cluster

Implementation

Implemented through NDB (Network database) storage Engine.

NDB cluster- against shared storage.

Nodes in MySQL Cluster are categorized into three categories namely:

1. Data Nodes

2. SQL Nodes

3. Management Nodes

MySQL Cluster

Architecture

Image source MySQL Cluster

Data Node

Implemented as a ndb or ndbmtd in the NDB cluster.

Primary function is to process and retrieve information.

Monitoring other nodes in the cluster and notifying the Management node.

Perform recovery on restart.

MySQL Cluster

SQL Node

Runs as the mysqld program, provides application access to the data nodes by receiving all interaction requests as queries.

Interaction layer between MySQL clients and the data nodes.

Typically , SQL node is a MySQL server which uses the NDB cluster storage engine.

MySQL Cluster

Management Node

Manages all other nodes in the Cluster.

Performs functions such as providing configuration details, starting and stopping the nodes, running back up etc..

Key point - a node of this type should be started first, before any other node.

MySQL Cluster

Cluster Configuration

Information regarding the number of nodes (processes), hosts , and their properties is necessary to set up the cluster.

Local configuration file- residing on each data/API node.

Global configuration file- the central one residing on one or more management nodes.

MySQL Cluster

Configuration files

## config.ini#

[NDB_MGMD DEFAULT]Portnumber=1186

[NDB_MGMD]NodeId=49HostName=10.30.12.23DataDir=C:/Users/kummadisingu/MySQL_Cluster/49/Portnumber=1186

[NDBD]NodeId=1HostName=10.30.12.28DataDir=C:/Users/kummadisingu/MySQL_Cluster/1/

[MYSQLD]NodeId=54HostName=10.30.12.30

# my.cnf# [mysqld]log-error=mysqld.54.errdatadir="C:/Users/kummadisingu/MySQL_Cluster/54/"basedir="C:/Program Files/MySQL/MySQL Cluster 7.3/"port=3307ndbcluster=onndb-nodeid=54ndb-connectstring=10.30.12.23:1186,

MySQL Cluster

Auto-Sharding

MySQL Cluster automatically partitions the tables across data nodes in the NDB cluster.

By default, sharding is based on hashing of the primary key, which generally leads to a more even distribution of data and queries across the cluster

Automatically creates node groups based on configuration parameters.

MySQL Cluster

How it happens ?

MySQL Cluster Image-source

Continued Operation of MySQL Cluster

1. As long as each node group participating in the cluster has at least one node operating, the cluster has a complete copy of all data and remains viable.

2. However, if both nodes from either node group fail the cluster has lost an entire partition and so can no longer provide access to a complete set of all cluster data

F1~ fragment 1 of partition 1MySQL Cluster Image-source

MySQL Cluster Replication

Multiple clusters within a cluster – scenario when Geographical replication is in demand.

Synchronous replication- happens only between data nodes and uses the two phase commit protocol.

Asynchronous replication- replication between two or more clusters (multi master).

MySQL Cluster

CAP theorem and MySQL Cluster

A single MySQL Cluster prioritizes data consistency over availability when network partitions occur.

A pair of asynchronously replicating MySQL Clusters prioritizes service availability over data consistency when network partitions occur.

That’s Great!

MySQL Cluster

Single MySQL Cluster - CP

When a network partition occurs, live nodes in each partition regroup and decide what to do next:

If there are not enough live nodes to serve all of the data stored – shutdown ~ degrade availability.

After partition if the live nodes still holds all of the data- Continue to provide service.

MySQL Cluster

Asynchronously replicating clusters - AP

 Data consistency within each cluster is guaranteed as normal, but data consistency across the two clusters is not because of asynchronous replication.

And yet, continue to accept read and write requests by implementing a

special type of consistency mechanism known as …?

Eventually Consistent

MySQL Cluster

NoSQL and MySQL Cluster

All the benefits of an ACID RDBMS + performance capabilities of Key/Value store = MySQL Cluster

By pass the SQL layer and directly access the data nodes via the memcache API or various other NoSQL interfaces.

MySQL Cluster

MySQL Cluster & Memcache API

1. key-value interaction with NDB engine via the familiar memcached API.

2. Extra caching layer~ very low latency.

MySQL Cluster

Image-source

Scheme-less storage in MySQL Cluster

By default, every Key / Value is written to the same table with each Key / Value pair stored in a single row - thus allowing schema-less data storage

MySQL Cluster

Image-source

NoSQL + SQL

Alternatively, we can define a key-prefix so that each value is linked to a pre-defined column in a specific table.

MySQL Cluster

Image-source

Additional Features

Online scaling & up-gradation without temporary outage.

Elastic in nature ~ compatible with the cloud computing framework.

Unlike other distributed databases, MYSQL Cluster supports execution of complex join queries- preserving ACID properties.

MySQL Cluster

Resilient to Failures

Employs a self healing auto recovery mechanism with :

1. Automatic transfer of control.

2. Automatic restart and resynchronize.

3. ~No Single point failures.

MySQL Cluster

Queries: Two key points to remember

For a table to be replicated in the cluster, it must use the NDB Cluster storage engine by specifying “ENGINE=NDBCLUSTER”:

CREATE TABLE table_name (col_name column_definitions) ENGINE=NDBCLUSTER;

ALTER TABLE table_name ENGINE=NDBCLUSTER;

Every NDBCluster has a primary key. If no primary key is defined by the user when a table is created, the NDBCluster storage engine automatically generates a hidden one.

MySQL Cluster

Limitations

Partitioning.

- Limited partitioning schemes.

- Upper limit on the maximum number of partitions:

~ 8 * [ number of node groups ]

Storage size.

- Single MYSQL cluster has a storage limit of 3TB, which is very low.

MySQL Cluster

Compare

1. Unrestricted data size

2. Batch processing

3. Disk storage

1. Real time analytics

2. Mission critical applications

3. Data Integrity

MySQL Cluster

Conclusion

MySQL’s answer to the NoSQL competitors in the big data web market.

Certainly brings out the best of the both worlds.

MySQL Cluster is already serving some of the most demanding web and mobile services on the planet

MySQL Cluster

Customers

MySQL Cluster

Available Versions

MySQL Cluster GA (Generally available)

MySQL Cluster Carrier Guide Edition (Community and Commercial versions)

Includes MySQL Cluster Manager

24 hour online support.

MySQL Cluster

MySQL Cluster