• [COMPUTER]: Defines the the cluster hosts.

• [NDBD]: Defines the cluster's data nodes.

• [MYSQLD]: Defines the cluster's MySQL server nodes.

• [MGM] or [NDB_MGMD]: Defines the cluster's management server node.

• [TCP]: Defines TCP/IP connections between nodes in the cluster, with TCP/IP being the default connection protocol.

• [SHM]: Defines shared-memory connections between nodes. Formerly, this type of connection was available only in binaries that were built using the --with-ndb-shm option. In MySQL 5.0-Max, it is enabled by default, but should still be considered experimental.

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

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

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

  1. Set the NDB_CONNECTSTRING environment variable to contain the connectstring.

  2. Write the connectstring for each executable into a text file named Ndb.cfg and place this file in the executable's startup directory.

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

• [COMPUTER]Id

  This is an integer value, used to refer to the host computer elsewhere in the configuration file.

• [COMPUTER]HostName

  This is the computer's hostname or IP address.

• [NDB_MGMD]Id

  Each node in the cluster has a unique identity, which is represented by an integer value in the range 1 to 63 inclusive. This ID is used by all internal cluster messages for addressing the node.

• [NDB_MGMD]ExecuteOnComputer

  This refers to one of the computers defined in the [COMPUTER] section.

• [NDB_MGMD]PortNumber

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

• [NDB_MGMD]LogDestination

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

  • CONSOLE outputs the log to stdout:

    CONSOLE
    

  • SYSLOG sends the log to a syslog facility, possible values being one of auth, authpriv, cron, daemon, ftp, kern, lpr, mail, news, syslog, user, uucp, local0, local1, local2, local3, local4, local5, local6, or local7.

    Note: Not every facility is necessarily supported by every operating system.

    SYSLOG:facility=syslog
    

  • FILE pipes the cluster log output to a regular file on the same machine. The following values can be specified:

    • filename: The name of the logfile.

    • maxsize: The maximum size to which the file can grow before logging rolls over to a new file. When this occurs, the old logfile is renamed by appending .x to the filename, where x is the next number not yet used with this name.

    • maxfiles: The maximum number of logfiles.

    FILE:filename=cluster.log,maxsize=1000000,maxfiles=6
    

    It is possible to specify multiple log destinations as shown here, using a semicolon-delimited string:

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

    The default value for the FILE parameter is FILE:filename=ndb_node_id_cluster.log,maxsize=1000000,maxfiles=6, where node_id is the ID of the node.

• [NDB_MGMD]ArbitrationRank

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

  • 0: The node will never be used as an arbitrator.

  • 1: The node has high priority; that is, it will be preferred as an arbitrator over low-priority nodes.

  • 2: Indicates a low-priority node which be used as an arbtrator only if a node with a higher priority is not available for that purpose.

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

• [NDB_MGMD]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.

• [NDB_MGMD]DataDir

  This sets 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 pid file. (For log files, this can be overridden by setting the FILE parameter for [NDB_MGMD]LogDestination as discussed previously in this section.)

• Either ExecuteOnComputer or HostName.

• The parameter NoOfReplicas

• [NBDB]Id

  This is the node ID used as the address of the node for all cluster internal messages. This is an integer between 1 and 63. Each node in the cluster has a unique identity.

• [NDBD]ExecuteOnComputer

  This refers to one of the computers (hosts) defined in the COMPUTER section.

• [NDBD]HostName

  Specifying this parameter has an effect similar to specifying ExecuteOnComputer. It defines the hostname of the computer the storage node on which is to reside. Either this parameter or ExecuteOnComputer is required in order to specify a hostname other than localhost.

• (OBSOLETE) [NDBD]ServerPort

  Each node in the cluster uses a port to connect to other nodes. This port is used also for non-TCP transporters in the connection setup phase. Since, the default port is allocated dynamically in such a way as to ensure that no two nodes on the same computer receive the same port number, it should not normally be necessary to specify a value for this parameter.

• [NDBD]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, as in this situation a single hardware failure would cause the entire cluster to crash.

  If no node IDs are provided then 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.

  There is no default value for NoOfReplicas; the maximum possible value is 4.

• [NDBD]DataDir

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

• [NDBD]FileSystemPath

  This parameter specifies the directory where all files created for metadata, REDO logs, UNDO logs and data files are placed. The default is the directory specified by DataDir. Note: This directory must exist before the ndbd process is initiated.

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

• [NDBD]BackupDataDir

  It is also possible to specify the directory in which backups are placed. By default, this directory is FileSystemPath/BACKUP. (See above.)

• [NDBD]DataMemory

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

  The memory allocated by DataMemory is used to store both the the actual records and indexes. Each record is currently of fixed size. (Even VARCHAR columns are stored as fixed-width columns.) 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. The maximum record size is currently 8052 bytes.

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

  The memory space allocated by DataMemory consists of 32KB pages, which are allocated to table fragments. Each table is normally partitioned into the same number of fragments as there are data nodes in the cluster. Thus, for each node, there are the same number of fragments as are set in NoOfReplicas. 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. Performing a node recovery also will compress 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:

  • Large transactions are not any faster than smaller ones

  • Large transactions increase the number of operations that are lost and must be repeated in event of transaction failure

  • Large transactions use more memory

  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. In addition, it is also possible to run more than one ndbd process per machine, and this may prove advantageous on machines with multiple CPUs.

• [NDBD]IndexMemory

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

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

  Consider a table defined by

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

  There are 12 bytes overhead (having no nullable columns saves 4 bytes of overhead) plus 12 bytes of data per record. 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 table will consume an additional 29 bytes of index memory per record in the example table as well as 12 bytes of overhead, plus 8 bytes of record data.

  Thus, for one million records, we need 58 MB for index memory to handle the hash indexes for the primary key and the unique constraint. We also need 64 MB 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 2 replicas, then 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. Since data is distributed evenly over all nodes in the cluster 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, then restarting the management server followed by restarting each data node in turn.

  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.

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

• [NDBD]MaxNoOfConcurrentTransactions

  For each active transaction in the cluster there must be a record in one of the cluster nodes. The task of coordinating transactions is spread amongst the 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. Normally there is at least one transaction record allocated per connection that using any table in the cluster. For this reason, one should ensure that there are more transaction records in the cluster than there are concurrent connections to all MySQL servers in the cluster.

  This parameter must be set to the same value for all cluster nodes.

  Changing this parameter is never safe and doing so can cause a cluster to crash. When a node crashes one of the nodes (actually the oldest surviving node) will build up the transaction state of all transactions ongoing in the crashed node at the time of the crash. It is thus important that this node has as many transaction records as the failed node.

  The default value for this parameter is 4096.

• [NDBD]MaxNoOfConcurrentOperations

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

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

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

  Read queries which set locks also cause operation records to be created. Some extra space is allocated within individual nodes to accomodate 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 for this parameter 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 co-ordinator. The second part specifies how many operation records are to be local to the database.

  A very large transaction performed on an 8-node cluster requires as many operation records in the transaction co-ordinator as there are reads, updates, and deletes involved in the transaction. However, the operation records of the are spread over all 8 nodes. Thus, if it is necessary to configure the system for one very large transaction, then 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 co-ordinator 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.

• [NDBD]MaxNoOfLocalOperations

  By default, this parameter is calculated as 1.1 * MaxNoOfConcurrentOperations which 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, then it is a good idea to override the default value by explicitly specifying this parameter.

• [NDBD]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 only allocated 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 parallelism is not required for the cluster.

• [NDBD]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.

• [NDBD]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.

  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 info, ZDATABUF_FILESIZE (also in Dbtc.hpp) contains 4000 * 16 = 62.5KB of buffer space. Dbtc is the module which handles transaction co-ordination.

There are additional parameters in the Dblqh module (in ndb/src/kernel/blocks/Dblqh/Dblqh.hpp) which 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.

The default value for TransactionBufferMemory is 1MB.

• [NDBD]MaxNoOfConcurrentScans

  This parameter is used to control the number of parallel scans that can be performed in the cluster. Each transaction co-ordinator 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. Since the order is kept on the local partitions only, it is necessary to perform the index scan on all partitions.

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

  This parameter specifies the number of scans possible in the transaction co-ordinator. If the number of local scan records is not provided, it is calculated as the