MySQL Cluster overview

Cluster overview

A cluster is a collection of commodity components to provide scalability and availability at a low cost. MySQL Cluster is a technology that enables clustering of in-memory databases in a shared-nothing system. The current GA version is MySQL Cluster 7.3. MySQL 5.6 is integrated and bundled with MySQL Cluster.

MySQL Cluster is designed to run on commodity hardware. Using specialized hardware such as Infiniband network interconnects one can achieve even higher levels of performance, especially over large clusters with many nodes.

MySQL Cluster Features:

• MySQL Cluster is a highly scalable, real-time, ACID-compliant transactional database,
• Combining above 99.999% availability with the low TCO of open source.
• Designed around a distributed, multi-master architecture with no single point of failure.
• MySQL Cluster scales horizontally on commodity hardware to serve read and write intensive workloads.
• Accessed via SQL and NoSQL interfaces.
• MySQL Cluster's real-time design delivers predictable, millisecond response times with the ability to service millions of operations per second.
• Support for in-memory and disk-based data, automatic data partitioning (sharding) with load balancing
• Ability to add nodes to a running cluster with zero downtime allows linear database scalability to handle the most unpredictable web-based workloads.

System requirements for MySQL Cluster

MySQL Cluster: When to use

• To shard your database to meet growing volumes of write (UPDATE, INSERT, DELETE) operations
• To ensure results from SELECT operations are consistent, regardless of which node they are returned from
• Would a failure of the database result in application downtime.
• Would data loss (even if just several seconds worth) during a failover.
• To replicate database across geographic regions, with each region serving both read and write operations.
• Application primarily consists of "short" transactions (ie 10s of operations per transaction versus 1000s) executed in parallel.
• Application mainly consists of: Primary key database access, with some JOIN operations versus regular execution of full table scans and JOINs returning 10s of thousands of rows

MySQL Cluster: Where to use

Applications include :

• High volume OLTP (online transaction processing)
• Real time analytics
• Ecommerce and financial trading with fraud detection
• Mobile and micro-payments
• Session management & caching
• Feed streaming, analysis and recommendations
• Content management and delivery
• Massively Multiplayer Online Games
• Communications and presence services
• Subscriber/user profile management and entitlements

MySQL Cluster: Performance metrics

• Availability
  • 99.999% (<5 min downtime/year)
• Performance
  • Response Time: sub 5 millisecond . Faster response times can be achieved using one of the NoSQL access methods.
  • The throughput of 600,000+ replicated UPDATE operations/sec on a dual-socket Intel server equipped with 64GB RAM. 1 Billion UPDATE operations per minute across a cluster of 30 x Intel Servers.
• Failover
  • Sub-second failover enables you to deliver service without interruption
• Scalability
  • Scale out, scale up and scale dynamically
  • For cost-effective scale-out:
    • Add more application and data nodes per cluster, or
    • Add more CPU threads (16, 32, 64, etc.) or
    • Add more Memory (32GB, 64GB, etc.) per data node

MySQL Cluster: No. of physical servers to create a minimum Cluster configuration

For evaluation and development purposes, you can run all nodes on a single host. For full redundancy and fault tolerance, you would need a minimum 6 x physical hosts :

• 2 x data nodes
• 2 x SQL/NoSQL Application Nodes
• 2 x Management Nodes

MySQL Cluster: Diskless option

For the Diskless option the following restrictions apply :

• No disk data
• Loss of data in case of Cluster failure
• No backup

MySQL Cluster Manager

MySQL Cluster Manager is software which simplifies the creation and management of the MySQL Cluster database by automating common management tasks. MySQL Cluster Manager is not an open source software. It is available only as a part of the commercial MySQL Cluster Carrier Grade Edition (CGE) database. By using MySQL Cluster Manager, Database Administrators (DBAs) and Systems Administrator are more productive, enabling them to focus on strategic IT initiatives and respond more quickly to changing user requirements. At the same time, risks of database downtime that previously resulted from manual configuration errors, are significantly reduced. MySQL Cluster database are supported by MySQL Cluster 6.3 and above. This url lists the supported platforms of MySQL Cluster Manager.

Practical example

As an example, management operations requiring rolling restarts of a MySQL Cluster database that previously demanded 46 manual commands and which consumed 2.5 hours of DBA time can now be performed with a single command, and are fully automated with MySQL Cluster Manager, serving to reduce:

• Management complexity and overhead;
• Risk of downtime through the automation of configuration and change management processes;
• Custom scripting of management commands or developing and maintaining in-house management tools.

MySQL Cluster Manager : Management functionality features

• Administrators are able to create and delete entire clusters.
• Administrators can start, stop and restart the cluster with a single command , as well as add nodes on-line.
• Therefore administrators no longer need to manually restart each data node in turn, in the correct sequence, or to create custom scripts to automate the process.
• MySQL Cluster Manager automates on-line management operations, including the upgrade, downgrade, and reconfiguration of running clusters.
• Administrators no longer need to manually edit configuration files and distribute them to all other cluster nodes.
• MySQL Cluster Manager handles all of these tasks, thereby enforcing best practices and making on-line operations significantly simpler, faster and less error-prone.
• MySQL Cluster Manager provides the ability to control the entire cluster as a single entity, while also supporting very granular control down to individual processes within the cluster itself.
• MySQL Cluster Manager is able to monitor cluster health at both an Operating System and per-process level by automatically polling each node in the cluster.
• MySQL Cluster Manager can detect if a process or server host is alive, dead or has hung, allowing for faster problem detection, resolution, and recovery.
• MySQL Cluster Manager integrates and extends existing management functionality by automating tasks that were previously performed manually by an administrator.
• MySQL Cluster itself has the capability to self-heal from failures by automatically restarting failed Data Nodes, without manual intervention.
• MySQL Cluster Manager can manage, monitor and recover all nodes within a cluster (with the exception of application nodes accessing the Cluster via the C++, Cluster Connector for Java, OpenLDAP etc.).
• MySQL Cluster Manager is decoupled from the actual database processes, so if a management agent stops or is upgraded, it does not impact the running database in any way.
• MySQL Cluster Manager is implemented as a set of agents – one running on each physical host that will contain MySQL Cluster nodes (processes) to be managed.
• When using MySQL Cluster Manager to manage a MySQL Cluster deployment, the administrator no longer edits the configuration files (for example config.ini and my.cnf); instead, these files are created and maintained by the agents
• MySQL Cluster Manager supports asynchronous communication between each management agent in order to reliably propagate reconfiguration requests. As a result, configurations remain consistent across all nodes in the cluster.

Reference : MySQL cluster CGE

Previous: MySQL backup and restore
Next: MySQL Questions Answers