MySQL 5.6 Reference Manual Including MySQL NDB Cluster 7.3-7.4 Reference Guide
The [mysqld]
and [api]
sections in the config.ini
file define the
behavior of the MySQL servers (SQL nodes) and other applications
(API nodes) used to access cluster data. None of the parameters
shown is required. If no computer or host name is provided, any
host can use this SQL or API node.
Generally speaking, a [mysqld]
section is
used to indicate a MySQL server providing an SQL interface to
the cluster, and an [api]
section is used for
applications other than mysqld processes
accessing cluster data, but the two designations are actually
synonymous; you can, for instance, list parameters for a MySQL
server acting as an SQL node in an [api]
section.
For a discussion of MySQL server options for NDB Cluster, see Section 18.3.3.8.1, “MySQL Server Options for NDB Cluster”. For information about MySQL server system variables relating to NDB Cluster, see Section 18.3.3.8.2, “NDB Cluster System Variables”.
Version (or later) | NDB 7.3.1 |
---|---|
Type or units | unsigned |
Default | [...] |
Range | 1 - 255 |
Restart Type | IS |
The Id
is an integer value used to
identify the node in all cluster internal messages. The
permitted range of values is 1 to 255 inclusive. This value
must be unique for each node in the cluster, regardless of
the type of node.
Data node IDs must be less than 49, regardless of the NDB Cluster version used. If you plan to deploy a large number of data nodes, it is a good idea to limit the node IDs for API nodes (and management nodes) to values greater than 48.
NodeId
is the
preferred parameter name to use when identifying API nodes.
(Id
continues to be supported for
backward compatibility, but is now deprecated and generates
a warning when used. It is also subject to future removal.)
Version (or later) | NDB 7.3.1 |
---|---|
Type or units | string |
Default | [...] |
Range | ... |
Restart Type | N |
Specifies which data nodes to connect.
Version (or later) | NDB 7.3.1 |
---|---|
Type or units | unsigned |
Default | [...] |
Range | 1 - 255 |
Restart Type | IS |
The NodeId
is an integer value used to
identify the node in all cluster internal messages. The
permitted range of values is 1 to 255 inclusive. This value
must be unique for each node in the cluster, regardless of
the type of node.
Data node IDs must be less than 49, regardless of the NDB Cluster version used. If you plan to deploy a large number of data nodes, it is a good idea to limit the node IDs for API nodes (and management nodes) to values greater than 48.
NodeId
is the
preferred parameter name to use when identifying management
nodes. An alias, Id
, was used for this
purpose in very old versions of NDB Cluster, and continues
to be supported for backward compatibility; it is now
deprecated and generates a warning when used, and is subject
to removal in a future release of NDB Cluster.
Version (or later) | NDB 7.3.1 |
---|---|
Type or units | name |
Default | [...] |
Range | ... |
Restart Type | S |
Deprecated | Yes (in NDB 7.5) |
This refers to the Id
set for one of the
computers (hosts) defined in a [computer]
section of the configuration file.
Version (or later) | NDB 7.3.1 |
---|---|
Type or units | name or IP address |
Default | [...] |
Range | ... |
Restart Type | N |
Specifying this parameter defines the hostname of the
computer on which the SQL node (API node) is to reside. To
specify a hostname, either this parameter or
ExecuteOnComputer
is required.
If no HostName
or
ExecuteOnComputer
is specified in a given
[mysql]
or [api]
section of the config.ini
file, then an
SQL or API node may connect using the corresponding
“slot” from any host which can establish a
network connection to the management server host machine.
This differs from the default behavior for data
nodes, where localhost
is assumed for
HostName
unless otherwise
specified.
Version (or later) | NDB 7.3.1 |
---|---|
Type or units | 0-2 |
Default | 0 |
Range | 0 - 2 |
Restart Type | N |
This parameter defines which nodes can act as arbitrators.
Both management nodes and SQL nodes can be arbitrators. A
value of 0 means that the given node is never used as an
arbitrator, a value of 1 gives the node high priority as an
arbitrator, and a value of 2 gives it low priority. A normal
configuration uses the management server as arbitrator,
setting its ArbitrationRank
to 1 (the
default for management nodes) and those for all SQL nodes to
0 (the default for SQL nodes).
By setting ArbitrationRank
to 0 on all
management and SQL nodes, you can disable arbitration
completely. You can also control arbitration by overriding
this parameter; to do so, set the
Arbitration
parameter in the [ndbd default]
section
of the config.ini
global configuration
file.
Version (or later) | NDB 7.3.1 |
---|---|
Type or units | milliseconds |
Default | 0 |
Range | 0 - 4294967039 (0xFFFFFEFF) |
Restart Type | N |
Setting this parameter to any other value than 0 (the default) means that responses by the arbitrator to arbitration requests are delayed by the stated number of milliseconds. It is usually not necessary to change this value.
Version (or later) | NDB 7.3.1 |
---|---|
Type or units | bytes |
Default | 16K |
Range | 1K - 1M |
Restart Type | N |
For queries that are translated into full table scans or
range scans on indexes, it is important for best performance
to fetch records in properly sized batches. It is possible
to set the proper size both in terms of number of records
(BatchSize
) and in
terms of bytes (BatchByteSize
). The
actual batch size is limited by both parameters.
The speed at which queries are performed can vary by more than 40% depending upon how this parameter is set.
This parameter is measured in bytes. The default value in NDB Cluster 7.3 and later is 16K.
Version (or later) | NDB 7.3.1 |
---|---|
Type or units | records |
Default | 256 |
Range | 1 - 992 |
Restart Type | N |
This parameter is measured in number of records and is by default set to 256. The maximum size is 992.
Version (or later) | NDB 7.3.1 |
---|---|
Type or units | bytes |
Default | 0 |
Range | 0 - 4294967039 (0xFFFFFEFF) |
Restart Type | N |
This parameter specifies the amount of transporter send
buffer memory to allocate in addition to any that has been
set using
TotalSendBufferMemory
,
SendBufferMemory
, or
both.
Version (or later) | NDB 7.3.1 |
---|---|
Type or units | string |
Default | [...] |
Range | ... |
Restart Type | S |
Use this parameter to set the scheduling policy and priority of heartbeat threads for management and API nodes. The syntax for setting this parameter is shown here:
HeartbeatThreadPriority =policy
[,priority
]policy
: {FIFO | RR}
When setting this parameter, you must specify a policy. This
is one of FIFO
(first in, first in) or
RR
(round robin). This followed
optionally by the priority (an integer).
Version (or later) | NDB 7.3.1 |
---|---|
Type or units | bytes |
Default | 256K |
Range | 32K - 16M |
Restart Type | N |
The batch size is the size of each batch sent from each data node. Most scans are performed in parallel to protect the MySQL Server from receiving too much data from many nodes in parallel; this parameter sets a limit to the total batch size over all nodes.
The default value of this parameter is set to 256KB. Its maximum size is 16MB.
Version (or later) | NDB 7.3.1 |
---|---|
Type or units | bytes |
Default | 0 |
Range | 256K - 4294967039 (0xFFFFFEFF) |
Restart Type | N |
This parameter is used to determine the total amount of memory to allocate on this node for shared send buffer memory among all configured transporters.
If this parameter is set, its minimum permitted value is 256KB; 0 indicates that the parameter has not been set. For more detailed information, see Section 18.3.3.12, “Configuring NDB Cluster Send Buffer Parameters”.
Version (or later) | NDB 7.3.1 |
---|---|
Type or units | boolean |
Default | false |
Range | true, false |
Restart Type | N |
This parameter is false
by default. This
forces disconnected API nodes (including MySQL Servers
acting as SQL nodes) to use a new connection to the cluster
rather than attempting to re-use an existing one, as re-use
of connections can cause problems when using
dynamically-allocated node IDs. (Bug #45921)
This parameter can be overridden using the NDB API. For more information, see Ndb_cluster_connection::set_auto_reconnect(), and Ndb_cluster_connection::get_auto_reconnect().
DefaultOperationRedoProblemAction
Version (or later) | NDB 7.3.1 |
---|---|
Type or units | enumeration |
Default | QUEUE |
Range | ABORT, QUEUE |
Restart Type | S |
This parameter (along with
RedoOverCommitLimit
and
RedoOverCommitCounter
)
controls the data node's handling of operations when
too much time is taken flushing redo logs to disk. This
occurs when a given redo log flush takes longer than
RedoOverCommitLimit
seconds, more than
RedoOverCommitCounter
times, causing any pending transactions to be aborted.
When this happens, the node can respond in either of two
ways, according to the value of
DefaultOperationRedoProblemAction
, listed
here:
ABORT
: Any pending operations from
aborted transactions are also aborted.
QUEUE
: Pending operations from
transactions that were aborted are queued up to be
re-tried. This the default. In NDB 7.3.10 and later as
well as NDB 7.4.7 and later, pending operations are
still aborted when the redo log runs out of
space—that is, when
P_TAIL_PROBLEM errors occur. (Bug
#20782580)
Version (or later) | NDB 7.3.1 |
---|---|
Type or units | buckets |
Default | 3840 |
Range | 0 - 3840 |
Restart Type | N |
The size of the table hash maps used by
NDB
is configurable using this
parameter. DefaultHashMapSize
can take
any of three possible values (0, 240, 3840).
The original intended use for this parameter was to facilitate upgrades and especially downgrades to and from very old releases with differing default hash map sizes. This is not an issue when upgrading from NDB Cluster 7.3 (or later) to later versions.
Decreasing this parameter online after any tables have been
created or modified with
DefaultHashMapSize
equal to 3840 is not
currently supported.
Version (or later) | NDB 7.3.1 |
---|---|
Type or units | boolean |
Default | false |
Range | true, false |
Restart Type | N |
Use WAN TCP setting as default.
Version (or later) | NDB 7.3.7 |
---|---|
Type or units | integer |
Default | 0 |
Range | 0 - 4294967039 (0xFFFFFEFF) |
Restart Type | N |
Version (or later) | NDB 7.4.2 |
Type or units | integer |
Default | 0 |
Range | 0 - 4294967039 (0xFFFFFEFF) |
Restart Type | N |
Added | NDB 7.3.7 |
Added | NDB 7.4.2 |
Starting with NDB 7.3.7 and NDB 7.4.2, in an NDB Cluster
with many unstarted data nodes, the value of this parameter
can be raised to circumvent connection attempts to data
nodes which have not yet begun to function in the cluster,
as well as moderate high traffic to management nodes. As
long as the API node is not connected to any new data nodes,
the value of the
StartConnectBackoffMaxTime
parameter is applied; otherwise,
ConnectBackoffMaxTime
is used to
determine the length of time in milliseconds to wait between
connection attempts.
Time elapsed during node connection
attempts is not taken into account when calculating elapsed
time for this parameter. The timeout is applied with
approximately 100 ms resolution, starting with a 100 ms
delay; for each subsequent attempt, the length of this
period is doubled until it reaches
ConnectBackoffMaxTime
milliseconds, up to
a maximum of 100000 ms (100s).
Once the API node is connected to a data node and that node
reports (in a heartbeat message) that it has connected to
other data nodes, connection attempts to those data nodes
are no longer affected by this parameter, and are made every
100 ms thereafter until connected. Once a data node has
started, it can take up
HeartbeatIntervalDbApi
for the API node to be notified that this has occurred.
Version (or later) | NDB 7.3.7 |
---|---|
Type or units | integer |
Default | 0 |
Range | 0 - 4294967039 (0xFFFFFEFF) |
Restart Type | N |
Version (or later) | NDB 7.4.2 |
Type or units | integer |
Default | 0 |
Range | 0 - 4294967039 (0xFFFFFEFF) |
Restart Type | N |
Added | NDB 7.3.7 |
Added | NDB 7.4.2 |
Starting with NDB 7.3.7 and NDB 7.4.2, in an NDB Cluster
with many unstarted data nodes, the value of this parameter
can be raised to circumvent connection attempts to data
nodes which have not yet begun to function in the cluster,
as well as moderate high traffic to management nodes. As
long as the API node is not connected to any new data nodes,
the value of the
StartConnectBackoffMaxTime
parameter is
applied; otherwise,
ConnectBackoffMaxTime
is used to determine the length of time in milliseconds to
wait between connection attempts.
Time elapsed during node connection
attempts is not taken into account when calculating elapsed
time for this parameter. The timeout is applied with
approximately 100 ms resolution, starting with a 100 ms
delay; for each subsequent attempt, the length of this
period is doubled until it reaches
StartConnectBackoffMaxTime
milliseconds,
up to a maximum of 100000 ms (100s).
Once the API node is connected to a data node and that node
reports (in a heartbeat message) that it has connected to
other data nodes, connection attempts to those data nodes
are no longer affected by this parameter, and are made every
100 ms thereafter until connected. Once a data node has
started, it can take up
HeartbeatIntervalDbApi
for the API node to be notified that this has occurred.
API Node Debugging Parameters.
Beginning with NDB 7.4.12, you can use the
ApiVerbose
configuration parameter to
enable debugging output from a given API node. This parameter
takes an integer value. 0 is the default, and disables such
debugging; 1 enables debugging output to the cluster log; 2
adds DBDICT
debugging
output as well. (Bug #20638450) See also
DUMP 1229.
You can also obtain information from a MySQL server running as
an NDB Cluster SQL node using SHOW
STATUS
in the mysql client, as
shown here:
mysql> SHOW STATUS LIKE 'ndb%';
+-----------------------------+----------------+
| Variable_name | Value |
+-----------------------------+----------------+
| Ndb_cluster_node_id | 5 |
| Ndb_config_from_host | 198.51.100.112 |
| Ndb_config_from_port | 1186 |
| Ndb_number_of_storage_nodes | 4 |
+-----------------------------+----------------+
4 rows in set (0.02 sec)
For information about the status variables appearing in the output from this statement, see Section 18.3.3.8.3, “NDB Cluster Status Variables”.
To add new SQL or API nodes to the configuration of a running
NDB Cluster, it is necessary to perform a rolling restart of
all cluster nodes after adding new [mysqld]
or [api]
sections to the
config.ini
file (or files, if you are
using more than one management server). This must be done
before the new SQL or API nodes can connect to the cluster.
It is not necessary to perform any restart of the cluster if new SQL or API nodes can employ previously unused API slots in the cluster configuration to connect to the cluster.
Restart types. Information about the restart types used by the parameter descriptions in this section is shown in the following table:
Table 18.13 NDB Cluster restart types
Symbol | Restart Type | Description |
---|---|---|
N | Node | The parameter can be updated using a rolling restart (see Section 18.5.5, “Performing a Rolling Restart of an NDB Cluster”) |
S | System | All cluster nodes must be shut down completely, then restarted, to effect a change in this parameter |
I | Initial | Data nodes must be restarted using the
--initial option |