Skip navigation links
3.4.4 MySQL Cluster Status Variables »
Section Navigation      [Toggle]

3.4.3. MySQL Cluster System Variables

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

  • have_ndbcluster

    Variable Name have_ndbcluster
    Variable Scope Global
    Dynamic Variable No
      Permitted Values
    Type boolean

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

  • multi_range_count

    Command-Line Format --multi_range_count=#
    Config-File Format multi_range_count
    Option Sets Variable Yes, multi_range_count
    Variable Name multi_range_count
    Variable Scope Both
    Dynamic Variable Yes
      Permitted Values
    Type numeric
    Default 256
    Range 1-4294967295

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

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

  • ndb_autoincrement_prefetch_sz

    Command-Line Format --ndb_autoincrement_prefetch_sz
    Config-File Format ndb_autoincrement_prefetch_sz
    Option Sets Variable Yes, ndb_autoincrement_prefetch_sz
    Variable Name ndb_autoincrement_prefetch_sz
    Variable Scope Both
    Dynamic Variable Yes
      Permitted Values (<= 5.1.22)
    Type numeric
    Default 32
    Range 1-256
      Permitted Values (>= 5.1.23)
    Type numeric
    Default 1
    Range 1-256

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

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

    Beginning with MySQL Cluster NDB 6.3.31 and MySQL CLuster NDB 7.0.11, the maximum value for ndb_autoincrement_prefetch_sz is increased, from 256 to 65536. (Bug#50621)

  • ndb_cache_check_time

    Command-Line Format --ndb_cache_check_time
    Config-File Format ndb_cache_check_time
    Option Sets Variable Yes, ndb_cache_check_time
    Variable Name ndb_cache_check_time
    Variable Scope Global
    Dynamic Variable Yes
      Permitted Values
    Type numeric
    Default 0

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

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

  • ndb_extra_logging

    Version Introduced 5.1.6
    Command-Line Format ndb_extra_logging=#
    Config-File Format ndb_extra_logging
    Variable Name ndb_extra_logging
    Variable Scope Global
    Dynamic Variable Yes
      Permitted Values
    Type numeric
    Default 0

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

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

    This variable was added in MySQL 5.1.6.

  • ndb_force_send

    Command-Line Format --ndb-force-send
    Config-File Format ndb_force_send
    Option Sets Variable Yes, ndb_force_send
    Variable Name ndb_force_send
    Variable Scope Both
    Dynamic Variable Yes
      Permitted Values
    Type boolean
    Default TRUE

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

  • ndb_index_stat_cache_entries

    Version Removed 5.1.14
    Command-Line Format --ndb_index_stat_cache_entries
    Config-File Format ndb_index_stat_cache_entries
      Permitted Values
    Type numeric
    Default 32
    Range 0-4294967295

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

    Note

    If ndb_index_stat_enable is OFF, then setting this variable has no effect.

  • ndb_index_stat_enable

    Version Removed 5.1.19
    Command-Line Format --ndb_index_stat_enable
    Config-File Format ndb_index_stat_enable
      Permitted Values
    Type boolean
    Default ON

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

  • ndb_index_stat_update_freq

    Version Removed 5.1.14
    Command-Line Format --ndb_index_stat_update_freq
    Config-File Format ndb_index_stat_update_freq
      Permitted Values
    Type numeric
    Default 20
    Range 0-4294967295

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

    Note

    If ndb_index_stat_cache_entries is 0, then setting this variable has no effect; in this case, every query is sent directly to the data nodes.

  • ndb_optimized_node_selection

    Command-Line Format --ndb-optimized-node-selection=#
    Config-File Format ndb_optimized_node_selection
      Permitted Values (<= 5.1.22-ndb-6.33)
    Type boolean
    Default ON

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

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

    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. (This is the same “round-robin” behavior as caused by setting this option to 0 or OFF in previous versions of MySQL Cluster.)

    • 1: Proximity to the SQL node is used to determine the transaction coordinator. (This is the same behavior as caused by setting this option to 1 or ON in previous MySQL versions.)

    • 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 in MySQL Cluster NDB 6.3.4 and later.)

    Important

    Beginning with MySQL Cluster NDB 6.3.4, it is no longer possible to set --ndb_optimized_node_selection to ON or OFF; attempting to do so causes mysqld to abort with an error.

  • ndb_report_thresh_binlog_epoch_slip

    Command-Line Format --ndb_report_thresh_binlog_epoch_slip
    Config-File Format ndb_report_thresh_binlog_epoch_slip
      Permitted Values
    Type numeric
    Default 3
    Range 0-256

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

  • ndb_report_thresh_binlog_mem_usage

    Command-Line Format --ndb_report_thresh_binlog_mem_usage
    Config-File Format ndb_report_thresh_binlog_mem_usage
      Permitted Values
    Type numeric
    Default 10
    Range 0-10

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

  • ndb_use_copying_alter_table

    Version Introduced 5.1.12
    Variable Name ndb_use_copying_alter_table
    Variable Scope Both
    Dynamic Variable No

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

    This variable was added in MySQL 5.1.12.

  • ndb_use_exact_count

    Variable Name ndb_use_exact_count
    Variable Scope Both
    Dynamic Variable Yes
      Permitted Values
    Type boolean
    Default ON

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

  • ndb_use_transactions

    Command-Line Format --ndb_use_transactions
    Config-File Format ndb_use_transactions
    Variable Name ndb_use_transactions
    Variable Scope Both
    Dynamic Variable Yes
      Permitted Values
    Type boolean
    Default ON

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

    Note

    The setting for this variable was not honored in MySQL Cluster NDB 6.4.3 and MySQL Cluster NDB 7.0.4. (Bug#43236)

  • ndb_wait_connected

    Version Introduced 5.1.16-ndb-6.2.0
    Command-Line Format ndb_wait_connected
    Config-File Format ndb_wait_connected
    Option Sets Variable Yes, ndb_wait_connected
    Variable Name ndb_wait_connected
    Variable Scope  
    Dynamic Variable No
      Permitted Values
    Type numeric
    Default 0
    Range 0-31536000

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

  • ndb_wait_setup

    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 15.