Documentation Home
MySQL 9.1 Reference Manual
Related Documentation
MySQL 9.1 Release Notes
Download this Manual
PDF (US Ltr) - 40.3Mb
PDF (A4) - 40.4Mb
Man Pages (TGZ) - 259.3Kb
Man Pages (Zip) - 366.4Kb
Info (Gzip) - 4.0Mb
Info (Zip) - 4.0Mb
MySQL 9.1 Reference Manual  /  ...  /  MySQL Server Options and Variables for NDB Cluster

25.4.3.9 MySQL Server Options and Variables for NDB Cluster

This section provides information about MySQL server options, server and status variables that are specific to NDB Cluster. For general information on using these, and for other options and variables not specific to NDB Cluster, see Section 7.1, “The MySQL Server”.

For NDB Cluster configuration parameters used in the cluster configuration file (usually named config.ini), see Section 25.4, “Configuration of NDB Cluster”.

25.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 Section 7.1.7, “Server Command Options”.

For information about command-line options used with other NDB Cluster processes, see Section 25.5, “NDB Cluster Programs”.

  • --ndbcluster

    Command-Line Format --ndbcluster[=value]
    Disabled by skip-ndbcluster
    Type Enumeration
    Default Value ON
    Valid Values

    OFF

    FORCE

    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.

    The --ndbcluster option is ignored (and the NDB storage engine is not enabled) if --initialize is also used. (It is neither necessary nor desirable to use this option together with --initialize.)

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

    Command-Line Format --ndb-allow-copying-alter-table[={OFF|ON}]
    System Variable ndb_allow_copying_alter_table
    Scope Global, Session
    Dynamic Yes
    SET_VAR Hint Applies No
    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-applier-allow-skip-epoch

    Command-Line Format --ndb-applier-allow-skip-epoch
    System Variable ndb_applier_allow_skip_epoch
    Scope Global
    Dynamic No
    SET_VAR Hint Applies No

    Use together with --replica-skip-errors to cause NDB to ignore skipped epoch transactions. Has no effect when used alone.

  • --ndb-batch-size=#

    Command-Line Format --ndb-batch-size
    System Variable ndb_batch_size
    Scope Global, Session
    Dynamic Yes
    SET_VAR Hint Applies No
    Type Integer
    Default Value 32768
    Minimum Value 0
    Maximum Value 2147483648
    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
    SET_VAR Hint Applies No
    SET_VAR Hint Applies 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
    System Variable ndb_cluster_connection_pool_nodeids
    Scope Global
    Dynamic No
    SET_VAR Hint Applies 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-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
    SET_VAR Hint Applies No
    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
    SET_VAR Hint Applies No
    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 25.4.3.3, “NDB Cluster Connection Strings”, for syntax.

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

    Command-Line Format --ndb-default-column-format={FIXED|DYNAMIC}
    System Variable ndb_default_column_format
    Scope Global
    Dynamic Yes
    SET_VAR Hint Applies No
    Type Enumeration
    Default Value FIXED
    Valid Values

    FIXED

    DYNAMIC

    Sets the default COLUMN_FORMAT and ROW_FORMAT for new tables (see Section 15.1.20, “CREATE TABLE Statement”). The default is FIXED.

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

    Command-Line Format --ndb-deferred-constraints
    System Variable ndb_deferred_constraints
    Scope Global, Session
    Dynamic Yes
    SET_VAR Hint Applies No
    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-schema-dist-timeout=#

    Command-Line Format --ndb-schema-dist-timeout=#
    System Variable ndb_schema_dist_timeout
    Scope Global
    Dynamic No
    SET_VAR Hint Applies No
    Type Integer
    Default Value 120
    Minimum Value 5
    Maximum Value 1200
    Unit seconds

    Specifies the maximum time in seconds that this mysqld waits for a schema operation to complete before marking it as having timed out.

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

    Command-Line Format --ndb-distribution={KEYHASH|LINHASH}
    System Variable ndb_distribution
    Scope Global
    Dynamic Yes
    SET_VAR Hint Applies No
    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
    SET_VAR Hint Applies 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 (sourcess).

    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 replication 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
    SET_VAR Hint Applies No
    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_replica_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
    SET_VAR Hint Applies No
    Type Boolean
    Default Value OFF

    Causes updates that produced no changes to be written to the ndb_apply_status and ndb_binlog_index tables, even when log_replica_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
    SET_VAR Hint Applies No
    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
    System Variable ndb_log_fail_terminate
    Scope Global
    Dynamic No
    SET_VAR Hint Applies 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
    SET_VAR Hint Applies 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 25.7.4, “NDB Cluster Replication Schema and Tables”.

  • --ndb-log-transaction-dependency

    Command-Line Format --ndb-log-transaction-dependency={true|false}
    System Variable ndb_log_transaction_dependency
    Scope Global
    Dynamic No
    SET_VAR Hint Applies No
    Type Boolean
    Default Value false

    Causes the NDB binary logging thread to calculate transaction dependencies for each transaction which it writes to the binary log. The default value is FALSE.

    This option cannot be set at runtime; the corresponding ndb_log_transaction_dependency system variable is read-only.

  • --ndb-log-transaction-id

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

    Causes a replica mysqld to write the NDB transaction ID in each row of the binary log. The default value is FALSE.

    --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 25.7.12, “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
    SET_VAR Hint Applies No
    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
    SET_VAR Hint Applies No
    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}]
    System Variable ndb_log_update_minimal
    Scope Global
    Dynamic Yes
    SET_VAR Hint Applies No
    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-mgm-tls=[relaxed|strict]

    Command-Line Format --ndb-mgm-tls=[strict|relaxed]
    System Variable ndb_mgm_tls
    Scope Global
    Dynamic No
    SET_VAR Hint Applies No
    Type Enumeration
    Default Value relaxed
    Valid Values

    relaxed

    strict

    Sets the level of TLS support required for TLS connections to NDB Cluster; the value is one of relaxed or strict. relaxed means that a TLS connection is attempted, but success is not required; strict means that TLS is required to connect. The default is relaxed.

  • --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, it 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 25.4.3.7, “Defining SQL and Other API Nodes in an NDB Cluster”.

  • --ndbinfo={ON|OFF|FORCE}

    Command-Line Format --ndbinfo[=value]
    Type Enumeration
    Default Value ON
    Valid Values

    ON

    OFF

    FORCE

    Enables the plugin for the ndbinfo information database. By default this is ON whenever NDBCLUSTER is enabled.

  • --ndb-optimization-delay=milliseconds

    Command-Line Format --ndb-optimization-delay=#
    System Variable ndb_optimization_delay
    Scope Global
    Dynamic Yes
    SET_VAR Hint Applies No
    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-tls-search-path=path

    Command-Line Format --ndb-tls-search-path=path
    System Variable ndb_tls_search_path
    Scope Global
    Dynamic No
    SET_VAR Hint Applies No
    Type Path name
    Default Value (Unix) $HOME/tls
    Default Value (Windows) $HOMEDIR/tls

    List of directories to search for CAs and private keys for NDB TLS connections. The list is comma-delimited on Unix platforms and semicolon-delimited on Windows.

  • --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
    SET_VAR Hint Applies No
    Type Integer
    Default Value 120
    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
    SET_VAR Hint Applies No
    Type Integer
    Default Value 120
    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 25.4.1, “Quick Test Setup of NDB Cluster”, for an example.

25.4.3.9.2 NDB Cluster System Variables

This section provides detailed information about MySQL server system variables that are specific to NDB Cluster and the NDB storage engine. For system variables not specific to NDB Cluster, see Section 7.1.8, “Server System Variables”. For general information on using system variables, see Section 7.1.9, “Using System Variables”.

  • ndb_autoincrement_prefetch_sz

    Command-Line Format --ndb-autoincrement-prefetch-sz=#
    System Variable ndb_autoincrement_prefetch_sz
    Scope Global, Session
    Dynamic Yes
    SET_VAR Hint Applies No
    Type Integer
    Default Value 512
    Minimum Value 1
    Maximum Value 65536

    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 of consecutive autoincrement numbers being used in a batch of inserts.

    This variable affects only the number of AUTO_INCREMENT IDs that are fetched between statements; within a given statement, at least 32 IDs are obtained at a time.

    Important

    This variable does not affect inserts performed using INSERT ... SELECT.

  • ndb_clear_apply_status

    Command-Line Format --ndb-clear-apply-status[={OFF|ON}]
    System Variable ndb_clear_apply_status
    Scope Global
    Dynamic Yes
    SET_VAR Hint Applies No
    Type Boolean
    Default Value ON

    By the default, executing RESET REPLICA causes an NDB Cluster replica to purge all rows from its ndb_apply_status table. You can disable this by setting ndb_clear_apply_status=OFF.

  • ndb_conflict_role

    Command-Line Format --ndb-conflict-role=value
    System Variable ndb_conflict_role
    Scope Global
    Dynamic Yes
    SET_VAR Hint Applies No
    Type Enumeration
    Default Value NONE
    Valid Values

    NONE

    PRIMARY

    SECONDARY

    PASS

    Determines the role of this SQL node (and NDB Cluster) in a circular (“active-active”) replication setup. ndb_conflict_role can take any one of the values PRIMARY, SECONDARY, PASS, or NULL (the default). The replica SQL thread must be stopped before you can change ndb_conflict_role. In addition, it is not possible to change directly between PASS and either of PRIMARY or SECONDARY directly; in such cases, you must ensure that the SQL thread is stopped, then execute SET @@GLOBAL.ndb_conflict_role = 'NONE' first.

    This variable replaces the deprecated ndb_slave_conflict_role.

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

  • ndb_data_node_neighbour

    Command-Line Format --ndb-data-node-neighbour=#
    System Variable ndb_data_node_neighbour
    Scope Global
    Dynamic Yes
    SET_VAR Hint Applies No
    Type Integer
    Default Value 0
    Minimum Value 0
    Maximum Value 255

    Sets the ID of a “nearest” data node—that is, a preferred nonlocal data node is chosen to execute the transaction, rather than one running on the same host as the SQL or API node. This used to ensure that when a fully replicated table is accessed, we access it on this data node, to ensure that the local copy of the table is always used whenever possible. This can also be used for providing hints for transactions.

    This can improve data access times in the case of a node that is physically closer than and thus has higher network throughput than others on the same host.

    See Section 15.1.20.12, “Setting NDB Comment Options”, for further information.

    Note

    An equivalent method set_data_node_neighbour() is provided for use in NDB API applications.

  • ndb_dbg_check_shares

    Command-Line Format --ndb-dbg-check-shares=#
    System Variable ndb_dbg_check_shares
    Scope Global, Session
    Dynamic Yes
    SET_VAR Hint Applies No
    Type Integer
    Default Value 0
    Minimum Value 0
    Maximum Value 1

    When set to 1, check that no shares are lingering. Available in debug builds only.

  • ndb_default_column_format

    Command-Line Format --ndb-default-column-format={FIXED|DYNAMIC}
    System Variable ndb_default_column_format
    Scope Global
    Dynamic Yes
    SET_VAR Hint Applies No
    Type Enumeration
    Default Value FIXED
    Valid Values

    FIXED

    DYNAMIC

    Sets the default COLUMN_FORMAT and ROW_FORMAT for new tables (see Section 15.1.20, “CREATE TABLE Statement”). The default is FIXED.

  • ndb_deferred_constraints

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

    Controls whether or not constraint checks are deferred, where these are supported. 0 is the default.

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

  • ndb_distribution

    Command-Line Format --ndb-distribution={KEYHASH|LINHASH}
    System Variable ndb_distribution
    Scope Global
    Dynamic Yes
    SET_VAR Hint Applies No
    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_eventbuffer_free_percent

    Command-Line Format --ndb-eventbuffer-free-percent=#
    System Variable ndb_eventbuffer_free_percent
    Scope Global
    Dynamic Yes
    SET_VAR Hint Applies No
    Type Integer
    Default Value 20
    Minimum Value 1
    Maximum Value 99

    Sets the percentage of the maximum memory allocated to the event buffer (ndb_eventbuffer_max_alloc) that should be available in event buffer after reaching the maximum, before starting to buffer again.

  • ndb_eventbuffer_max_alloc

    Command-Line Format --ndb-eventbuffer-max-alloc=#
    System Variable ndb_eventbuffer_max_alloc
    Scope Global
    Dynamic Yes
    SET_VAR Hint Applies No
    Type Integer
    Default Value 0
    Minimum Value 0
    Maximum Value 9223372036854775807

    Sets the maximum amount memory (in bytes) that can be allocated for buffering events by the NDB API. 0 means that no limit is imposed, and is the default.

  • ndb_extra_logging

    Command-Line Format ndb_extra_logging=#
    System Variable ndb_extra_logging
    Scope Global
    Dynamic Yes
    SET_VAR Hint Applies No
    Type Integer
    Default Value 1
    Minimum Value 0
    Maximum Value 1

    This variable enables recording in the MySQL error log of information specific to the NDB storage engine.

    When this variable is set to 0, the only information specific to NDB that is written to the MySQL error log relates to transaction handling. If it set to a value 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. The default is 1.

  • ndb_force_send

    Command-Line Format --ndb-force-send[={OFF|ON}]
    System Variable ndb_force_send
    Scope Global, Session
    Dynamic Yes
    SET_VAR Hint Applies No
    Type Boolean
    Default Value ON

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

  • ndb_fully_replicated

    Command-Line Format --ndb-fully-replicated[={OFF|ON}]
    System Variable ndb_fully_replicated
    Scope Global, Session
    Dynamic Yes
    SET_VAR Hint Applies No
    Type Boolean
    Default Value OFF

    Determines whether new NDB tables are fully replicated. This setting can be overridden for an individual table using COMMENT="NDB_TABLE=FULLY_REPLICATED=..." in a CREATE TABLE or ALTER TABLE statement; see Section 15.1.20.12, “Setting NDB Comment Options”, for syntax and other information.

  • ndb_index_stat_enable

    Command-Line Format --ndb-index-stat-enable[={OFF|ON}]
    System Variable ndb_index_stat_enable
    Scope Global, Session
    Dynamic Yes
    SET_VAR Hint Applies No
    Type Boolean
    Default Value ON

    Use NDB index statistics in query optimization. The default is ON.

    The index statistics tables are always created when the server starts, regardless of this option's value.

  • ndb_index_stat_option

    Command-Line Format --ndb-index-stat-option=value
    System Variable ndb_index_stat_option
    Scope Global, Session
    Dynamic Yes
    SET_VAR Hint Applies No
    Type String
    Default Value loop_checkon=1000ms,loop_idle=1000ms,loop_busy=100ms, update_batch=1,read_batch=4,idle_batch=32,check_batch=32, check_delay=1m,delete_batch=8,clean_delay=0,error_batch=4, error_delay=1m,evict_batch=8,evict_delay=1m,cache_limit=32M, cache_lowpct=90

    This variable is used for providing tuning options for NDB index statistics generation. The list consist of comma-separated name-value pairs of option names and values, and this list must not contain any space characters.

    Options not used when setting ndb_index_stat_option are not changed from their default values. For example, you can set ndb_index_stat_option = 'loop_idle=1000ms,cache_limit=32M'.

    Time values can be optionally suffixed with h (hours), m (minutes), or s (seconds). Millisecond values can optionally be specified using ms; millisecond values cannot be specified using h, m, or s.) Integer values can be suffixed with K, M, or G.

    The names of the options that can be set using this variable are shown in the table that follows. The table also provides brief descriptions of the options, their default values, and (where applicable) their minimum and maximum values.

    Table 25.19 ndb_index_stat_option options and values

    Name Description Default/Units Minimum/Maximum
    loop_enable 1000 ms 0/4G
    loop_idle Time to sleep when idle 1000 ms 0/4G
    loop_busy Time to sleep when more work is waiting 100 ms 0/4G
    update_batch 1 0/4G
    read_batch 4 1/4G
    idle_batch 32 1/4G
    check_batch 8 1/4G
    check_delay How often to check for new statistics 10 m 1/4G
    delete_batch 8 0/4G
    clean_delay 1 m 0/4G
    error_batch 4 1/4G
    error_delay 1 m 1/4G
    evict_batch 8 1/4G
    evict_delay Clean LRU cache, from read time 1 m 0/4G
    cache_limit Maximum amount of memory in bytes used for cached index statistics by this mysqld; clean up the cache when this is exceeded. 32 M 0/4G
    cache_lowpct 90 0/100
    zero_total Setting this to 1 resets all accumulating counters in ndb_index_stat_status to 0. This option value is also reset to 0 when this is done. 0 0/1

  • ndb_join_pushdown

    System Variable ndb_join_pushdown
    Scope Global, Session
    Dynamic Yes
    SET_VAR Hint Applies No
    Type Boolean
    Default Value ON

    This variable controls whether joins on NDB tables are pushed down to the NDB kernel (data nodes). Previously, a join was handled using multiple accesses of NDB by the SQL node; however, when ndb_join_pushdown is 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.

    By default, ndb_join_pushdown is enabled.

    Conditions for NDB pushdown joins.  In order for a join to be pushable, it must meet the following conditions:

    1. Only columns can be compared, and all columns to be joined must use exactly the same data type. This means that (for example) a join on an INT column and a BIGINT column also cannot be pushed down.

      Expressions comparing columns from the same table can also be pushed down. The columns (or the result of any operations on those columns) must be of exactly the same type, including the same signedness, length, character set and collation, precision, and scale, where these are applicable.

    2. Queries referencing BLOB or TEXT columns are not supported.

    3. Explicit locking is not supported; however, the NDB storage engine's characteristic implicit row-based locking is enforced.

      This means that a join using FOR UPDATE cannot be pushed down.

    4. In order for a join to be pushed down, child tables in the join must be accessed using one of the ref, eq_ref, or  const access methods, or some combination of these methods.

      Outer joined child tables can only be pushed using eq_ref.

      If the root of the pushed join is an eq_ref or const, only child tables joined by eq_ref can be appended. (A table joined by ref is likely to become the root of another pushed join.)

      If the query optimizer decides on Using join cache for a candidate child table, that table cannot be pushed as a child. However, it may be the root of another set of pushed tables.

    5. Joins referencing tables explicitly partitioned by [LINEAR] HASH, LIST, or RANGE currently cannot be pushed down.

    You can see whether a given join can be pushed down by checking it with EXPLAIN; when the join can be pushed down, you can see references to the pushed join in the Extra column of the output, as shown in this example: 

    mysql> EXPLAIN
    ->     SELECT e.first_name, e.last_name, t.title, d.dept_name
    ->         FROM employees e
    ->         JOIN dept_emp de ON e.emp_no=de.emp_no
    ->         JOIN departments d ON d.dept_no=de.dept_no
    ->         JOIN titles t ON e.emp_no=t.emp_no\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: d
         type: ALL
possible_keys: PRIMARY
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 9
        Extra: Parent of 4 pushed join@1
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: de
         type: ref
possible_keys: PRIMARY,emp_no,dept_no
          key: dept_no
      key_len: 4
          ref: employees.d.dept_no
         rows: 5305
        Extra: Child of 'd' in pushed join@1
*************************** 3. row ***************************
           id: 1
  select_type: SIMPLE
        table: e
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: employees.de.emp_no
         rows: 1
        Extra: Child of 'de' in pushed join@1
*************************** 4. row ***************************
           id: 1
  select_type: SIMPLE
        table: t
         type: ref
possible_keys: PRIMARY,emp_no
          key: emp_no
      key_len: 4
          ref: employees.de.emp_no
         rows: 19
        Extra: Child of 'e' in pushed join@1
4 rows in set (0.00 sec)
    Note

    If inner joined child tables are joined by ref, and the result is ordered or grouped by a sorted index, this index cannot provide sorted rows, which forces writing to a sorted tempfile.

    Two additional sources of information about pushed join performance are available:

    1. The status variables Ndb_pushed_queries_defined, Ndb_pushed_queries_dropped, Ndb_pushed_queries_executed, and Ndb_pushed_reads.

    2. The counters in the ndbinfo.counters table that belong to the DBSPJ kernel block.

  • ndb_log_apply_status

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

    A read-only variable which shows whether the server was started with the --ndb-log-apply-status option.

  • ndb_log_bin

    Command-Line Format --ndb-log-bin[={OFF|ON}]
    System Variable ndb_log_bin
    Scope Global, Session
    Dynamic No
    SET_VAR Hint Applies No
    Type Boolean
    Default Value OFF

    Causes updates to NDB tables to be written to the binary log. The setting for this variable has no effect if binary logging is not already enabled on the server using log_bin. ndb_log_bin defaults to 0 (FALSE).

  • ndb_log_binlog_index

    Command-Line Format --ndb-log-binlog-index[={OFF|ON}]
    System Variable ndb_log_binlog_index
    Scope Global
    Dynamic Yes
    SET_VAR Hint Applies No
    Type Boolean
    Default Value ON

    Causes a mapping of epochs to positions in the binary log to be inserted into the ndb_binlog_index table. Setting this variable has no effect if binary logging is not already enabled for the server using log_bin. (In addition, ndb_log_bin must not be disabled.) ndb_log_binlog_index defaults to 1 (ON); normally, there is never any need to change this value in a production environment.

  • ndb_log_cache_size

    Command-Line Format --ndb-log-cache-size=#
    Introduced 9.1.0-ndb-9.1.0
    System Variable ndb_log_cache_size
    Scope Global
    Dynamic Yes
    SET_VAR Hint Applies No
    Type Integer
    Default Value 64M
    Minimum Value 4096
    Maximum Value 18446744073709551615

    Set the size of the transaction cache used session for writing the NDB binary log.

  • ndb_log_empty_epochs

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

    When this variable is set to 0, epoch transactions with no changes are not written to the binary log, although a row is still written even for an empty epoch in ndb_binlog_index.

  • ndb_log_empty_update

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

    When this variable is set to ON (1), update transactions with no changes are written to the binary log, even when log_replica_updates is enabled.

  • ndb_log_exclusive_reads

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

    This variable determines whether primary key reads are logged with exclusive locks, which allows for NDB Cluster Replication conflict detection and resolution based on read conflicts. To enable these locks, set the value of ndb_log_exclusive_reads to 1. 0, which disables such locking, is the default.

    For more information, see Read conflict detection and resolution.

  • ndb_log_orig

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

    Shows whether the originating server ID and epoch are logged in the ndb_binlog_index table. Set using the --ndb-log-orig server option.

  • ndb_log_transaction_id

    System Variable ndb_log_transaction_id
    Scope Global
    Dynamic No
    SET_VAR Hint Applies No
    Type Boolean
    Default Value OFF

    This read-only, Boolean system variable shows whether a replica mysqld writes NDB transaction IDs in the binary log (required to use “active-active” NDB Cluster Replication with NDB$EPOCH_TRANS() conflict detection). To change the setting, use the --ndb-log-transaction-id option.

    ndb_log_transaction_id is not supported in mainline MySQL Server 9.1.

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

  • ndb_log_transaction_compression

    Command-Line Format --ndb-log-transaction-compression
    System Variable ndb_log_transaction_compression
    Scope Global
    Dynamic Yes
    SET_VAR Hint Applies No
    Type Boolean
    Default Value OFF

    Whether a replica mysqld writes compressed transactions in the binary log; present only if mysqld was compiled with support for NDB.

    You should note that starting the MySQL server with --binlog-transaction-compression forces this variable to be enabled (ON), and that this overrides any setting for --ndb-log-transaction-compression made on the command line or in a my.cnf file, as shown here: 

    $> mysqld_safe --ndbcluster --ndb-connectstring=127.0.0.1 \
  --binlog-transaction-compression=ON --ndb-log-transaction-compression=OFF &
[1] 27667
$> 2022-07-07T12:29:20.459937Z mysqld_safe Logging to '/usr/local/mysql/data/myhost.err'.
2022-07-07T12:29:20.509873Z mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/data

$> mysql -e 'SHOW VARIABLES LIKE "%transaction_compression%"'
+--------------------------------------------+-------+
| Variable_name                              | Value |
+--------------------------------------------+-------+
| binlog_transaction_compression             | ON    |
| binlog_transaction_compression_level_zstd  | 3     |
| ndb_log_transaction_compression            | ON    |
| ndb_log_transaction_compression_level_zstd | 3     |
+--------------------------------------------+-------+

    To disable binary log transaction compression for NDB tables only, set the ndb_log_transaction_compression system variable to OFF in a mysql or other client session after starting mysqld.

    Setting the binlog_transaction_compression variable after startup has no effect on the value of ndb_log_transaction_compression.

    For more information on binary log transaction compression, such as which events are or are not compressed and as well as behavior changes to be aware of when this feature is used, see Section 7.4.4.5, “Binary Log Transaction Compression”.

  • ndb_log_transaction_compression_level_zstd

    Command-Line Format --ndb-log-transaction-compression-level-zstd=#
    System Variable ndb_log_transaction_compression_level_zstd
    Scope Global
    Dynamic Yes
    SET_VAR Hint Applies No
    Type Integer
    Default Value 3
    Minimum Value 1
    Maximum Value 22

    The ZSTD compression level used for writing compressed transactions to the replica's binary log if enabled by ndb_log_transaction_compression. Not supported if mysqld was not compiled with support for the NDB storage engine.

    See Section 7.4.4.5, “Binary Log Transaction Compression”, for more information.

  • ndb_metadata_check

    Command-Line Format --ndb-metadata-check[={OFF|ON}]
    System Variable ndb_metadata_check
    Scope Global
    Dynamic Yes
    SET_VAR Hint Applies No
    Type Boolean
    Default Value ON

    NDB uses a background thread to check for metadata changes each ndb_metadata_check_interval seconds as compared with the MySQL data dictionary. This metadata change detection thread can be disabled by setting ndb_metadata_check to OFF. The thread is enabled by default.

  • ndb_metadata_check_interval

    Command-Line Format --ndb-metadata-check-interval=#
    System Variable ndb_metadata_check_interval
    Scope Global
    Dynamic Yes
    SET_VAR Hint Applies No
    Type Integer
    Default Value 60
    Minimum Value 0
    Maximum Value 31536000
    Unit seconds

    NDB runs a metadata change detection thread in the background to determine when the NDB dictionary has changed with respect to the MySQL data dictionary. By default,the interval between such checks is 60 seconds; this can be adjusted by setting the value of ndb_metadata_check_interval. To enable or disable the thread, use ndb_metadata_check.

  • ndb_metadata_sync

    System Variable ndb_metadata_sync
    Scope Global
    Dynamic Yes
    SET_VAR Hint Applies No
    Type Boolean
    Default Value false

    Setting this variable causes the change monitor thread to override any values set for ndb_metadata_check or ndb_metadata_check_interval, and to enter a period of continuous change detection. When the thread ascertains that there are no more changes to be detected, it stalls until the binary logging thread has finished synchronization of all detected objects. ndb_metadata_sync is then set to false, and the change monitor thread reverts to the behavior determined by the settings for ndb_metadata_check and ndb_metadata_check_interval.

    Setting this variable to true causes the list of excluded objects to be cleared; setting it to false clears the list of objects to be retried.

  • ndb_optimized_node_selection

    Command-Line Format --ndb-optimized-node-selection=#
    System Variable ndb_optimized_node_selection
    Scope Global
    Dynamic No
    SET_VAR Hint Applies No
    Type Integer
    Default Value 3
    Minimum Value 0
    Maximum Value 3

    There are two forms of optimized node selection, described here:

    1. The SQL node uses promixity to determine the transaction coordinator; that is, the “closest” data node to the SQL node is chosen as the transaction coordinator. For this purpose, a data node having a shared memory connection with the SQL node is considered to be “closest” to the SQL node; the next closest (in order of decreasing proximity) are: TCP connection to localhost, followed by TCP connection from a host other than localhost.

    2. The SQL thread uses distribution awareness to select the data node. That is, the data node housing the cluster partition accessed by the first statement of a given transaction is used as the transaction coordinator for the entire transaction. (This is effective only if the first statement of the transaction accesses no more than one cluster partition.)

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

    • 0: Node selection is not optimized. Each data node is employed as the transaction coordinator 8 times before the SQL thread proceeds to the next data node.

    • 1: Proximity to the SQL node is used to determine the transaction coordinator.

    • 2: Distribution awareness is used to select the transaction coordinator. However, if the first statement of the transaction accesses more than one cluster partition, the SQL node reverts to the round-robin behavior seen when this option is set to 0.

    • 3: If distribution awareness can be employed to determine the transaction coordinator, then it is used; otherwise proximity is used to select the transaction coordinator. (This is the default behavior.)

    Proximity is determined as follows:

    1. Start with the value set for the Group parameter (default 55).

    2. For an API node sharing the same host with other API nodes, decrement the value by 1. Assuming the default value for Group, the effective value for data nodes on same host as the API node is 54, and for remote data nodes 55.

    3. Setting ndb_data_node_neighbour further decreases the effective Group value by 50, causing this node to be regarded as the nearest node. This is needed only when all data nodes are on hosts other than that hosts the API node and it is desirable to dedicate one of them to the API node. In normal cases, the default adjustment described previously is sufficient.

    Frequent changes in ndb_data_node_neighbour are not advisable, since this changes the state of the cluster connection and thus may disrupt the selection algorithm for new transactions from each thread until it stablilizes.

  • ndb_read_backup

    Command-Line Format --ndb-read-backup[={OFF|ON}]
    System Variable ndb_read_backup
    Scope Global
    Dynamic Yes
    SET_VAR Hint Applies No
    Type Boolean
    Default Value ON

    Enable read from any fragment replica for any NDB table subsequently created; doing so greatly improves the table read performance at a relatively small cost to writes.

    If the SQL node and the data node use the same host name or IP address, this fact is detected automatically, so that the preference is to send reads to the same host. If these nodes are on the same host but use different IP addresses, you can tell the SQL node to use the correct data node by setting the value of ndb_data_node_neighbour on the SQL node to the node ID of the data node.

    To enable or disable read from any fragment replica for an individual table, you can set the NDB_TABLE option READ_BACKUP for the table accordingly, in a CREATE TABLE or ALTER TABLE statement; see Section 15.1.20.12, “Setting NDB Comment Options”, for more information.

  • ndb_recv_thread_activation_threshold

    Command-Line Format --ndb-recv-thread-activation-threshold=#
    System Variable ndb_recv_thread_activation_threshold
    Scope Global
    Dynamic Yes
    SET_VAR Hint Applies No
    Type Integer
    Default Value 8
    Minimum Value 0 (MIN_ACTIVATION_THRESHOLD)
    Maximum Value 16 (MAX_ACTIVATION_THRESHOLD)

    When this number of concurrently active threads is reached, the receive thread takes over polling of the cluster connection.

    This variable is global in scope. It can also be set at startup.

  • ndb_recv_thread_cpu_mask

    Command-Line Format --ndb-recv-thread-cpu-mask=mask
    System Variable ndb_recv_thread_cpu_mask
    Scope Global
    Dynamic Yes
    SET_VAR Hint Applies No
    Type Bitmap
    Default Value [empty]

    CPU mask for locking receiver threads to specific CPUs. This is specified as a hexadecimal bitmask. For example, 0x33 means that one CPU is used per receiver thread. An empty string is the default; setting ndb_recv_thread_cpu_mask to this value removes any receiver thread locks previously set.

    This variable is global in scope. It can also be set at startup.

  • ndb_report_thresh_binlog_epoch_slip

    Command-Line Format --ndb-report-thresh-binlog-epoch-slip=#
    System Variable ndb_report_thresh_binlog_epoch_slip
    Scope Global
    Dynamic Yes
    SET_VAR Hint Applies No
    Type Integer
    Default Value 10
    Minimum Value 0
    Maximum Value 256

    This represents the threshold for the number of epochs completely buffered in the event buffer, but not yet consumed by the binlog injector thread. When this degree of slippage (lag) is exceeded, an event buffer status message is reported, with BUFFERED_EPOCHS_OVER_THRESHOLD supplied as the reason (see Section 25.6.2.3, “Event Buffer Reporting in the Cluster Log”). Slip is increased when an epoch is received from data nodes and buffered completely in the event buffer; it is decreased when an epoch is consumed by the binlog injector thread, it is reduced. Empty epochs are buffered and queued, and so included in this calculation only when this is enabled using the Ndb::setEventBufferQueueEmptyEpoch() method from the NDB API.

  • ndb_report_thresh_binlog_mem_usage

    Command-Line Format --ndb-report-thresh-binlog-mem-usage=#
    System Variable ndb_report_thresh_binlog_mem_usage
    Scope Global
    Dynamic Yes
    SET_VAR Hint Applies No
    Type Integer
    Default Value 10
    Minimum Value 0
    Maximum Value 10

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

  • ndb_row_checksum

    System Variable ndb_row_checksum
    Scope Global, Session
    Dynamic Yes
    SET_VAR Hint Applies No
    Type Integer
    Default Value 1
    Minimum Value 0
    Maximum Value 1

    Traditionally, NDB has created tables with row checksums, which checks for hardware issues at the expense of performance. Setting ndb_row_checksum to 0 means that row checksums are not used for new or altered tables, which has a significant impact on performance for all types of queries. This variable is set to 1 by default, to provide backward-compatible behavior.

  • ndb_schema_dist_lock_wait_timeout

    Command-Line Format --ndb-schema-dist-lock-wait-timeout=value
    System Variable ndb_schema_dist_lock_wait_timeout
    Scope Global
    Dynamic Yes
    SET_VAR Hint Applies No
    Type Integer
    Default Value 30
    Minimum Value 0
    Maximum Value 1200
    Unit seconds

    Number of seconds to wait during schema distribution for the metadata lock taken on each SQL node in order to change its local data dictionary to reflect the DDL statement change. After this time has elapsed, a warning is returned to the effect that a given SQL node's data dictionary was not updated with the change. This avoids having the binary logging thread wait an excessive length of time while handling schema operations.

  • ndb_schema_dist_timeout

    Command-Line Format --ndb-schema-dist-timeout=value
    System Variable ndb_schema_dist_timeout
    Scope Global
    Dynamic No
    SET_VAR Hint Applies No
    Type Integer
    Default Value 120
    Minimum Value 5
    Maximum Value 1200
    Unit seconds

    Number of seconds to wait before detecting a timeout during schema distribution. This can indicate that other SQL nodes are experiencing excessive activity, or that they are somehow being prevented from acquiring necessary resources at this time.

  • ndb_schema_dist_upgrade_allowed

    Command-Line Format --ndb-schema-dist-upgrade-allowed=value
    System Variable ndb_schema_dist_upgrade_allowed
    Scope Global
    Dynamic No
    SET_VAR Hint Applies No
    Type Boolean
    Default Value true

    Allow upgrading of the schema distribution table when connecting to NDB. When true (the default), this change is deferred until all SQL nodes have been upgraded to the same version of the NDB Cluster software.

    Note

    The performance of the schema distribution may be somewhat degraded until the upgrade has been performed.

  • ndb_show_foreign_key_mock_tables

    Command-Line Format --ndb-show-foreign-key-mock-tables[={OFF|ON}]
    System Variable ndb_show_foreign_key_mock_tables
    Scope Global
    Dynamic Yes
    SET_VAR Hint Applies No
    Type Boolean
    Default Value OFF

    Show the mock tables used by NDB to support foreign_key_checks=0. When this is enabled, extra warnings are shown when creating and dropping the tables. The real (internal) name of the table can be seen in the output of SHOW CREATE TABLE.

  • ndb_slave_conflict_role

    Command-Line Format --ndb-slave-conflict-role=value
    Deprecated Yes
    System Variable ndb_slave_conflict_role
    Scope Global
    Dynamic Yes
    SET_VAR Hint Applies No
    Type Enumeration
    Default Value NONE
    Valid Values

    NONE

    PRIMARY

    SECONDARY

    PASS

    Deprecated synonym for ndb_conflict_role.

  • ndb_table_no_logging

    System Variable ndb_table_no_logging
    Scope Session
    Dynamic Yes
    SET_VAR Hint Applies No
    Type Boolean
    Default Value OFF

    When this variable is set to ON or 1, it causes all tables created or altered using ENGINE NDB to be nonlogging; that is, no data changes for this table are written to the redo log or checkpointed to disk, just as if the table had been created or altered using the NOLOGGING option for CREATE TABLE or ALTER TABLE.

    For more information about nonlogging NDB tables, see NDB_TABLE Options.

    ndb_table_no_logging has no effect on the creation of NDB table schema files; to suppress these, use ndb_table_temporary instead.

  • ndb_table_temporary

    System Variable ndb_table_temporary
    Scope Session
    Dynamic Yes
    SET_VAR Hint Applies No
    Type Boolean
    Default Value OFF

    When set to ON or 1, this variable causes NDB tables not to be written to disk: This means that no table schema files are created, and that the tables are not logged.

    Note

    Setting this variable currently has no effect. This is a known issue; see Bug #34036.

  • ndb_use_copying_alter_table

    System Variable ndb_use_copying_alter_table
    Scope Global, Session
    Dynamic No
    SET_VAR Hint Applies No

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

  • ndb_use_exact_count

    System Variable ndb_use_exact_count
    Scope Global, Session
    Dynamic Yes
    SET_VAR Hint Applies No
    Type Boolean
    Default Value OFF

    Forces NDB to use a count of records during SELECT COUNT(*) query planning to speed up this type of query. The default value is OFF, which allows for faster queries overall.

  • ndb_use_transactions

    Command-Line Format --ndb-use-transactions[={OFF|ON}]
    System Variable ndb_use_transactions
    Scope Global, Session
    Dynamic Yes
    SET_VAR Hint Applies No
    Type Boolean
    Default Value ON

    You can disable NDB transaction support by setting this variable's value to OFF. This is generally not recommended, although it may be useful to disable transaction support within a given client session when that session is used to import one or more dump files with large transactions; this allows a multi-row insert to be executed in parts, rather than as a single transaction. In such cases, once the import has been completed, you should either reset the variable value for this session to ON, or simply terminate the session.

  • ndb_version

    System Variable ndb_version
    Scope Global
    Dynamic No
    SET_VAR Hint Applies No
    Type String
    Default Value

    NDB engine version, as a composite integer.

  • ndb_version_string

    System Variable ndb_version_string
    Scope Global
    Dynamic No
    SET_VAR Hint Applies No
    Type String
    Default Value

    NDB engine version in ndb-x.y.z format.

  • replica_allow_batching

    Command-Line Format --replica-allow-batching[={OFF|ON}]
    System Variable replica_allow_batching
    Scope Global
    Dynamic Yes
    SET_VAR Hint Applies No
    Type Boolean
    Default Value ON

    Whether or not batched updates are enabled on NDB Cluster replicas.

    Allowing batched updates on the replica greatly improves performance, particularly when replicating TEXT, BLOB, and JSON columns. For this reason, replica_allow_batching is enabled by default.

    Setting this variable has an effect only when using replication with the NDB storage engine; in MySQL Server 9.1, it is present but does nothing. For more information, see Section 25.7.6, “Starting NDB Cluster Replication (Single Replication Channel)”.

  • ndb_replica_batch_size

    Command-Line Format --ndb-replica-batch-size=#
    System Variable ndb_replica_batch_size
    Scope Global
    Dynamic Yes
    SET_VAR Hint Applies No
    Type Integer
    Default Value 2097152
    Minimum Value 0
    Maximum Value 2147483648
    Unit bytes

    Determines the batch size in bytes used by the replication applier thread. Set this variable rather than the --ndb-batch-size option to apply this setting to the replica, exclusive of any other sessions.

    If this variable is unset (default 2 MB), its effective value is the greater of the value of --ndb-batch-size and 2 MB.

  • ndb_replica_blob_write_batch_bytes

    Command-Line Format --ndb-replica-blob-write-batch-bytes=#
    System Variable ndb_replica_blob_write_batch_bytes
    Scope Global
    Dynamic Yes
    SET_VAR Hint Applies No
    Type Integer
    Default Value 2097152
    Minimum Value 0
    Maximum Value 2147483648
    Unit bytes

    Control the batch write size used for blob data by the replication applier thread.

    Use this variable rather than the --ndb-blob-write-batch-bytes option to control the blob batch write size on the replica, exclusive of any other sessions. The reason for this is that, when ndb_replica_blob_write_batch_bytes​is not set,​the effective blob batch size (that is, the maximum number of pending bytes to write for blob columns) is determined by the greater of the value of --ndb-blob-write-batch-bytes and 2 MB (the default for ndb_replica_blob_write_batch_bytes).

    Setting ndb_replica_blob_write_batch_bytes to 0 means that NDB imposes no limit on the size of blob batch writes on the replica.

  • server_id_bits

    Command-Line Format --server-id-bits=#
    System Variable server_id_bits
    Scope Global
    Dynamic No
    SET_VAR Hint Applies No
    Type Integer
    Default Value 32
    Minimum Value 7
    Maximum Value 32

    This variable indicates the number of least significant bits within the 32-bit server_id which actually identify the server. Indicating that the server is actually identified by fewer than 32 bits makes it possible for some of the remaining bits to be used for other purposes, such as storing user data generated by applications using the NDB API's Event API within the AnyValue of an OperationOptions structure (NDB Cluster uses the AnyValue to store the server ID).

    When extracting the effective server ID from server_id for purposes such as detection of replication loops, the server ignores the remaining bits. The server_id_bits variable is used to mask out any irrelevant bits of server_id in the I/O and SQL threads when deciding whether an event should be ignored based on the server ID.

    This data can be read from the binary log by mysqlbinlog, provided that it is run with its own server_id_bits variable set to 32 (the default).

    If the value of server_id greater than or equal to 2 to the power of server_id_bits; otherwise, mysqld refuses to start.

    This system variable is supported only by NDB Cluster. It is not supported in the standard MySQL 9.1 Server.

  • slave_allow_batching

    Command-Line Format --slave-allow-batching[={OFF|ON}]
    Deprecated Yes
    System Variable slave_allow_batching
    Scope Global
    Dynamic Yes
    SET_VAR Hint Applies No
    Type Boolean
    Default Value ON

    Deprecated synonym for replica_allow_batching.

  • transaction_allow_batching

    System Variable transaction_allow_batching
    Scope Session
    Dynamic Yes
    SET_VAR Hint Applies No
    Type Boolean
    Default Value OFF

    When set to 1 or ON, this variable enables batching of statements within the same transaction. To use this variable, autocommit must first be disabled by setting it to 0 or OFF; otherwise, setting transaction_allow_batching has no effect.

    It is safe to use this variable with transactions that performs writes only, as having it enabled can lead to reads from the “before” image. You should ensure that any pending transactions are committed (using an explicit COMMIT if desired) before issuing a SELECT.

    Important

    transaction_allow_batching should not be used whenever there is the possibility that the effects of a given statement depend on the outcome of a previous statement within the same transaction.

    This variable is currently supported for NDB Cluster only.

The system variables in the following list all relate to the ndbinfo information database.

  • ndbinfo_database

    System Variable ndbinfo_database
    Scope Global
    Dynamic No
    SET_VAR Hint Applies No
    Type String
    Default Value ndbinfo

    Shows the name used for the NDB information database; the default is ndbinfo. This is a read-only variable whose value is determined at compile time.

  • ndbinfo_max_bytes

    Command-Line Format --ndbinfo-max-bytes=#
    System Variable ndbinfo_max_bytes
    Scope Global, Session
    Dynamic Yes
    SET_VAR Hint Applies No
    Type Integer
    Default Value 0
    Minimum Value 0
    Maximum Value 65535

    Used in testing and debugging only.

  • ndbinfo_max_rows

    Command-Line Format --ndbinfo-max-rows=#
    System Variable ndbinfo_max_rows
    Scope Global, Session
    Dynamic Yes
    SET_VAR Hint Applies No
    Type Integer
    Default Value 10
    Minimum Value 1
    Maximum Value 256

    Used in testing and debugging only.

  • ndbinfo_offline

    System Variable ndbinfo_offline
    Scope Global
    Dynamic Yes
    SET_VAR Hint Applies No
    Type Boolean
    Default Value OFF

    Place the ndbinfo database into offline mode, in which tables and views can be opened even when they do not actually exist, or when they exist but have different definitions in NDB. No rows are returned from such tables (or views).

  • ndbinfo_show_hidden

    Command-Line Format --ndbinfo-show-hidden[={OFF|ON}]
    System Variable ndbinfo_show_hidden
    Scope Global, Session
    Dynamic Yes
    SET_VAR Hint Applies No
    Type Boolean
    Default Value OFF
    Valid Values

    ON

    OFF

    Whether or not the ndbinfo database's underlying internal tables are shown in the mysql client. The default is OFF.

    Note

    When ndbinfo_show_hidden is enabled, the internal tables are shown in the ndbinfo database only; they are not visible in TABLES or other INFORMATION_SCHEMA tables, regardless of the variable's setting.

  • ndbinfo_table_prefix

    System Variable ndbinfo_table_prefix
    Scope Global
    Dynamic No
    SET_VAR Hint Applies No
    Type String
    Default Value ndb$

    The prefix used in naming the ndbinfo database's base tables (normally hidden, unless exposed by setting ndbinfo_show_hidden). This is a read-only variable whose default value is ndb$; the prefix itself is determined at compile time.

  • ndbinfo_version

    System Variable ndbinfo_version
    Scope Global
    Dynamic No
    SET_VAR Hint Applies No
    Type String
    Default Value

    Shows the version of the ndbinfo engine in use; read-only.

25.4.3.9.3 NDB Cluster Status Variables

This section provides detailed information about MySQL server status variables that relate to NDB Cluster and the NDB storage engine. For status variables not specific to NDB Cluster, and for general information on using status variables, see Section 7.1.10, “Server Status Variables”.


PREV   HOME   UP   NEXT
Related Documentation
MySQL 9.1 Release Notes
Download this Manual
PDF (US Ltr) - 40.3Mb
PDF (A4) - 40.4Mb
Man Pages (TGZ) - 259.3Kb
Man Pages (Zip) - 366.4Kb
Info (Gzip) - 4.0Mb
Info (Zip) - 4.0Mb