Contents
Highlights: NoSQL JavaScript with node.js, Foreign Keys, and Auto-Tuned Clustering
The MySQL team at Oracle are excited to announce the General Availability of MySQL Cluster 7.3, ready for production workloads.
Some might call MySQL Cluster 7.3 “the foreign keys release” – and sure enough it is a major engineering achievement to build a distributed database that enforces referential integrity across a shared-nothing cluster, while maintaining ACID compliance and cross-shard JOINs. But MySQL Cluster 7.3 is so much more as well – especially if you are developing new JavaScript-based services along with node.
The design focus for MySQL Cluster 7.2 is enabling developer agility – making it simpler and faster than ever to build new services with a highly scalable, fault tolerant, real-time database The key enhancements delivered by MySQL Cluster 7.3 are summarized below.
Figure 1: MySQL Cluster 7.3, Faster & Easier Application Development- NoSQL JavaScript Connector for Node.js: Enables a single programming language and a single tool-chain by extending JavaScript from the client to the server, all the way through to the database, bypassing the SQL layer to deliver lower latency and reduced development cycles.
- Foreign Keys: Strengthens data modeling and simplifies application logic by automatically enforcing referential integrity between different tables distributed on different shards, on different nodes … even in different data centers
- MySQL 5.6 Support: Developers can combine the InnoDB and MySQL Cluster NDB storage engines within a single database, using the very latest MySQL 5.6 release.
- Connection Thread Scalability: Increases cluster performance and capacity by improving the throughput of each connection to the data nodes, thus reducing the number of connections that need to be provisioned, and enabling greater scale-out headroom. Performance testing is showing up to 7.5x higher throughput per connection, enabling more client threads to use each connection.
- Auto-Installer: Get it all up and running in minutes! Graphically configure and provision a production-grade cluster, automatically tuned for your workload and environment, without ever resorting to “RTFM”.
Lets take a closer look at MySQL Cluster 7.3. You can also get started by downloading the MySQL Cluster Evaluation Guide.
MySQL Cluster NoSQL JavaScript Connector for Node.js
Node.js is hot! In a little over 3 years, it has become one of the most popular environments for developing next generation web, cloud, mobile and social applications. Bringing JavaScript from the browser to the server, the design goal of Node.js is to build new real-time applications supporting millions of client connections, serviced by a single CPU core.
Making it simple to further extend the flexibility and power of Node.js to the database layer, the new JavaScript for Node.js Connector is part of MySQL Cluster 7.3.
With its non-blocking, event-driven asynchronous design, MySQL Cluster is the perfect architectural fit to node in building real-time, distributed services with tens of thousands of concurrent connections. Support for on-line schema changes enable these services to evolve rapidly, without downtime.
Implemented as a module for the V8 engine, the new driver provides node with a native, asynchronous JavaScript connector that can be used to both read and write results sets directly from MySQL Cluster, without transformations to SQL. The benefits are three-fold:
- Developers only need to use JavaScript to access the database, enabling rapid development cycles and faster time to market;
- The SQL layer is bypassed, delivering lower runtime latency and higher throughput for simple queries.
- As the client connects directly to the cluster rather than a MySQL layer, there is no need for any failover handling as this is all handled at the data node layer.
Figure 2: MySQL Cluster NoSQL Connector for Node enables end-to-end JavaScript development
Rather than just presenting a simple interface to the database, the Node.js module integrates MySQL Cluster’s native API library directly within the web application itself, enabling developers to seamlessly couple their high performance, distributed applications with a high performance, distributed, database delivering 99.999% availability.
Developers can re-use JavaScript from the client to the server, all the way through to the database supporting real-time, high-scale services such as:
- Process streaming data from digital advertising, real-time bidding and analytics systems;
- Gaming and social networks, powering the back-end infrastructure for serving mobile devices.
As an added benefit, you can direct the connector to use SQL so that the same API can be used with InnoDB tables.
The JavaScript Connector for Node.js joins a growing portfolio of NoSQL interfaces for MySQL Cluster, which already include Memcached, Java, JPA and HTTP/REST. And of course, developers can still depend on SQL to execute complex queries and access the rich ecosystem of connectors, frameworks, tooling and skills.
Whichever interface is chosen for an application, SQL and NoSQL can be used concurrently across the same data set, providing the ultimate in developer flexibility. Therefore, MySQL Cluster may be supporting multiple applications, each with different query models and access patterns:
- Relational queries using SQL;
- Key/Value or Key/Object based web services using Node.js, Memcached or REST/HTTP;
- Enterprise applications with the ClusterJ and JPA connectors;
- Ultra low-latency services using the C++ NDB connector.
Learn more by reading the MySQL Cluster with node.js tutorial.
Foreign Keys
Foreign Key (FK) support has been one of the most requested enhancements to MySQL Cluster – bringing powerful new functionality while eliminating development complexity. FKs in MySQL Cluster enable new use-cases including:
- Packaged applications such as eCommerce and Web Content Management or 3rd party middleware that depend on databases with Foreign Key support;
- Custom projects requiring Foreign Key constraints to be implemented at the database layer.
Implementation
The definition and behaviour of FKs largely mirrors that of InnoDB, allowing developers to re-use existing MySQL skills in new projects.
FKs are enforced within the MySQL Cluster data nodes, allowing any client API accessing the cluster to benefit from them – whether they are SQL or one of the NoSQL interfaces (Memcached, C++, Java, JPA, HTTP/REST or the new JavaScript for Node.js API).
The core referential actions defined in the SQL:2003 standard are implemented:
- CASCADE
- RESTRICT
- NO ACTION
- SET NULL
In addition, the design supports online adding and dropping of Foreign Keys, enabling the database to continue serving client requests during DDL operations.
Configuration and Getting Started
There is nothing special you have to configure – FK constraint checking is enabled by default.
If you intend to migrate existing tables from another database or storage engine, for example from InnoDB, the DBA should drop FK constraints prior to the import process and then recreate them when complete.
MySQL Workbench can be used to view the relationships and FK constraints between tables, as demonstrated in the figure below. The engineering team are working on the ability to introduce constraints between existing tables within Workbench.
Figure 3: Viewing MySQL Cluster FK Constraints with MySQL Workbench
Learn more by reading this blog for a demonstration of using Foreign Keys with MySQL Cluster.
MySQL 5.6 Support
The SQL layer of MySQL Cluster 7.3 is based on the latest MySQL 5.6 GA release, enabling developers to take advantage of enhanced query throughput and replication robustness.
Enhanced Optimizer for Improved Query Throughput
The MySQL 5.6 Optimizer has been re-factored for better efficiency and performance, and provides an improved feature set for diagnostics. The key MySQL 5.6 optimizer improvements include:
- Subquery Optimizations: Using semi-JOINs and materialization, the MySQL Optimizer delivers greatly improved subquery performance, simplifying how developers construct queries;
- Multi-Range Reads: Improves query execution times by returning data more efficiently.
- Better Optimizer Diagnostics: Enhanced EXPLAIN output and traces for tracking the optimizer decision-making process.
Cross-Data Center and Cross-Database Replication Flexibility
MySQL Cluster uses the MySQL Server’s replication for geographic distribution of clusters, enabling users to:
- Mirror complete clusters across regions for disaster recovery;
- Replicate data from the MySQL Cluster NDB storage engine to InnoDB or MyISAM slaves, typically for active archives or report generation.
MySQL 5.6 includes the broadest set of replication enhancements ever delivered in a single release, with key features available to MySQL Cluster 7.3 including:
- Replication event checksums to detect and prevent corrupt data being replicated between clusters;
- Crash-safe, transactional replication, providing self-healing recovery in the event of an outage in the replication channel between clusters;
It is worth noting geographic replication in MySQL Cluster is active/active (multi-master) – so two remote clusters can both service write requests.
For readers new to MySQL Cluster, Multi-Site Clustering can be used as an alternative to geographic replication when splitting a single cluster between data centers – though it should be noted that the datacenters should be connected via high speed, high quality WAN links.
This flexibility in cross-data center replication makes MySQL Cluster very popular for those applications that rely on the geographic distribution of data – for example PayPal’s fraud detection system is deployed on MySQL Cluster, spread across five Amazon Web Services regions.
Mixing and Matching with InnoDB
MySQL’s pluggable storage engine architecture enables developers to configure InnoDB or MySQL Cluster alongside each other in a single application, determined by the attributes and access patterns of each table.
It is not uncommon to find most tables configured to use InnoDB, while those that have the highest write loads, lowest latency or strictest availability requirements configured to use MySQL Cluster.
With support for MySQL 5.6 across both InnoDB and MySQL Cluster, developers can take advantage of the latest MySQL Server, whichever storage engine they are using.
Connection Thread Scalability
To fully exploit the distributed architecture of MySQL Cluster, users are advised to configure multiple connections between their MySQL Servers or API nodes to the data nodes. This allows MySQL Cluster to execute many more simultaneous operations in parallel.
Each of the connections to the data node layer consumes one of the 256 available node-ids, which in some scenarios can cap the scalability of the cluster.
MySQL Cluster 7.3 increases the throughput of each connection so that fewer connections (and therefore node-ids) are needed to service the same workload. Performance testing shows up to 7.5x higher throughput per connection depending on workload, enabling more client threads to use a single connection.
As a result of Connection Thread Scalability, more nodes can be added to the Cluster to further scale capacity and performance without hitting the 256 node-id limit.
MySQL Cluster GUI-Based Auto-Installer
The Auto-Installer makes it simple for DevOps teams to quickly configure and provision highly optimized MySQL Cluster deployments. Developers can spend more time innovating in their code, rather than figuring out how to install, configure and start the database.
Implemented with a standard HTML GUI and Python-based web server back-end, the Auto-Installer intelligently configures MySQL Cluster based on application requirements and available hardware resources, stepping users through each stage of cluster creation:
- Workload Optimized: On launching the browser-based installer, users can specify the throughput, latency and write-load characteristics of their application;
- Auto-Discovery: The Installer automatically discovers the underlying resources available from the local and remote servers that will make up the Cluster, including CPU architecture, cores and memory.
With these parameters, the installer creates optimized configuration files and starts the cluster.
Figure 4: Automated Tuning and Configuration of MySQL Cluster
The user remains in complete control of the installation:
- Individual configuration parameters can be modified by the user;
- The user may override the topology defined by the installer, specifying which hosts run each of the cluster processes;
- The Cluster can be remotely started and stopped from a single browser window.
Developed by the same engineering team responsible for the development of the MySQL Cluster database, the installer provides standardized configurations that make it simple, quick and easy to build stable and high performance clustered environments.
Figure 5: DevOps maintains complete control over Cluster configuration and deployment
[Source:- Dev.msql]
