Documentation Home
MySQL 5.7 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 35.7Mb
PDF (A4) - 35.8Mb
PDF (RPM) - 34.8Mb
EPUB - 8.7Mb
HTML Download (TGZ) - 8.5Mb
HTML Download (Zip) - 8.5Mb
HTML Download (RPM) - 7.3Mb
Eclipse Doc Plugin (TGZ) - 9.3Mb
Eclipse Doc Plugin (Zip) - 11.5Mb
Man Pages (TGZ) - 203.7Kb
Man Pages (Zip) - 309.0Kb
Info (Gzip) - 3.4Mb
Info (Zip) - 3.4Mb
Excerpts from this Manual

MySQL 5.7 Reference Manual  /  ...  /  MySQL Server Options and Variables for NDB Cluster

19.3.3.8 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 6.1, “The MySQL Server”.

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

19.3.3.8.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 6.1.4, “Server Command Options”.

For information about command-line options used with other NDB Cluster processes (ndbd, ndb_mgmd, and ndb_mgm), see Section 19.4.27, “Options Common to NDB Cluster Programs — Options Common to NDB Cluster Programs”. For information about command-line options used with NDB utility programs (such as ndb_desc, ndb_size.pl, and ndb_show_tables), see Section 19.4, “NDB Cluster Programs”.

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

    Table 19.10 Type and value information for ndb-allow-copying-alter-table

    Command Line System Variable Status Variable
    Option File Scope Dynamic
    From Version Type Default, Range
    Notes

    ndb-allow-copying-alter-table

    Yes Yes No
    Yes Both Yes
    NDB 7.5 boolean ON

    DESCRIPTION: Set to OFF to keep ALTER TABLE from using copying operations on NDB tables


    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=#

    Table 19.11 Type and value information for ndb-batch-size

    Command Line System Variable Status Variable
    Option File Scope Dynamic
    From Version Type Default, Range
    Notes

    ndb-batch-size

    Yes Yes No
    Yes Global No
    NDB 7.5 integer 32768 / 0 - 31536000

    DESCRIPTION: Size (in bytes) to use for NDB transaction batches


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

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

    Table 19.12 Type and value information for ndb-cluster-connection-pool

    Command Line System Variable Status Variable
    Option File Scope Dynamic
    From Version Type Default, Range
    Notes

    ndb-cluster-connection-pool

    Yes Yes Yes
    Yes Global No
    NDB 7.5 integer 1 / 1 - 63

    DESCRIPTION: Number of connections to the cluster used by MySQL


    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

    Table 19.13 Type and value information for ndb-cluster-connection-pool-nodeids

    Command Line System Variable Status Variable
    Option File Scope Dynamic
    From Version Type Default, Range
    Notes

    ndb-cluster-connection-pool-nodeids

    Yes Yes No
    Yes Global No
    NDB 7.5 set /

    DESCRIPTION: Comma-separated list of node IDs for connections to the cluster used by MySQL; the number of nodes in the list must be the same as the value set for --ndb-cluster-connection-pool


    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

    Table 19.14 Type and value information for ndb-blob-read-batch-bytes

    Command Line System Variable Status Variable
    Option File Scope Dynamic
    From Version Type Default, Range
    Notes

    ndb-blob-read-batch-bytes

    Yes Yes No
    Yes Both Yes
    NDB 7.5 integer 65536 / 0 - 4294967295

    DESCRIPTION: Specifies size in bytes that large BLOB reads should be batched into. 0 = no limit.


    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

    Table 19.15 Type and value information for ndb-blob-write-batch-bytes

    Command Line System Variable Status Variable
    Option File Scope Dynamic
    From Version Type Default, Range
    Notes

    ndb-blob-write-batch-bytes

    Yes Yes No
    Yes Both Yes
    NDB 7.5 integer 65536 / 0 - 4294967295

    DESCRIPTION: Specifies size in bytes that large BLOB writes should be batched into. 0 = no limit.


    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

    Table 19.16 Type and value information for ndb-connectstring

    Command Line System Variable Status Variable
    Option File Scope Dynamic
    From Version Type Default, Range
    Notes

    ndb-connectstring

    Yes No No
    Yes No
    NDB 7.5 string

    DESCRIPTION: Point to the management server that distributes the cluster configuration


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

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

    Table 19.17 Type and value information for ndb-default-column-format

    Command Line System Variable Status Variable
    Option File Scope Dynamic
    From Version Type Default, Range
    Notes

    ndb-default-column-format

    Yes Yes No
    Yes Global Yes
    5.7.11-ndb-7.5.1 enumeration DYNAMIC / FIXED, DYNAMIC
    5.7.16-ndb-7.5.4 enumeration FIXED / FIXED, DYNAMIC

    DESCRIPTION: Use this value (FIXED or DYNAMIC) by default for COLUMN_FORMAT and ROW_FORMAT options when creating or adding columns to a table.


    In NDB 7.5.1 and later, sets the default COLUMN_FORMAT and ROW_FORMAT for new tables (see Section 14.1.18, “CREATE TABLE Syntax”).

    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]

    Table 19.18 Type and value information for ndb-deferred-constraints

    Command Line System Variable Status Variable
    Option File Scope Dynamic
    From Version Type Default, Range
    Notes

    ndb-deferred-constraints

    Yes Yes No
    Yes Both Yes
    NDB 7.5 integer 0 / 0 - 1

    DESCRIPTION: Specifies that constraint checks on unique indexes (where these are supported) should be deferred until commit time. Not normally needed or used; for testing purposes only.


    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]

    Table 19.19 Type and value information for ndb-distribution

    Command Line System Variable Status Variable
    Option File Scope Dynamic
    From Version Type Default, Range
    Notes

    ndb-distribution

    Yes Yes No
    Yes Global Yes
    NDB 7.5 enumeration KEYHASH / LINHASH, KEYHASH

    DESCRIPTION: Default distribution for new tables in NDBCLUSTER (KEYHASH or LINHASH, default is 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-mgmd-host=host[:port]

    Table 19.20 Type and value information for ndb-mgmd-host

    Command Line System Variable Status Variable
    Option File Scope Dynamic
    From Version Type Default, Range
    Notes

    ndb-mgmd-host

    Yes No No
    Yes No
    NDB 7.5 string localhost:1186

    DESCRIPTION: Set the host (and port, if desired) for connecting to management server


    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.

  • --ndbcluster

    Table 19.21 Type and value information for ndbcluster

    Command Line System Variable Status Variable
    Option File Scope Dynamic
    From Version Type Default, Range
    Notes

    ndbcluster

    Yes No No
    Yes No
    NDB 7.5 boolean FALSE

    DESCRIPTION: Enable NDB Cluster (if this version of MySQL supports it)

    Disabled by --skip-ndbcluster


    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-log-apply-status

    Table 19.22 Type and value information for ndb-log-apply-status

    Command Line System Variable Status Variable
    Option File Scope Dynamic
    From Version Type Default, Range
    Notes

    ndb-log-apply-status

    Yes Yes No
    Yes Global No
    NDB 7.5 boolean OFF

    DESCRIPTION: Cause a MySQL server acting as a slave to log mysql.ndb_apply_status updates received from its immediate master in its own binary log, using its own server ID. Effective only if the server is started with the --ndbcluster option.


    Causes a slave mysqld to log any updates received from its immediate master to the mysql.ndb_apply_status table in its own binary log using its own server ID rather than the server ID of the master. 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 slaves of the current mysqld.

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

    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 master 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]

    Table 19.23 Type and value information for ndb-log-empty-epochs

    Command Line System Variable Status Variable
    Option File Scope Dynamic
    From Version Type Default, Range
    Notes

    ndb-log-empty-epochs

    Yes Yes No
    Yes Global Yes
    NDB 7.5 boolean OFF

    DESCRIPTION: When enabled, causes epochs in 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.


    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]

    Table 19.24 Type and value information for ndb-log-empty-update

    Command Line System Variable Status Variable
    Option File Scope Dynamic
    From Version Type Default, Range
    Notes

    ndb-log-empty-update

    Yes Yes No
    Yes Global Yes
    NDB 7.5 boolean OFF

    DESCRIPTION: When enabled, causes updates that produced no changes to be written to the ndb_apply_status and ndb_binlog_index tables, even when --log-slave-updates is enabled.


    Causes updates that produced 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 (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]

    Table 19.25 Type and value information for ndb-log-exclusive-reads

    Command Line System Variable Status Variable
    Option File Scope Dynamic
    From Version Type Default, Range
    Notes

    ndb-log-exclusive-reads

    Yes Yes No
    Yes Both Yes
    NDB 7.5 boolean 0

    DESCRIPTION: Log primary key reads with exclusive locks; allow conflict resolution based on read conflicts.


    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-orig

    Table 19.26 Type and value information for ndb-log-orig

    Command Line System Variable Status Variable
    Option File Scope Dynamic
    From Version Type Default, Range
    Notes

    ndb-log-orig

    Yes Yes No
    Yes Global No
    NDB 7.5 boolean OFF

    DESCRIPTION: Log originating server id and epoch in mysql.ndb_binlog_index table.


    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 19.6.4, “NDB Cluster Replication Schema and Tables”.

  • --ndb-log-transaction-id

    Table 19.27 Type and value information for ndb-log-transaction-id

    Command Line System Variable Status Variable
    Option File Scope Dynamic
    From Version Type Default, Range
    Notes

    ndb-log-transaction-id

    Yes Yes No
    Yes Global No
    NDB 7.5 boolean OFF

    DESCRIPTION: Write NDB transaction IDs in the binary log. Requires --log-bin-v1-events=OFF.


    Causes a slave 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, --log-bin-use-v1-row-events must be set to FALSE in order to use this option.

    This option is not supported in mainline MySQL Server 5.7. It is required to enable NDB Cluster Replication conflict detection and resolution using the NDB$EPOCH_TRANS() function (see NDB$EPOCH_TRANS()).

    The default value is FALSE.

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

  • --ndb-nodeid=#

    Table 19.28 Type and value information for ndb-nodeid

    Command Line System Variable Status Variable
    Option File Scope Dynamic
    From Version Type Default, Range
    Notes

    ndb-nodeid

    Yes No Yes
    Yes Global No
    5.0.45 integer / 1 - 63
    5.1.5 integer / 1 - 255

    DESCRIPTION: MySQL Cluster node ID for this MySQL server


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

  • --ndb_optimization_delay=milliseconds

    Table 19.29 Type and value information for ndb_optimization_delay

    Command Line System Variable Status Variable
    Option File Scope Dynamic
    From Version Type Default, Range
    Notes

    ndb_optimization_delay

    No Yes No
    No Global Yes
    NDB 7.5 integer 10 / 0 - 100000

    DESCRIPTION: Sets the number of milliseconds to wait between processing sets of rows by OPTIMIZE TABLE on NDB tables.


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

  • --ndb-recv-thread-activation-threshold=threshold

    Table 19.30 Type and value information for ndb-recv-thread-activation-threshold

    Command Line System Variable Status Variable
    Option File Scope Dynamic
    From Version Type Default, Range
    Notes

    ndb-recv-thread-activation-threshold

    Yes No No
    Yes No
    5.6.10-ndb-7.3.1 integer 8 / 0 (MIN_ACTIVATION_THRESHOLD) - 16 (MAX_ACTIVATION_THRESHOLD)

    DESCRIPTION: Activation threshold when receive thread takes over the polling of the cluster connection (measured in concurrently active threads)


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

  • --ndb-recv-thread-cpu-mask=bitmask

    Table 19.31 Type and value information for ndb-recv-thread-cpu-mask

    Command Line System Variable Status Variable
    Option File Scope Dynamic
    From Version Type Default, Range
    Notes

    ndb-recv-thread-cpu-mask

    Yes No No
    Yes No
    NDB 7.5 bitmap [empty]

    DESCRIPTION: CPU mask for locking receiver threads to specific CPUs; specified as hexadecimal. See documentation for details.


    Set a 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 (no locking of receiver threads) is the default.

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

    Table 19.32 Type and value information for ndb-transid-mysql-connection-map

    Command Line System Variable Status Variable
    Option File Scope Dynamic
    From Version Type Default, Range
    Notes

    ndb-transid-mysql-connection-map

    Yes No No
    No No
    NDB 7.5 enumeration ON / ON, OFF, FORCE

    DESCRIPTION: Enable or disable the ndb_transid_mysql_connection_map plugin; that is, enable or disable the INFORMATION_SCHEMA table having that name.


    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

    Table 19.33 Type and value information for ndb-wait-connected

    Command Line System Variable Status Variable
    Option File Scope Dynamic
    From Version Type Default, Range
    Notes

    ndb-wait-connected

    Yes Yes No
    Yes Global No
    NDB 7.5 integer 0 / 0 - 31536000
    5.1.56-ndb-7.0.27 integer 30 / 0 - 31536000
    NDB 7.5 integer 0 / 0 - 31536000
    5.1.56-ndb-7.1.16 integer 30 / 0 - 31536000

    DESCRIPTION: Time (in seconds) for the MySQL server to wait for connection to cluster management and data nodes before accepting MySQL client connections.


    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

    Table 19.34 Type and value information for ndb-wait-setup

    Command Line System Variable Status Variable
    Option File Scope Dynamic
    From Version Type Default, Range
    Notes

    ndb-wait-setup

    Yes Yes No
    Yes Global No
    5.1.39-ndb-6.2.19 integer 15 / 0 - 31536000
    5.1.39-ndb-6.3.28 integer 15 / 0 - 31536000
    5.1.39-ndb-7.0.9 integer 15 / 0 - 31536000
    5.1.56-ndb-7.0.27 integer 30 / 0 - 31536000
    5.1.39-ndb-7.1.0 integer 15 / 0 - 31536000
    5.1.56-ndb-7.1.16 integer 30 / 0 - 31536000

    DESCRIPTION: Time (in seconds) for the MySQL server to wait for NDB engine setup to complete.


    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.

  • --server-id-bits=#

    Table 19.35 Type and value information for server-id-bits

    Command Line System Variable Status Variable
    Option File Scope Dynamic
    From Version Type Default, Range
    Notes

    server-id-bits

    Yes Yes No
    Yes Global No
    NDB 7.5 integer 32 / 7 - 32

    DESCRIPTION: Sets the number of least significant bits in the server_id actually used for identifying the server, permitting NDB API applications to store application data in the most significant bits. server_id must be less than 2 to the power of this value.


    This option 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 option is used to mask out any irrelevant bits of server_id in the IO 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 option set to 32 (the default).

    The value of server_id must be less than 2 ^ 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 5.7 Server.

  • --skip-ndbcluster

    Table 19.36 Type and value information for skip-ndbcluster

    Command Line System Variable Status Variable
    Option File Scope Dynamic
    From Version Type Default, Range
    Notes

    skip-ndbcluster

    Yes No No
    Yes No

    DESCRIPTION: Disable the NDB Cluster storage engine


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

19.3.3.8.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 6.1.5, “Server System Variables”. For general information on using system variables, see Section 6.1.6, “Using System Variables”.

  • ndb_autoincrement_prefetch_sz

    Table 19.37 Type and value information for ndb_autoincrement_prefetch_sz

    Command Line System Variable Status Variable
    Option File Scope Dynamic
    From Version Type Default, Range
    Notes

    ndb_autoincrement_prefetch_sz

    Yes Yes No
    Yes Both Yes
    NDB 7.5 integer 32 / 1 - 256
    5.0.56 integer 1 / 1 - 256
    5.1.1 integer 32 / 1 - 256
    5.1.23 integer 1 / 1 - 256
    5.1.16-ndb-6.2.0 integer 32 / 1 - 256
    5.1.23-ndb-6.2.10 integer 1 / 1 - 256
    5.1.19-ndb-6.3.0 integer 32 / 1 - 256
    5.1.23-ndb-6.3.7 integer 1 / 1 - 256
    5.1.41-ndb-6.3.31 integer 1 / 1 - 65536
    5.1.30-ndb-6.4.0 integer 32 / 1 - 256
    5.1.41-ndb-7.0.11 integer 1 / 1 - 65536
    5.5.15-ndb-7.2.1 integer 1 / 1 - 65536

    DESCRIPTION: NDB auto-increment prefetch size


    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. The mininum and default value is 1. The maximum value for ndb_autoincrement_prefetch_sz is 65536.

    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. The default value is 1.

    Important

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

  • ndb_cache_check_time

    Table 19.38 Type and value information for ndb_cache_check_time

    Command Line System Variable Status Variable
    Option File Scope Dynamic
    From Version Type Default, Range
    Notes

    ndb_cache_check_time

    Yes Yes No
    Yes Global Yes
    NDB 7.5 integer 0 / -

    DESCRIPTION: Number of milliseconds between checks of cluster SQL nodes made by the MySQL query cache


    The number of milliseconds that elapse between checks of NDB 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_clear_apply_status

    Table 19.39 Type and value information for ndb_clear_apply_status

    Command Line System Variable Status Variable
    Option File Scope Dynamic
    From Version Type Default, Range
    Notes

    ndb_clear_apply_status

    Yes Yes No
    No Global Yes
    NDB 7.5 boolean ON

    DESCRIPTION: Causes RESET SLAVE to clear all rows from the ndb_apply_status table. ON by default.


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

  • ndb_data_node_neighbour

    Table 19.40 Type and value information for ndb_data_node_neighbour

    Command Line System Variable Status Variable
    Option File Scope Dynamic
    From Version Type Default, Range
    Notes

    ndb_data_node_neighbour

    Yes Yes No
    Yes Global Yes
    5.7.12-ndb-7.5.2 integer 0 / 0 - 255

    DESCRIPTION: Specifies cluster data node "closest" to this MySQL Server, for transaction hinting and fully replicated tables


    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 14.1.18.9, “Setting NDB_TABLE Options in Table Comments”, for further information.

    Added in NDB 7.5.2.

    Note

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

  • ndb_default_column_format

    Table 19.41 Type and value information for ndb_default_column_format

    Command Line System Variable Status Variable
    Option File Scope Dynamic
    From Version Type Default, Range
    Notes

    ndb_default_column_format

    Yes Yes No
    Yes Global Yes
    5.7.11-ndb-7.5.1 enumeration DYNAMIC / FIXED, DYNAMIC
    5.7.16-ndb-7.5.4 enumeration FIXED / FIXED, DYNAMIC

    DESCRIPTION: Sets default row format and column format (FIXED or DYNAMIC) used for new NDB tables.


    In NDB 7.5.1 and later, sets the default COLUMN_FORMAT and ROW_FORMAT for new tables (see Section 14.1.18, “CREATE TABLE Syntax”).

    In NDB 7.5.1, the default for this variable 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

    Table 19.42 Type and value information for ndb_deferred_constraints

    Command Line System Variable Status Variable
    Option File Scope Dynamic
    From Version Type Default, Range
    Notes

    ndb_deferred_constraints

    Yes Yes No
    Yes Both Yes
    NDB 7.5 integer 0 / 0 - 1

    DESCRIPTION: Specifies that constraint checks should be deferred (where these are supported). Not normally needed or used; for testing purposes only.


    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

    Table 19.43 Type and value information for ndb_distribution

    Command Line System Variable Status Variable
    Option File Scope Dynamic
    From Version Type Default, Range
    Notes

    ndb_distribution

    Yes Yes No
    Yes Global Yes
    NDB 7.5 enumeration KEYHASH / LINHASH, KEYHASH

    DESCRIPTION: Default distribution for new tables in NDBCLUSTER (KEYHASH or LINHASH, default is 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

    Table 19.44 Type and value information for ndb_eventbuffer_free_percent

    Command Line System Variable Status Variable
    Option File Scope Dynamic
    From Version Type Default, Range
    Notes

    ndb_eventbuffer_free_percent

    Yes Yes No
    Yes Global Yes
    NDB 7.5 integer 20 / 1 - 99

    DESCRIPTION: Percentage of free memory that should be available in event buffer before resumption of buffering, after reaching limit set by ndb_eventbuffer_max_alloc.


    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

    Table 19.45 Type and value information for ndb_eventbuffer_max_alloc

    Command Line System Variable Status Variable
    Option File Scope Dynamic
    From Version Type Default, Range
    Notes

    ndb_eventbuffer_max_alloc

    Yes Yes No
    Yes Global Yes
    NDB 7.5 integer 0 / 0 - 4294967295

    DESCRIPTION: Maximum memory that can be allocated for buffering events by the NDB API. Defaults to 0 (no limit).


    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

    Table 19.46 Type and value information for ndb_extra_logging

    Command Line System Variable Status Variable
    Option File Scope Dynamic
    From Version Type Default, Range
    Notes

    ndb_extra_logging

    Yes Yes No
    Yes Global Yes
    NDB 7.5 integer 0 / -
    5.1.19-ndb-6.3.0 integer 1 / -

    DESCRIPTION: Controls logging of MySQL Cluster schema, connection, and data distribution events in the MySQL error log


    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

    Table 19.47 Type and value information for ndb_force_send

    Command Line System Variable Status Variable
    Option File Scope Dynamic
    From Version Type Default, Range
    Notes

    ndb_force_send

    Yes Yes No
    Yes Both Yes
    NDB 7.5 boolean TRUE

    DESCRIPTION: Forces sending of buffers to NDB immediately, without waiting for other threads


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

  • ndb_fully_replicated

    Table 19.48 Type and value information for ndb_fully_replicated

    Command Line System Variable Status Variable
    Option File Scope Dynamic
    From Version Type Default, Range
    Notes

    ndb_fully_replicated

    Yes Yes No
    Yes Both Yes
    5.7.12-ndb-7-5-2 boolean OFF

    DESCRIPTION: Whether new NDB tables are fully replicated


    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 14.1.18.9, “Setting NDB_TABLE Options in Table Comments”, for syntax and other information.

    Added in NDB 7.5.2.

  • ndb_index_stat_enable

    Table 19.49 Type and value information for ndb_index_stat_enable

    Command Line System Variable Status Variable
    Option File Scope Dynamic
    From Version Type Default, Range
    Notes

    ndb_index_stat_enable

    Yes Yes No
    Yes Both Yes
    NDB 7.5 boolean OFF
    5.5.15-ndb-7.2.1 boolean ON

    DESCRIPTION: Use NDB index statistics in query optimization


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

  • ndb_index_stat_option

    Table 19.50 Type and value information for ndb_index_stat_option

    Command Line System Variable Status Variable
    Option File Scope Dynamic
    From Version Type Default, Range
    Notes

    ndb_index_stat_option

    Yes Yes No
    Yes Both Yes
    NDB 7.5 string loop_enable=1000ms,loop_idle=1000ms,loop_busy=100ms, update_batch=1,read_batch=4,idle_batch=32,check_batch=8, check_delay=10m,delete_batch=8, clean_delay=1m,error_batch=4, error_delay=1m,evict_batch=8,evict_delay=1m,cache_limit=32M, cache_lowpct=90,zero_total=0
    5.1.56-ndb-7.1.17 string 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

    DESCRIPTION: Comma-separated list of tunable options for NDB index statistics; the list should contain no spaces


    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.

    NameDescriptionDefault/UnitsMinimum/Maximum
    loop_enable 1000 ms0/4G
    loop_idleTime to sleep when idle1000 ms0/4G
    loop_busyTime to sleep when more work is waiting100 ms0/4G
    update_batch 10/4G
    read_batch 41/4G
    idle_batch 321/4G
    check_batch 81/4G
    check_delayHow often to check for new statistics10 m1/4G
    delete_batch 80/4G
    clean_delay 1 m0/4G
    error_batch 41/4G
    error_delay 1 m1/4G
    evict_batch 81/4G
    evict_delayClean LRU cache, from read time1 m0/4G
    cache_limitMaximum amount of memory in bytes used for cached index statistics by this mysqld; clean up the cache when this is exceeded.32 M0/4G
    cache_lowpct 900/100
    zero_totalSetting 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.00/1
  • ndb_join_pushdown

    Table 19.51 Type and value information for ndb_join_pushdown

    Command Line System Variable Status Variable
    Option File Scope Dynamic
    From Version Type Default, Range
    Notes

    ndb_join_pushdown

    No Yes No
    No Both Yes
    5.1.51-ndb-7.2.0 boolean TRUE

    DESCRIPTION: Enables pushing down of joins to data nodes


    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 expressions such as t1.a = t2.a + constant cannot be pushed down, and that (for example) a join on an INT column and a BIGINT column also cannot be pushed down.

    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. See Section 19.5.10.9, “The ndbinfo counters Table”, for information about these counters. See also The DBSPJ Block, in the NDB Cluster API Developer Guide.

  • ndb_log_apply_status

    Table 19.52 Type and value information for ndb_log_apply_status

    Command Line System Variable Status Variable
    Option File Scope Dynamic
    From Version Type Default, Range
    Notes

    ndb_log_apply_status

    Yes Yes No
    Yes Global No
    NDB 7.5 boolean OFF

    DESCRIPTION: Whether or not a MySQL server acting as a slave logs mysql.ndb_apply_status updates received from its immediate master in its own binary log, using its own server ID.


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

  • ndb_log_bin

    Table 19.53 Type and value information for ndb_log_bin

    Command Line System Variable Status Variable
    Option File Scope Dynamic
    From Version Type Default, Range
    Notes

    ndb_log_bin

    Yes Yes No
    No Both Yes
    NDB 7.5 boolean ON

    DESCRIPTION: Write updates to NDB tables in the binary log. Effective only if binary logging is enabled with --log-bin.


    Causes updates to NDB tables to be written to the binary log. Setting this variable has no effect if binary logging is not already enabled for the server using log_bin. ndb_log_bin defaults to 1 (ON); normally, there is never any need to change this value in a production environment.

  • ndb_log_binlog_index

    Table 19.54 Type and value information for ndb_log_binlog_index

    Command Line System Variable Status Variable
    Option File Scope Dynamic
    From Version Type Default, Range
    Notes

    ndb_log_binlog_index

    Yes Yes No
    No Global Yes
    NDB 7.5 boolean ON

    DESCRIPTION: Insert mapping between epochs and binary log positions into the ndb_binlog_index table. Defaults to ON. Effective only if binary logging is enabled on the server.


    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_empty_epochs

    Table 19.55 Type and value information for ndb_log_empty_epochs

    Command Line System Variable Status Variable
    Option File Scope Dynamic
    From Version Type Default, Range
    Notes

    ndb_log_empty_epochs

    Yes Yes No
    Yes Global Yes
    NDB 7.5 boolean OFF

    DESCRIPTION: When enabled, epochs in which there were no changes are written to the ndb_apply_status and ndb_binlog_index tables, even when log_slave_updates is enabled.


    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

    Table 19.56 Type and value information for ndb_log_empty_update

    Command Line System Variable Status Variable
    Option File Scope Dynamic
    From Version Type Default, Range
    Notes

    ndb_log_empty_update

    Yes Yes No
    Yes Global Yes
    NDB 7.5 boolean OFF

    DESCRIPTION: When enabled, updates which produce no changes are written to the ndb_apply_status and ndb_binlog_index tables, even when log_slave_updates is enabled.


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

  • ndb_log_exclusive_reads

    Table 19.57 Type and value information for ndb_log_exclusive_reads

    Command Line System Variable Status Variable
    Option File Scope Dynamic
    From Version Type Default, Range
    Notes

    ndb_log_exclusive_reads

    Yes Yes No
    Yes Both Yes
    NDB 7.5 boolean 0

    DESCRIPTION: Log primary key reads with exclusive locks; allow conflict resolution based on read conflicts.


    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

    Table 19.58 Type and value information for ndb_log_orig

    Command Line System Variable Status Variable
    Option File Scope Dynamic
    From Version Type Default, Range
    Notes

    ndb_log_orig

    Yes Yes No
    Yes Global No
    NDB 7.5 boolean OFF

    DESCRIPTION: Whether the id and epoch of the originating server are recorded in the mysql.ndb_binlog_index table. Set using the --ndb-log-orig option when starting mysqld.


    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

    Table 19.59 Type and value information for ndb_log_transaction_id

    Command Line System Variable Status Variable
    Option File Scope Dynamic
    From Version Type Default, Range
    Notes

    ndb_log_transaction_id

    No Yes No
    No Global No
    NDB 7.5 boolean OFF

    DESCRIPTION: Whether NDB transaction IDs are written into the binary log. (Read-only.)


    This read-only, Boolean system variable shows whether a slave 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 5.7.

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

  • ndb_optimized_node_selection

    Table 19.60 Type and value information for ndb_optimized_node_selection

    Command Line System Variable Status Variable
    Option File Scope Dynamic
    From Version Type Default, Range
    Notes

    ndb_optimized_node_selection

    Yes Yes No
    Yes Global No
    NDB 7.5 boolean ON
    5.1.22-ndb-6.3.4 integer 3 / 0 - 3

    DESCRIPTION: Determines how an SQL node chooses a cluster data node to use as transaction coordinator


    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; SCI connection; 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. (NDB 7.5.2 and later:) 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

    Table 19.61 Type and value information for ndb_read_backup

    Command Line System Variable Status Variable
    Option File Scope Dynamic
    From Version Type Default, Range
    Notes

    ndb_read_backup

    Yes Yes No
    Yes Global Yes
    5.7.12-ndb-7.5.2 boolean OFF

    DESCRIPTION: Enable read from any replica


    Enable read from any replica for any NDB table subsequently created.

    Added in NDB 7.5.2.

  • ndb_recv_thread_activation_threshold

    Table 19.62 Type and value information for ndb_recv_thread_activation_threshold

    Command Line System Variable Status Variable
    Option File Scope Dynamic
    From Version Type Default, Range
    Notes

    ndb_recv_thread_activation_threshold

    No No No
    No No
    5.6.10-ndb-7.3.1 integer 8 / 0 (MIN_ACTIVATION_THRESHOLD) - 16 (MAX_ACTIVATION_THRESHOLD)

    DESCRIPTION: Activation threshold when receive thread takes over the polling of the cluster connection (measured in concurrently active threads)


    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 on startup using the --ndb-recv-thread-activation-threshold option.

  • ndb_recv_thread_cpu_mask

    Table 19.63 Type and value information for ndb_recv_thread_cpu_mask

    Command Line System Variable Status Variable
    Option File Scope Dynamic
    From Version Type Default, Range
    Notes

    ndb_recv_thread_cpu_mask

    No Yes No
    No Global Yes
    NDB 7.5 bitmap [empty]

    DESCRIPTION: CPU mask for locking receiver threads to specific CPUs; specified as hexadecimal. See documentation for details.


    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 on startup using the --ndb-recv-thread-cpu-mask option.

  • ndb_report_thresh_binlog_epoch_slip

    Table 19.64 Type and value information for ndb_report_thresh_binlog_epoch_slip

    Command Line System Variable Status Variable
    Option File Scope Dynamic
    From Version Type Default, Range
    Notes

    ndb_report_thresh_binlog_epoch_slip

    Yes Yes No
    Yes Global Yes
    NDB 7.5 integer 3 / 0 - 256
    5.7.16-ndb-7.5.4 integer 10 / 0 - 256

    DESCRIPTION: NDB 7.5.4 and later: Threshold for number of epochs completely buffered, but not yet consumed by binlog injector thread which when exceeded generates BUFFERED_EPOCHS_OVER_THRESHOLD event buffer status message; prior to NDB 7.5.4: Threshold for number of epochs to lag behind before reporting binary log status


    In NDB 7.5.4 and later, 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 19.5.7.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.

    Prior to NDB 7.5.4, the value of this vairable served as a threshold for the number of epochs to be behind before reporting binary log status. In these previous releases, 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 binary log is 3 or more, a status message is then sent to the cluster log.

  • ndb_report_thresh_binlog_mem_usage

    Table 19.65 Type and value information for ndb_report_thresh_binlog_mem_usage

    Command Line System Variable Status Variable
    Option File Scope Dynamic
    From Version Type Default, Range
    Notes

    ndb_report_thresh_binlog_mem_usage

    Yes Yes No
    Yes Global Yes
    NDB 7.5 integer 10 / 0 - 10

    DESCRIPTION: This is a threshold on the percentage of free memory remaining before reporting binary log status


    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.

  • slave_allow_batching

    Table 19.66 Type and value information for slave_allow_batching

    Command Line System Variable Status Variable
    Option File Scope Dynamic
    From Version Type Default, Range
    Notes

    slave_allow_batching

    Yes Yes No
    Yes Global Yes
    NDB 7.5 boolean off

    DESCRIPTION: Turns update batching on and off for a replication slave


    Whether or not batched updates are enabled on NDB Cluster replication slaves.

    Currently, this variable is available for mysqld only as supplied with NDB Cluster or built from the NDB Cluster sources. For more information, see Section 19.6.6, “Starting NDB Cluster Replication (Single Replication Channel)”.

  • ndb_show_foreign_key_mock_tables

    Table 19.67 Type and value information for ndb_show_foreign_key_mock_tables

    Command Line System Variable Status Variable
    Option File Scope Dynamic
    From Version Type Default, Range
    Notes

    ndb_show_foreign_key_mock_tables

    Yes Yes No
    Yes Global Yes
    NDB 7.5 boolean OFF

    DESCRIPTION: Show the mock tables used to support foreign_key_checks=0.


    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

    Table 19.68 Type and value information for ndb_slave_conflict_role

    Command Line System Variable Status Variable
    Option File Scope Dynamic
    From Version Type Default, Range
    Notes

    ndb_slave_conflict_role

    Yes Yes No
    Yes Global Yes
    NDB 7.5 enumeration NONE / NONE, PRIMARY, SECONDARY, PASS

    DESCRIPTION: Role for slave to play in conflict detection and resolution. Value is one of PRIMARY, SECONDARY, PASS, or NONE (default). Can be changed only when slave SQL thread is stopped. See documentation for further information.


    Determine the role of this SQL node (and NDB Cluster ) in a circular (active-active) replication setup. ndb_slave_conflict_role can take any one of the values PRIMARY, SECONDARY, PASS, or NULL (the default). The slave SQL thread must be stopped before you can change ndb_slave_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_slave_conflict_role = 'NONE' first.

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

  • ndb_table_no_logging

    Table 19.69 Type and value information for ndb_table_no_logging

    Command Line System Variable Status Variable
    Option File Scope Dynamic
    From Version Type Default, Range
    Notes

    ndb_table_no_logging

    No Yes No
    No Session Yes
    NDB 7.5 boolean FALSE

    DESCRIPTION: NDB tables created when this setting is enabled are not checkpointed to disk (although table schema files are created). The setting in effect when the table is created with or altered to use NDBCLUSTER persists for the lifetime of the table.


    When this variable is set to ON or 1, it causes NDB tables not to be checkpointed to disk. More specifically, this setting applies to tables which are created or altered using ENGINE NDB when ndb_table_no_logging is enabled, and continues to apply for the lifetime of the table, even if ndb_table_no_logging is later changed. Suppose that A, B, C, and D are tables that we create (and perhaps also alter), and that we also change the setting for ndb_table_no_logging as shown here:

    SET @@ndb_table_no_logging = 1;
    
    CREATE TABLE A ... ENGINE NDB;
    
    CREATE TABLE B ... ENGINE MYISAM;
    CREATE TABLE C ... ENGINE MYISAM;
    
    ALTER TABLE B ENGINE NDB;
    
    SET @@ndb_table_no_logging = 0;
    
    CREATE TABLE D ... ENGINE NDB;
    ALTER TABLE C ENGINE NDB;
    
    SET @@ndb_table_no_logging = 1;
    

    After the previous sequence of events, tables A and B are not checkpointed; A was created with ENGINE NDB and B was altered to use NDB, both while ndb_table_no_logging was enabled. However, tables C and D are logged; C was altered to use NDB and D was created using ENGINE NDB, both while ndb_table_no_logging was disabled. Setting ndb_table_no_logging back to 1 or ON does not cause table C or D to be checkpointed.

    Note

    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

    Table 19.70 Type and value information for ndb_table_temporary

    Command Line System Variable Status Variable
    Option File Scope Dynamic
    From Version Type Default, Range
    Notes

    ndb_table_temporary

    No Yes No
    No Session Yes
    NDB 7.5 boolean FALSE

    DESCRIPTION: NDB tables are not persistent on disk: no schema files are created and the tables are not logged


    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

    Table 19.71 Type and value information for ndb_use_copying_alter_table

    Command Line System Variable Status Variable
    Option File Scope Dynamic
    From Version Type Default, Range
    Notes

    ndb_use_copying_alter_table

    No Yes No
    No Both No

    DESCRIPTION: Use copying ALTER TABLE operations in MySQL Cluster


    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

    Table 19.72 Type and value information for ndb_use_exact_count

    Command Line System Variable Status Variable
    Option File Scope Dynamic
    From Version Type Default, Range
    Notes

    ndb_use_exact_count

    No Yes No
    No Both Yes
    NDB 7.5 boolean ON
    5.1.47-ndb-7.1.8 boolean OFF

    DESCRIPTION: Use exact row count when planning queries


    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

    Table 19.73 Type and value information for ndb_use_transactions

    Command Line System Variable Status Variable
    Option File Scope Dynamic
    From Version Type Default, Range
    Notes

    ndb_use_transactions

    Yes Yes No
    Yes Both Yes
    NDB 7.5 boolean ON

    DESCRIPTION: Forces NDB to use a count of records during SELECT COUNT(*) query planning to speed up this type of query


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

  • ndb_version

    Table 19.74 Type and value information for ndb_version

    Command Line System Variable Status Variable
    Option File Scope Dynamic
    From Version Type Default, Range
    Notes

    ndb_version

    No Yes No
    No Global No
    NDB 7.5 string

    DESCRIPTION: Shows build and NDB engine version as an integer.


    NDB engine version, as a composite integer.

  • ndb_version_string

    Table 19.75 Type and value information for ndb_version_string

    Command Line System Variable Status Variable
    Option File Scope Dynamic
    From Version Type Default, Range
    Notes

    ndb_version_string

    No Yes No
    No Global No
    NDB 7.5 string

    DESCRIPTION: Shows build information including NDB engine version in ndb-x.y.z format.


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

  • server_id_bits

    Table 19.76 Type and value information for server_id_bits

    Command Line System Variable Status Variable
    Option File Scope Dynamic
    From Version Type Default, Range
    Notes

    server_id_bits

    Yes Yes No
    Yes Global No
    NDB 7.5 integer 32 / 7 - 32

    DESCRIPTION: The effective value of server_id if the server was started with the --server-id-bits option set to a nondefault value.


    The effective value of server_id if the server was started with the --server-id-bits option set to a nondefault value.

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

    This system variable is supported only by NDB Cluster . server_id_bits is not supported by the standard MySQL Server.

  • transaction_allow_batching

    Table 19.77 Type and value information for transaction_allow_batching

    Command Line System Variable Status Variable
    Option File Scope Dynamic
    From Version Type Default, Range
    Notes

    transaction_allow_batching

    No Yes No
    No Session Yes
    NDB 7.5 boolean FALSE

    DESCRIPTION: Allows batching of statements within a transaction. Disable AUTOCOMMIT to use.


    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

    Table 19.78 Type and value information for ndbinfo_database

    Command Line System Variable Status Variable
    Option File Scope Dynamic
    From Version Type Default, Range
    Notes

    ndbinfo_database

    No Yes No
    No Global No
    NDB 7.5 string ndbinfo

    DESCRIPTION: The name used for the NDB information database; read only.


    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; you can set it by starting the server using --ndbinfo-database=name, which sets the value shown for this variable but does not actually change the name used for the NDB information database.

  • ndbinfo_max_bytes

    Table 19.79 Type and value information for ndbinfo_max_bytes

    Command Line System Variable Status Variable
    Option File Scope Dynamic
    From Version Type Default, Range
    Notes

    ndbinfo_max_bytes

    Yes Yes No
    No Both Yes
    NDB 7.5 integer 0 / -

    DESCRIPTION: Used for debugging only.


    Used in testing and debugging only.

  • ndbinfo_max_rows

    Table 19.80 Type and value information for ndbinfo_max_rows

    Command Line System Variable Status Variable
    Option File Scope Dynamic
    From Version Type Default, Range
    Notes

    ndbinfo_max_rows

    Yes Yes No
    No Both Yes
    NDB 7.5 integer 10 / -

    DESCRIPTION: Used for debugging only.


    Used in testing and debugging only.

  • ndbinfo_offline

    Table 19.81 Type and value information for ndbinfo_offline

    Command Line System Variable Status Variable
    Option File Scope Dynamic
    From Version Type Default, Range
    Notes

    ndbinfo_offline

    No Yes No
    No Global Yes
    NDB 7.5 boolean OFF

    DESCRIPTION: Put the ndbinfo database into offline mode, in which no rows are returned from tables or views.


    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

    Table 19.82 Type and value information for ndbinfo_show_hidden

    Command Line System Variable Status Variable
    Option File Scope Dynamic
    From Version Type Default, Range
    Notes

    ndbinfo_show_hidden

    Yes Yes No
    No Both Yes
    NDB 7.5 boolean OFF

    DESCRIPTION: Whether to show ndbinfo internal base tables in the mysql client. The default is OFF.


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

  • ndbinfo_table_prefix

    Table 19.83 Type and value information for ndbinfo_table_prefix

    Command Line System Variable Status Variable
    Option File Scope Dynamic
    From Version Type Default, Range
    Notes

    ndbinfo_table_prefix

    Yes Yes No
    No Both Yes
    NDB 7.5 string ndb$

    DESCRIPTION: The prefix to use for naming ndbinfo internal base tables


    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$. You can start the server with the --ndbinfo-table-prefix option, but this merely sets the variable and does not change the actual prefix used to name the hidden base tables; the prefix itself is determined at compile time.

  • ndbinfo_version

    Table 19.84 Type and value information for ndbinfo_version

    Command Line System Variable Status Variable
    Option File Scope Dynamic
    From Version Type Default, Range
    Notes

    ndbinfo_version

    No Yes No
    No Global No
    NDB 7.5 string

    DESCRIPTION: The version of the ndbinfo engine; read only.


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

19.3.3.8.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 6.1.7, “Server Status Variables”.


User Comments
Sign Up Login You must be logged in to post a comment.