A number of features for MySQL Cluster were implemented in MySQL 5.1 through MySQL 5.1.23, when support for MySQL Cluster was moved to MySQL Cluster NDB. All of the features in the following list are also available in all MySQL Cluster NDB (6.1 and later) releases.
Integration of MySQL Cluster into MySQL Replication. MySQL Cluster Replication makes it possible to replicate from one MySQL Cluster to another. Updates on any SQL node (MySQL server) in the cluster acting as the master are replicated to the slave cluster; the state of the slave side remains consistent with the cluster acting as the master. This is sometimes referred to as asynchronous replication between clusters, providing geographic redundancy. It is also possible to replicate from a MySQL Cluster acting as the master to a standalone MySQL server acting as the slave, or from a standalone MySQL master server to a slave cluster; in either of these cases, the standalone MySQL server uses a storage engine other than
NDBCLUSTER. Multi-master replication setups such as circular replication are also supported.
Support for storage of rows on disk. Storage of
NDBCLUSTERtable data on disk is now supported. Indexed columns, including the primary key hash index, must still be stored in RAM; however, all other columns can be stored on disk.
Variable-size columns. In MySQL 5.0, an
NDBCLUSTERtable column defined as
VARCHAR(255)used 260 bytes of storage independent of what was stored in any particular record. In MySQL 5.1 Cluster tables, only the portion of the column actually taken up by the record is stored. This makes possible a significant reduction in space requirements for such columns as compared to previous release series—by a factor of up to 5 in many cases.
User-defined partitioning. Users can define partitions based on columns that are part of the primary key. It is possible to partition
NDBtables based on
LINEAR KEYschemes. This feature is also available for many other MySQL storage engines, which support additional partitioning types that are not available with
The MySQL Server can also determine whether it is possible to “prune away” some of the partitions from the
WHEREclause, which can greatly speed up some queries. See Section 18.4, “Partition Pruning”, for information about designing tables and queries to take advantage of partition pruning.
Autodiscovery of table schema changes. In MySQL 5.0, it was necessary to issue a
FLUSH TABLESstatement or a “dummy”
NDBCLUSTERtables or changes made to schemas of existing
NDBCLUSTERtables on one SQL node to be visible on the cluster's other SQL nodes. In MySQL 5.1, this is no longer necessary; new Cluster tables and changes in the definitions of existing
NDBCLUSTERtables made on one SQL node are immediately visible to all SQL nodes connected to the cluster.
Distribution awareness (NDB API). Distribution awareness is a mechanism by which the best data node is automatically selected to be queried for information. (Conceptually, it is similar in some ways to partition pruning (see Section 18.4, “Partition Pruning”). To take advantage of distribution awareness, you should do the following:
Determine which table column is most likely to be used for finding matching records.
Make this column part of the table's primary key.
Explicitly partition the table by
KEY, using this column as the table' partitioning key.
Following these steps causes records with the same value for the partitioning column to be stored on the same partition (that is, in the same node group). When reading data, transactions are begun on the data node actually having the desired rows instead of this node being determined by the usual round-robin mechanism.Important
To see a measureable impact on performance, the cluster must have at least four data nodes, since, with only two data nodes, both data nodes have exactly the same data.
Using distribution awareness can yield performance increase of as great as 45% when using four data nodes, and possibly more when using a greater number of data nodes.Note
In mainline MySQL 5.1 releases, distribution awareness was supported only when using the NDB API; support was added for SQL and API nodes in MySQL Cluster NDB 6.3 (see Section 126.96.36.199, “MySQL Cluster Development in MySQL Cluster NDB 6.3”, which includes an example showing how to create a table to take advantage of distribution awareness).
Online adding and dropping of indexes. For
DROP INDEXoperations for dynamic (variable-width) columns on
NDBtables are now performed as online operations (no table copying). This is also true for
DROP INDEX. As a result, these operations are now performed much more quickly than previously.
For more information, see Section 188.8.131.52, “ALTER TABLE Online Operations in MySQL Cluster”, Section 13.1.13, “CREATE INDEX Syntax”, and Section 13.1.24, “DROP INDEX Syntax”.