Chapter 18 MySQL Cluster NDB 7.2

Based on MySQL Server 5.5.  Previous MySQL Cluster release series, including MySQL Cluster NDB 7.1, used MySQL 5.1 as a base. Beginning with MySQL Cluster NDB 7.2.1, MySQL Cluster NDB 7.2 is based on MySQL Server 5.5, so that MySQL Cluster users can benefit from MySQL 5.5's improvements in scalability and performance monitoring. As with MySQL 5.5, MySQL Cluster NDB 7.2.1 and later use CMake for configuring and building from source in place of GNU Autotools (used in MySQL 5.1 and MySQL Cluster releases based on MySQL 5.1). For more information about changes and improvements in MySQL 5.5, see Section 1.4, “What Is New in MySQL 5.5”.

Conflict detection using GCI Reflection.  MySQL Cluster Replication implements a new “primary wins” conflict detection and resolution mechanism. GCI Reflection applies in two-cluster circulation “active-active” replication setups, tracking the order in which changes are applied on the MySQL Cluster designated as primary relative to changes originating on the other MySQL Cluster (referred to as the secondary). This relative ordering is used to determine whether changes originating on the slave are concurrent with any changes that originate locally, and are therefore potentially in conflict. Two new conflict detection functions are added: When using NDB$EPOCH(), rows that are out of sync on the secondary are realigned with those on the primary; with NDB$EPOCH_TRANS(), this realignment is applied to transactions. For more information, see Section 18.6.11, “MySQL Cluster Replication Conflict Resolution”.

Version 2 binary log row events.  A new format for binary log row events, known as Version 2 binary log row events, provides support for improvements in MySQL Cluster Replication conflict detection (see previous item) and is intended to facilitate further improvements in MySQL Replication. You can cause a given mysqld use Version 1 or Version 2 binary logging row events with the --log-bin-use-v1-row-events option. For backward compatibility, Version 2 binary log row events are also available in MySQL Cluster NDB 7.0 (7.0.27 and later) and MySQL Cluster NDB 7.1 (7.1.16 and later). However, MySQL Cluster NDB 7.0 and MySQL Cluster NDB 7.1 continue to use Version 1 binary log row events as the default, whereas the default in MySQL Cluster NDB 7.2.1 and later is use Version 2 row events for binary logging.

Distribution of MySQL users and privileges.  Automatic distribution of MySQL users and privileges across all SQL nodes in a given MySQL Cluster is now supported. To enable this support, you must first import an SQL script share/mysql/ndb_dist_priv.sql that is included with the MySQL Cluster NDB 7.2 distribution. This script creates several stored procedures which you can use to enable privilege distribution and perform related tasks.

When a new MySQL Server joins a MySQL Cluster where privilege distribution is in effect, it also participates in the privilege distribution automatically.

Once privilege distribution is enabled, all changes to the grant tables made on any mysqld attached to the cluster are immediately available on any other attached MySQL Servers. This is true whether the changes are made using CREATE USER, GRANT, or any of the other statements described elsewhere in this Manual (see Section 13.7.1, “Account Management Statements”.) This includes privileges relating to stored routines and views; however, automatic distribution of the views or stored routines themselves is not currently supported.

For more information, see Section 18.5.14, “Distributed MySQL Privileges for MySQL Cluster”.

Distributed pushed-down joins.  Many joins can now be pushed down to the NDB kernel for processing on MySQL Cluster data nodes. Previously, a join was handled in MySQL Cluster by means of repeated accesses of NDB by the SQL node; however, when pushed-down joins are enabled, a pushable join is sent in its entirety to the data nodes, where it can be distributed among the data nodes and executed in parallel on multiple copies of the data, with a single, merged result being returned to mysqld. This can reduce greatly the number of round trips between an SQL node and the data nodes required to handle such a join, leading to greatly improved performance of join processing.

It is possible to determine when joins can be pushed down to the data nodes by examining the join with EXPLAIN. A number of new system status variables (Ndb_pushed_queries_defined, Ndb_pushed_queries_dropped, Ndb_pushed_queries_executed, and Ndb_pushed_reads) and additions to the counters table (in the ndbinfo information database) can also be helpful in determining when and how well joins are being pushed down.

More information and examples are available in the description of the ndb_join_pushdown server system variable. See also the description of the status variables referenced in the previous paragraph, as well as Section 18.5.10.7, “The ndbinfo counters Table”.

Improved default values for data node configuration parameters.  In order to provide more resiliency to environmental issues and better handling of some potential failure scenarios, and to perform more reliably with increases in memory and other resource requirements brought about by recent improvements in join handling by NDB, the default values for a number of MySQL Cluster data node configuration parameters have been changed. The parameters and changes are described in the following list:

In addition, the value computed for MaxNoOfLocalScans when this parameter is not set in config.ini has been increased by a factor of 4.

Fail-fast data nodes.  Beginning with MySQL Cluster NDB 7.2.1, data nodes handle corrupted tuples in a fail-fast manner by default. This is a change from previous versions of MySQL Cluster where this behavior had to be enabled explicitly by enabling the CrashOnCorruptedTuple configuration parameter. In MySQL Cluster NDB 7.2.1 and later, this parameter is enabled by default and must be explicitly disabled, in which case data nodes merely log a warning whenever they detect a corrupted tuple.

Memcache API support (ndbmemcache).  The Memcached server is a distributed in-memory caching server that uses a simple text-based protocol. It is often employed with key-value stores. The Memcache API for MySQL Cluster, available beginning with MySQL Cluster NDB 7.2.2, is implemented as a loadable storage engine for memcached version 1.6 and later. This API can be used to access a persistent MySQL Cluster data store employing the memcache protocol. It is also possible for the memcached server to provide a strictly defined interface to existing MySQL Cluster tables.

Each memcache server can both cache data locally and access data stored in MySQL Cluster directly. Caching policies are configurable. For more information, see ndbmemcache—Memcache API for MySQL Cluster, in the MySQL Cluster API Developers Guide.

Rows per partition limit removed.  Previously it was possible to store a maximum of 46137488 rows in a single MySQL Cluster partition—that is, per data node. Beginning with MySQL Cluster NDB 7.2.9, this limitation has been lifted, and there is no longer any practical upper limit to this number. (Bug #13844405, Bug #14000373)

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 data types (WKT and WKB) are supported for NDB tables. 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 NDB tables with user-defined partitioning.  Support for user-defined partitioning in MySQL Cluster is restricted to [LINEAR] KEY partitioning. Using any other partitioning type with ENGINE=NDB or ENGINE=NDBCLUSTER in a CREATE TABLE statement results in an error.

It is possible to override this restriction, but doing so is not supported for use in production settings. For details, see User-defined partitioning and the NDB storage engine (MySQL Cluster).

Default partitioning scheme.  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 NDB storage engine is used instead. For additional discussion of these and related issues, see Section 19.2.5, “KEY Partitioning”.

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 not permitted, and fail with an error:

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 18.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 optimized. See Section 19.3.1, “Management of RANGE and LIST Partitions” and Section 13.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. However, this memory can be made available for general re-use by performing OPTIMIZE TABLE.

A rolling restart of the cluster also frees any memory used by deleted rows. See Section 18.5.5, “Performing a Rolling Restart of a MySQL Cluster”.

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.

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 18.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 18.1.6.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 18.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:

Rollbacks.  There are no partial transactions, and no partial rollbacks of transactions. A duplicate key or similar error causes the entire transaction to be rolled back.

This behavior differs from that of other transactional storage engines such as InnoDB that may roll back individual statements.

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

Database and table names.  When using the NDB storage engine, the maximum allowed length both for database names and for table names is 63 characters.

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) that can belong to a given table is 512.

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

Row size.  The maximum permitted size of any one row is 14000 bytes (as of MySQL Cluster NDB 7.0). Each BLOB or TEXT column contributes 256 + 8 = 264 bytes to this total.

Foreign key constraints.  Prior to MySQL Cluster NDB 7.3, the foreign key construct is ignored, just as it is by MyISAM tables. Foreign keys are supported in MySQL Cluster NDB 7.3 and later.

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.

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 18.6, “MySQL Cluster Replication”, for more information.

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 8.9.3, “Index Hints”, 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¹⁶ (65536)

Maximum data file size: The theoretical limit is 64G; however, the practical upper limit is 32G. This is equivalent to 32768 extents of 1M each.

Since a MySQL Cluster Disk Data table can use at most 1 tablespace, this means that the theoretical upper limit to the amount of data (in bytes) that can be stored on disk by a single NDB table is 32G * 65536 = 2251799813685248, or approximately 2 petabytes.

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

The minimum and maximum possible sizes of extents for tablespace data files are 32K and 2G, respectively. See Section 13.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 connect strings because automatic allocation of node IDs does not work across multiple management servers.

When a management server starts, it first checks for any other management server in the same MySQL Cluster, and upon successful connection to the other management server uses its configuration data. This means that the management server --reload and --initial startup options are ignored unless the management server is the only one running. It also means that, when performing a rolling restart of a MySQL Cluster with multiple management nodes, the management server reads its own configuration file if (and only if) it is the only management server running in this MySQL Cluster. See Section 18.5.5, “Performing a Rolling Restart of a MySQL Cluster”, for more information.

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 11.7, “Data Type Storage Requirements”).

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

Circular Replication.  Circular replication is also supported with MySQL Cluster, beginning with MySQL 5.1.18. See Section 18.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 MySQL Cluster Replication.

Backup and restore between architectures.  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)

Multiple data nodes, multi-threaded data nodes.  MySQL Cluster NDB 7.2 supports multiple data node processes on a single host as well as multi-threaded data node processes. See Section 18.4.3, “ndbmtd — The MySQL Cluster Data Node Daemon (Multi-Threaded)”, for more information.

Identifiers.  Formerly (in MySQL 5.0 and earlier), database names, table names and attribute names could not be as long for NDB tables as tables using other storage engines, because attribute names were truncated internally. In MySQL 5.1 and later, names of MySQL Cluster databases, tables, and table columns follow the same rules regarding length as they do for any other storage engine.

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.  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 is 63, of which up to 48 can be data nodes.

Recovery of memory from deleted rows.  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).

Number of tables.  The maximum number of NDBCLUSTER tables in a single MySQL Cluster is included in the total maximum number of NDBCLUSTER database objects (20320). (See Section 18.1.6.5, “Limits Associated with Database Objects in MySQL Cluster”.)

Adding and dropping of data nodes.  In MySQL Cluster NDB 7.2 (MySQL Cluster NDB 7.0 and later), 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, you should be aware of and take into account the following issues:

For more information, see Section 18.5.13, “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.

Distribution of MySQL users and privileges.  Previously, MySQL users and privileges created on one SQL node were unique to that SQL node, due to the fact that the MySQL grant tables were restricted to using the MyISAM storage engine. Beginning with MySQL Cluster NDB 7.2.0, it is possible, following installation of the MySQL Cluster software and setup of the desired users and privileges on one SQL node, to convert the grant tables to use NDB and thus to distribute the users and privileges across all SQL nodes connected to the cluster. You can do this by loading and making use of a set of stored procedures defined in an SQL script supplied with the MySQL Cluster distribution. For more information, see Section 18.5.14, “Distributed MySQL Privileges for MySQL Cluster”.

Number of rows per partition.  Previously, a single MySQL Cluster partition could hold a maximum of 46137488 rows. This limitation was removed in MySQL Cluster NDB 7.2.9. (Bug #13844405, Bug #14000373)

If you are still using a previous MySQL Cluster release, you can work around this limitation by taking advantage of the fact that the number of partitions is the same as the number of data nodes in the cluster (see Section 18.1.2, “MySQL Cluster Nodes, Node Groups, Replicas, and Partitions”). This means that, by increasing the number of data nodes, you can increase the available space for storing data.

MySQL Cluster NDB 7.2 also supports increasing the number of data nodes in the cluster while the cluster remains in operation. See Section 18.5.13, “Adding MySQL Cluster Data Nodes Online”, for more information.

It is also possible to increase the number of partitions for NDB tables by using explicit KEY or LINEAR KEY partitioning (see Section 19.2.5, “KEY Partitioning”).

The server RPM (for example, MySQL-Cluster-server-gpl-7.2.21-1.sles11.i386.rpm), which supplies the core files needed to run a MySQL Server with NDBCLUSTER storage engine support (that is, as a MySQL Cluster SQL node) as well as all MySQL Cluster executables, including the management node, data node, and ndb_mgm client binaries. This RPM is always required for installing MySQL Cluster NDB 7.2.

If you do not have your own client application capable of administering a MySQL server, you should also obtain and install the client RPM (for example, MySQL-Cluster-client-gpl-7.2.21-1.sles11.i386.rpm), which supplies the mysql client

To build MySQL Cluster, you must use the MySQL Cluster sources, which you can obtain from http://dev.mysql.com/downloads/cluster/.

Attempting to build MySQL Cluster from the source code for the standard MySQL Server is likely not to be successful, and is not supported by Oracle.

You must configure the build using the WITH_NDBCLUSTER_STORAGE_ENGINE or WITH_NDBCLUSTER option in addition to any other build options you wish to use with CMake. (WITH_NDBCLUSTER is supported as an alias for WITH_NDBCLUSTER_STORAGE_ENGINE, and works in exactly the same way.)

[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 18.3.2.4, “Defining Computers in a MySQL Cluster”, for more information.

[ndbd]: Defines a cluster data node (ndbd process). See Section 18.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 18.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 management nodes, see Section 18.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 18.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 18.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.5, it is enabled by default, but should still be considered experimental. For a discussion of SHM interconnects, see Section 18.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 18.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 connection string 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 255, inclusive. 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.

The use of the Id parameter for identifying management nodes is deprecated in favor of NodeId. Although Id continues to be supported for backward compatibility, it now generates a warning and is subject to removal in a future version of MySQL Cluster.

NodeId

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

NodeId is the preferred parameter name to use when identifying management nodes. Although the older Id continues to be supported for backward compatibility, it is now deprecated and generates a warning when used; it is also subject to removal in a future MySQL Cluster release.

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

You can disable arbitration completely either by setting ArbitrationRank to 0 on all management and SQL nodes, or by setting the Arbitration parameter in the [ndbd default] section of the config.ini global configuration file. Setting Arbitration causes any settings for ArbitrationRank to be disregarded.

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.

PortNumberStats

This parameter specifies the port number used to obtain statistical information from a MySQL Cluster management server. It has no default value.

Wan

Use WAN TCP setting as default.

HeartbeatThreadPriority

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 out) or RR (round robin). The policy value is followed optionally by the priority (an integer).

ExtraSendBufferMemory

This parameter specifies the amount of transporter send buffer memory to allocate in addition to any that has been set using TotalSendBufferMemory, SendBufferMemory, or both.

This parameter was added in MySQL Cluster NDB 7.2.14. (Bug #14555359)

TotalSendBufferMemory

This parameter 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 permitted value is 256KB; 0 indicates that the parameter has not been set. For more detailed information, see Section 18.3.2.12, “Configuring MySQL Cluster Send Buffer Parameters”.

HeartbeatIntervalMgmdMgmd

Specify the interval between heartbeat messages used to determine whether another management node is on contact with this one. The management node waits after 3 of these intervals to declare the connection dead; thus, the default setting of 1500 milliseconds causes the management node to wait for approximately 1600 ms before timing out.

This parameter was added in MySQL Cluster NDB 7.2.12. (Bug #16426805, Bug #17807768)

Id

A unique node ID is used as the node's address 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.

NodeId is the preferred parameter name to use when identifying data nodes. Although the older Id is still supported for backward compatibility, it is now deprecated, and generates a warning when used. Id is also subject to removal in a future MySQL Cluster release.

NodeId

A unique node ID is used as the node's address 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.

NodeId is the preferred parameter name to use when identifying data nodes. Although Id continues to be supported for backward compatibility, it is now deprecated, generates a warning when used, and is subject to removal in a future version of MySQL Cluster.

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

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 65536 (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 18.5.13.3, “Adding MySQL Cluster Data Nodes Online: Detailed Example”.

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.

The default value for NoOfReplicas is 2, which is the recommended setting in most common usage scenarios.

The maximum possible value is 4; currently, only the values 1 and 2 are actually supported.

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.

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, the data is stored on separate data pages, 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 14000 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.

Currently, MySQL Cluster can use a maximum of 512 MB for hash indexes per partition, which means in some cases it is possible to get Table is full errors in MySQL client applications even when ndb_mgm -e "ALL REPORT MEMORYUSAGE" shows significant free DataMemory. This can also pose a problem with data node restarts on nodes that are heavily loaded with data. You can force NDB to create extra partitions for MySQL Cluster tables and thus have more memory available for hash indexes by using the MAX_ROWS option for CREATE TABLE. In general, setting MAX_ROWS to twice the number of rows that you expect to store in the table should be sufficient. In MySQL Cluster 7.2.3 and later, you can also use the MinFreePct configuration parameter to help avoid problems with node restarts. (Bug #13436216)

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.

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 data 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. When defining a primary key or a unique index, two indexes are created, one of which is a hash index used for all tuple accesses as well as lock handling. This index is also used to enforce unique constraints.

You can estimate the size of a hash index using this formula:

  size  = ( (fragments * 32K) + (rows * 18) ) 
          * replicas

          

fragments is the number of fragments, replicas is the number of replicas (normally 2), and rows is the number of rows. If a table has one million rows, 8 fragments, and 2 replicas, the expected index memory usage is calculated as shown here:

            
  ((8 * 32K) + (1000000 * 18)) * 2 = ((8 * 32768) + (1000000 * 18)) * 2
  = (262144 + 18000000) * 2
  = 18262144 * 2 = 36524288 bytes = ~35MB

In MySQL Cluster NDB 7.2 and later, index statistics (when enabled) for ordered indexes are stored in the mysql.ndb_index_stat_sample table. Since this table has a hash index, this adds to index memory usage. An upper bound to the number of rows for a given ordered index can be calculated as follows:

  sample_size= key_size + ((key_attributes + 1) * 4)
  
  sample_rows = IndexStatSaveSize 
                * ((0.01 * IndexStatSaveScale * log2(rows * sample_size)) + 1) 
                / sample_size

In the preceding formula, key_size is the size of the ordered index key in bytes, key_attributes is the number ot attributes in the ordered index key, and rows is the number of rows in the base table.

Assume that table t1 has 1 million rows and an ordered index named ix1 on two four-byte integers. Assume in addition that IndexStatSaveSize and IndexStatSaveScale are set to their default values (32K and 100, respectively). Using the previous 2 formulas, we can calculate as follows:

  sample_size = 8  + ((1 + 2) * 4) = 20 bytes
  
  sample_rows = 32K 
                * ((0.01 * 100 * log2(1000000*20)) + 1) 
                / 20 
                = 32768 * ( (1 * ~16.811) +1) / 20
                = 32768 * ~17.811 / 20 
                = ~29182 rows

The expected index memory usage is thus 2 * 18 * 29182 = ~1050550 bytes.

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.

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

The default value is 25—that is, 25 percent of the default maximum.

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 the total number of transactions in the cluster can be expressed as

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

Suppose that there are 10 SQL nodes using the cluster. A single join involving 10 tables requires 11 transaction records; if there are 10 such joins in a transaction, then 10 * 11 = 110 transaction records are required for this transaction, per MySQL server, or 110 * 10 = 1100 transaction records total. Each data node can be expected to handle TotalNoOfConcurrentTransactions / number of data nodes. For a MySQL Cluster having 4 data nodes, this would mean setting MaxNoOfConcurrentTransactions on each data node to 1100 / 4 = 275. In addition, you should provide for failure recovery by ensuring that a single node group can accommodate all concurrent transactions; in other words, that each data node's MaxNoOfConcurrentTransactions is sufficient to cover a number of transaction equal to TotalNoOfConcurrentTransactions / number of node groups. If this cluster has a single node group, then MaxNoOfConcurrentTransactions should be set to 1100 (the same as the total number of concurrent transactions for the entire cluster).

In addition, each transaction involves at least one operation; for this reason, the value set for MaxNoOfConcurrentTransactions should always be no more than the value of MaxNoOfConcurrentOperations.

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.

It is possible to change this value using a rolling restart, but the amount of traffic on the cluster must be such that no more transactions occur than the lower of the old and new levels while this is taking place.

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 which involve only a few operations and records, the default value for this parameter is usually sufficient. Performing large transactions involving many records usually requires that you increase its value.

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 at a minimum 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 one million concurrent updates using transactions, you should set this value to 1000000 / 4 = 250000. To help provide resiliency against failures, it is suggested that you set this parameter to a value that is high enough to permit an individual data node to handle the load for its node group. In other words, you should set the value equal to total number of concurrent operations / number of node groups. (In the case where there is a single node group, this is the same as the total number of concurrent operations for the entire cluster.)

Because each transaction always involves at least one operation, the value of MaxNoOfConcurrentOperations should always be greater than or equal to the value of MaxNoOfConcurrentTransactions.

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.

MaxDMLOperationsPerTransaction

This parameter limits the size of a transaction. The transaction is aborted if it requires more than this many DML operations. The minimum number of operations per transaction is 32; however, you can set MaxDMLOperationsPerTransaction to 0 to disable any limitation on the number of DML operations per transaction. The maximum (and default) is 4294967295.

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. In MySQL Cluster NDB 7.2.0 and later, when the number of local scan records is not provided, it is calculated as shown here:

4 * MaxNoOfConcurrentScans * [# data nodes] + 2

The minimum value is 32.

BatchSizePerLocalScan

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

BatchSizePerLocalScan has a strong connection to the BatchSize defined in the SQL nodes.

LongMessageBuffer

This is an internal buffer used for passing messages within individual nodes and between nodes. The default is 64MB. (Prior to MySQL Cluster NDB 7.2.16, this was 4MB.)

This parameter seldom needs to be changed from the default.

MaxParallelScansPerFragment

It is possible to configure the maximum number of parallel scans (TUP scans and TUX scans) allowed before they begin queuing for serial handling. You can increase this to take advantage of any unused CPU when performing large number of scans in parallel and improve their performance.

Beginning with MySQL Cluster NDB 7.2.0, the default value for this parameter was increased from 32 to 256.

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. (In MySQL Cluster NDB 7.2, only 2 local checkpoints are necessary). Checkpointing frequency is determined by its own set of configuration parameters discussed elsewhere in this chapter.

The default parameter value is 16, which by default means 16 sets of 4 16MB files for a total of 1024MB. The size of the individual log files is configurable using the FragmentLogFileSize parameter. 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 enables 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.

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

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. However, 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 means of this parameter. InitFragmentLogFiles takes either 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. 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, 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.

It is possible to force a delay in the local checkpoint to permit 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, 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.

LcpScanProgressTimeout

A local checkpoint fragment scan watchdog checks periodically for no progress in each fragment scan performed as part of a local checkpoint, and shuts down the node if there is no progress after a given amount of time has elapsed. Prior to MySQL Cluster NDB 7.2.14, this interval is always 60 seconds (Bug #16630410). In MySQL Cluster NDB 7.2.14 and later, this interval can be set using the LcpScanProgressTimeout data node configuration parameter, which sets the maximum time for which the local checkpoint can be stalled before the LCP fragment scan watchdog shuts down the node.

The default value is 60 seconds (providing compatibility with previous releases). Setting this parameter to 0 disables the LCP fragment scan watchdog altogether.

MaxNoOfAttributes

This parameter sets a suggested maximum number of attributes that can be defined in the cluster; like MaxNoOfTables, it is not intended to function as a hard upper limit.

(In older MySQL Cluster releases, this parameter was sometimes treated as a hard limit for certain operations. This caused problems with MySQL Cluster Replication, when it was possible to create more tables than could be replicated, and sometimes led to confusion when it was possible [or not possible, depending on the circumstances] to create more than MaxNoOfAttributes attributes.)

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 permit 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 a suggested maximum number of table objects for the cluster as a whole; like MaxNoOfAttributes, it is not intended to function as a hard upper limit.

(In older MySQL Cluster releases, this parameter was sometimes treated as a hard limit for certain operations. This caused problems with MySQL Cluster Replication, when it was possible to create more tables than could be replicated, and sometimes led to confusion when it was possible [or not possible, depending on the circumstances] to create more than MaxNoOfTables tables.)

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

For each ordered index in the cluster, an object is allocated describing what is being indexed and its storage segments. By default, each index so defined also defines an ordered index. Each unique index and primary key has both an ordered index and a hash index. MaxNoOfOrderedIndexes sets the total number of ordered indexes that can be in use in the system at any one time.

The default value of this parameter is 128. Each index object consumes approximately 10KB of data per node.

MaxNoOfUniqueHashIndexes

For each unique index that is not a primary key, a special table is allocated that maps the unique key to the primary key of the indexed table. By default, an ordered index is also defined for each unique index. To prevent this, you must specify the USING HASH option when defining the unique index.

The default value is 64. Each index consumes approximately 15KB per node.

MaxNoOfTriggers

Internal update, insert, and delete triggers are allocated for each unique hash index. (This means that three triggers are created for each unique hash index.) However, an ordered index requires only a single trigger object. Backups also use three trigger objects for each normal table in the cluster.

Replication between clusters also makes use of internal triggers.

This parameter sets the maximum number of trigger objects in the cluster.

The default value is 768.

MaxNoOfIndexes

This parameter is deprecated and subject to removal in a future version of MySQL Cluster. You should use MaxNoOfOrderedIndexes and MaxNoOfUniqueHashIndexes instead.

This parameter is used only by unique hash indexes. There needs to be one record in this pool for each unique hash index defined in the cluster.

The default value of this parameter is 128.

MaxNoOfSubscriptions

Each NDB table in a MySQL Cluster requires a subscription in the NDB kernel. For some NDB API applications, it may be necessary or desirable to change this parameter. However, for normal usage with MySQL servers acting as SQL nodes, there is not any need to do so.

The default value for MaxNoOfSubscriptions is 0, which is treated as equal to MaxNoOfTables. Each subscription consumes 108 bytes.

MaxNoOfSubscribers

This parameter is of interest only when using MySQL Cluster Replication. The default value is 0, which is treated as 2 * MaxNoOfTables; that is, there is one subscription per NDB table for each of two MySQL servers (one acting as the replication master and the other as the slave). Each subscriber uses 16 bytes of memory.

When using circular replication, multi-master replication, and other replication setups involving more than 2 MySQL servers, you should increase this parameter to the number of mysqld processes included in replication (this is often, but not always, the same as the number of clusters). For example, if you have a circular replication setup using three MySQL Clusters, with one mysqld attached to each cluster, and each of these mysqld processes acts as a master and as a slave, you should set MaxNoOfSubscribers equal to 3 * MaxNoOfTables.

For more information, see Section 18.6, “MySQL Cluster Replication”.

MaxNoOfConcurrentSubOperations

This parameter sets a ceiling on the number of operations that can be performed by all API nodes in the cluster at one time. The default value (256) is sufficient for normal operations, and might need to be adjusted only in scenarios where there are a great many API nodes each performing a high volume of operations concurrently.

LateAlloc

Allocate memory for this data node after a connection to the management server has been established. Enabled by default.

LockPagesInMainMemory

For a number of operating systems, including Solaris and Linux, it is possible to lock a process into memory and so avoid any swapping to disk. This can be used to help guarantee the cluster's real-time characteristics.

This parameter takes one of the integer values 0, 1, or 2, which act as shown in the following list:

If the operating system is not configured to permit unprivileged users to lock pages, then the data node process making use of this parameter may have to be run as system root. (LockPagesInMainMemory uses the mlockall function. From Linux kernel 2.6.9, unprivileged users can lock memory as limited by max locked memory. For more information, see ulimit -l and http://linux.die.net/man/2/mlock).

StopOnError

This parameter specifies whether a data node process should exit or perform an automatic restart when an error condition is encountered.

This parameter's default value is 1; this means that, by default, an error causes the data node process to halt.

CrashOnCorruptedTuple