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 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.

See Section 17.6, “MySQL Cluster Replication”.

Support for storage of rows on disk.  Storage of NDBCLUSTER table 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.

See Section 17.5.10, “MySQL Cluster Disk Data Tables”.

Variable-size columns.  In MySQL 5.0, an NDBCLUSTER table 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 NDB tables based on KEY and LINEAR KEY schemes. This feature is also available for many other MySQL storage engines, which support additional partitioning types that are not available with NDBCLUSTER tables.

For additional general information about user-defined partitioning in MySQL 5.1, see Chapter 18, Partitioning. Specifics of partitioning types are discussed in Section 18.2, “Partition Types”.

The MySQL Server can also determine whether it is possible to “prune away” some of the partitions from the WHERE clause, 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 TABLES statement or a “dummy” SELECT in order for new NDBCLUSTER tables or changes made to schemas of existing NDBCLUSTER tables 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 NDBCLUSTER tables 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:

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.

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.

Increased number of cluster nodes.  The maximum number of all nodes in a MySQL Cluster has been increased to 255. For more information, see Section 17.1.5.10, “Limitations Relating to Multiple MySQL Cluster Nodes”.

Disabling arbitration.  It is now possible to disable arbitration by setting ArbitrationRank=0 on all cluster management and SQL nodes. For more information, see Defining the Management Server: ArbitrationRank and Defining SQL and Other API Nodes: ArbitrationRank.

Additional DUMP commands.  New management client DUMP commands provide help with tracking transactions, scan operations, and locks. See Section 17.5.2, “Commands in the MySQL Cluster Management Client”, and DUMP Commands, for more information.

Faster Disk Data backups.  Improvements in backups of Disk Data tables can yield a 10 to 15% increase in backup speed of Disk Data tables.

Batched slave updates.  Batching of updates on cluster replication slaves, enabled using the --slave-allow-batching option for mysqld, increases replication efficiency. For more information, see Section 17.6.6, “Starting MySQL Cluster Replication (Single Replication Channel)”.

Enhanced backup status reporting.  Backup status reporting has been improved, aided in part by the introduction of a BackupReportFrequency configuration parameter; see Defining Data Nodes: BackupReportFrequency, for more information.

Multiple cluster connections per SQL node.  A single MySQL server acting as a MySQL Cluster SQL node can employ multiple connections to the cluster using the --ndb-cluster-connection-pool startup option for mysqld. This option is described in MySQL Cluster-Related Command Options for mysqld: --ndb-cluster-connection-pool option.

New data access interface.  The NdbRecord interface provides a new and simplified data handler for use in NDB API applications. See The NdbRecord Interface, for more information.

New reporting commands.  The new management client REPORT BackupStatus and REPORT MemoryUsage commands provide better access to information about the status of MySQL Cluster backups and how much memory is being used by MySQL Cluster for data and index storage. See Section 17.5.2, “Commands in the MySQL Cluster Management Client”, for more information about the REPORT commands. In addition, in-progress status reporting is provided by the ndb_restore utility; see Section 17.4.17, “ndb_restore — Restore a MySQL Cluster Backup”.

Improved memory allocation and configuration.  Memory is now allocated by the NDB kernel to tables on a page-by-page basis, which significantly reduces the memory overhead required for maintaining NDBCLUSTER tables. In addition, the MaxAllocate configuration parameter now makes it possible to set the maximum size of the allocation unit used for table memory; for more information about this configuration parameter, see Defining Data Nodes: MaxAllocate.

Choice of fixed-width or variable-width columns.  You can control whether fixed-width or variable-width storage is used for a given column of an NDB table by employing of the COLUMN_FORMAT specifier as part of the column's definition in a CREATE TABLE or ALTER TABLE statement. In addition, the ability to control whether a given column of an NDB table is stored in memory or on disk, using the STORAGE specifier as part of the column's definition in a CREATE TABLE or ALTER TABLE statement. For more information, see Section 12.1.17, “CREATE TABLE Syntax”, and Section 12.1.7, “ALTER TABLE Syntax”.

Controlling management client connections.  The --bind-address cluster management server startup option makes it possible to restrict management client connections to ndb_mgmd to a single host (IP address or host name) and port, which can make MySQL Cluster management operations more secure. For more information about this option, see Section 17.4.4, “ndb_mgmd — The MySQL Cluster Management Server Daemon”.

Micro-GCPs.  Due to a change in the protocol for handling of global checkpoints (GCPs handled in this manner sometimes being referred to as “micro-GCPs”), it is now possible to control how often the GCI number is updated, and how often global checkpoints are written to disk, using the TimeBetweenEpochs configuration parameter. This improves the reliability and performance of MySQL Cluster Replication. For more information, see Defining Data Nodes: TimeBetweenEpochs and Defining Data Nodes: TimeBetweenEpochsTimeout.

Core online schema change support.  Support for the online ALTER TABLE operations ADD COLUMN, ADD INDEX, and DROP INDEX is available. When the ONLINE keyword is used, the ALTER TABLE is noncopying, which means that indexes do not have to be re-created, which provides these benefits:

Online CREATE INDEX and DROP INDEX statements are also supported. Online changes can be suppressed using the OFFLINE key word. See Section 12.1.7, “ALTER TABLE Syntax”, Section 12.1.13, “CREATE INDEX Syntax”, and Section 12.1.24, “DROP INDEX Syntax”, for more detailed information.

mysql.ndb_binlog_index improvements.  More information has been added to the mysql.ndb_binlog_index table so that it is possible to determine which originating epochs have been applied inside an epoch. This is particularly useful for 3-way replication. See Section 17.6.4, “MySQL Cluster Replication Schema and Tables”, for more information.

Epoch lag control.  The MaxBufferedEpochs data node configuration parameter provides a means to control the maximum number of unprocessed epochs by which a subscribing node can lag. Subscribers which exceed this number are disconnected and forced to reconnect. For a discussion of this configuration parameter, see Defining Data Nodes: MaxBufferedEpochs.

Fully automatic database discovery.  It is no longer a requirement for database autodiscovery that an SQL node already be connected to the cluster at the time that a database is created on another SQL node, or for a CREATE DATABASE or CREATE SCHEMA statement to be issued on the new SQL node after it joins the cluster.

Multiple data node processes per host.  In earlier MySQL Cluster release series, we did not support MySQL Cluster deployments in production where more than one ndbd process was run on a single physical machine. However, beginning with MySQL Cluster NDB 6.2.0, you can use multiple data node processes on a single host.

Improved Disk Data filesystem configuration.  As of MySQL Cluster NDB 6.2.17, you can specify default locations for MySQL Cluster Disk Data data files and undo log files using the data node configuration parameters FileSystemPathDD, FileSystemPathDataFiles, and FileSystemPathUndoFiles. This eliminates the need to use symbolic links in order to place Disk Data files separately from other files in data node filesystems to improve Disk Data performance. For more information, see Disk Data filesystem parameters.

Automatic creation of Disk Data log file groups and tablespaces.  Beginning with MySQL Cluster NDB 6.2.17, using the data node configuration parameters InitialLogFileGroup and InitialTablespace, you can cause the creation of a MySQL Cluster Disk Data log file group, tablespace, or both, when the cluster is first started. When using these parameters, no SQL statements are required to create these Disk Data objects. For more information, see Disk Data object creation parameters.

Conflict detection and resolution.  It is now possible to detect and resolve conflicts that arise in multi-master replication scenarios, such as circular replication, when different masters may try to update the same row on the slave with different data. Both “greatest timestamp wins” and “same timestamp wins” scenarios are supported. For more information, see Section 17.6.11, “MySQL Cluster Replication Conflict Resolution”.

Recovery of “one master, many slaves” replication setups.  Recovery of multi-way replication setups (“one master, many slaves”) is now supported via the --ndb-log-orig server option and changes in the mysql.ndb_binlog_index table. See Section 17.6.4, “MySQL Cluster Replication Schema and Tables”, for more information.

Enhanced selection options for transaction coordinator.  New values and behaviors are introduced for --ndb_optimized_node_selection allowing for greater flexibility when an SQL node chooses a transaction coordinator. For more information, see the description of ndb_optimized_node_selection in Section 17.3.4.3, “MySQL Cluster System Variables”.

Replication heartbeats.  Replication heartbeats facilitate the task of monitoring and detecting failures in master-slave connections in real time. This feature is implemented via a new MASTER_HEARTBEAT_PERIOD = value clause for the CHANGE MASTER TO statement and the addition of two status variables Slave_heartbeat_period and Slave_received_heartbeats. For more information, see Section 12.6.2.1, “CHANGE MASTER TO Syntax”.

NDB thread locks.  It is possible to lock NDB execution threads and maintenance threads (such as file system and other operating system threads) to specific CPUs on multiprocessor data node hosts, and to leverage real-time scheduling.

Improved performance of updates using primary keys or unique keys.  The number of unnecessary reads when performing a primary key or unique key update has been greatly reduced. Since it is seldom necessary to read a record prior to an update, this can yield a considerable improvement in performance. In addition, primary key columns are no longer written to when not needed during update operations.

Batching improvements.  Support of batched DELETE and UPDATE operations has been significantly improved. Batching of UPDATE WHERE... and multiple DELETE operations is also now implemented.

Improved SQL statement performance metrics.  The Ndb_execute_count system status variable measures the number of round trips made by SQL statements to the NDB kernel, providing an improved metric for determining efficiency with which statements are excuted. For more information, see MySQL Cluster Status Variables: Ndb_execute_count.

Compressed LCPs and backups.  Compressed local checkpoints and backups can save 50% or more of the disk space used by uncompressed LCPs and backups. These can be enabled using the two new data node configuration parameters CompressedLCP and CompressedBackup, respectively. See MySQL Cluster Status Variables: CompressedBackup, and MySQL Cluster Status Variables: CompressedLCP, for more information about these parameters.

OPTIMIZE TABLE support with NDBCLUSTER tables.  OPTIMIZE TABLE is supported for dynamic columns of in-memory NDB tables. In such cases, it is no longer necessary to drop (and possibly to re-create) a table, or to perform a rolling restart, in order to recover memory from deleted rows for general re-use by Cluster. The performance of OPTIMIZE on Cluster tables can be tuned by adjusting the value of the ndb_optimization_delay system variable, which controls the number of milliseconds to wait between processing batches of rows by OPTIMIZE TABLE. In addition, OPTIMIZE TABLE on an NDBCLUSTER table can be interrupted by, for example, killing the SQL thread performing the OPTIMIZE operation.

Batching of transactions.  It is possible to cause statements occurring within the same transaction to be run as a batch by setting the session variable transaction_allow_batching to 1 or ON. To use this feature, autocommit must be set to 0 or OFF. Batch sizes can be controlled using the --ndb-batch-size option for mysqld. For more information, see Section 17.3.4.2, “mysqld Command Options for MySQL Cluster”, and Section 17.3.4.3, “MySQL Cluster System Variables”.

Attribute promotion with ndb_restore.  It is possible using ndb_restore to restore data reliably from a column of a given type to a column that uses a “larger” type. This is sometimes referred to as attribute promotion. For example, MySQL Cluster backup data that originated in a SMALLINT column can be restored to a MEDIUMINT, INT, or BIGINT column. See Section 17.4.17, “ndb_restore — Restore a MySQL Cluster Backup”, for more information.

Parallel data node recovery.  Recovery of multiple data nodes can now be done in parallel, rather than sequentially. In other words, several data nodes can be restored concurrently, which can often result in much faster recovery times than when they are restored one at a time.

Increased local checkpoint efficiency.  Only 2 local checkpoints are stored, rather than 3, lowering disk space requirements and the size and number of redo log files.

NDBCLUSTER table persistence control.  Persistence of NDB tables can be controlled using the session variables ndb_table_temporary and ndb_table_no_logging. ndb_table_no_logging causes NDB tables not to be checkpointed to disk; ndb_table_temporary does the same, and in addition, no schema files are created. See Section 17.3.4.1, “MySQL Cluster Server Option and Variable Reference”.

Epoll support (Linux only).  Epoll is an improved method for handling file descriptors, which is more efficient than scanning to determine whether a file descriptor has data to be read. (The term epoll is specific to Linux and equivalent functionality is known by other names on other platforms such as Solaris and FreeBSD.) Currently, MySQL Cluster supports this functionality on Linux only.

Distribution awareness (SQL nodes).  In MySQL Cluster NDB 6.3, SQL nodes can take advantage of distribution awareness. Here we provide a brief example showing how to design a table to make a given class of queries distrubtion-aware. Suppose an NDBCLUSTER table t1 has the following schema:

CREATE TABLE t1 (
    userid INT NOT NULL,
    serviceid INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    data VARCHAR(255)
)   ENGINE=NDBCLUSTER;

Suppose further that most of the queries to be used in our application test values of the userid column of this table. The form of such a query looks something like this:

SELECT columns FROM t1
    WHERE userid relation value;

In this query, relation represents some relational operator, such as =, <, >, and so on. Queries using IN and a list of values can also be used:

SELECT columns FROM t1
    WHERE userid IN value_list;

In order to make use of distribution awareness, we need to make the userid column part of the table's primary key, then explicitly partition the table with this column being used as the partitioning key. (Recall that for a partitioned table having one or more unique keys, all columns of the table's partitioning key must also be part of all of the unique keys — for more information and examples, see Section 18.5.1, “Partitioning Keys, Primary Keys, and Unique Keys”.) In other words, the table schema should be equivalent to the following CREATE TABLE statement:

CREATE TABLE t1 (
    userid INT NOT NULL,
    serviceid INT NOT NULL AUTO_INCREMENT,
    data VARCHAR(255),
    PRIMARY KEY p (userid,serviceid)
)   ENGINE=NDBCLUSTER
    PARTITION BY KEY(userid);

When the table is partitioned in this way, all rows having the same userid value are found on the same node group, and the MySQL Server can immediately select the optimal node to use as the transaction coordinator.

Realtime extensions for multiple CPUs.  When running MySQL Cluster data nodes on hosts with multiple processors, the realtime extensions make it possible to give priority to the data node process and control on which CPU cores it should operate. This can be done using the data node configuration parameters RealtimeScheduler, SchedulerExecutionTimer and SchedulerSpinTimer. Doing so properly can significantly lower response times and make them much more predictable response. For more information about using these parameters, see Defining Data Nodes: Realtime Performance Parameters

Fully automatic database discovery.  It is no longer a requirement for database autodiscovery that an SQL node already be connected to the cluster at the time that a database is created on another SQL node, or for a CREATE DATABASE or CREATE SCHEMA statement to be issued on the new SQL node after it joins the cluster.

Restoring specific databases, tables, or columns from a MySQL Cluster backup.  It is now possible to exercise more fine-grained control when restoring a MySQL Cluster from backup using ndb_restore. Beginning with MySQL Cluster NDB 6.3.22, you can choose to restore only specified tables or databases, or exclude specific tables or databases from being restored, using the new ndb_restore options --include-tables, --include-databases, --exclude-tables, and --exclude-databases. Beginning with MySQL Cluster NDB 6.3.26, it is also possible to restore to a table having fewer columns than the original using the --exclude-missing-columns option. For more information about all of these options, see Section 17.4.17, “ndb_restore — Restore a MySQL Cluster Backup”.

Improved Disk Data filesystem configuration.  As of MySQL Cluster NDB 6.3.22, you can specify default locations for MySQL Cluster Disk Data data files and undo log files using the data node configuration parameters FileSystemPathDD, FileSystemPathDataFiles, and FileSystemPathUndoFiles. This eliminates the need to use symbolic links in order to place Disk Data files separately from other files in data node filesystems to improve Disk Data performance. For more information, see Disk Data filesystem parameters.

Automatic creation of Disk Data log file groups and tablespaces.  Beginning with MySQL Cluster NDB 6.3.22, using the data node configuration parameters InitialLogFileGroup and InitialTablespace, you can cause the creation of a MySQL Cluster Disk Data log file group, tablespace, or both, when the cluster is first started. When using these parameters, no SQL statements are required to create these Disk Data objects. For more information, see Disk Data object creation parameters.

Configuration parameter data dumps.  Starting with MySQL Cluster NDB 6.3.25, the ndb_config utility supports a --configinfo option that causes it to dump a list of all configuration parameters supported by the cluster, along with brief descriptions, information about the parameters' default and allowed values, and the sections of the config.ini file in which the parameters apply. An additional --xml switch causes ndb_config to use XML rather than plaintext output. Using ndb_config --configinfo or ndb_config --configinfo --xml requires no access to a running MySQL Cluster, any other programs, or any files. For more information and examples, see Section 17.4.6, “ndb_config — Extract MySQL Cluster Configuration Information”.

Per-table reporting of free space on disk.  The INFORMATION_SCHEMA.FILES table shows information about used and free space in MySQL Cluster Disk Data data files, but this information is not applicable to individual tables. In MySQL Cluster NDB 6.3.27 and later, the ndb_desc utility provides two additional columns in its output that show the amount of space allocated on disk for a given NDB table as well the amount of space that remains available for additional storage of disk-based column data for that table. For more information, see Section 17.4.9, “ndb_desc — Describe NDB Tables”.

Improved restart times.  Optimizations in redo log handling and other filesystem operations introduced in MySQL Cluster NDB 6.3.28 have the potential to reduce considerably the time required for restarts. While actual performance benefits observed in production setups will naturally vary depending on database size, hardware, and other conditions, our own preliminary testing has shown that these improvements can yield startup times that are faster than those typical of previous MySQL Cluster NDB 6.3 releases by a factor of 50 or more.

Increased flexibility in online upgrade procedure.  Previously, when performing an upgrade of a running MySQL cluster, the order in which the types of cluster nodes had to be upgraded was very strict. However, beginning with MySQL Cluster NDB 6.3.29, MySQL Cluster supports online upgrading of API nodes (including MySQL servers running as SQL nodes) before upgrading management nodes, data nodes, or both.

New replication conflict resolution strategy.  Beginning with MySQL Cluster NDB 6.3.31, the function NDB$MAX_DELETE_WIN() is available to implement “greatest timestamp, delete wins” conflict resolution. See NDB$MAX_DELETE_WIN(column_name), for more information.

Heartbeat thread policy and priority.  Beginning with MySQL Cluster NDB 6.3.32, a new configuration parameter HeartbeatThreadPriority makes it possible to set the policy and the priority for the heartbeat thread on management and API nodes.

MySQL Cluster on Windows (alpha).  MySQL Cluster is now available on an experimental basis for Windows operating systems. Features and behavior comparable to those found on platforms that are already supported — such as Linux and Solaris — are planned for MySQL Cluster on Windows. Currently, you must build from source, although we intend to start making Windows binaries available in the near future. To enable MySQL Cluster support on Windows, you must configure the build using the WITH_NDBCLUSTER_STORAGE_ENGINE option. For more information, see Section 2.5.10, “Installing MySQL from Source on Windows”.

Ability to add nodes and node groups online.  Beginning with MySQL Cluster NDB 6.4.0, it is possible to add new node groups (and thus new data nodes) to a running MySQL Cluster without shutting down and reloading the cluster. As part of enabling this feature, a new command CREATE NODEGROUP has been added to the cluster management client and the functionality of the ALTER ONLINE TABLE ... REORGANIZE PARTITION SQL statement has been extended. For more information, see Section 17.5.11, “Adding MySQL Cluster Data Nodes Online”.

Data node multithreading support.  Beginning with MySQL Cluster NDB 6.4.0, a multithreaded version of the data node daemon, named ndbmtd, is available for use on data node hosts with multiple CPU cores. This binary is built automatically when compiling with MySQL Cluster support; no additional options other than those needed to provide MySQL Cluster support are needed when configuring the build. In most respects, ndbmtd functions in the same way as ndbd, and can use the same command-line options and configuration parameters. In addition, the new MaxNoOfExecutionThreads configuration parameter can be used to determine the number of data node process threads for ndbmtd. For more information, see Section 17.4.3, “ndbmtd — The MySQL Cluster Data Node Daemon (Multi-Threaded)”.

Configuration cache.  Formerly, MySQL Cluster configuration was stateless — that is, configuration information was reloaded from the cluster's global configuration file (usually config.ini) each time ndb_mgmd was started. Beginning with MySQL Cluster NDB 6.4.0, the cluster's configuration is cached internally, and the global configuration file is no longer automatically re-read when the management server is restarted. This behavior can be controlled via the three new management server options --configdir, --initial, and --reload. For more information about this change, see Section 17.3.2, “MySQL Cluster Configuration Files”. For more information about the new management server options, see Section 17.4.4, “ndb_mgmd — The MySQL Cluster Management Server Daemon”.

Snapshot options for backups.  Beginning with MySQL Cluster NDB 6.4.0, you can determine when performing a cluster backup whether the backup matches the state of the data when the backup was started or when it was completed, using the new options SNAPSHOTSTART and SNAPSHOTEND for the management client's START BACKUP command. See Section 17.5.3.2, “Using The MySQL Cluster Management Client to Create a Backup”, for more information.

Dynamic NDB transporter send buffer memory allocation.  Previously, the NDB kernel used a fixed-size send buffer for every data node in the cluster, which was allocated when the node started. Because the size of this buffer could not be changed after the cluster was started, it was necessary to make it large enough in advance to accomodate the maximum possible load on any transporter socket. However, this was an inefficient use of memory, since much of it often went unused. Beginning with MySQL Cluster NDB 6.4.0, send buffer memory is allocated dynamically from a memory pool shared between all transporters, which means that the size of the send buffer can be adjusted as necessary. This change is reflected by the addition of the configuration parameters TotalSendBufferMemory, ReservedSendBufferMemory, and OverLoadLimit, as well as a change in how the existing SendBufferMemory configuration parameter is used. For more information, see Section 17.3.2.13, “Configuring MySQL Cluster Send Buffer Parameters”.

Robust DDL operations.  Beginning with MySQL Cluster NDB 6.4.0, DDL operations (such as CREATE TABLE or ALTER TABLE) are protected from data node failures; in the event of a data node failure, such operations are now rolled back gracefully. Previously, if a data node failed while trying to perform a DDL operation, the MySQL Cluster data dictionary became locked and no further DDL statements could be executed without restarting the cluster.

IPv6 support in MySQL Cluster Replication.  Beginning with MySQL Cluster NDB 6.4.1, IPv6 networking is supported between MySQL Cluster SQL nodes, which makes it possible to replicate between instances of MySQL Cluster using IPv6 addresses. However, IPv6 is supported only for direct connections between MySQL servers; all connections within an individual MySQL Cluster must use IPv4. For more information, see Section 17.6.3, “Known Issues in MySQL Cluster Replication”.

Restoring specific databases, tables, or columns from a MySQL Cluster backup.  It is now possible to exercise more fine-grained control when restoring a MySQL Cluster from backup using ndb_restore. Beginning with MySQL Cluster NDB 6.4.3, you can choose to restore only specified tables or databases, or exclude specific tables or databases from being restored, using the new ndb_restore options --include-tables, --include-databases, --exclude-tables, and --exclude-databases. Beginning with MySQL Cluster NDB 7.0.7, it is also possible to restore to a table having fewer columns than the original using the --exclude-missing-columns option. For more information about all of these options, see Section 17.4.17, “ndb_restore — Restore a MySQL Cluster Backup”.

Improved Disk Data filesystem configuration.  As of MySQL Cluster NDB 6.4.3, you can specify default locations for MySQL Cluster Disk Data data files and undo log files using the data node configuration parameters FileSystemPathDD, FileSystemPathDataFiles, and FileSystemPathUndoFiles. This eliminates the need to use symbolic links in order to place Disk Data files separately from other files in data node filesystems to improve Disk Data performance. For more information, see Disk Data filesystem parameters.

Automatic creation of Disk Data log file groups and tablespaces.  Beginning with MySQL Cluster NDB 6.4.3, using the data node configuration parameters InitialLogFileGroup and InitialTablespace, you can cause the creation of a MySQL Cluster Disk Data log file group, tablespace, or both, when the cluster is first started. When using these parameters, no SQL statements are required to create these Disk Data objects. For more information, see Disk Data object creation parameters.

Improved internal message passing and record handling.  MySQL Cluster NDB 7.0 contains 2 changes that optimize the use of network connections by addressing the size and number of messages passed between data nodes, and between data nodes and API nodes, which can increase MySQL Cluster and application performance:

Both of these optimizations are internal to the NDB API, and so is transparent to applications; this is true whether an application uses the NDB API directly or does so indirectly through an SQL node.

Configuration parameter data dumps.  Starting with MySQL Cluster NDB 7.0.6, the ndb_config utility supports a --configinfo option that causes it to dump a list of all configuration parameters supported by the cluster, along with brief descriptions, information about the parameters' default and allowed values, and the sections of the config.ini file in which the parameters apply. An additional --xml switch causes ndb_config to use XML rather than plaintext output. Using ndb_config --configinfo or ndb_config --configinfo --xml requires no access to a running MySQL Cluster, any other programs, or any files. For more information and examples, see Section 17.4.6, “ndb_config — Extract MySQL Cluster Configuration Information”.

Per-table reporting of free space on disk.  The INFORMATION_SCHEMA.FILES table shows information about used and free space in MySQL Cluster Disk Data data files, but this information is not applicable to individual tables. In MySQL Cluster NDB 7.0.8 and later, the ndb_desc utility provides two additional columns in its output that show the amount of space allocated on disk for a given NDB table as well the amount of space that remains available for additional storage of disk-based column data for that table. For more information, see Section 17.4.9, “ndb_desc — Describe NDB Tables”.

Improved restart times.  Optimizations in redo log handling and other filesystem operations introduced in MySQL Cluster NDB 7.0.9 have the potential to reduce considerably the time required for restarts. While actual performance benefits observed in production setups will naturally vary depending on database size, hardware, and other conditions, our own preliminary testing has shown that these improvements can yield startup times that are faster than those typical of previous MySQL Cluster NDB 7.0 releases by a factor of 50 or more.

--nowait-nodes option for management servers.  Starting with MySQL Cluster NDB 7.0.10, it is possible to configure a cluster with two management servers, but to start the cluster using only one of them by starting the management node daemon with the --nowait-nodes option. The other management server can then be started at a later time to join the running MySQL Cluster.

Increased flexibility in online upgrade procedure.  Previously, when performing an upgrade of a running MySQL cluster, the order in which the types of cluster nodes had to be upgraded was very strict. However, beginning with MySQL Cluster NDB 7.0.10, MySQL Cluster supports online upgrading of API nodes (including MySQL servers running as SQL nodes) online upgrading management nodes, data nodes, or both.

New replication conflict resolution strategy.  Beginning with MySQL Cluster NDB 7.0.11, the function NDB$MAX_DELETE_WIN() is available to implement “greatest timestamp, delete wins” conflict resolution. See NDB$MAX_DELETE_WIN(column_name), for more information.

Improved lock handling for primary key lookups on BLOB tables.  A MySQL Cluster table stores all but the first 256 bytes of any BLOB or TEXT column values in a separate BLOB table; when executing queries against such tables, a shared lock is obtained. Previously, when the query used a primary key lookup and took place within a transaction, the lock was held for the duration of the transaction, even after no more data was being read from the NDB table. Now in such cases, the lock is released when all BLOB data associated with the table has been read. (Bug#49190)

Heartbeat thread policy and priority.  Beginning with MySQL Cluster NDB 7.0.13, a new configuration parameter HeartbeatThreadPriority makes it possible to set the policy and the priority for the heartbeat thread on management and API nodes.

Java connectors for MySQL Cluster.  The MySQL Cluster distribution now includes 2 new Java user APIs, ClusterJ and ClusterJPA. ClusterJ is an object-relational interface in a manner similar to that of Java persistence frameworks such as Hibernate. Cluster JPA is a reimplementation of OpenJPA. ClusterJ uses a backend library (NdbJTie) that provides access to the NDB storage engine without using a MySQL Server connection or JDBC. ClusterJPA also uses NdbJTie when it improves performance, but can also process complex queries using JDBC and a MySQL Server connection, where it can take advantage of the MySQL query optimizer.

ClusterJ and Cluster JPA can also be made to work with recent MySQL Cluster NDB 7.0 releases although the necessary library and JAR files are included only in MySQL Cluster NDB 7.1.1 and later.

MySQL Cluster information database (ndbinfo).  The ndbinfo information database makes it possible to obtain real-time characteristics of a MySQL Cluster by issuing queries from the mysql client or other MySQL client applications. ndbinfo provides metadata specific to MySQL Cluster similarly to how the INFORMATION_SCHEMA database provides metadata for the standard MySQL Server. This eliminates much of the need to read log files, issue REPORT or DUMP commands in the ndb_mgm client, or parse the output of ndb_config in order to get configuration and status information from a running MySQL Cluster.

For more information, see Section 17.5.8, “The ndbinfo MySQL Cluster Information Database”.

Native support for default column values.  Starting with MySQL Cluster NDB 7.1.0, default values for table columns are stored in the NDB kernel rather than by the MySQL server as was done previously. This means that inserts on tables having column value defaults can be smaller and faster than before, because less data must be sent from SQL nodes to NDBCLUSTER.

Tables created using previous MySQL Cluster releases can still be used in MySQL Cluster 7.1.0 and later; however, they do not support native default values until they are upgraded. You can upgrade a table with non-native default values to support native default values using an offline ALTER TABLE statement.

Further enhancements based on this work that we hope to implement in future MySQL Cluster releases include the following:

--nowait-nodes option for management servers.  It is now possible to configure a cluster with two management servers, but to start the cluster using only one of them by starting the management node daemon with the --nowait-nodes option. The other management server can then be started at a later time to join the running MySQL Cluster.

Heartbeat thread policy and priority.  Beginning with MySQL Cluster NDB 7.1.2, a new configuration parameter HeartbeatThreadPriority makes it possible to set the policy and the priority for the heartbeat thread on management and API nodes.

Temporary tables.  Temporary tables are not supported. Trying either to create a temporary table that uses the NDB storage engine or to alter an existing temporary table to use NDB fails with the error Table storage engine 'ndbcluster' does not support the create option 'TEMPORARY'.

Indexes and keys in NDB tables.  Keys and indexes on MySQL Cluster tables are subject to the following limitations:

MySQL Cluster and geometry data types.  Geometry datatypes (WKT and WKB) are supported in NDB tables in MySQL 5.1 (including MySQL Cluster NDB 6.X and 7.X through 7.1). However, spatial indexes are not supported.

Character sets and binary log files.  Currently, the ndb_apply_status and ndb_binlog_index tables are created using the latin1 (ASCII) character set. Because names of binary logs are recorded in this table, binary log files named using non-Latin characters are not referenced correctly in these tables. This is a known issue, which we are working to fix. (Bug#50226)

To work around this problem, use only Latin-1 characters when naming binary log files or setting any the --basedir, --log-bin, or --log-bin-index options.

Creating NDBCLUSTER tables with user-defined partitioning.  Support for user-defined partitioning for MySQL Cluster in MySQL 5.1 (including MySQL Cluster NDB 6.X and 7.X through 7.1) is restricted to [LINEAR] KEY partitioning. Beginning with MySQL 5.1.12, using any other partitioning type with ENGINE=NDB or ENGINE=NDBCLUSTER in a CREATE TABLE statement results in an error.

Default partitioning scheme.  As of MySQL 5.1.6, all MySQL Cluster tables are by default partitioned by KEY using the table's primary key as the partitioning key. If no primary key is explicitly set for the table, the “hidden” primary key automatically created by the NDBCLUSTER storage engine is used instead. For additional discussion of these and related issues, see Section 18.2.4, “KEY Partitioning”.

Beginning with MySQL Cluster NDB 6.2.18, MySQL Cluster NDB 6.3.25, and MySQL Cluster NDB 7.0.6, CREATE TABLE and ALTER TABLE statements that would cause a user-partitioned NDBCLUSTER table not to meet either or both of the following two requirements are disallowed, and fail with an error (Bug#40709):

Exception.  If a user-partitioned NDBCLUSTER table is created using an empty column-list (that is, using PARTITION BY [LINEAR] KEY()), then no explicit primary key is required.

Maximum number of partitions for NDBCLUSTER tables.  The maximum number of partitions that can defined for a NDBCLUSTER table when employing user-defined partitioning is 8 per node group. (See Section 17.1.2, “MySQL Cluster Nodes, Node Groups, Replicas, and Partitions”, for more information about MySQL Cluster node groups.

DROP PARTITION not supported.  It is not possible to drop partitions from NDB tables using ALTER TABLE ... DROP PARTITION. The other partitioning extensions to ALTER TABLE — ADD PARTITION, REORGANIZE PARTITION, and COALESCE PARTITION — are supported for Cluster tables, but use copying and so are not optimised. See Section 18.3.1, “Management of RANGE and LIST Partitions” and Section 12.1.7, “ALTER TABLE Syntax”.

Row-based replication.  When using row-based replication with MySQL Cluster, binary logging cannot be disabled. That is, the NDB storage engine ignores the value of sql_log_bin. (Bug#16680)

A DELETE statement on an NDB table makes the memory formerly used by the deleted rows available for re-use by inserts on the same table only. This memory cannot be used by other NDB tables.

A DROP TABLE or TRUNCATE TABLE operation on an NDB table frees the memory that was used by this table for re-use by any NDB table, either by the same table or by another NDB table.

Memory freed by DELETE operations but still allocated to a specific table can also be made available for general re-use by performing a rolling restart of the cluster. See Section 17.2.6.1, “Performing a Rolling Restart of a MySQL Cluster”.

Beginning with MySQL Cluster NDB 6.3.7, this limitation can be overcome using OPTIMIZE TABLE. See Section 17.1.5.11, “Previous MySQL Cluster Issues Resolved in MySQL 5.1, MySQL Cluster NDB 6.x, and MySQL Cluster NDB 7.x”, for more information.

Limits imposed by the cluster's configuration.  A number of hard limits exist which are configurable, but available main memory in the cluster sets limits. See the complete list of configuration parameters in Section 17.3.2, “MySQL Cluster Configuration Files”. Most configuration parameters can be upgraded online. These hard limits include:

Node and data object maximums.  The following limits apply to numbers of cluster nodes and metadata objects:

See Section 17.1.5.11, “Previous MySQL Cluster Issues Resolved in MySQL 5.1, MySQL Cluster NDB 6.x, and MySQL Cluster NDB 7.x”, for more information.

Transaction isolation level.  The NDBCLUSTER storage engine supports only the READ COMMITTED transaction isolation level. (InnoDB, for example, supports READ COMMITTED, READ UNCOMMITTED, REPEATABLE READ, and SERIALIZABLE.) See Section 17.5.3.4, “MySQL Cluster Backup Troubleshooting”, for information on how this can affect backing up and restoring Cluster databases.)

Transactions and BLOB or TEXT columns.  NDBCLUSTER stores only part of a column value that uses any of MySQL's BLOB or TEXT data types in the table visible to MySQL; the remainder of the BLOB or TEXT is stored in a separate internal table that is not accessible to MySQL. This gives rise to two related issues of which you should be aware whenever executing SELECT statements on tables that contain columns of these types:

Transactions and memory usage.  As noted elsewhere in this chapter, MySQL Cluster does not handle large transactions well; it is better to perform a number of small transactions with a few operations each than to attempt a single large transaction containing a great many operations. Among other considerations, large transactions require very large amounts of memory. Because of this, the transactional behavior of a number of MySQL statements is effected as described in the following list:

Transactions and the COUNT() function.  When using MySQL Cluster Replication, it is not possible to guarantee the transactional consistency of the COUNT() function on the slave. In other words, when performing on the master a series of statements (INSERT, DELETE, or both) that changes the number of rows in a table within a single transaction, executing SELECT COUNT(*) FROM table queries on the slave may yield intermediate results. This is due to the fact that SELECT COUNT(...) may perform dirty reads, and is not a bug in the NDB storage engine. (See Bug#31321 for more information.)

Temporary errors.  When first starting a node, it is possible that you may see Error 1204 Temporary failure, distribution changed and similar temporary errors.

Errors due to node failure.  The stopping or failure of any data node can result in a number of different node failure errors. (However, there should be no aborted transactions when performing a planned shutdown of the cluster.)

Identifiers.  Database names, table names and attribute names cannot be as long in NDB tables as when using other table handlers. Attribute names are truncated to 31 characters, and if not unique after truncation give rise to errors. Database names and table names can total a maximum of 122 characters. In other words, the maximum length for an NDB table name is 122 characters, less the number of characters in the name of the database of which that table is a part.

Table names containing special characters.  NDB tables whose names contain characters other than letters, numbers, dashes, and underscores and which are created on one SQL node were not always discovered correctly by other SQL nodes. (Bug#31470)

Number of database objects.  The maximum number of all NDB database objects in a single MySQL Cluster — including databases, tables, and indexes — is limited to 20320.

Attributes per table.  The maximum number of attributes (that is, columns and indexes) per table is limited to 128.

Attributes per key.  The maximum number of attributes per key is 32.

Row size.  The maximum permitted size of any one row is 8KB. Note that each BLOB or TEXT column contributes 256 + 8 = 264 bytes towards this total.

Foreign key constraints.  The foreign key construct is ignored, just as it is in MyISAM tables.

Index prefixes.  Prefixes on indexes are not supported for NDBCLUSTER tables. If a prefix is used as part of an index specification in a statement such as CREATE TABLE, ALTER TABLE, or CREATE INDEX, the prefix is ignored.

OPTIMIZE operations.  OPTIMIZE operations are not supported.

Beginning with MySQL Cluster NDB 6.3.7, this limitation has been lifted. See Section 17.1.5.11, “Previous MySQL Cluster Issues Resolved in MySQL 5.1, MySQL Cluster NDB 6.x, and MySQL Cluster NDB 7.x”, for more information.

LOAD TABLE ... FROM MASTER.  LOAD TABLE ... FROM MASTER is not supported.

Savepoints and rollbacks.  Savepoints and rollbacks to savepoints are ignored as in MyISAM.

Durability of commits.  There are no durable commits on disk. Commits are replicated, but there is no guarantee that logs are flushed to disk on commit.

Replication.  Statement-based replication is not supported. Use --binlog-format=ROW (or --binlog-format=MIXED) when setting up cluster replication. See Section 17.6, “MySQL Cluster Replication”, for more information.

InnoDB plugin not supported.  Currently, MySQL Cluster is not compatible with the InnoDB Plugin. You must use the version of InnoDB that is supplied with the MySQL Server. See Section 17.2.1, “MySQL Cluster Multi-Computer Installation”, for information about enabling InnoDB storage engine support with MySQL Cluster.

Range scans.  There are query performance issues due to sequential access to the NDB storage engine; it is also relatively more expensive to do many range scans than it is with either MyISAM or InnoDB.

Reliability of Records in range.  The Records in range statistic is available but is not completely tested or officially supported. This may result in nonoptimal query plans in some cases. If necessary, you can employ USE INDEX or FORCE INDEX to alter the execution plan. See Section 12.2.8.2, “Index Hint Syntax”, for more information on how to do this.

Unique hash indexes.  Unique hash indexes created with USING HASH cannot be used for accessing a table if NULL is given as part of the key.

Machine architecture.  All machines used in the cluster must have the same architecture. That is, all machines hosting nodes must be either big-endian or little-endian, and you cannot use a mixture of both. For example, you cannot have a management node running on a PowerPC which directs a data node that is running on an x86 machine. This restriction does not apply to machines simply running mysql or other clients that may be accessing the cluster's SQL nodes.

Binary logging.  MySQL Cluster has the following limitations or restrictions with regard to binary logging:

Maximum number of tablespaces: 2³² (4294967296)

Maximum number of data files per tablespace: 2¹⁶ (65535)

The theoretical maximum number of extents per tablespace data file is 2¹⁶ (65525); however, for practical purposes, the recommended maximum number of extents per data file is 2⁸ (32768).

Maximum data file size: The theoretical limit is 64G; however, in MySQL 5.1 (including MySQL Cluster NDB 6.X and 7.X through 7.1), the practical upper limit is 32G. This is equivalent to 32768 extents of 1M each.

The minimum and maximum possible sizes of extents for tablespace data files are 32K and 2G, respectively. See Section 12.1.18, “CREATE TABLESPACE Syntax”, for more information.

No distributed table locks.  A LOCK TABLES works only for the SQL node on which the lock is issued; no other SQL node in the cluster “sees” this lock. This is also true for a lock issued by any statement that locks tables as part of its operations. (See next item for an example.)

ALTER TABLE operations.  ALTER TABLE is not fully locking when running multiple MySQL servers (SQL nodes). (As discussed in the previous item, MySQL Cluster does not support distributed table locks.)

You must give nodes explicit IDs in connectstrings because automatic allocation of node IDs does not work across multiple management servers.

You must take extreme care to have the same configurations for all management servers. No special checks for this are performed by the cluster.

Variable-length column support.  The NDBCLUSTER storage engine now supports variable-length column types for in-memory tables.

Previously, for example, any Cluster table having one or more VARCHAR fields which contained only relatively small values, much more memory and disk space were required when using the NDBCLUSTER storage engine than would have been the case for the same table and data using the MyISAM engine. In other words, in the case of a VARCHAR column, such a column required the same amount of storage as a CHAR column of the same size. In MySQL 5.1, this is no longer the case for in-memory tables, where storage requirements for variable-length column types such as VARCHAR and BINARY are comparable to those for these column types when used in MyISAM tables (see Section 10.5, “Data Type Storage Requirements”).

Replication with MySQL Cluster.  It is now possible to use MySQL replication with Cluster databases. For details, see Section 17.6, “MySQL Cluster Replication”.

Circular Replication.  Circular replication is also supported with MySQL Cluster, beginning with MySQL 5.1.18. See Section 17.6.10, “MySQL Cluster Replication — Multi-Master and Circular Replication”.

auto_increment_increment and auto_increment_offset.  The auto_increment_increment and auto_increment_offset server system variables are supported for Cluster replication beginning with MySQL 5.1.20, MySQL Cluster NDB 6.2.5, and MySQL Cluster 6.3.2.

Database autodiscovery and online schema changes.  Autodiscovery of databases is now supported for multiple MySQL servers accessing the same MySQL Cluster. Formerly, autodiscovery in MySQL Cluster 5.1 and MySQL Cluster NDB 6.x releases required that a given mysqld was already running and connected to the cluster at the time that the database was created on a different mysqld — in other words, when a mysqld process connected to the cluster after a database named db_name was created, it was necessary to issue a CREATE DATABASE db_name or CREATE SCHEMA db_name statement on the “new” MySQL server when it first accesseed that MySQL Cluster. Beginning with MySQL Cluster NDB 6.2.16 and MySQL Cluster NDB 6.3.18, such a CREATE statement is no longer required. (Bug#39612)

This also means that online schema changes in NDB tables are now possible. That is, the result of operations such as ALTER TABLE and CREATE INDEX performed on one SQL node in the cluster are now visible to the cluster's other SQL nodes without any additional action being taken.

Backup and restore between architectures.  Beginning with MySQL 5.1.10, it is possible to perform a Cluster backup and restore between different architectures. Previously — for example — you could not back up a cluster running on a big-endian platform and then restore from that backup to a cluster running on a little-endian system. (Bug#19255)

Character set directory.  Beginning with MySQL 5.1.10, it is possible to install MySQL with Cluster support to a nondefault location and change the search path for font description files using either the --basedir or --character-sets-dir options. (Previously, ndbd in MySQL 5.1 searched only the default path — typically /usr/local/mysql/share/mysql/charsets — for character sets.)

Multiple management servers.  In MySQL 5.1 (including all MySQL Cluster NDB 6.x versions), it is no longer necessary, when running multiple management servers, to restart all the cluster's data nodes to enable the management nodes to see one another.

Also, when using multiple management servers and starting concurrently several API nodes (possibly including one or more SQL nodes) whose connectstrings listed the management servers in different order, it was possible for 2 API nodes to be assigned the same node ID. This issue is resolved in MySQL Cluster NDB 6.2.17, 6.3.23, and 6.4.3. (Bug#42973)

Multiple data node processes per host.  Beginning with MySQL Cluster NDB 6.2.0, you can use multiple data node processes on a single host. (In MySQL Cluster NDB 6.1, MySQL 5.1, and earlier release series, we did not support production MySQL Cluster deployments in which more than one ndbd process was run on a single physical machine.)

In addition, MySQL Cluster NDB 7.0 introduces support for multi-threaded data nodes (ndbmtd). See Section 17.1.4.5, “MySQL Cluster Development in MySQL Cluster NDB 7.0”, and Section 17.4.3, “ndbmtd — The MySQL Cluster Data Node Daemon (Multi-Threaded)”, for more information.

Length of CREATE TABLE statements.  CREATE TABLE statements may be no more than 4096 characters in length. This limitation affects MySQL 5.1.6, 5.1.7, and 5.1.8 only. (See Bug#17813)

IGNORE and REPLACE functionality.  In MySQL 5.1.7 and earlier, INSERT IGNORE, UPDATE IGNORE, and REPLACE were supported only for primary keys, but not for unique keys. It was possible to work around this issue by removing the constraint, then dropping the unique index, performing any inserts, and then adding the unique index again.

This limitation was removed for INSERT IGNORE and REPLACE in MySQL 5.1.8. (See Bug#17431.)

AUTO_INCREMENT columns.  In MySQL 5.1.10 and earlier versions, the maximum number of tables having AUTO_INCREMENT columns — including those belonging to hidden primary keys — was 2048.

This limitation was lifted in MySQL 5.1.11.

Maximum number of cluster nodes.  Prior to MySQL Cluster NDB 6.1.1, the total maximum number of nodes in a MySQL Cluster was 63, including all SQL nodes (MySQL Servers), API nodes (applications accessing the cluster other than MySQL servers), data nodes, and management servers.

Starting with MySQL Cluster NDB 6.1.1, the total maximum number of nodes in a MySQL Cluster is 255, including all SQL nodes (MySQL Servers), API nodes (applications accessing the cluster other than MySQL servers), data nodes, and management servers. The total number of data nodes and management nodes beginning with this version is 63, of which up to 48 can be data nodes.

Recovery of memory from deleted rows.  Beginning with MySQL Cluster NDB 6.3.7, memory can be reclaimed from an NDB table for reuse with any NDB table by employing OPTIMIZE TABLE, subject to the following limitations:

You can regulate the effects of OPTIMIZE on performance by adjusting the value of the global system variable ndb_optimization_delay, which sets the number of milliseconds to wait between batches of rows being processed by OPTIMIZE. The default value is 10 milliseconds. It is possible to set a lower value (to a minimum of 0), but not recommended. The maximum is 100000 milliseconds (that is, 100 seconds).

Implicit Rollbacks.  Prior to MySQL Cluster NDB 6.2.17 and MySQL Cluster NDB 6.3.19, MySQL Cluster did not automtically roll back a transaction that was aborted by a duplicate key or similar error, and subsequent statements raised ERROR 1296 (HY000): Got error 4350 'Transaction already aborted' from NDBCLUSTER. In such cases, it was necessary to issue an explicit ROLLBACK statement first, and then to retry the entire transaction.

Beginning with MySQL Cluster NDB 6.2.17 and MySQL Cluster NDB 6.3.19, this limitation has been removed; now, an error which causes a transaction to be aborted generates an implicit rollback of the entire transaction. This is logged with the warning Storage engine NDB does not support rollback for this statement. Transaction rolled back and must be restarted. A statement subsequent to this starts a new transaction. (Bug#32656)

Number of tables.  Previously, the maximum number of NDBCLUSTER tables in a single MySQL Cluster was 1792, but this is no longer the case in MySQL 5.1 and later MySQL Cluster releases. However, the number of tables is still included in the total maximum number of NDBCLUSTER database objects (20320). (See Section 17.1.5.5, “Limits Associated with Database Objects in MySQL Cluster”.)

DDL operations.  Beginning with MySQL Cluster NDB 6.4.0, DDL operations (such as CREATE TABLE or ALTER TABLE) are protected from data node failures. Previously, if a data node failed while trying to perform one of these, the data dictionary became locked and no further DDL statements could be executed without restarting the cluster (Bug#36718).

Adding and dropping of data nodes.  In MySQL Cluster NDB 6.3 and previous versions of MySQL Cluster, the online adding or dropping of data nodes was not possible; such operations required a complete shutdown and restart of the entire cluster. In MySQL Cluster NDB 7.0 (beginning with MySQL Cluster NDB 6.4.0) and later MySQL Cluster release series, it is possible to add new data nodes to a running MySQL Cluster by performing a rolling restart, so that the cluster and the data stored in it remain available to applications.

When planning to increase the number of data nodes in the cluster online in MySQL Cluster NDB 7.0 or MySQL Cluster NDB 7.1, you should be aware of and take into account the following issues:

For more information, see Section 17.5.11, “Adding MySQL Cluster Data Nodes Online”.

Native support for default column values.  Starting with MySQL Cluster NDB 7.1.0, default values for table columns are stored by NDBCLUSTER, rather than by the MySQL server as was previously the case. Because less data must be sent from an SQL node to the data nodes, inserts on tables having column value defaults can be performed more efficiently than before.

Tables created using previous MySQL Cluster releases can still be used in MySQL Cluster 7.1.0 and later, although they do not support native default values and continue to use defaults supplied by the MySQL server until they are upgraded. This can be done by means of an offline ALTER TABLE statement.

Cluster configuration change.  To make a change in the cluster's configuration, such as adding an SQL node to the cluster, or setting a configuration parameter to a new value.

Cluster software upgrade/downgrade.  To upgrade the cluster to a newer version of the MySQL Cluster software (or to downgrade it to an older version). This is usually referred to as a “rolling upgrade” (or “rolling downgrade”, when reverting to an older version of MySQL Cluster).

Change on node host.  To make changes in the hardware or operating system on which one or more cluster nodes are running.

Cluster reset.  To reset the cluster because it has reached an undesirable state. In such cases it is often desirable to reload the data and metadata of one or more data nodes. This can be done 1 of 3 ways:

Freeing of resources.  To allow memory allocated to a table by successive INSERT and DELETE operations to be freed for re-use by other MySQL Cluster tables.

This functionality is intended for use as part of an online upgrade only. A mix of node binaries from different MySQL Cluster releases is neither intended nor supported for constant, long-term use in a production setting.

All management nodes must be upgraded before any data nodes are upgraded. This remains true regardless of the order in which you upgrade the cluster's API and SQL nodes.

For MySQL Cluster NDB 6.3, the ability to upgrade API nodes in any order relative to upgading management nodes and data nodes is supported only for MySQL Cluster NDB 6.3.29 and later; for MySQL Cluster NDB 7.0, it supported only for MySQL Cluster NDB 7.0.10 and later. This means that, if you are upgrading from a MySQL Cluster NDB 6.3 release to a MySQL Cluster NDB 7.0 release, the “old” NDB engine version must be 6.3.29 or later, and the “new” NDB engine version must be 7.0.10 or later.

When upgrading the cluster from a MySQL Cluster NDB 6.3 release to a MySQL Cluster NDB 7.0 release: Once you have started upgraded the API nodes, you should not perform DDL operations until all management nodes and data nodes have been upgraded. DML operations should be unaffected, and can continue while the upgrade is in progress.

However, it is possible to perform DDL from an “old” (NDB 6.3 version) API node as long as the master data node is also running the “old” version of MySQL Cluster. You should keep in mind that a data node restart could result in the master node running a “new” (NDB 7.0 version) binary while one or more data nodes are still using the “old” (NDB 6.3) version; in this situation, no DDL can be performed from any API node, because the master data node is no longer using an NDB 6.3 binary, but the cluster still contains nodes which are not yet using NDB 7.0. For this reason, we recommend that you avoid performing DDL at any time while the upgrade is in progress.

Features specific to the “new” version must not be used until all management nodes and data nodes have been upgraded.

This also applies to any MySQL Server version change that may apply, in addition to the NDB engine version change, so do not forget to take this into account when planning the upgrade. (This is true for online upgrades of MySQL Cluster in general.)

MySQL 5.1.3 was the first public release in this series.

Direct upgrades or downgrades between MySQL Cluster 5.0 and 5.1 are not supported; you must dump all NDBCLUSTER tables using mysqldump, install the new version of the software, and then reload the tables from the dump.

You cannot downgrade a MySQL 5.1.6 or later Cluster using Disk Data tables to MySQL 5.1.5 or earlier unless you convert all such tables to in-memory Cluster tables first.

MySQL 5.1.8, MySQL 5.1.10, and MySQL 5.1.13 were not released.

Online cluster upgrades and downgrades between MySQL 5.1.11 (or an earlier version) and 5.1.12 (or a later version) are not possible due to major changes in the cluster file system. In such cases, you must perform a backup or dump, upgrade (or downgrade) the software, start each data node with --initial, and then restore from the backup or dump. You can use NDB backup/restore or mysqldump for this purpose.

Online downgrades from MySQL 5.1.14 or later to versions previous to 5.1.14 are not supported due to incompatible changes in the cluster system tables.

Online upgrades from MySQL 5.1.17 and earlier to 5.1.18 and later MySQL 5.1.x releases are not supported for clusters using replication due to incompatible changes in the mysql.ndb_apply_status table. (Online upgrades from MySQL 5.1 to MySQL Cluster NDB 6.2 and later are not supported, as discussed elsewhere in this section.) However, it should not be necessary to shut down the cluster entirely, if you follow this modified rolling restart procedure:

As with any other MySQL Cluster version upgrade, you should also update the MySQL Cluster management client (ndb_mgm) and other MySQL Cluster client programs such as ndb_config and ndb_error_reporter; however, this does not have to be done in any particular order.

The internal specifications for columns in NDBCLUSTER tables changed in MySQL 5.1.18 to allow compatibility with later MySQL Cluster releases that allow online adding and dropping of columns. This change is not backward-compatible with earlier MySQL versions.

In order to make tables created in MySQL 5.1.17 and earlier compatible with online adding and dropping of columns (available beginning with beginning with MySQL Cluster NDB 6.2.5 and MySQL Cluster NDB 6.3.3 — see Section 12.1.7, “ALTER TABLE Syntax”, for more information), it is necessary to force MySQL 5.1.18 and later to convert the tables to the new format by following this procedure:

This is not necessary for NDBCLUSTER tables created in MySQL 5.1.18 and later; such tables will automatically be compatible with online adding and dropping of columns (as implemented beginning with MySQL Cluster NDB 6.2.5 and MySQL Cluster NDB 6.3.2).

In order to minimise possible later difficulties, it is strongly advised that the procedure outlined above be followed as soon as possible after to upgrading from MySQL 5.1.17 or earlier to MySQL 5.1.18 or later.

Information about how this change effects users of MySQL Cluster NDB 6.x/7.0 is provided later in this section.

MySQL Cluster is not supported in standard MySQL 5.1 releases, beginning with MySQL 5.1.25. If you are using MySQL Cluster in a standard MySQL 5.1 release, you should upgrade to the most recent MySQL Cluster NDB 6.2 or 6.3 release.

MySQL Cluster NDB 6.1 is no longer in production; if you are still using a MySQL Cluster NDB 6.1 release, you should upgrade to the most recent MySQL Cluster NDB 6.2 or 6.3 as soon as possible.

It is not possible to upgrade from MySQL Cluster NDB 6.1.2 (or an older 6.1 release) directly to 6.1.4 or a newer NDB 6.1 release, or to downgrade from 6.1.4 (or a newer 6.1 release) directly to 6.1.2 or an older NDB 6.1 release; in either case, you must upgrade or downgrade to MySQL Cluster NDB 6.1.3 first.

It is not possible to perform an online downgrade from MySQL Cluster NDB 6.1.8 (or a newer 6.1 release) to MySQL Cluster NDB 6.1.7 (or an older 6.1 release).

MySQL Cluster NDB 6.1.6 and 6.1.18 were not released.

It is not possible to perform an online upgrade or downgrade between MySQL Cluster NDB 6.2 and any previous release series (including mainline MySQL 5.1 and MySQL Cluster NDB 6.1); it is necessary to perform a dump and reload. However, it should be possible to perform online upgrades or downgrades between any MySQL Cluster NDB 6.2 release and any MySQL Cluster NDB 6.3 release up to and including 6.3.7.

The internal specifications for columns in NDB tables changed in MySQL Cluster NDB 6.1.17 and 6.2.1 to allow compatibility with future MySQL Cluster releases that are expected to implement online adding and dropping of columns. This change is not backward-compatible with earlier MySQL or MySQL Cluster NDB 6.x versions.

In order to make tables created in earlier versions compatible with online adding and dropping of columns in later versions, it is necessary to force MySQL Cluster to convert the tables to the new format by following this procedure following an upgrade:

In order to minimise possible later difficulties, it is strongly advised that the procedure outlined above be followed as soon as possible after to upgrading between the versions indicated. The procedure is not necessary for NDBCLUSTER tables created in any of the following versions:

Tables created in the listed versions (or later ones, as indicated) are already compatible with online adding and dropping of columns (as implemented beginning with MySQL Cluster NDB 6.2.5 and MySQL Cluster NDB 6.3.2).

It was not possible to perform an online upgrade between any MySQL Cluster NDB 6.2 release and MySQL Cluster NDB 6.3.8 and later MySQL Cluster 6.3 releases. This issue was fixed in MySQL Cluster NDB 6.3.21. (Bug#41435)

Online downgrades between MySQL Cluster NDB 6.2.5 and earlier releases are not supported.

Online downgrades between MySQL Cluster NDB 6.3.8 and earlier releases are not supported.

Online upgrades from any MySQL Cluster NDB 7.0 release up to and including MySQL Cluster NDB 7.0.4 (as well as all early releases numbered NDB 6.4.x) to MySQL Cluster NDB 7.0.5 or later are not possible. Upgrades to MySQL Cluster NDB 7.0.6 or later from MySQL Cluster NDB 6.3.8 or a later MySQL Cluster NDB 6.3 release, or from MySQL Cluster NDB 7.0.5 or later, are supported. (Bug#44294)

When upgrading online from a MySQL Cluster NDB 6.3 release to a MySQL Cluster NDB 7.0 release, you should not try to upgrade the data nodes from ndbd to ndbmtd at the same time. Instead, perform the upgrade using the new ndbd executable (from the MySQL Cluster NDB 7.0.x distribution to which you are upgrading) to replace the one in use on the data nodes. Once the version upgrade is complete, you can perform a second (online) upgrade to replace the data node executables with ndbmtd from the MySQL Cluster NDB 7.0.x distribution.

In MySQL Cluster NDB 7.0.4, the default values for a number of MySQL Cluster configuration parameters relating to memory usage and buffering changed (see Section 17.7.2.13, “Changes in MySQL Cluster NDB 7.0.4 (5.1.32-ndb-7.0.4) (18 March 2008)”, for a list of the parameters whose defaults changed). For this reason, you may encounter issues if you try to use a configuration that does not explicitly define each of these buffers (because it was developed for a previous version of MySQL Cluster, SendBufferMemory and ReceiveBufferMemory in particular.

Prior to MySQL Cluster NDB 7.0.7, DML statements failed if executed while performing an online upgrade from a MySQL Cluster NDB 6.3 release. (Bug#45917)

Following an upgrade from any MySQL Cluster NDB 6.3.x release to MySQL Cluster NDB 7.0.6, DDL and backup operations failed. This issue was resolved in MySQL Cluster NDB 7.0.7. (Bug#46494, Bug#46563)

In some cases, there could be problems with online upgrades from MySQL Cluster NDB 6.3 releases to MySQL Cluster NDB 7.0 releases due to a previous change in the signalling format used between nodes. This issue was corrected in MySQL Cluster NDB 7.0.9.

Once an NDB table had an ALTER ONLINE TABLE operation performed on it using a MySQL Cluster NDB 6.3.x release, it could not be upgraded online to MySQL Cluster NDB 7.0. This issue was resolved in MySQL Cluster NDB 7.0.8. (See Bug#47542.)

Following an upgrade from MySQL Cluster NDB 6.3 to MySQL Cluster NDB 7.0, if there were any tables having unique indexes prior to the upgrade, attempts to create unique indexes failed. This could also occur when performing offline ALTER TABLE operations on tables having indexes that were not dropped as a result of the ALTER TABLE. This issue was due to a change in the way that NDB tracked unique indexes internally, and was resolved in MySQL Cluster NDB 7.0.9. (Bug#48416)

Workaround.  For upgrades to MySQL Cluster NDB 7.0 releases prior to version 7.0.9, a workaround are available: Following the upgrade, perform a second rolling restart of the cluster before before performing any ALTER TABLE operations involving indexes.

A table created in a previous version of MySQL Cluster does not automatically support NDB-native default values after the cluster is upgraded to MySQL Cluster NDB 7.1.0 or later. Such a table continues to use default values supplied by the MySQL server until it is upgraded by performing an offline ALTER TABLE on it.

When upgrading to a MySQL Cluster NDB 7.1 or later release from a MySQL Cluster NDB 7.0 release, you should not attempt to create any new tables until all data nodes are using the new ndbd or ndbmtd binary. This is because the older binaries do not provide support for native default values; tables created with native default value support cannot be used with NDB 7.0.x or earlier versions of the software.

Due to an issue discovered after the release of MySQL Cluster NDB 7.0.10 (Bug#50433), it is not possible to perform an online upgrade from MySQL Cluster NDB 7.0.9b and earlier MySQL Cluster NDB 7.0 releases to MySQL Cluster NDB 7.0.10. Instead, you should upgrade your MySQL Cluster NDB 7.0 cluster directly to MySQL Cluster NDB 7.0.11 or later.

This issue did not appear to affect MySQL Cluster NDB 6.3, and it should be possible to upgrade online from MySQL Cluster NDB 6.3 to MySQL Cluster NDB 7.0.10 without any problems other than those noted preciously.

[computer]: Defines cluster hosts. This is not required to configure a viable MySQL Cluster, but be may used as a convenience when setting up a large cluster. See Section 17.3.2.4, “Defining Computers in a MySQL Cluster”, for more information.

[ndbd]: Defines a cluster data node (ndbd process). See Section 17.3.2.6, “Defining MySQL Cluster Data Nodes”, for details.

[mysqld]: Defines the cluster's MySQL server nodes (also called SQL or API nodes). For a discussion of SQL node configuration, see Section 17.3.2.7, “Defining SQL and Other API Nodes in a MySQL Cluster”.

[mgm] or [ndb_mgmd]: Defines a cluster management server (MGM) node. For information concerning the configuration of MGM nodes, see Section 17.3.2.5, “Defining a MySQL Cluster Management Server”.

[tcp]: Defines a TCP/IP connection between cluster nodes, with TCP/IP being the default connection protocol. Normally, [tcp] or [tcp default] sections are not required to set up a MySQL Cluster, as the cluster handles this automatically; however, it may be necessary in some situations to override the defaults provided by the cluster. See Section 17.3.2.8, “MySQL Cluster TCP/IP Connections”, for information about available TCP/IP configuration parameters and how to use them. (You may also find Section 17.3.2.9, “MySQL Cluster TCP/IP Connections Using Direct Connections” to be of interest in some cases.)

[shm]: Defines shared-memory connections between nodes. In MySQL 5.1, it is enabled by default, but should still be considered experimental. For a discussion of SHM interconnects, see Section 17.3.2.10, “MySQL Cluster Shared-Memory Connections”.

[sci]:Defines Scalable Coherent Interface connections between cluster data nodes. Such connections require software which, while freely available, is not part of the MySQL Cluster distribution, as well as specialized hardware. See Section 17.3.2.11, “SCI Transport Connections in MySQL Cluster” for detailed information about SCI interconnects.

MySQL Cluster software version

Numbers of data nodes and SQL nodes

Hardware

Operating system

Amount of data to be stored

Size and type of load under which the cluster is to operate

--ndb-use-exact-count=0

--ndb-index-stat-enable=0

--ndb-force-send=1

--engine-condition-pushdown=1

Each executable has its own command-line option which enables specifying the management server at startup. (See the documentation for the respective executable.)

It is also possible to set the connectstring for all nodes in the cluster at once by placing it in a [mysql_cluster] section in the management server's my.cnf file.

For backward compatibility, two other options are available, using the same syntax:

However, these are now deprecated and should not be used for new installations.

Id

This is a unique identifier, used to refer to the host computer elsewhere in the configuration file.

HostName

This is the computer's hostname or IP address.

Id

Each node in the cluster has a unique identity. For a management node, this is represented by an integer value in the range 1 to 63 inclusive (previous to MySQL Cluster NDB 6.1.1), or in the range 1 to 255 inclusive (MySQL Cluster NDB 6.1.1 and later). This ID is used by all internal cluster messages for addressing the node, and so must be unique for each MySQL Cluster node, regardless of the type of node.

This parameter can also be written as NodeId, although the short form is sufficient (and preferred for this reason).

ExecuteOnComputer

This refers to the Id set for one of the computers defined in a [computer] section of the config.ini file.

PortNumber

This is the port number on which the management server listens for configuration requests and management commands.

HostName

Specifying this parameter defines the hostname of the computer on which the management node is to reside. To specify a hostname other than localhost, either this parameter or ExecuteOnComputer is required.

LogDestination

This parameter specifies where to send cluster logging information. There are three options in this regard — CONSOLE, SYSLOG, and FILE — with FILE being the default:

It is possible to specify multiple log destinations separated by semicolons as shown here:

CONSOLE;SYSLOG:facility=local0;FILE:filename=/var/log/mgmd

ArbitrationRank

This parameter is used to define which nodes can act as arbitrators. Only management nodes and SQL nodes can be arbitrators. ArbitrationRank can take one of the following values:

Normally, the management server should be configured as an arbitrator by setting its ArbitrationRank to 1 (the default for management nodes) and those for all SQL nodes to 0 (the default for SQL nodes).

Beginning with MySQL 5.1.16 and MySQL Cluster NDB 6.1.3, it is possible to disable arbitration completely by setting ArbitrationRank to 0 on all management and SQL nodes. In MySQL Cluster NDB 7.0.7 and later releases, you can also control arbitration by overriding this parameter; to do this, set the Arbitration parameter in the [ndbd default] section of the config.ini global configuration file.

ArbitrationDelay

An integer value which causes the management server's responses to arbitration requests to be delayed by that number of milliseconds. By default, this value is 0; it is normally not necessary to change it.

DataDir

This specifies the directory where output files from the management server will be placed. These files include cluster log files, process output files, and the daemon's process ID (PID) file. (For log files, this location can be overridden by setting the FILE parameter for LogDestination as discussed previously in this section.)

The default value for this parameter is the directory in which ndb_mgmd is located.

HeartbeatThreadPriority

Beginning with MySQL Cluster NDB 6.3.32, MySQL Cluster NDB 7.0.13, and MySQL Cluster NDB 7.1.2, it is possible to use this parameter to set the scheduling policy and priority of heartbeat threads for management and API nodes.

The syntax for setting this parameter is shown here:

HeartbeatThreadPriority = policy[, priority]

policy:
  {FIFO | RR}

When setting this parameter, you must specify a policy. This is one of FIFO (first in, first in) or RR (round robin). This followed optionally by the priority (an integer).

TotalSendBufferMemory

This parameter is available beginning with MySQL Cluster NDB 6.4.0. It is used to determine the total amount of memory to allocate on this node for shared send buffer memory among all configured transporters.

If this parameter is set, its minimum allowed value is 256K; the maxmimum is 4294967039. For more detailed information about the behavior and use of TotalSendBufferMemory and configuring send buffer memory parameters in MySQL Cluster NDB 6.4.0 and later, see Section 17.3.2.13, “Configuring MySQL Cluster Send Buffer Parameters”.

Either ExecuteOnComputer or HostName, which must be defined in the local [ndbd] section.

The parameter NoOfReplicas, which must be defined in the[ndbd default]section, as it is common to all Cluster data nodes.

Id

This is the node ID used as the address of the node for all cluster internal messages. For data nodes, this is an integer in the range 1 to 48 inclusive. Each node in the cluster must have a unique identifier.

This parameter can also be written as NodeId, although the short form is sufficient (and preferred for this reason).

ExecuteOnComputer

This refers to the Id set for one of the computers defined in a [computer] section.

HostName

Specifying this parameter defines the hostname of the computer on which the data node is to reside. To specify a hostname other than localhost, either this parameter or ExecuteOnComputer is required.

ServerPort

Each node in the cluster uses a port to connect to other nodes. By default, this port is allocated dynamically in such a way as to ensure that no two nodes on the same host computer receive the same port number, so it should normally not be necessary to specify a value for this parameter.

However, if you need to be able to open specific ports in a firewall to permit communication between data nodes and API nodes (including SQL nodes), you can set this parameter to the number of the desired port in an [ndbd] section or (if you need to do this for multiple data nodes) the [ndbd default] section of the config.ini file, and then open the port having that number for incoming connections from SQL nodes, API nodes, or both.

TcpBind_INADDR_ANY

Setting this parameter to TRUE or 1 binds IP_ADDR_ANY so that connections can be made from anywhere (for autogenerated connections). The default is FALSE (0).

This parameter was added in MySQL Cluster NDB 6.2.0.

NodeGroup

This parameter can be used to assign a data node to a specific node group. It is read only when the cluster is started for the first time, and cannot be used to reassign a data node to a different node group online. It is generally not desirable to use this parameter in the [ndbd default] section of the config.ini file, and care must be taken not to assign nodes to node groups in such a way that an invalid numbers of nodes are assigned to any node groups.

The NodeGroup parameter is chiefly intended for use in adding a new node group to a running MySQL Cluster without having to perform a rolling restart. For this purpose, you should set it to 65535 (the maximum value). You are not required to set a NodeGroup value for all cluster data nodes, only for those nodes which are to be started and added to the cluster as a new node group at a later time. For more information, see Section 17.5.11.3, “Adding MySQL Cluster Data Nodes Online: Detailed Example”.

This parameter was added in MySQL Cluster NDB 6.4.0.

NoOfReplicas

This global parameter can be set only in the [ndbd default] section, and defines the number of replicas for each table stored in the cluster. This parameter also specifies the size of node groups. A node group is a set of nodes all storing the same information.

Node groups are formed implicitly. The first node group is formed by the set of data nodes with the lowest node IDs, the next node group by the set of the next lowest node identities, and so on. By way of example, assume that we have 4 data nodes and that NoOfReplicas is set to 2. The four data nodes have node IDs 2, 3, 4 and 5. Then the first node group is formed from nodes 2 and 3, and the second node group by nodes 4 and 5. It is important to configure the cluster in such a manner that nodes in the same node groups are not placed on the same computer because a single hardware failure would cause the entire cluster to fail.

If no node IDs are provided, the order of the data nodes will be the determining factor for the node group. Whether or not explicit assignments are made, they can be viewed in the output of the management client's SHOW command.

Prior to MySQL Cluster NDB 6.3.25 and MySQL Cluster NDB 7.0.6, there was no default value for NoOfReplicas; beginning with these versions, the default value is 2, which is the recommended setting in most common usage scenarios. (Bug#44746)

The maximum possible value is 4; currently, only the values 1 and 2 are actually supported (see Bug#18621).

The value for this parameter must divide evenly into the number of data nodes in the cluster. For example, if there are two data nodes, then NoOfReplicas must be equal to either 1 or 2, since 2/3 and 2/4 both yield fractional values; if there are four data nodes, then NoOfReplicas must be equal to 1, 2, or 4.

DataDir

This parameter specifies the directory where trace files, log files, pid files and error logs are placed.

The default is the data node process working directory.

FileSystemPath

This parameter specifies the directory where all files created for metadata, REDO logs, UNDO logs (for Disk Data tables), and data files are placed. The default is the directory specified by DataDir.

The recommended directory hierarchy for MySQL Cluster includes /var/lib/mysql-cluster, under which a directory for the node's file system is created. The name of this subdirectory contains the node ID. For example, if the node ID is 2, this subdirectory is named ndb_2_fs.

BackupDataDir

This parameter specifies the directory in which backups are placed. If omitted, the default backup location is the directory named BACKUP under the location specified by the FileSystemPath parameter. (See above.)

DataMemory

This parameter defines the amount of space (in bytes) available for storing database records. The entire amount specified by this value is allocated in memory, so it is extremely important that the machine has sufficient physical memory to accommodate it.

The memory allocated by DataMemory is used to store both the actual records and indexes. There is a 16-byte overhead on each record; an additional amount for each record is incurred because it is stored in a 32KB page with 128 byte page overhead (see below). There is also a small amount wasted per page due to the fact that each record is stored in only one page.

For variable-size table attributes in MySQL 5.1, the data is stored on separate datapages, allocated from DataMemory. Variable-length records use a fixed-size part with an extra overhead of 4 bytes to reference the variable-size part. The variable-size part has 2 bytes overhead plus 2 bytes per attribute.

The maximum record size is currently 8052 bytes.

The memory space defined by DataMemory is also used to store ordered indexes, which use about 10 bytes per record. Each table row is represented in the ordered index. A common error among users is to assume that all indexes are stored in the memory allocated by IndexMemory, but this is not the case: Only primary key and unique hash indexes use this memory; ordered indexes use the memory allocated by DataMemory. However, creating a primary key or unique hash index also creates an ordered index on the same keys, unless you specify USING HASH in the index creation statement. This can be verified by running ndb_desc -d db_name table_name in the management client.

The memory space allocated by DataMemory consists of 32KB pages, which are allocated to table fragments. Each table is normally partitioned into the same number of fragments as there are data nodes in the cluster. Thus, for each node, there are the same number of fragments as are set in NoOfReplicas.

In addition, due to the way in which new pages are allocated when the capacity of the current page is exhausted, there is an additional overhead of approximately 18.75%. When more DataMemory is required, more than one new page is allocated, according to the following formula:

number of new pages = FLOOR(number of current pages ? 0.1875) + 1

For example, if 15 pages are currently allocated to a given table and an insert to this table requires additional storage space, the number of new pages allocated to the table is FLOOR(15 ? 0.1875) + 1 = FLOOR(2.8125) + 1 = 2 + 1 = 3. Now 15 + 3 = 18 memory pages are allocated to the table. When the last of these 18 pages becomes full, FLOOR(18 ? 0.1875) + 1 = FLOOR(3.3750) + 1 = 3 + 1 = 4 new pages are allocated, so the total number of pages allocated to the table is now 22.

Once a page has been allocated, it is currently not possible to return it to the pool of free pages, except by deleting the table. (This also means that DataMemory pages, once allocated to a given table, cannot be used by other tables.) Performing a node recovery also compresses the partition because all records are inserted into empty partitions from other live nodes.

The DataMemory memory space also contains UNDO information: For each update, a copy of the unaltered record is allocated in the DataMemory. There is also a reference to each copy in the ordered table indexes. Unique hash indexes are updated only when the unique index columns are updated, in which case a new entry in the index table is inserted and the old entry is deleted upon commit. For this reason, it is also necessary to allocate enough memory to handle the largest transactions performed by applications using the cluster. In any case, performing a few large transactions holds no advantage over using many smaller ones, for the following reasons:

The default value for DataMemory is 80MB; the minimum is 1MB. There is no maximum size, but in reality the maximum size has to be adapted so that the process does not start swapping when the limit is reached. This limit is determined by the amount of physical RAM available on the machine and by the amount of memory that the operating system may commit to any one process. 32-bit operating systems are generally limited to 2–4GB per process; 64-bit operating systems can use more. For large databases, it may be preferable to use a 64-bit operating system for this reason.

IndexMemory

This parameter controls the amount of storage used for hash indexes in MySQL Cluster. Hash indexes are always used for primary key indexes, unique indexes, and unique constraints. Note that when defining a primary key and a unique index, two indexes will be created, one of which is a hash index used for all tuple accesses as well as lock handling. It is also used to enforce unique constraints.

The size of the hash index is 25 bytes per record, plus the size of the primary key. For primary keys larger than 32 bytes another 8 bytes is added.

The default value for IndexMemory is 18MB. The minimum is 1MB.

StringMemory

This parameter determines how much memory is allocated for strings such as table names, and is specified in an [ndbd] or [ndbd default] section of the config.ini file. A value between 0 and 100 inclusive is interpreted as a percent of the maximum default value, which is calculated based on a number of factors including the number of tables, maximum table name size, maximum size of .FRM files, MaxNoOfTriggers, maximum column name size, and maximum default column value. In general it is safe to assume that the maximum default value is approximately 5 MB for a MySQL Cluster having 1000 tables.

A value greater than 100 is interpreted as a number of bytes.

Beginning with MySQL Cluster NDB 6.2.18, MySQL Cluster NDB 6.3.24, and MySQL Cluster NDB 7.0.5, the default value is 25 — that is, 25 percent of the default maximum, or approximately 25 KB. (Previously, the default value was 5 beginning with MySQL 5.1.6; prior to MySQL 5.1.6, the default was 0.)

Under most circumstances, the default value should be sufficient, but when you have a great many Cluster tables (1000 or more), it is possible to get Error 773 Out of string memory, please modify StringMemory config parameter: Permanent error: Schema error, in which case you should increase this value. 25 (25 percent) is not excessive, and should prevent this error from recurring in all but the most extreme conditions.

MaxNoOfConcurrentTransactions

Each cluster data node requires a transaction record for each active transaction in the cluster. The task of coordinating transactions is distributed among all of the data nodes. The total number of transaction records in the cluster is the number of transactions in any given node times the number of nodes in the cluster.

Transaction records are allocated to individual MySQL servers. Each connection to a MySQL server requires at least one transaction record, plus an additional transaction object per table accessed by that connection. This means that a reasonable minimum for this parameter is

MaxNoOfConcurrentTransactions =
    (maximum number of tables accessed in any single transaction + 1)
    * number of cluster SQL nodes

Suppose that there are 4 SQL nodes using the cluster. A single join involving 5 tables requires 6 transaction records; if there are 5 such joins in a transaction, then 5 * 6 = 30 transaction records are required for this transaction, per MySQL server, or 30 * 4 = 120 transaction records total.

This parameter must be set to the same value for all cluster data nodes. This is due to the fact that, when a data node fails, the oldest surviving node re-creates the transaction state of all transactions that were ongoing in the failed node.

Changing the value of MaxNoOfConcurrentTransactions requires a complete shutdown and restart of the cluster.

The default value is 4096.

MaxNoOfConcurrentOperations

It is a good idea to adjust the value of this parameter according to the size and number of transactions. When performing transactions of only a few operations each and not involving a great many records, there is no need to set this parameter very high. When performing large transactions involving many records need to set this parameter higher.

Records are kept for each transaction updating cluster data, both in the transaction coordinator and in the nodes where the actual updates are performed. These records contain state information needed to find UNDO records for rollback, lock queues, and other purposes.

This parameter should be set to the number of records to be updated simultaneously in transactions, divided by the number of cluster data nodes. For example, in a cluster which has four data nodes and which is expected to handle 1,000,000 concurrent updates using transactions, you should set this value to 1000000 / 4 = 250000.

Read queries which set locks also cause operation records to be created. Some extra space is allocated within individual nodes to accommodate cases where the distribution is not perfect over the nodes.

When queries make use of the unique hash index, there are actually two operation records used per record in the transaction. The first record represents the read in the index table and the second handles the operation on the base table.

The default value is 32768.

This parameter actually handles two values that can be configured separately. The first of these specifies how many operation records are to be placed with the transaction coordinator. The second part specifies how many operation records are to be local to the database.

A very large transaction performed on an eight-node cluster requires as many operation records in the transaction coordinator as there are reads, updates, and deletes involved in the transaction. However, the operation records of the are spread over all eight nodes. Thus, if it is necessary to configure the system for one very large transaction, it is a good idea to configure the two parts separately. MaxNoOfConcurrentOperations will always be used to calculate the number of operation records in the transaction coordinator portion of the node.

It is also important to have an idea of the memory requirements for operation records. These consume about 1KB per record.

MaxNoOfLocalOperations

By default, this parameter is calculated as 1.1 ? MaxNoOfConcurrentOperations. This fits systems with many simultaneous transactions, none of them being very large. If there is a need to handle one very large transaction at a time and there are many nodes, it is a good idea to override the default value by explicitly specifying this parameter.

MaxNoOfConcurrentIndexOperations

For queries using a unique hash index, another temporary set of operation records is used during a query's execution phase. This parameter sets the size of that pool of records. Thus, this record is allocated only while executing a part of a query. As soon as this part has been executed, the record is released. The state needed to handle aborts and commits is handled by the normal operation records, where the pool size is set by the parameter MaxNoOfConcurrentOperations.

The default value of this parameter is 8192. Only in rare cases of extremely high parallelism using unique hash indexes should it be necessary to increase this value. Using a smaller value is possible and can save memory if the DBA is certain that a high degree of parallelism is not required for the cluster.

MaxNoOfFiredTriggers

The default value of MaxNoOfFiredTriggers is 4000, which is sufficient for most situations. In some cases it can even be decreased if the DBA feels certain the need for parallelism in the cluster is not high.

A record is created when an operation is performed that affects a unique hash index. Inserting or deleting a record in a table with unique hash indexes or updating a column that is part of a unique hash index fires an insert or a delete in the index table. The resulting record is used to represent this index table operation while waiting for the original operation that fired it to complete. This operation is short-lived but can still require a large number of records in its pool for situations with many parallel write operations on a base table containing a set of unique hash indexes.

TransactionBufferMemory

The memory affected by this parameter is used for tracking operations fired when updating index tables and reading unique indexes. This memory is used to store the key and column information for these operations. It is only very rarely that the value for this parameter needs to be altered from the default.

The default value for TransactionBufferMemory is 1MB.

Normal read and write operations use a similar buffer, whose usage is even more short-lived. The compile-time parameter ZATTRBUF_FILESIZE (found in ndb/src/kernel/blocks/Dbtc/Dbtc.hpp) set to 4000 ? 128 bytes (500KB). A similar buffer for key information, ZDATABUF_FILESIZE (also in Dbtc.hpp) contains 4000 ? 16 = 62.5KB of buffer space. Dbtc is the module that handles transaction coordination.

MaxNoOfConcurrentScans

This parameter is used to control the number of parallel scans that can be performed in the cluster. Each transaction coordinator can handle the number of parallel scans defined for this parameter. Each scan query is performed by scanning all partitions in parallel. Each partition scan uses a scan record in the node where the partition is located, the number of records being the value of this parameter times the number of nodes. The cluster should be able to sustain MaxNoOfConcurrentScans scans concurrently from all nodes in the cluster.

Scans are actually performed in two cases. The first of these cases occurs when no hash or ordered indexes exists to handle the query, in which case the query is executed by performing a full table scan. The second case is encountered when there is no hash index to support the query but there is an ordered index. Using the ordered index means executing a parallel range scan. The order is kept on the local partitions only, so it is necessary to perform the index scan on all partitions.

The default value of MaxNoOfConcurrentScans is 256. The maximum value is 500.

MaxNoOfLocalScans

Specifies the number of local scan records if many scans are not fully parallelized. If the number of local scan records is not provided, it is calculated as the product of MaxNoOfConcurrentScans and the number of data nodes in the system. The minimum value is 32.

BatchSizePerLocalScan

This parameter is used to calculate the number of lock records used to handle concurrent scan operations.

The default value is 64; this value has a strong connection to the ScanBatchSize defined in the SQL nodes.

LongMessageBuffer

This is an internal buffer used for passing messages within individual nodes and between nodes. Although it is highly unlikely that this would need to be changed, it is configurable. In MySQL Cluster NDB 6.4.3 and earlier, the default is 1MB; beginning with MySQL Cluster NDB 7.0.4, it is 4MB.

NoOfFragmentLogFiles

This parameter sets the number of REDO log files for the node, and thus the amount of space allocated to REDO logging. Because the REDO log files are organized in a ring, it is extremely important that the first and last log files in the set (sometimes referred to as the “head” and “tail” log files, respectively) do not meet. When these approach one another too closely, the node begins aborting all transactions encompassing updates due to a lack of room for new log records.

A REDO log record is not removed until the required number of local checkpoints has been completed since that log record was inserted (prior to MySQL Cluster NDB 6.3.8, this was 3 local checkpoints; in later versions of MySQL Cluster, only 2 local checkpoints are necessary). Checkpointing frequency is determined by its own set of configuration parameters discussed elsewhere in this chapter.

How these parameters interact and proposals for how to configure them are discussed in Section 17.3.2.12, “Configuring MySQL Cluster Parameters for Local Checkpoints”.

The default parameter value is 16, which by default means 16 sets of 4 16MB files for a total of 1024MB. Beginning with MySQL Cluster NDB 6.1.1, the size of the individual log files is configurable using the FragmentLogFileSize parameter; more information about this parameter can be found here. In scenarios requiring a great many updates, the value for NoOfFragmentLogFiles may need to be set as high as 300 or even higher to provide sufficient space for REDO logs.

If the checkpointing is slow and there are so many writes to the database that the log files are full and the log tail cannot be cut without jeopardizing recovery, all updating transactions are aborted with internal error code 410 (Out of log file space temporarily). This condition prevails until a checkpoint has completed and the log tail can be moved forward.

FragmentLogFileSize

Setting this parameter allows you to control directly the size of redo log files. This can be useful in situations when MySQL Cluster is operating under a high load and it is unable to close fragment log files quickly enough before attempting to open new ones (only 2 fragment log files can be open at one time); increasing the size of the fragment log files gives the cluster more time before having to open each new fragment log file. The default value for this parameter is 16M. FragmentLogFileSize was added in MySQL Cluster NDB 6.1.11.

For more information about fragment log files, see the description of the NoOfFragmentLogFiles parameter.

InitFragmentLogFiles

By default, fragment log files are created sparsely when performing an initial start of a data node — that is, depending on the operating system and file system in use, not all bytes are necessarily written to disk. Beginning with MySQL Cluster NDB 6.3.19, it is possible to override this behavior and force all bytes to be written regardless of the platform and file system type being used by mean of this parameter.

InitFragmentLogFiles takes one of two values:

Depending on your operating system and file system, setting InitFragmentLogFiles=FULL may help eliminate I/O errors on writes to the REDO log.

MaxNoOfOpenFiles

This parameter sets a ceiling on how many internal threads to allocate for open files. Any situation requiring a change in this parameter should be reported as a bug.

The default value is 0. (Prior to MySQL 5.1.16, the default was 40.) However, the minimum value to which this parameter can be set is 20.

InitialNoOfOpenFiles

This parameter sets the initial number of internal threads to allocate for open files.

The default value is 27.

MaxNoOfSavedMessages

This parameter sets the maximum number of trace files that are kept before overwriting old ones. Trace files are generated when, for whatever reason, the node crashes.

The default is 25 trace files.

MaxLCPStartDelay

In parallel data node recovery (supported in MySQL Cluster NDB 6.3.8 and later), only table data is actually copied and synchronized in parallel; synchronization of metadata such as dictionary and checkpoint information is done in a serial fashion. In addition, recovery of dictionary and checkpoint information cannot be executed in parallel with performing of local checkpoints. This means that, when starting or restarting many data nodes concurrently, data nodes may be forced to wait while a local checkpoint is performed, which can result in longer node recovery times.

Beginning with MySQL Cluster NDB 6.3.23 and MySQL Cluster NDB 6.4.3, it is possible to force a delay in the local checkpoint to allow more (and possibly all) data nodes to complete metadata synchronization; once each data node's metadata synchronization is complete, all of the data nodes can recover table data in parallel, even while the local checkpoint is being executed.

To force such a delay, you can set MaxLCPStartDelay, which determines the number of seconds the cluster can wait to begin a local checkpoint while data nodes continue to synchronize metadata. This parameter should be set in the [ndbd default] section of the config.ini file, so that it is the same for all data nodes. The maximum value is 600; the default is 0.

MaxNoOfAttributes

Defines the number of attributes that can be defined in the cluster.

The default value is 1000, with the minimum possible value being 32. The maximum is 4294967039. Each attribute consumes around 200 bytes of storage per node due to the fact that all metadata is fully replicated on the servers.

When setting MaxNoOfAttributes, it is important to prepare in advance for any ALTER TABLE statements that you might want to perform in the future. This is due to the fact, during the execution of ALTER TABLE on a Cluster table, 3 times the number of attributes as in the original table are used, and a good practice is to allow double this amount. For example, if the MySQL Cluster table having the greatest number of attributes (greatest_number_of_attributes) has 100 attributes, a good starting point for the value of MaxNoOfAttributes would be 6 * greatest_number_of_attributes = 600.

You should also estimate the average number of attributes per table and multiply this by MaxNoOfTables. If this value is larger than the value obtained in the previous paragraph, you should use the larger value instead.

Assuming that you can create all desired tables without any problems, you should also verify that this number is sufficient by trying an actual ALTER TABLE after configuring the parameter. If this is not successful, increase MaxNoOfAttributes by another multiple of MaxNoOfTables and test it again.

MaxNoOfTables

A table object is allocated for each table and for each unique hash index in the cluster. This parameter sets the maximum number of table objects for the cluster as a whole.

For each attribute that has a BLOB data type an extra table is used to store most of the BLOB data. These tables also must be taken into account when defining the total number of tables.

The default value of this parameter is 128. The minimum is 8 and the maximum is 20320. Each table object consumes approximately 20KB per node.

MaxNoOfOrderedIndexes