4.3.9.1 MySQL Server Options for NDB Cluster

This section provides descriptions of mysqld server options relating to NDB Cluster. For information about mysqld options not specific to NDB Cluster, and for general information about the use of options with mysqld, see Server Command Options.

For information about command-line options used with other NDB Cluster processes, see Chapter 5, NDB Cluster Programs.

  • --ndbcluster

    Command-Line Format --ndbcluster[=value]
    Disabled by skip-ndbcluster
    Type Boolean
    Default Value OFF

    The NDBCLUSTER storage engine is necessary for using NDB 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.

    It is not necessary or desirable to use this option together with --initialize. Beginning with NDB 7.5.4, --ndbcluster is ignored (and the NDB storage engine is not enabled) if --initialize is also used. (Bug #81689, Bug #23518923)

  • --ndb-allow-copying-alter-table=[ON|OFF]

    Command-Line Format --ndb-allow-copying-alter-table[={OFF|ON}]
    Introduced 5.7.10-ndb-7.5.0
    System Variable ndb_allow_copying_alter_table
    Scope Global, Session
    Dynamic Yes
    Type Boolean
    Default Value ON

    Let ALTER TABLE and other DDL statements use copying operations on NDB tables. Set to OFF to keep this from happening; doing so may improve performance of critical applications.

  • --ndb-batch-size=#

    Command-Line Format --ndb-batch-size
    System Variable ndb_batch_size
    Scope Global, Session
    Dynamic Yes
    Type Integer
    Default Value 32768
    Minimum Value 0
    Maximum Value (≥ 5.7.37-ndb-7.6.22) 2147483648
    Maximum Value (≥ 5.7.37-ndb-7.5.26) 2147483648
    Maximum Value (≤ 5.7.37-ndb-7.5.25) 31536000
    Maximum Value (≤ 5.7.37-ndb-7.6.21) 31536000
    Maximum Value 31536000
    Unit bytes

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

  • --ndb-cluster-connection-pool=#

    Command-Line Format --ndb-cluster-connection-pool
    System Variable ndb_cluster_connection_pool
    System Variable ndb_cluster_connection_pool
    Scope Global
    Scope Global
    Dynamic No
    Dynamic No
    Type Integer
    Default Value 1
    Minimum Value 1
    Maximum Value 63

    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.

    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.

    Important

    Because each SQL node using connection pooling occupies multiple API node slots—each slot having its own node ID in the cluster—you must not use a node ID as part of the cluster connection string when starting any mysqld process that employs connection pooling.

    Setting a node ID in the connection string when using the --ndb-cluster-connection-pool option causes node ID allocation errors when the SQL node attempts to connect to the cluster.

  • --ndb-cluster-connection-pool-nodeids=list

    Command-Line Format --ndb-cluster-connection-pool-nodeids
    Introduced 5.7.10-ndb-7.5.0
    System Variable (≥ 5.7.10-ndb-7.5.0) ndb_cluster_connection_pool_nodeids
    Scope (≥ 5.7.10-ndb-7.5.0) Global
    Dynamic (≥ 5.7.10-ndb-7.5.0) No
    Type Set
    Default Value

    Specifies a comma-separated list of node IDs for connections to the cluster used by an SQL node. The number of nodes in this list must be the same as the value set for the --ndb-cluster-connection-pool option.

    --ndb-cluster-connection-pool-nodeids was added in NDB 7.5.0.

  • --ndb-blob-read-batch-bytes=bytes

    Command-Line Format --ndb-blob-read-batch-bytes
    System Variable ndb_blob_read_batch_bytes
    Scope Global, Session
    Dynamic Yes
    Type Integer
    Default Value 65536
    Minimum Value 0
    Maximum Value 4294967295

    This option can be used to set the size (in bytes) for batching of BLOB data reads in NDB Cluster applications. When this batch size is exceeded by the amount of BLOB data to be read within the current transaction, any pending BLOB read operations are immediately executed.

    The maximum value for this option is 4294967295; the default is 65536. Setting it to 0 has the effect of disabling BLOB read batching.

    Note

    In NDB API applications, you can control BLOB write batching with the setMaxPendingBlobReadBytes() and getMaxPendingBlobReadBytes() methods.

  • --ndb-blob-write-batch-bytes=bytes

    Command-Line Format --ndb-blob-write-batch-bytes
    System Variable ndb_blob_write_batch_bytes
    Scope Global, Session
    Dynamic Yes
    Type Integer
    Default Value 65536
    Minimum Value 0
    Maximum Value 4294967295
    Unit bytes

    This option can be used to set the size (in bytes) for batching of BLOB data writes in NDB Cluster applications. When this batch size is exceeded by the amount of BLOB data to be written within the current transaction, any pending BLOB write operations are immediately executed.

    The maximum value for this option is 4294967295; the default is 65536. Setting it to 0 has the effect of disabling BLOB write batching.

    Note

    In NDB API applications, you can control BLOB write batching with the setMaxPendingBlobWriteBytes() and getMaxPendingBlobWriteBytes() methods.

  • --ndb-connectstring=connection_string

    Command-Line Format --ndb-connectstring
    Type String

    When using the NDBCLUSTER storage engine, this option specifies the management server that distributes cluster configuration data. See Section 4.3.3, “NDB Cluster Connection Strings”, for syntax.

  • --ndb-default-column-format=[FIXED|DYNAMIC]

    Command-Line Format --ndb-default-column-format={FIXED|DYNAMIC}
    Introduced 5.7.11-ndb-7.5.1
    System Variable ndb_default_column_format
    Scope Global
    Dynamic Yes
    Type Enumeration
    Default Value (≥ 5.7.16-ndb-7.5.4) FIXED
    Default Value (≥ 5.7.11-ndb-7.5.1, ≤ 5.7.13-ndb-7.5.3) DYNAMIC
    Valid Values

    FIXED

    DYNAMIC

    In NDB 7.5.1 and later, sets the default COLUMN_FORMAT and ROW_FORMAT for new tables (see CREATE TABLE Statement).

    In NDB 7.5.1, the default for this option was DYNAMIC; in NDB 7.5.4, the default was changed to FIXED to maintain backwards compatibility with older release series (Bug #24487363).

  • --ndb-deferred-constraints=[0|1]

    Command-Line Format --ndb-deferred-constraints
    System Variable ndb_deferred_constraints
    Scope Global, Session
    Dynamic Yes
    Type Integer
    Default Value 0
    Minimum Value 0
    Maximum Value 1

    Controls whether or not constraint checks on unique indexes are deferred until commit time, where such checks are supported. 0 is the default.

    This option is not normally needed for operation of NDB Cluster or NDB Cluster Replication, and is intended primarily for use in testing.

  • --ndb-distribution=[KEYHASH|LINHASH]

    Command-Line Format --ndb-distribution={KEYHASH|LINHASH}
    System Variable ndb_distribution
    Scope Global
    Dynamic Yes
    Type Enumeration
    Default Value KEYHASH
    Valid Values

    LINHASH

    KEYHASH

    Controls the default distribution method for NDB tables. Can be set to either of KEYHASH (key hashing) or LINHASH (linear hashing). KEYHASH is the default.

  • --ndb-log-apply-status

    Command-Line Format --ndb-log-apply-status[={OFF|ON}]
    System Variable ndb_log_apply_status
    Scope Global
    Dynamic No
    Type Boolean
    Default Value OFF

    Causes a replica mysqld to log any updates received from its immediate source to the mysql.ndb_apply_status table in its own binary log using its own server ID rather than the server ID of the source. In a circular or chain replication setting, this allows such updates to propagate to the mysql.ndb_apply_status tables of any MySQL servers configured as replicas of the current mysqld.

    In a chain replication setup, using this option allows downstream (replica) clusters to be aware of their positions relative to all of their upstream contributors (sources).

    In a circular replication setup, this option causes changes to ndb_apply_status tables to complete the entire circuit, eventually propagating back to the originating NDB Cluster. This also allows a cluster acting as a source to see when its changes (epochs) have been applied to the other clusters in the circle.

    This option has no effect unless the MySQL server is started with the --ndbcluster option.

  • --ndb-log-empty-epochs=[ON|OFF]

    Command-Line Format --ndb-log-empty-epochs[={OFF|ON}]
    System Variable ndb_log_empty_epochs
    Scope Global
    Dynamic Yes
    Type Boolean
    Default Value OFF

    Causes epochs during which there were no changes to be written to the ndb_apply_status and ndb_binlog_index tables, even when log_slave_updates is enabled.

    By default this option is disabled. Disabling --ndb-log-empty-epochs causes epoch transactions with no changes not to be written to the binary log, although a row is still written even for an empty epoch in ndb_binlog_index.

    Because --ndb-log-empty-epochs=1 causes the size of the ndb_binlog_index table to increase independently of the size of the binary log, users should be prepared to manage the growth of this table, even if they expect the cluster to be idle a large part of the time.

  • --ndb-log-empty-update=[ON|OFF]

    Command-Line Format --ndb-log-empty-update[={OFF|ON}]
    System Variable ndb_log_empty_update
    Scope Global
    Dynamic Yes
    Type Boolean
    Default Value OFF

    Causes updates that produced no changes to be written to the ndb_apply_status and ndb_binlog_index tables, when when log_slave_updates is enabled.

    By default this option is disabled (OFF). Disabling --ndb-log-empty-update causes updates with no changes not to be written to the binary log.

  • --ndb-log-exclusive-reads=[0|1]

    Command-Line Format --ndb-log-exclusive-reads[={OFF|ON}]
    System Variable ndb_log_exclusive_reads
    Scope Global, Session
    Dynamic Yes
    Type Boolean
    Default Value 0

    Starting the server with this option causes primary key reads to be logged with exclusive locks, which allows for NDB Cluster Replication conflict detection and resolution based on read conflicts. You can also enable and disable these locks at runtime by setting the value of the ndb_log_exclusive_reads system variable to 1 or 0, respectively. 0 (disable locking) is the default.

    For more information, see Read conflict detection and resolution.

  • --ndb-log-fail-terminate

    Command-Line Format --ndb-log-fail-terminate
    Introduced 5.7.29-ndb-7.6.14
    System Variable ndb_log_fail_terminate
    Scope Global
    Dynamic No
    Type Boolean
    Default Value FALSE

    When this option is specified, and complete logging of all found row events is not possible, the mysqld process is terminated.

  • --ndb-log-orig

    Command-Line Format --ndb-log-orig[={OFF|ON}]
    System Variable ndb_log_orig
    Scope Global
    Dynamic No
    Type Boolean
    Default Value OFF

    Log the originating server ID and epoch in the ndb_binlog_index table.

    Note

    This makes it possible for a given epoch to have multiple rows in ndb_binlog_index, one for each originating epoch.

    For more information, see Section 7.4, “NDB Cluster Replication Schema and Tables”.

  • --ndb-log-transaction-id

    Command-Line Format --ndb-log-transaction-id[={OFF|ON}]
    System Variable ndb_log_transaction_id
    Scope Global
    Dynamic No
    Type Boolean
    Default Value OFF

    Causes a replica mysqld to write the NDB transaction ID in each row of the binary log. Such logging requires the use of the Version 2 event format for the binary log; thus, the log_bin_use_v1_row_events system variable must be disabled to use this option.

    --ndb-log-transaction-id is required to enable NDB Cluster Replication conflict detection and resolution using the NDB$EPOCH_TRANS() function (see NDB$EPOCH_TRANS()).

    For more information, see Section 7.11, “NDB Cluster Replication Conflict Resolution”.

  • --ndb-log-update-as-write

    Command-Line Format --ndb-log-update-as-write[={OFF|ON}]
    System Variable ndb_log_update_as_write
    Scope Global
    Dynamic Yes
    Type Boolean
    Default Value ON

    Whether updates on the source are written to the binary log as updates (OFF) or writes (ON). When this option is enabled, and both --ndb-log-updated-only and --ndb-log-update-minimal are disabled, operations of different types are loǵged as described in the following list:

    • INSERT: Logged as a WRITE_ROW event with no before image; the after image is logged with all columns.

      UPDATE: Logged as a WRITE_ROW event with no before image; the after image is logged with all columns.

      DELETE: Logged as a DELETE_ROW event with all columns logged in the before image; the after image is not logged.

    This option can be used for NDB Replication conflict resolution in combination with the other two NDB logging options mentioned previously; see ndb_replication Table, for more information.

  • --ndb-log-updated-only

    Command-Line Format --ndb-log-updated-only[={OFF|ON}]
    System Variable ndb_log_updated_only
    Scope Global
    Dynamic Yes
    Type Boolean
    Default Value ON

    Whether mysqld writes updates only (ON) or complete rows (OFF) to the binary log. When this option is enabled, and both --ndb-log-update-as-write and --ndb-log-update-minimal are disabled, operations of different types are loǵged as described in the following list

    • INSERT: Logged as a WRITE_ROW event with no before image; the after image is logged with all columns.

    • UPDATE: Logged as an UPDATE_ROW event with primary key columns and updated columns present in both the before and after images.

    • DELETE: Logged as a DELETE_ROW event with primary key columns incuded in the before image; the after image is not logged.

    This option can be used for NDB Replication conflict resolution in combination with the other two NDB logging options mentioned previously; see ndb_replication Table, for more information about how these options interact with one another.

  • --ndb-log-update-minimal

    Command-Line Format --ndb-log-update-minimal[={OFF|ON}]
    Introduced 5.7.18-ndb-7.6.3
    System Variable ndb_log_update_minimal
    Scope Global
    Dynamic Yes
    Type Boolean
    Default Value OFF

    Log updates in a minimal fashion, by writing only the primary key values in the before image, and only the changed columns in the after image. This may cause compatibility problems if replicating to storage engines other than NDB. When this option is enabled, and both --ndb-log-updated-only and --ndb-log-update-as-write are disabled, operations of different types are loǵged as described in the following list:

    • INSERT: Logged as a WRITE_ROW event with no before image; the after image is logged with all columns.

    • UPDATE: Logged as an UPDATE_ROW event with primary key columns in the before image; all columns except primary key columns are logged in the after image.

    • DELETE: Logged as a DELETE_ROW event with all columns in the before image; the after image is not logged.

    This option can be used for NDB Replication conflict resolution in combination with the other two NDB logging options mentioned previously; see ndb_replication Table, for more information.

  • --ndb-mgmd-host=host[:port]

    Command-Line Format --ndb-mgmd-host=host_name[:port_num]
    Type String
    Default Value localhost:1186

    Can be used to set the host and port number of a single management server for the program to connect to. If the program requires node IDs or references to multiple management servers (or both) in its connection information, use the --ndb-connectstring option instead.

  • --ndb-nodeid=#

    Command-Line Format --ndb-nodeid=#
    Status Variable Ndb_cluster_node_id
    Scope Global
    Dynamic No
    Type Integer
    Default Value N/A
    Minimum Value 1
    Maximum Value 255
    Maximum Value 63

    Set this MySQL server's node ID in an NDB Cluster.

    The --ndb-nodeid option overrides any node ID set with --ndb-connectstring, regardless of the order in which the two options are used.

    In addition, if --ndb-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 a NodeId or Id parameter specified). This is also true if the node ID is specified as part of the connection string.

    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 NDB Cluster SQL nodes, see Section 4.3.7, “Defining SQL and Other API Nodes in an NDB Cluster”.

  • --ndb-optimization-delay=milliseconds

    Command-Line Format --ndb-optimization-delay=#
    System Variable ndb_optimization_delay
    Scope Global
    Dynamic Yes
    Type Integer
    Default Value 10
    Minimum Value 0
    Maximum Value 100000
    Unit milliseconds

    Set the number of milliseconds to wait between sets of rows by OPTIMIZE TABLE statements on NDB tables. The default is 10.

  • --ndb-optimized-node-selection

    Command-Line Format --ndb-optimized-node-selection

    Enable optimizations for selection of nodes for transactions. Enabled by default; use --skip-ndb-optimized-node-selection to disable.

  • --ndb-transid-mysql-connection-map=state

    Command-Line Format --ndb-transid-mysql-connection-map[=state]
    Type Enumeration
    Default Value ON
    Valid Values

    ON

    OFF

    FORCE

    Enables or disables the plugin that handles the ndb_transid_mysql_connection_map table in the INFORMATION_SCHEMA database. Takes one of the values ON, OFF, or FORCE. ON (the default) enables the plugin. OFF disables the plugin, which makes ndb_transid_mysql_connection_map inaccessible. FORCE keeps the MySQL Server from starting if the plugin fails to load and start.

    You can see whether the ndb_transid_mysql_connection_map table plugin is running by checking the output of SHOW PLUGINS.

  • --ndb-wait-connected=seconds

    Command-Line Format --ndb-wait-connected=#
    System Variable ndb_wait_connected
    Scope Global
    Dynamic No
    Type Integer
    Default Value 30
    Minimum Value 0
    Maximum Value 31536000
    Unit seconds

    This option sets the period of time that the MySQL server waits for connections to NDB Cluster management and data nodes to be established before accepting MySQL client connections. The time is specified in seconds. The default value is 30.

  • --ndb-wait-setup=seconds

    Command-Line Format --ndb-wait-setup=#
    System Variable ndb_wait_setup
    Scope Global
    Dynamic No
    Type Integer
    Default Value 30
    Default Value 30
    Default Value 15
    Default Value 15
    Minimum Value 0
    Maximum Value 31536000
    Unit seconds

    This variable shows the period of time that the MySQL server waits for the NDB storage engine to complete setup before timing out and treating NDB as unavailable. The time is specified in seconds. The default value is 30.

  • --skip-ndbcluster

    Command-Line Format --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 4.1, “Quick Test Setup of NDB Cluster”, for an example.