Mysql Administration - NDB

MySQL Cluster is a technology providing shared-nothing clustering and auto-sharding for the MySQL database management system. It is designed to provide high availability and high throughput with low latency, while allowing for near linear scalability.^[2]

MySQL Cluster is designed around a distributed, multi-master ACID compliant architecture with no single point of failure. MySQL Cluster uses automatic sharding(partitioning) to scale out read and write operations on commodity hardware and can be accessed via SQL and Non-SQL (NoSQL) APIs

Replication[edit]

Internally MySQL Cluster uses synchronous replication through a two-phase commit mechanism in order to guarantee that data is written to multiple nodes upon committing the data. (This is in contrast to what is usually referred to as "MySQL Replication", which is asynchronous.) Two copies (known as replicas) of the data are required to guarantee availability. MySQL Cluster automatically creates “node groups” from the number of replicas and data nodes specified by the user. Updates are synchronously replicated between members of the node group to protect against data loss and support fast failover between nodes.

It is also possible to replicate asynchronously between clusters; this is sometimes referred to as "MySQL Cluster Replication" or "geographical replication". This is typically used to replicate clusters between data centers for Disaster recovery or to reduce the effects of network latency by locating data physically closer to a set of users. Unlike standard MySQL replication, MySQL Cluster's geographic replication uses optimistic concurrency control and the concept of Epochs to provide a mechanism for conflict detection and resolution,^[3] enabling active/active clustering between data centers.

Starting with MySQL Cluster 7.2, support for synchronous replication between data centers was supported with the Multi-Site Clustering feature.^[4]

Horizontal data partitioning (Auto-Sharding)[edit]

MySQL Cluster is implemented as a fully distributed multi-master database ensuring updates made by any application or SQL node are instantly available to all of the other nodes accessing the cluster, and each data node can accept write operations.

Data within MySQL Cluster (NDB) tables is automatically partitioned across all of the data nodes in the system. This is done based on a hashing algorithm based on the PRIMARY KEY on the table, and is transparent to the end application. Clients can connect to any node in the cluster and have queries automatically access the correct shards needed to satisfy a query or commit a transaction. MySQL Cluster is able to support cross-shard queries and transactions.

Users can define their own partitioning schemes. This allows developers to add “distribution awareness” to applications by partitioning based on a sub-key that is common to all rows being accessed by high running transactions. This ensures that data used to complete transactions is localized on the same shard, thereby reducing network hops.

Hybrid Storage[edit]

MySQL Cluster allows datasets larger than the capacity of a single machine to be stored and accessed across multiple machines.

MySQL Cluster maintains all indexed columns in distributed memory. Non-indexed columns can also be maintained in distributed memory or can be maintained ondisk with an in-memory page cache. Storing non-indexed columns on disk allows MySQL Cluster to store datasets larger than the aggregate memory of the clustered machines.

MySQL Cluster writes Redo logs to disk for all data changes as well as check pointing data to disk regularly. This allows the cluster to consistently recover from disk after a full cluster outage. As the Redo logs are written asynchronously with respect to transaction commit, some small number of transactions can be lost if the full cluster fails, however this can be mitigated by using geographic replication or multi-site cluster discussed above. The current default asynchronous write delay is 2 seconds, and is configurable. Normal single point of failure scenarios do not result in any data loss due to the synchronous data replication within the cluster.

When a MySQL Cluster table is maintained in memory, the cluster will only access disk storage to write Redo records and checkpoints. As these writes are sequential and limited random access patterns are involved, MySQL Cluster can achieve higher write throughput rates with limited disk hardware compared to a traditional disk-based caching RDBMS. This checkpointing to disk of in-memory table data can be disabled (on a per-table basis) if disk-based persistence isn't needed.

Shared nothing[edit]

MySQL Cluster is designed to have no single point of failure. Provided that the cluster is set up correctly, any single node, system, or piece of hardware can fail without the entire cluster failing. Shared disk (SAN) is not required. The interconnects between nodes can be standard Ethernet. Gigabit Ethernet, InfiniBand andSCI interconnects are also supported.

MySQL Cluster Manager[edit]

Part of the commercial MySQL Cluster CGE, MySQL Cluster Manager is a tool designed to simplify the creation and administration of the MySQL Cluster CGE database by automating common management tasks, including on-line scaling, upgrades, backup/restore and reconfiguration. MySQL Cluster Manager also monitors and automatically recovers MySQL Server application nodes and management nodes, as well as the MySQL Cluster data nodes.

Implementation[edit]

MySQL Cluster uses three different types of nodes (processes) :

• Data node (ndbd/ndbmtd process): These nodes store the data. Tables are automatically sharded across the data nodes which also transparently handle load balancing, replication, failover and self-healing.
• Management node (ndb_mgmd process): Used for configuration and monitoring of the cluster. They are required only to start or restart a cluster node. They can also be configured as arbitrators, but this is not mandatory (MySQL Servers can be configured as arbitrators instead).^[5]
• Application node or SQL node (mysqld process): A MySQL server (mysqld) that connects to all of the data nodes in order to perform data storage and retrieval. This node type is optional; it is possible to query data nodes directly via the NDB API, either natively using the C++ API or one of the additional NoSQL APIs described above.

Generally, it is expected that each node will run on a separate physical host, VM or cloud instance (although it is very common to co-locate Management Nodes with MySQL Servers). For best practice, it is recommended not to co-locate nodes within the same node group on a single physical host (as that would represent a single point of failure).

Differences Between the NDB and InnoDB Storage Engines

The MySQL Cluster NDB storage engine is implemented using a distributed, shared-nothing architecture, which causes it to behave differently from InnoDB in a number of ways. For those unaccustomed to working with NDB, unexpected behaviors can arise due to its distributed nature with regard to transactions, foreign keys, table limits, and other characteristics. These are shown in the following table:

Feature	InnoDB 1.1	MySQL Cluster NDB 7.5
MySQL Server Version	5.7	5.7
InnoDB Version	InnoDB 5.7.15	InnoDB 5.7.15
MySQL Cluster Version	N/A	NDB 7.5.4
Storage Limits	64TB	3TB (Practical upper limit based on 48 data nodes with 64GB RAM each; can be increased with disk-based data and BLOBs)
Foreign Keys	Yes	Yes.
Transactions	All standard types	READ COMMITTED
MVCC	Yes	No
Data Compression	Yes	No (MySQL Cluster checkpoint and backup files can be compressed)
Large Row Support (> 14K)	Supported for VARBINARY, VARCHAR,BLOB, and TEXT columns	Supported for BLOB and TEXT columns only (Using these types to store very large amounts of data can lower MySQL Cluster performance)
Replication Support	Asynchronous and semisynchronous replication using MySQL Replication	Automatic synchronous replication within a MySQL Cluster. Asynchronous replication between MySQL Clusters, using MySQL Replication
Scaleout for Read Operations	Yes (MySQL Replication)	Yes (Automatic partitioning in MySQL Cluster; MySQL Cluster Replication)
Scaleout for Write Operations	Requires application-level partitioning (sharding)	Yes (Automatic partitioning in MySQL Cluster is transparent to applications)
High Availability (HA)	Requires additional software	Yes (Designed for 99.999% uptime)
Node Failure Recovery and Failover	Requires additional software	Automatic (Key element in MySQL Cluster architecture)
Time for Node Failure Recovery	30 seconds or longer	Typically < 1 second
Real-Time Performance	No	Yes
In-Memory Tables	No	Yes (Some data can optionally be stored on disk; both in-memory and disk data storage are durable)
NoSQL Access to Storage Engine	Yes	Yes Multiple APIs, including Memcached, Node.js/JavaScript, Java, JPA, C++, and HTTP/REST
Concurrent and Parallel Writes	Not supported	Up to 48 writers, optimized for concurrent writes
Conflict Detection and Resolution (Multiple Replication Masters)	No	Yes
Hash Indexes	No	Yes
Online Addition of Nodes	Read-only replicas using MySQL Replication	Yes (all node types)
Online Upgrades	No	Yes
Online Schema Modifications	Yes, as part of MySQL 5.6.	Yes.

Noncompliance with SQL Syntax in MySQL Cluster

Some SQL statements relating to certain MySQL features produce errors when used with NDB tables, as described in the following list:

• Temporary tables.  Temporary tables are not supported. Trying either to create a temporary table that uses the NDB storage engine or to alter an existing temporary table to use NDB fails with the error Table storage engine 'ndbcluster' does not support the create option 'TEMPORARY'.
• Indexes and keys in NDB tables.  Keys and indexes on MySQL Cluster tables are subject to the following limitations:

Restrictions on foreign keys.  Support for foreign key constraints in MySQL Cluster NDB 7.5 is comparable to that provided byInnoDB, subject to the following restrictions:

• MySQL Cluster and geometry data types.  Geometry data types (WKT and WKB) are supported for NDB tables. However, spatial indexes are not supported.
• Character sets and binary log files.  Currently, the ndb_apply_status and ndb_binlog_index tables are created using the latin1(ASCII) character set. Because names of binary logs are recorded in this table, binary log files named using non-Latin characters are not referenced correctly in these tables. This is a known issue, which we are working to fix. (Bug #50226)