17.3.2.6 Defining MySQL Cluster Data Nodes

The [ndbd] and [ndbd default] sections are used to configure the behavior of the cluster's data nodes.

[ndbd] and [ndbd default] are always used as the section names whether you are using ndbd or (in MySQL Cluster NDB 6.4.0 and later) ndbmtd binaries for the data node processes.

There are many parameters which control buffer sizes, pool sizes, timeouts, and so forth. The only mandatory parameters are:

Note

It is no longer strictly necessary to set NoOfReplicas starting with MySQL Cluster NDB 6.3.25 and MySQL Cluster NDB 7.0.6, where it acquires a default value (2). However, it remains good practice to set it explicitly.

Most data node parameters are set in the [ndbd default] section. Only those parameters explicitly stated as being able to set local values are permitted to be changed in the [ndbd] section. Where present, HostName, NodeId and ExecuteOnComputer must be defined in the local [ndbd] section, and not in any other section of config.ini. In other words, settings for these parameters are specific to one data node.

For those parameters affecting memory usage or buffer sizes, it is possible to use K, M, or G as a suffix to indicate units of 1024, 1024×1024, or 1024×1024×1024. (For example, 100K means 100 × 1024 = 102400.) Parameter names and values are currently case-sensitive.

Information about configuration parameters specific to MySQL Cluster Disk Data tables can be found later in this section (see Disk Data Configuration Parameters).

Beginning with MySQL Cluster NDB 6.4.0, all of these parameters also apply to ndbmtd (the multi-threaded version of ndbd). Two additional data node configuration parameters—MaxNoOfExecutionThreads and ThreadConfig—apply to ndbmtd only; these have no effect when used with ndbd. For more information, see Multi-Threading Configuration Parameters (ndbmtd). See also Section 17.4.3, “ndbmtd — The MySQL Cluster Data Node Daemon (Multi-Threaded)”.

Identifying data nodes.  The NodeId or Id value (that is, the data node identifier) can be allocated on the command line when the node is started or in the configuration file.

Data Memory, Index Memory, and String Memory

DataMemory and IndexMemory are [ndbd] parameters specifying the size of memory segments used to store the actual records and their indexes. In setting values for these, it is important to understand how DataMemory and IndexMemory are used, as they usually need to be updated to reflect actual usage by the cluster:

The following example illustrates how memory is used for a table. Consider this table definition:

CREATE TABLE example (
  a INT NOT NULL,
  b INT NOT NULL,
  c INT NOT NULL,
  PRIMARY KEY(a),
  UNIQUE(b)
) ENGINE=NDBCLUSTER;

For each record, there are 12 bytes of data plus 12 bytes overhead. Having no nullable columns saves 4 bytes of overhead. In addition, we have two ordered indexes on columns a and b consuming roughly 10 bytes each per record. There is a primary key hash index on the base table using roughly 29 bytes per record. The unique constraint is implemented by a separate table with b as primary key and a as a column. This other table consumes an additional 29 bytes of index memory per record in the example table as well 8 bytes of record data plus 12 bytes of overhead.

Thus, for one million records, we need 58MB for index memory to handle the hash indexes for the primary key and the unique constraint. We also need 64MB for the records of the base table and the unique index table, plus the two ordered index tables.

You can see that hash indexes takes up a fair amount of memory space; however, they provide very fast access to the data in return. They are also used in MySQL Cluster to handle uniqueness constraints.

Currently, the only partitioning algorithm is hashing and ordered indexes are local to each node. Thus, ordered indexes cannot be used to handle uniqueness constraints in the general case.

An important point for both IndexMemory and DataMemory is that the total database size is the sum of all data memory and all index memory for each node group. Each node group is used to store replicated information, so if there are four nodes with two replicas, there will be two node groups. Thus, the total data memory available is 2 × DataMemory for each data node.

It is highly recommended that DataMemory and IndexMemory be set to the same values for all nodes. Data distribution is even over all nodes in the cluster, so the maximum amount of space available for any node can be no greater than that of the smallest node in the cluster.

DataMemory and IndexMemory can be changed, but decreasing either of these can be risky; doing so can easily lead to a node or even an entire MySQL Cluster that is unable to restart due to there being insufficient memory space. Increasing these values should be acceptable, but it is recommended that such upgrades are performed in the same manner as a software upgrade, beginning with an update of the configuration file, and then restarting the management server followed by restarting each data node in turn.

Beginning with MySQL Cluster NDB 7.0.29 and MySQL Cluster NDB 7.1.18, a proportion (5% by default) of data node resources including DataMemory and IndexMemory is kept in reserve to insure that the data node does not exhaust its memory when performing a restart. This can be adjusted using the MinFreePct data node configuration parameter (default 5) introduced in the same versions of MySQL Cluster.

Effective VersionType/UnitsDefaultRange/ValuesRestart Type
NDB 7.0.29unsigned50 - 100S
NDB 7.1.18unsigned50 - 100S
NDB 7.2.3unsigned50 - 100S

Updates do not increase the amount of index memory used. Inserts take effect immediately; however, rows are not actually deleted until the transaction is committed.

Transaction parameters.  The next few [ndbd] parameters that we discuss are important because they affect the number of parallel transactions and the sizes of transactions that can be handled by the system. MaxNoOfConcurrentTransactions sets the number of parallel transactions possible in a node. MaxNoOfConcurrentOperations sets the number of records that can be in update phase or locked simultaneously.

Both of these parameters (especially MaxNoOfConcurrentOperations) are likely targets for users setting specific values and not using the default value. The default value is set for systems using small transactions, to ensure that these do not use excessive memory.

MaxDMLOperationsPerTransaction, added in MySQL Cluster NDB 7.0.26 and MySQL Cluster NDB 7.1.15, sets the maximum number of DML operations that can be performed in a given transaction.

Transaction temporary storage.  The next set of [ndbd] parameters is used to determine temporary storage when executing a statement that is part of a Cluster transaction. All records are released when the statement is completed and the cluster is waiting for the commit or rollback.

The default values for these parameters are adequate for most situations. However, users with a need to support transactions involving large numbers of rows or operations may need to increase these values to enable better parallelism in the system, whereas users whose applications require relatively small transactions can decrease the values to save memory.

Scans and buffering.  There are additional [ndbd] parameters in the Dblqh module (in ndb/src/kernel/blocks/Dblqh/Dblqh.hpp) that affect reads and updates. These include ZATTRINBUF_FILESIZE, set by default to 10000 × 128 bytes (1250KB) and ZDATABUF_FILE_SIZE, set by default to 10000*16 bytes (roughly 156KB) of buffer space. To date, there have been neither any reports from users nor any results from our own extensive tests suggesting that either of these compile-time limits should be increased.

Memory Allocation

MaxAllocate

Effective VersionType/UnitsDefaultRange/ValuesRestart Type
NDB 6.1.19unsigned32M1M - 1GS
NDB 6.2.3unsigned32M1M - 1GS
MySQL 5.1.20unsigned32M1M - 1GS

This is the maximum size of the memory unit to use when allocating memory for tables. In cases where NDB gives Out of memory errors, but it is evident by examining the cluster logs or the output of DUMP 1000 (see DUMP 1000) that all available memory has not yet been used, you can increase the value of this parameter (or MaxNoOfTables, or both) to cause NDB to make sufficient memory available.

This parameter was introduced in MySQL 5.1.20, MySQL Cluster NDB 6.1.12 and MySQL Cluster NDB 6.2.3.

Hash Map Size

DefaultHashMapSize

Effective VersionType/UnitsDefaultRange/ValuesRestart Type
NDB 7.0.38LDM threads2400 - 3840S
NDB 7.1.27LDM threads2400 - 3840S

Beginning with MySQL Cluster NDB 7.0.38 and MySQL Cluster NDB 7.1.26, the size of the table hash maps used by NDB is configurable using this parameter; previously this value was hard-coded. DefaultHashMapSize can take any of three possible values (0, 240, 3840). These values and their effects are described in the following table:

ValueDescription / Effect
0Use the lowest value set, if any, for this parameter among all data nodes and API nodes in the cluster; if it is not set on any data or API node, use the default value.
240Original hash map size used by default in all MySQL Cluster NDB 7.1 and MySQL Cluster NDB 7.0 (and earlier) releases; effectively the only value prior to versions 7.0.38 and 7.1.26 (when this parameter was introduced).
3840Larger hash map size available beginning with MySQL Cluster NDB 7.0.38 and MySQL Cluster NDB 7.1.26

The primary intended use for this parameter is to facilitate upgrades and esecially downgrades between MySQL Cluster NDB 7.1 and later MySQL Cluster versions in which the larger hash map size (3840) is the default; by setting this parameter to 240 prior to performing an upgrade, you can cause the cluster to continue using the smaller size for table hash maps, in which case the tables remain compatible with earlier versions. DefaultHashMapSize can be set for individual data nodes, API nodes, or both, but setting it once only, in the [ndbd default] section of the config.ini file, is the recommended practice. You can also set this parameter to 0, in which case the default value is used.

After increasing this parameter, to have existing tables to take advantage of the new size, you can run ALTER TABLE ... REORGANIZE PARTITION on them, after which they can use the larger hash map size. This is in addition to performing a rolling restart, which makes the larger hash maps available to new tables, but does not enable existing tables to use them.

Decreasing this parameter online after any tables have been created or modified with DefaultHashMapSize equal to 3840 is not currently supported.

Logging and checkpointing.  The following [ndbd] parameters control log and checkpoint behavior.

Metadata objects.  The next set of [ndbd] parameters defines pool sizes for metadata objects, used to define the maximum number of attributes, tables, indexes, and trigger objects used by indexes, events, and replication between clusters. Note that these act merely as suggestions to the cluster, and any that are not specified revert to the default values shown.

Boolean parameters.  The behavior of data nodes is also affected by a set of [ndbd] parameters taking on boolean values. These parameters can each be specified as TRUE by setting them equal to 1 or Y, and as FALSE by setting them equal to 0 or N.

Controlling Timeouts, Intervals, and Disk Paging

There are a number of [ndbd] parameters specifying timeouts and intervals between various actions in Cluster data nodes. Most of the timeout values are specified in milliseconds. Any exceptions to this are mentioned where applicable.

Buffering and logging.  Several [ndbd] configuration parameters enable the advanced user to have more control over the resources used by node processes and to adjust various buffer sizes at need.

These buffers are used as front ends to the file system when writing log records to disk. If the node is running in diskless mode, these parameters can be set to their minimum values without penalty due to the fact that disk writes are faked by the NDB storage engine's file system abstraction layer.

Controlling log messages.  In managing the cluster, it is very important to be able to control the number of log messages sent for various event types to stdout. For each event category, there are 16 possible event levels (numbered 0 through 15). Setting event reporting for a given event category to level 15 means all event reports in that category are sent to stdout; setting it to 0 means that there will be no event reports made in that category.

By default, only the startup message is sent to stdout, with the remaining event reporting level defaults being set to 0. The reason for this is that these messages are also sent to the management server's cluster log.

An analogous set of levels can be set for the management client to determine which event levels to record in the cluster log.

Debugging Parameters.  Beginning with MySQL Cluster NDB 6.3.36, MySQL Cluster NDB 7.0.17, and MySQL Cluster NDB 7.1.6, it is possible to cause logging of traces for events generated by creating and dropping tables using DictTrace. This parameter is useful only in debugging NDB kernel code. DictTrace takes an integer value; currently, 0 (default - no logging) and 1 (logging enabled) are the only supported values.

Backup parameters.  The [ndbd] parameters discussed in this section define memory buffers set aside for execution of online backups.

Important

When specifying these parameters, the following relationships must hold true. Otherwise, the data node will be unable to start.

MySQL Cluster Realtime Performance Parameters

The [ndbd] parameters discussed in this section are used in scheduling and locking of threads to specific CPUs on multiprocessor data node hosts. They were introduced in MySQL Cluster NDB 6.3.4.

Note

To make use of these parameters, the data node process must be run as system root.

Multi-Threading Configuration Parameters (ndbmtd).  ndbmtd runs by default as a single-threaded process and must be configured to use multiple threads, using either of two methods, both of which require setting configuration parameters in the config.ini file. The first method is simply to set an appropriate value for the MaxNoOfExecutionThreads configuration parameter. In MySQL Cluster NDB 7.1.17 and later, a second method is also supported, whereby it is possible to set up more complex rules for ndbmtd multi-threading using ThreadConfig. The next few paragraphs provide information about these parameters and their use with multi-threaded data nodes.

Simple examples:

# Example 1.

ThreadConfig=ldm={count=2,cpubind=1,2},main={cpubind=12},rep={cpubind=11}

# Example 2.

Threadconfig=main={cpubind=0},ldm={count=4,cpubind=1,2,5,6},io={cpubind=3}

It is usually desirable when configuring thread usage for a data node host to reserve one or more CPUs for operating system and other tasks. Thus, for a host machine with 8 CPUs, you might want to use 8 CPU threads bound to 7 CPUs (leaving one for operating system and other functions), with 4 LDM threads, 1 TC thread, 1 receive thread, and 1 thread each for schema management, asynchronous replication, and I/O operations. (This is almost the same distribution of threads used when MaxNoOfExecutionThreads is set equal to 8.) The following ThreadConfig setting performs these assignments, additionally binding all of these threads to specific CPUs:

ThreadConfig=ldm{count=4,cpubind=1,2,3,4},main={cpubind=5}, \
io={cpubind=5},rep={cpubind=6},tc{cpubind=7},recv={cpubind=8}

It should be possible in most cases to bind the main (schema management) thread and the I/O thread to the same CPU, as we have done in the example just shown.

In order to take advantage of the enhanced stability that the use of ThreadConfig offers, it is necessary to insure that CPUs are isolated, and that they not subject to interrupts, or to being scheduled for other tasks by the operating system. On many Linux systems, you can do this by setting IRQBALANCE_BANNED_CPUS in /etc/sysconfig/irqbalance to 0xFFFFF0, and by using the isolcpus boot option in grub.conf. For specific information, see your operating system or platform documentation.

In MySQL Cluster NDB 7.1 and earlier, it is not possible to cause ndbmtd to use more than 1 TC thread; this capability is introduced in MySQL Cluster NDB 7.2.

Disk Data Configuration Parameters.  Configuration parameters affecting Disk Data behavior include the following:

Disk Data and GCP Stop errors.  Errors encountered when using Disk Data tables such as Node nodeid killed this node because GCP stop was detected (error 2303) are often referred to as GCP stop errors. Such errors occur when the redo log is not flushed to disk quickly enough; this is usually due to slow disks and insufficient disk throughput.

You can help prevent these errors from occurring by using faster disks, and by placing Disk Data files on a separate disk from the data node file system. Reducing the value of TimeBetweenGlobalCheckpoints tends to decrease the amount of data to be written for each global checkpoint, and so may provide some protection against redo log buffer overflows when trying to write a global checkpoint; however, reducing this value also permits less time in which to write the GCP, so this must be done with caution.

In addition, adjusting the cluster configuration as discussed here can also help:

GCP stops can be caused by save or commit timeouts; the TimeBetweenEpochsTimeout data node configuration parameter determines the timeout for commits. However, beginning with MySQL Cluster NDB 7.0.21 and MySQL Cluster NDB 7.1.10, it is possible to disable both types of timeouts by setting this parameter to 0.

Parameters for configuring send buffer memory allocation (MySQL Cluster NDB 7.0 and later).  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. (Previously, the NDB kernel used a fixed-size send buffer for every node in the cluster, which was allocated when the node started and could not be changed while the node was running.) The following data node configuration parameters were added in MySQL Cluster NDB 6.4.0 to permit the setting of limits on this memory allocation; this change is reflected by the addition of the configuration parameters TotalSendBufferMemory 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”.

For more detailed information about the behavior and use of TotalSendBufferMemory and about 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”.

Note

Previous to MySQL Cluster NDB 7.0, to add new data nodes to a MySQL Cluster, it was necessary to shut down the cluster completely, update the config.ini file, and then restart the cluster (that is, you had to perform a system restart). All data node processes had to be started with the --initial option.

Beginning with MySQL Cluster NDB 7.0, it is possible to add new data node groups to a running cluster online. See Section 17.5.13, “Adding MySQL Cluster Data Nodes Online”, for more information.

Redo log over-commit handling.  Beginning with MySQL Cluster NDB 7.1.10, it is possible to control the data node's handling of operations when too much time is taken flushing redo logs to disk. This occurs when a given redo log flush takes longer than RedoOverCommitLimit seconds, more than RedoOverCommitCounter times, causing any pending transactions to be aborted. When this happens, the API node that sent the transaction can handle the operations that should have been committed either by queuing the operations and re-trying them, or by aborting them, as determined by DefaultOperationRedoProblemAction. The data node configuration parameters for setting the timeout and number of times it may be exceeded before the API node takes this action are described in the following list:

Controlling restart attempts.  Beginning in MySQL Cluster NDB 6.2.19, MySQL Cluster NDB 6.3.37, MySQL Cluster NDB 7.0.18, and MySQL Cluster NDB 7.1.7, it is possible to exercise more finely-grained control over restart attempts by data nodes when they fail to start using two data node configuration parameters added in these releases. MaxStartFailRetries limits the total number of retries made before giving up on starting the data node; StartFailRetryDelay sets the number of seconds between retry attempts, as described in the following list: