MySQL Cluster has been getting a lot of attention because many organizations consider it as a cost-effective database solution, which can handle various types of applications.

Let’s have a closer look at what MySQL Cluster is and what advantages can it deliver to its customers.

MySQL Cluster is a transactional database that is scalable and ACID-compliant. It has high availability and is used by many industries in real-time. MySQL Cluster is designed around a distributed architecture that assures excellent performance, meeting the challenges of mission-critical applications. Its features include in-memory support, load balancing, automatic data partitioning (sharding), database scalability, and the capability to add nodes to executing cluster without failure.

MySQL Cluster’s performance

• High availability. MySQL Cluster boasts of 99.999% availability, or less than five minutes downtime in a year.
• Excellent performance. With MySQL Cluster, a transaction can be performed in five milliseconds. You can even achieve faster response times using one of the NoSQL access methods.

• Sub-second failover. This allows you to deliver service without interruption.
• Scalability. Using MySQL Cluster gives you the power to scale dynamically. For a cost-effective scale-out, you have options to add more application and data nodes per cluster, add more CPU threads, or add more memory per data node.

MySQL Cluster architecture

MySQL uses three types of nodes. These are the following:

1. Data Nodes. These are the main nodes responsible for managing and storing disk-based and in-memory data. It also handles the synchronous replication of data between Data Nodes and retrieval of data and transactions. Other functionalities include automatic and user-defined partitioning of tables, automatic failover, and self-healing capability from failures. Self-healing can be achieved by automatically restarting the failed Data Nodes, with no manual intervention.When you store and distribute data in a shared-nothing architecture and synchronously replicate it, there will always be another Data Node to store the same information if one Data Node fails. This feature enables the transactions and requests to be completed without interruption. Should there be any aborted transactions, those will be rolled back and can be run again.

2. Application Nodes. These connect the application logic to the Data Nodes. The applications can access the database through MySQL Servers. It offers a broad range of access technologies. Application Nodes are capable of accessing data from all Data Nodes. Since it can use the remaining nodes, your system can afford to fail without you having to worry about causing a loss of service.

3. Management Nodes. It essentially manages the publishing of the cluster configuration of all the nodes. These nodes are utilized at the startup – either when you need to reconfigure a system or when a node is joining the cluster. You can stop and restart the Management Nodes without worrying if the ongoing performance of the Data and Application Nodes will be affected.For development and evaluation purposes, you can run all nodes on one host. For fault tolerance and full redundancy, you’ll be required to have six physical hosts – two data nodes, two application nodes, and two management nodes. Nevertheless, many users co-locate both Application and Management Nodes. This approach reduces the number of nodes to four.

Qualifying a workload for MySQL Cluster

When looking at options for your application’s database, it helps to review the following items to see if your workloads are a good fit to MySQL Cluster:

• To accommodate the increasing volume of write operations such as update, insert, and delete, do you need to shard your database?
• Do you require consistency in the results of your Select operations, regardless of which particular node they are returned from?
• When your existing database fails, does it lead to application downtime? Do you experience business disruption that results not only in revenue loss but also tarnished reputation?
• Does data loss during a failover, even though it happened just for a few seconds, cause disruption to your business?
• Does your business require you to replicate your database across geographic regions? If so, is it necessary that each area serves both read and write operations?
• Is response time crucial to your business?
• In your business, are you dealing with a wide range of applications that would benefit from using direct access to data instead of always relying on SQL?
• Does the application you are running consist mostly of short transactions executed in parallel, such as tens of operations per transaction against thousands of operations per transaction?

If you answer yes to any of the questions asked above, it is worth considering MySQL Cluster as your transactional database.

A benchmark like no other

It is worthy to note that MySQL Cluster is designed to execute two kinds of workloads – On-Line Transaction Processing (OLTP) and Ad-hoc Searches.

1. On-Line Transaction Processing. Tables with optimized memory offer a high level of concurrency and sub-millisecond dormancy for OLTP workloads.
2. Ad-hoc Searches. With MySQL’s increased parallelism, doing searches on un-indexed columns is accelerated at a significant speed.

In a recent benchmarking study conducted by a MySQL Principal Product Manager, it was disclosed that MySQL Cluster delivers its best performance with OLTP workloads. The benchmark particularly mentioned the type of workload in which large numbers of transactions and queries were sent in parallel.

In the benchmark, each data node was running on a dedicated 56-thread Intel E5-2697 v3 machine. The throughput showed an escalating trend as the number of data nodes was increased from 2 to 32. Then at 32 data nodes, the performance remarkably hit the 200 million NoSQL queries per second.

What should you do next?

With its distributed, shared-nothing architecture and real-time design, MySQL Cluster has gained a lot of attention from many enterprises that would like to achieve scalability and high performance with their database.

Contact Four Cornerstone if you would like to be among the many organizations that benefit from MySQL Cluster. Our Oracle Consulting in Dallas will provide their expertise in showing how the scalability and high performance of MySQL Cluster can benefit your business.