This article provides an overview of MySQL Cluster in a simple understandable manner suitable for both database beginners and professionals.
Additionally, it covers some of the main aspects of clustering technology that are more or less common among other vendors but gives enough material to understand what lies behind them.
The readers are going to get a taste of how different components work together to form a very solid, sturdy, and reliable system in the form of MySQL Cluster and the challenges it may face from its counterparts.
About computers, supercomputers, clusters, and commodity components
Let us start by understanding the concept of MySQL Cluster by first getting familiar with a basic definition of computers, clusters, and supercomputers as they are all linked in some way that you are going to discover soon.
What is a general-purpose computer
A general-purpose computer is a machine that consists of a single processor and can do one task at a time.
However, you may see that your general-purpose computer (desktop/laptop) doing multiple things at a time but originally it is distributing work across one processor to give the impression that it is doing all in one go so it is not real-time parallel (it is giving fraction of second to one task and fraction of a second to the other tasks).
What is a Supercomputer
A supercomputer in its simplest form is just a computer with multiple or parallel processors
(CPUs) that can (actually) do multiple tasks simultaneously (in one shot) along with many other benefits that it offers.
Now having more and more processors for a machine makes it very powerful but at the same time it is a costly adventure and depending on the requirements and important factors like the budget of an organization it may or may not be the best possible solution to turn to.
What is a Cluster
A cluster as in the form of MySQL Cluster is an alternative way of improving the power of a machine by adding and linking it with a network of machines so even though we don’t achieve the parallel processing architecture like supercomputing we still get a lot of processing speed by intelligently joining several machines together.
Cluster vs Supercomputer
Now supercomputers keep multiple processors (CPUs) intact to give a lot of speed and power (performance) whereas clusters are based on commodity components giving a relatively cheaper alternative to supercomputing.
Clustering and commodity components
SQL Cluster just like clustering is based on commodity components which means the computers are generally available to get connected to join hands to build a powerful machine since the commodity components are easily available including software and hardware.
About Availability, Scalability, Redundancy, and speed of execution
The following features are part and parcel of both supercomputing and clustering except when it comes to supercomputing there is a world of difference (extremely powerful due to the high cost and superior multiple processor architectures involved):
- Redundancy
- Availability
- Scalability
- Speed of execution
Now MySQL Cluster does offer the above features too, however, interestingly they are linked beautifully.
How Redundancy, Availability, and Scalability are linked
Let us get back to the definition of both clustering and supercomputer to see how redundancy, availability, and scalability are linked.
As we either add more processors to a computer (in case of supercomputing) or we add more nodes (computers) to a network to join forces as a result we introduce redundancy meaning duplicating critical components of a system so that if one fails the other is ready to take on that redundancy produces high availability means more processors or nodes are highly available and then we can scale the system further by adding more components of same nature.
All the above-mentioned features boost the performance or speed of the execution of the queries against a database in a database solution using SQL Cluster.
MySQL Cluster Carrier Grade Edition (CGE)
According to a data sheet available on MySQL’s official website, MySQL Cluster CGE enables customers to run mission-critical applications with 99.9999% availability.
It is a distributed, real-time, ACID-compliant transactional database that scales horizontally for read-and-write-intensive workloads.
Let us try to understand the terms one by one excluding the ones we have already defined.
What do mission-critical applications stand for
Mission-critical applications are those applications that must operate for the survival of a business and if they fail or get interrupted, they cause a severe impact on the business.
What do we mean by 99.9999% availability and shared-nothing architecture
The MySQL Cluster data sheet explains what it means when they say 99.9999% availability actually more technical terms come into play when we try to understand this.
Actually, this means a shared-nothing architecture is in high use which does not rely on shared storage rather an online scaling (on-the-fly expansion of the configuration but horizontal scaling) makes it possible thereby unlike shared structures not losing the ability to use foreign keys and perform Join operations on the data (although different copies of the data may be distributed across different nodes)
What is a distributed database
A distributed database is a database in which the data is distributed across different nodes/servers thereby not restricting it to a single site ensuring that multiple images of the same version exist on multiple sites.
What is a real-time database (real-time performance)
A real-time database as the name employs means a database that is being updated in real-time that is as soon as a data-related event is happening such as live cricket match statistics, weather stations, tele communication applications, etc.
Real-time Performance Example
An example of real-time performance by MySQL Cluster (SQL Cluster CGE) is to be able to serve 200 million (NoSQL) queries per second.
What is an ACID-compliant transaction database
ACID stands for atomicity, consistency, isolation, and durability and an ACID-compliant transaction database is a database that guarantees consistent storage, access, and processing of data on time completely and concurrently.
ACID Compliance Example
ACID also guarantees that the relational database tables joined via keys (foreign keys) remain intact which means their referential integrity constraints are respected in the clustering architecture.
What is horizontal scaling
Horizontal scaling also called scale-out methodology is a way of adding more nodes (machines) to an existing infrastructure (server) to improve and increase the power and performance of a system.
No single point of failure and no downtime
One of the most important aspects of MySQL Cluster is that you have no single point of failure and no downtime even during maintenance operations.
When we say there is no single point of failure, we mean that if a component such as a data node fails then the next data node instantly takes over and the failure remains un-noticed for the end user and the application carries on serving the business requirements.
Creating a Database Cluster
In other words, SQL Cluster helps us to easily create a database cluster for very high-speed corporate-level applications and use the commodity nodes or data nodes (multiple nodes/computers) for storing, accessing, and processing data.
How to Install MySQL Cluster
Please download the file based on one of the following options of your choice available for multiple operating systems:
- Windows MSI Installer
- Windows Zip Archive
- Windows Zip Archive Debug Binaries and Test Suite
The download link is as follows:
MySQL :: Download MySQL Cluster
MySQL NDB Cluster Architecture
MySQL in its Clustering form follows a solid architecture backed up by numerous features and facilities including the core components that we are going to discuss in this article.
MySQL NDB Cluster is an enhanced version of MySQL offering high availability and high redundancy thereby forming distributed computing environment.
MySQL NDB Cluster and MySQL InnoDB Cluster
MySQL Cluster without going into the technical details can be MySQL NDB Cluster or MySQL InnoDB Cluster while NDB and InnoDB are the storage engines in use, and both are transactional MySQL storage engines.
From now by MySQL Cluster, we refer to MySQL NDB Cluster.
Main Components
MySQL Cluster architecture consists of the following main components:
- Data Nodes
- SQL Nodes
- NDB Management Server
- NDB Manager Client
- Clients (Applications)/APIs
These components are illustrated as follows:
All the above components put forward a joint effort in the form of MySQL Cluster to get the best out of it whether it be resilience against failure (no single point of failure) or availability (high availability).
Let us get a quick introduction to the components.
Storage Engine
NDB Storage Engine is the storage engine at the back of MySQL Cluster (NDB Cluster).
Data Nodes
The data is stored in the data nodes whereby data we mean both objects (definitions) and their data such as database tables and their records (row).
SQL Nodes
The data (tables) stored n the data nodes is directly accessible by MySQL Servers called SQL Nodes.
NDB Management Server
This manages system configurations and is required when a cluster needs to be reconfigured.
NDB Management Client
NDB Management Client manages the cluster as a client program and can perform the following tasks:
- Starting of a node
- Stopping a node
- Starting a backup
Applications
Applications serve as an endpoint for end-user interaction and for the applications the whole architecture is no different from connecting to a stand-alone MySQL Server instance while all the clustering hard work is done by the components mentioned above.
Congratulations, on getting familiar with MySQL Cluster and the components that make it possible while keeping the whole process somewhat invisible to the outer world.
Test your knowledge
Please test your knowledge by answering the following questions
- Why is supercomputing different than clusters
- What is the difference between horizontal scaling and vertical scaling and which technology uses horizontal scaling and vertical scaling
- What is the role of data nodes in MySQL Cluster
- What do SQL nodes do in MySQL NDB Cluster
- What is NDB in MySQL NDB Cluster
- How applications in SQL Cluster access database tables and data
He holds BSc and MSc Degrees in Computer Science and also received the OPF merit award.
He began his professional life as a computer programmer more than a decade ago, working on his first data venture to migrate and rewrite a public sector database driven examination system from IBM AS400 (DB2) to SQL Server 2000 using VB 6.0 and Classic ASP along with developing reports and archiving many years of data.
His work and interest revolves around Database-Centric Architectures and his expertise include database and reports design, development, testing, implementation and migration along with Database Life Cycle Management (DLM).
He has also received passing grade to earn DevOps for Databases verified certificate, an area in which he finds particular interest and potential.
View all posts by Haroon Ashraf
- SQL Machine Learning in simple words - May 15, 2023
- MySQL Cluster in simple words - February 23, 2023
- Common use cases of SQL SELECT Distinct - February 2, 2023