The “Shared Nothing” Architecture and Database Sharding

It is a distributed computing architecture with interconnected, but independent computing nodes with their own memory and disk storage that are not shared. Compared to a centralized controller based distributed architecture, the SN architecture eliminates presence of a “single point of failure”. An SN architecture offers great scalability by adding as many nodes as wanted. The data is distributed or partitioned among the different nodes that run on separate machines. To respond to user’s queries, some kind of coordination protocol is being used. This is also called database sharding, where the data is horizontally partitioned. A database shard that contains a partition will be holding on a separate database server instance to spread load and to eliminate failures. In horizontal partitioning, table rows are separated, rather than as columns in vertical partitioning or normalization. Each data set will be part of the shard that is distributed across different database servers and also at different physical locations, if needed.

MySQL Cluster and NDB Cluster

A MySQL Cluster is based on the shared-nothing clustering and database sharding architecture. It is built on the NDB or NDBCLUSTER storage engine for MySQL DBMS. MySQL Cluster provides high availability and scalability, high throughput with low latency, etc. It’s architecture is a real-time distributed, multi-master, ACID compliant and transactional, with no single point of failure. It has adopted the horizontally scalable auto-sharding on in-expensive commodity hardware and serves for intense workload read/write operations through both SQL and NoSQL queries.

MySQL Cluster is built on top of NDB Cluster while NDB Cluster is an independent MySQL storage engine named NDBCLUSTER (In-Memory type) that stores tables of rows as in database sharding. It can be accessed using the C++ based NDB API by any application. For the NDB Cluster, MySQL Server Instance is an API process that accesses the NDB Cluster using its API, along with other API processes that can access it, using API formats like Memcached, Node.JS, Java, HTTP/REST, etc. MySQL Cluster adds additional capabilities to the NDB Cluster, like: SQL processing, application connectors like JDBC/ODBC, Table Joins Mechanism, User authentication and authorization and Asynchronous data replication.

The NDB Cluster eliminates the “single point of failure” using the shared-nothing system in which each node has its own memory and disk. Network shares and file systems are not used by NDB Cluster. In technology documentations, the term NDB refers to the part of the setup related to storage engine and MySQL NDB Cluster refers to the integration of MySQL Server(s) with NDB storage engine. In an NDB Cluster there are multiple hosts that run their own node processes. These processes include MySQL server to access NDB data, data nodes for storing data, management servers and other co-ordination processes.

NDB Cluster Components

Image courtesy of MySQL Cluster Documentation – https://dev.mysql.com/doc/refman/5.7/en/mysql-cluster-overview.html 

MySQL Cluster Manager

MySQL Cluster Manager is responsible for creating, administering, monitoring and managing and MySQL Cluster setup. It is used to automate the common management tasks like scaling through node allocation, upgrades, configuration management, backup and restore. It manages both the MySQL server nodes and data nodes.

SQL and NoSQL APIs

In the MySQL Cluster, tables are stored in data nodes, instead of the MySQL server nodes. This data can be accessed using SQL through the MySQL Server and also by using NoSQL API. For this MySQL Cluster Libraries are there that can be used by any application to access the cluster data. The client libraries are available in the following formats:

1. Memcached
2. Node.js / JavaScript
3. Java and JPA
4. HTTP/REST
5. NDB API (C++)

MySQL Cluster Nodes

There are 3 types of nodes or processes in a MySQL Cluster:

1. Data node (ndbd/ndbmtd process) – The database tables are automatically sharded across the data nodes. Data nodes are programmed to handle replication, load balancing and failover.
2. Cluster manager node (ndb_mgmd process) – Configures, administers, monitors and perform other management activities like start/restart, arbitration etc.
3. Application node or SQL node (mysqld process) – This MySQL Server process connects to all data nodes for data access, reads and writes. This node is optional and other applications can access the cluster data directly through the NDB API.
4. Each node will run on separate hosts, either physical or VM or cloud instance.

Working of the MySQL Cluster

MySQL Cluster uses synchronous replication to commit data transactions across the multi-masters in the cluster. This is in contrast to the asynchronous MySQL replication. To ensure availability, replicas of the data, usually two, are created or mirrored and synchronously replicated to the nodes. This ensures fast failover and data loss protection. Sometimes nodes/clusters are hosted in geographically distant data centers and asynchronous data replication is used. This is for ensuring high data security, disaster recovery and to avoid latency by serving from the data center closest to the user.

The individual nodes can independently started/stopped/restarted and can re-join to the system after a failover. For making configuration changes and upgrades that are common to all nodes, a mechanism called Rolling Restarts is used in which all nodes are restarted in turn. Backup and restore of the MySQL Cluster is managed by the NDB Cluster Management client and the ndb_restore program included in the CDB Cluster Software. NDB Cluster nodes use TCP/IP over standard 100 Mbps pr faster hardware for inter-node communication.

Logs and Check Points

The NDB Cluster classifies events based upon categories like startup, shutdown etc), priority and severity. There are two types of event logs used by the cluster:

1. Cluster log – to log events for the cluster as a whole.
2. Node log – separate logs maintained by each nodes.
3. Nodes save data to the disk and the transaction is committed by adding a date/time checkpoint to identify last modified point. There are two types of checkpoints by which consistency of cluster data is maintained.
4. Local Checkpoint (LCP) – checkpoint specific to each node’s data saving activity. These depend upon the data activity involved with that node as well as on the cluster.
5. Global Checkpoint (GCB) – It occurs every few seconds after synchronizing transactions across all nodes and re-do log (logs data changes) is flushed to disk.