Chapter 4. MySQL Cluster Options and Variables

--ndb-batch-size=#

This sets the size in bytes that is used for NDB transaction batches.

--ndb-cluster-connection-pool=#

By setting this option to a value greater than 1 (the default), a mysqld process can use multiple connections to the cluster, effectively mimicking several SQL nodes. Each connection requires its own [api] or [mysqld] section in the cluster configuration (config.ini) file, and counts against the maximum number of API connections supported by the cluster.

For example, suppose that you have 2 cluster host computers, each running an SQL node whose mysqld process was started with --ndb-cluster-connection-pool=4; this means that the cluster must have 8 API slots available for these connections (instead of 2). All of these connections are set up when the SQL node connects to the cluster, and are allocated to threads in a round-robin fashion.

This option is useful only when running mysqld on host machines having multiple CPUs, multiple cores, or both. For best results, the value should be smaller than the total number of cores available on the host machine. Setting it to a value greater than this is likely to degrade performance severely.

This option was introduced in MySQL Cluster NDB 6.2.2. Beginning with MySQL Cluster NDB 6.2.16 and MySQL Cluster NDB 6.3.13, the value used for this option is available as a global status variable (Bug#35573).

--ndb-connectstring=connect_string

When using the NDBCLUSTER storage engine, this option specifies the management server that distributes cluster configuration data. See Section 3.4.3, “The MySQL Cluster Connectstring”, for syntax.

--ndbcluster

The NDBCLUSTER storage engine is necessary for using MySQL Cluster. If a mysqld binary includes support for the NDBCLUSTER storage engine, the engine is disabled by default. Use the --ndbcluster option to enable it. Use --skip-ndbcluster to explicitly disable the engine.

--ndb-nodeid=#

Set this MySQL server's node ID in a MySQL Cluster. This can be used instead of specifying the node ID as part of the connectstring or in the config.ini file, or allowing the cluster to determine an arbitrary node ID. If you use this option, then --ndb-nodeid must be specified before --ndb-connectstring. If --ndb-nodeid is used and a node ID is specified in the connectstring, then the MySQL server will not be able to connect to the cluster. In addition, if --nodeid is used, then either a matching node ID must be found in a [mysqld] or [api] section of config.ini, or there must be an “open” [mysqld] or [api] section in the file (that is, a section without an Id parameter specified).

Regardless of how the node ID is determined, its is shown as the value of the global status variable Ndb_cluster_node_id in the output of SHOW STATUS, and as cluster_node_id in the connection row of the output of SHOW ENGINE NDBCLUSTER STATUS.

For more information about node IDs for MySQL Cluster SQL nodes, see Section 3.4.7, “Defining SQL and Other API Nodes in a MySQL Cluster”.

--skip-ndbcluster

Disable the NDBCLUSTER storage engine. This is the default for binaries that were built with NDBCLUSTER storage engine support; the server allocates memory and other resources for this storage engine only if the --ndbcluster option is given explicitly. See Section 3.3, “Quick Test Setup of MySQL Cluster”, for an example.

have_ndbcluster

YES if mysqld supports NDBCLUSTER tables. DISABLED if --skip-ndbcluster is used.

multi_range_count

The maximum number of ranges to send to a table handler at once during range selects. The default value is 256. Sending multiple ranges to a handler at once can improve the performance of certain selects dramatically. This is especially true for the NDBCLUSTER table handler, which needs to send the range requests to all nodes. Sending a batch of those requests at once reduces communication costs significantly.

This variable is deprecated in MySQL 5.1, and is no longer supported in MySQL 6.0, in which arbitrarily long lists of ranges can be processed.

ndb_autoincrement_prefetch_sz

Determines the probability of gaps in an autoincremented column. Set it to 1 to minimize this. Setting it to a high value for optimization — makes inserts faster, but decreases the likelihood that consecutive autoincrement numbers will be used in a batch of inserts. Default value: 32. Minimum value: 1.

Beginning with MySQL Cluster NDB 6.2.10, MySQL Cluster NDB 6.3.7, and MySQL 5.1.23, this variable affects the number of AUTO_INCREMENT IDs that are fetched between statements only. Within a statement, at least 32 IDs are now obtained at a time. The default value for ndb_autoincrement_prefetch_sz is now 1, to increase the speed of statements inserting single rows. (Bug#31956)

ndb_cache_check_time

The number of milliseconds that elapse between checks of MySQL Cluster SQL nodes by the MySQL query cache. Setting this to 0 (the default and minimum value) means that the query cache checks for validation on every query.

The recommended maximum value for this variable is 1000, which means that the check is performed once per second. A larger value means that the check is performed and possibly invalidated due to updates on different SQL nodes less often. It is generally not desirable to set this to a value greater than 2000.

ndb_extra_logging

This variable can be used to enable recording in the MySQL error log of information specific to the NDB storage engine. It is normally of interest only when debugging NDB storage engine code.

The default value is 0, which means that the only NDB-specific information written to the MySQL error log relates to transaction handling. If the value is greater than 0 but less than 10, NDB table schema and connection events are also logged, as well as whether or not conflict resolution is in use, and other NDB errors and information. If the value is set to 10 or more, information about NDB internals, such as the progress of data distribution among cluster nodes, is also written to the MySQL error log.

This variable was added in MySQL 5.1.6.

ndb_force_send

Forces sending of buffers to NDB immediately, without waiting for other threads. Defaults to ON.

ndb_index_stat_cache_entries

Sets the granularity of the statistics by determining the number of starting and ending keys to store in the statistics memory cache. Zero means no caching takes place; in this case, the data nodes are always queried directly. Default value: 32.

ndb_index_stat_enable

Use NDB index statistics in query optimization. Defaults to ON.

ndb_index_stat_update_freq

How often to query data nodes instead of the statistics cache. For example, a value of 20 (the default) means to direct every 20^th query to the data nodes.

ndb_optimized_node_selection

Prior to MySQL Cluster NDB 6.3.4.  Causes an SQL node to use the “closest” data node as transaction coordinator. Enabled by default. Set to 0 or OFF to disable, in which case the SQL node uses each data node in the cluster in succession. When this option is disabled each SQL thread attempts to use a given data node 8 times before proceeding to the next one.

Beginning with MySQL Cluster NDB 6.3.4.  There are two forms of optimized node selection:

This option takes one of the integer values 0, 1, 2, or 3. 3 is the default. These values affect node selection as follows:

ndb_report_thresh_binlog_epoch_slip

This is a threshold on the number of epochs to be behind before reporting binlog status. For example, a value of 3 (the default) means that if the difference between which epoch has been received from the storage nodes and which epoch has been applied to the binlog is 3 or more, a status message will be sent to the cluster log.

ndb_report_thresh_binlog_mem_usage

This is a threshold on the percentage of free memory remaining before reporting binlog status. For example, a value of 10 (the default) means that if the amount of available memory for receiving binlog data from the data nodes falls below 10%, a status message will be sent to the cluster log.

ndb_use_copying_alter_table

Forces NDB to use copying of tables in the event of problems with online ALTER TABLE operations. The default value is OFF.

This variable was added in MySQL 5.1.12.

ndb_use_exact_count

Forces NDB to use a count of records during SELECT COUNT(*) query planning to speed up this type of query. The default value is ON. For faster queries overall, disable this feature by setting the value of ndb_use_exact_count to OFF.

ndb_use_transactions

You can disable NDB transaction support by setting this variable's values to OFF (not recommended). The default is ON.

ndb_wait_connected

This variable can be used to cause the MySQL server to wait a given period of time for connections to MySQL Cluster management and data nodes to be established before accepting MySQL client connections. The time is specified in seconds. The default value is 0.

Handler_discover

The MySQL server can ask the NDBCLUSTER storage engine if it knows about a table with a given name. This is called discovery. Handler_discover indicates the number of times that tables have been discovered via this mechanism.

Ndb_cluster_node_id

If the server is acting as a MySQL Cluster node, then the value of this variable its node ID in the cluster.

If the server is not part of a MySQL Cluster, then the value of this variable is 0.

Ndb_config_from_host

If the server is part of a MySQL Cluster, the value of this variable is the host name or IP address of the Cluster management server from which it gets its configuration data.

If the server is not part of a MySQL Cluster, then the value of this variable is an empty string.

Prior to MySQL 5.1.12, this variable was named Ndb_connected_host.

Ndb_config_from_port

If the server is part of a MySQL Cluster, the value of this variable is the number of the port through which it is connected to the Cluster management server from which it gets its configuration data.

If the server is not part of a MySQL Cluster, then the value of this variable is 0.

Prior to MySQL 5.1.12, this variable was named Ndb_connected_port.

Ndb_execute_count

Provides the number of round trips to the NDB kernel made by operations. Added in MySQL Cluster NDB 6.3.6.

Ndb_number_of_data_nodes

If the server is part of a MySQL Cluster, the value of this variable is the number of data nodes in the cluster.

If the server is not part of a MySQL Cluster, then the value of this variable is 0.

Prior to MySQL 5.1.12, this variable was named Ndb_number_of_storage_nodes.

Slave_heartbeat_period

Shows the replication heartbeat interval (in seconds) on a replication slave.

This variable was added in MySQL Cluster NDB 6.3.4.

Slave_received_heartbeats

This counter increments with each replication heartbeat received by a replication slave since the last time that the slave was restarted or reset, or a CHANGE MASTER TO statement was issued.

This variable was added in MySQL Cluster NDB 6.3.4.

Ndb_pruned_scan_count

This variable holds a count of the number of scans executed by NDBCLUSTER since the MySQL Cluster was last started where NDBCLUSTER was able to use partition pruning.

Using this variable together with Ndb_scan_count can be helpful in schema design to maximize the ability of the server to prune scans to a single table partition, thereby involving only a single data node.

This variable was added in MySQL Cluster NDB 6.3.25 and MySQL Cluster NDB 7.0.5.

Ndb_scan_count

This variable holds a count of the total number of scans executed by NDBCLUSTER since the MySQL Cluster was last started.

This variable was added in MySQL Cluster NDB 6.3.25 and MySQL Cluster NDB 7.0.5.