Chapter 23 MySQL NDB Cluster 8.0

Management node: The role of this type of node is to manage the other nodes within the NDB Cluster, performing such functions as providing configuration data, starting and stopping nodes, and running backups. Because this node type manages the configuration of the other nodes, a node of this type should be started first, before any other node. A management node is started with the command ndb_mgmd.

Data node: This type of node stores cluster data. There are as many data nodes as there are fragment replicas, times the number of fragments (see Section 23.1.2, “NDB Cluster Nodes, Node Groups, Fragment Replicas, and Partitions”). For example, with two fragment replicas, each having two fragments, you need four data nodes. One fragment replica is sufficient for data storage, but provides no redundancy; therefore, it is recommended to have two (or more) fragment replicas to provide redundancy, and thus high availability. A data node is started with the command ndbd (see Section 23.4.1, “ndbd — The NDB Cluster Data Node Daemon”) or ndbmtd (see Section 23.4.3, “ndbmtd — The NDB Cluster Data Node Daemon (Multi-Threaded)”).

NDB Cluster tables are normally stored completely in memory rather than on disk (this is why we refer to NDB Cluster as an in-memory database). However, some NDB Cluster data can be stored on disk; see Section 23.5.10, “NDB Cluster Disk Data Tables”, for more information.

SQL node: This is a node that accesses the cluster data. In the case of NDB Cluster, an SQL node is a traditional MySQL server that uses the NDBCLUSTER storage engine. An SQL node is a mysqld process started with the --ndbcluster and --ndb-connectstring options, which are explained elsewhere in this chapter, possibly with additional MySQL server options as well.

An SQL node is actually just a specialized type of API node, which designates any application which accesses NDB Cluster data. Another example of an API node is the ndb_restore utility that is used to restore a cluster backup. It is possible to write such applications using the NDB API. For basic information about the NDB API, see Getting Started with the NDB API.

Cluster log: Keeps a record of all desired reportable events for the cluster as a whole.

Node log: A separate log which is also kept for each individual node.

Local Checkpoint (LCP): This is a checkpoint that is specific to a single node; however, LCPs take place for all nodes in the cluster more or less concurrently. An LCP usually occurs every few minutes; the precise interval varies, and depends upon the amount of data stored by the node, the level of cluster activity, and other factors.

NDB 8.0 supports partial LCPs, which can significantly improve performance under some conditions. See the descriptions of the EnablePartialLcp and RecoveryWork configuration parameters which enable partial LCPs and control the amount of storage they use.

Global Checkpoint (GCP): A GCP occurs every few seconds, when transactions for all nodes are synchronized and the redo-log is flushed to disk.

Partition 0 is stored on node group 0; a primary fragment replica (primary copy) is stored on node 1, and a backup fragment replica (backup copy of the partition) is stored on node 2.

Partition 1 is stored on the other node group (node group 1); this partition's primary fragment replica is on node 3, and its backup fragment replica is on node 4.

Partition 2 is stored on node group 0. However, the placing of its two fragment replicas is reversed from that of Partition 0; for Partition 2, the primary fragment replica is stored on node 2, and the backup on node 1.

Partition 3 is stored on node group 1, and the placement of its two fragment replicas are reversed from those of partition 1. That is, its primary fragment replica is located on node 4, with the backup on node 3.

Security.  Communications between NDB Cluster nodes are not encrypted or shielded in any way. The only means of protecting transmissions within an NDB Cluster is to run your NDB Cluster on a protected network. If you intend to use NDB Cluster for Web applications, the cluster should definitely reside behind your firewall and not in your network's De-Militarized Zone (DMZ) or elsewhere.

See Section 23.5.17.1, “NDB Cluster Security and Networking Issues”, for more information.

Efficiency.  Setting up an NDB Cluster on a private or protected network enables the cluster to make exclusive use of bandwidth between cluster hosts. Using a separate switch for your NDB Cluster not only helps protect against unauthorized access to NDB Cluster data, it also ensures that NDB Cluster nodes are shielded from interference caused by transmissions between other computers on the network. For enhanced reliability, you can use dual switches and dual cards to remove the network as a single point of failure; many device drivers support failover for such communication links.

Compatibility enhancements.  The following changes reduce longstanding nonessential differences in NDB behavior as compared to that of other MySQL storage engines:

Schema and metadata distribution and synchronization.  NDB 8.0 makes use of the MySQL data dictionary to distribute schema information to SQL nodes joining a cluster and to synchronize new schema changes between existing SQL nodes. The following list describes individual enhancements relating to this integration work:

Synchronization of user privileges with NDB_STORED_USER.  A new mechanism for sharing and synchronizing users, roles, and privileges between SQL nodes is available beginning with NDB 8.0.18, using the NDB_STORED_USER privilege. Distributed privileges as implemented in NDB 7.6 and earlier (see Distributed Privileges Using Shared Grant Tables) are no longer supported.

Once a user account is created on an SQL node, the user and its privileges can be stored in NDB and thus shared between all SQL nodes in the cluster by issuing a GRANT statement such as this one:

GRANT NDB_STORED_USER ON *.* TO 'jon'@'localhost';

NDB_STORED_USER always has global scope and must be granted using ON *.*. System reserved accounts such as mysql.session@localhost or mysql.infoschema@localhost cannot be assigned this privilege.

Roles can also be shared between SQL nodes by issuing the appropriate GRANT NDB_STORED_USER statement. Assigning such a role to a user does not cause the user to be shared; the NDB_STORED_USER privilege must be granted to each user explicitly.

A user or role having NDB_STORED_USER, along with its privileges, is shared with all SQL nodes as soon as they join a given NDB Cluster. Changes to the privileges of the user or role are synchronized immediately with all connected SQL nodes. It is possible to make such changes from any connected SQL node, but recommended practice is to do so from a designated SQL node only, since the order of execution of statements affecting privileges from different SQL nodes cannot be guaranteed to be the same on all SQL nodes.

Implications for upgrades.  Due to changes in the MySQL server's privilege system (see Section 6.2.3, “Grant Tables”), privilege tables using the NDB storage engine do not function correctly in NDB 8.0. It is safe but not necessary to retain such privilege tables created in NDB 7.6 or earlier, but they are no longer used for access control. Beginning with NDB 8.0.16, a mysqld acting as an SQL node and detecting such tables in NDB writes a warning to the MySQL server log, and creates InnoDB shadow tables local to itself; such shadow tables are created on each MySQL server connected to the cluster. When performing an upgrade from NDB 7.6 or earlier, the privilege tables using NDB can be removed safely using ndb_drop_table once all MySQL servers acting as SQL nodes have been upgraded (see Section 23.2.7, “Upgrading and Downgrading NDB Cluster”).

The ndb_restore utility's --restore-privilege-tables option is deprecated but continues to be honored in NDB 8.0, and can still be used to restore distributed privilege tables present in a backup taken from a previous release of NDB Cluster to a cluster running NDB 8.0. These tables are handled as described in the preceeding paragraph.

Shared users and grants are stored in the ndb_sql_metadata table, which in NDB 8.0.19 and later ndb_restore by default does not restore; you can specify the --include-stored-grants option to cause it to do so.

INFORMATION_SCHEMA changes.  The following changes are made in the display of information regarding Disk Data files in the INFORMATION_SCHEMA.FILES table:

In addition, INFORMATION_SCHEMA tables are now populated with tablespace statistics for MySQL Cluster tables. (Bug #27167728)

Error information with ndb_perror.  The deprecated --ndb option for perror has been removed. Instead, use ndb_perror to obtain error message information from NDB error codes. (Bug #81704, Bug #81705, Bug #23523926, Bug #23523957)

Condition pushdown enhancements.  Previously, condition pushdown was limited to predicate terms referring to column values from the same table to which the condition was being pushed. In NDB 8.0.16, this restriction is removed such that column values from tables earlier in the query plan can also be referred to from pushed conditions. As of NDB 8.0.18, joins comparing column expressions are supported, as are comparisons between columns in the same table. Columns and column expressions to be compared must be of exactly the same type; this means they must also be of the same signedness, length, character set, precision, and scale, whenever these attributes apply.

Pushing down larger parts of a condition allows more rows to be filtered out by the data nodes, thereby reducing the number of rows which mysqld must handle during join processing. Another benefit of these enhancements is that filtering can be performed in parallel in the LDM threads, rather than in a single mysqld process on an SQL node; this has the potential to improve query performance significantly.

Existing rules for type compatibility between column values being compared continue to apply (see Section 8.2.1.5, “Engine Condition Pushdown Optimization”).

These additional improvements are made in NDB 8.0.21:

Increase in maximum row size.  NDB 8.0.18 increases the maximum number of bytes that can be stored in an NDBCLUSTER table from 14000 to 30000 bytes.

A BLOB or TEXT column continues to use 264 bytes of this total, as before.

The maximum offset for a fixed-width column of an NDB table is 8188 bytes; this is also unchanged from releases previous to 8.0.18.

See Section 23.1.7.5, “Limits Associated with Database Objects in NDB Cluster”, for more information.

ndb_mgm SHOW command and single user mode.  Beginning with NDB 8.0.17, when the cluster in single user mode, the output of the management client SHOW command indicates which API or SQL node has exclusive access while this mode is in effect.

Online column renames.  Beginning with NDB 8.0.18, columns of NDB tables can be renamed online, using ALGORITHM=INPLACE. See Section 23.5.11, “Online Operations with ALTER TABLE in NDB Cluster”, for more information.

Improved ndb_mgmd startup times.  Start times for management nodes daemon have been significantly improved in NDB 8.0.18 and later, in the following ways:

NDB API enhancements.  Beginning with NDB 8.0.18, NdbScanFilter::cmp() and several comparison methods of NdbInterpretedCode can be used to compare table column values with each other. The affected NdbInterpretedCode methods are listed here:

For all of the methods just listed, table column values to be compared much be of exactly matching types, including with respect to length, precision, signedness, scale, character set, and collation, as applicable.

See the descriptions of the individual API methods for more information.

Offline multithreaded index builds.  It is now possible to specify a set of cores to be used for I/O threads performing offline multithreaded builds of ordered indexes, as opposed to normal I/O duties such as file I/O， compression， or decompression. “Offline” in this context refers to building of ordered indexes performed when the parent table is not being written to; such building takes place when an NDB cluster performs a node or system restart, or as part of restoring a cluster from backup using ndb_restore --rebuild-indexes.

In addition, the default behaviour for offline index build work is modified to use all cores available to ndbmtd, rather limiting itself to the core reserved for the I/O thread. Doing so can improve restart and restore times and performance, availability, and the user experience.

This enhancement is implemented as follows:

In addition, NDB now distinguishes the thread types that are accessible to ThreadConfig by these two criteria:

For additonal information, see the descriptions of the indicated parameters in the Manual. (Bug #25835748, Bug #26928111)

logbuffers table backup process information.  When performing an NDB backup, the ndbinfo.logbuffers table now displays information regarding buffer usage by the backup process on each data node. This is implemented as rows reflecting two new log types in addition to REDO and DD-UNDO. One of these rows has the log type BACKUP-DATA, which shows the amount of data buffer used during backup to copy fragments to backup files. The other row has the log type BACKUP-LOG, which displays the amount of log buffer used during the backup to record changes made after the backup has started. One each of these log_type rows is shown in the logbuffers table for each data node in the cluster. Rows having these two log types are present in the table only while an NDB backup is currently in progress. (Bug #25822988)

ndbinfo.processes table on Windows.  The process ID of the monitor process used on Windows platforms by RESTART to spawn and restart a mysqld is now shown in the processes table as an angel_pid.

String hashing improvements.  Prior to NDB 8.0, all string hashing was based on first transforming the string into a normalized form, then MD5-hashing the resulting binary image. This could give rise to some performance problems, for the following reasons:

A collation provides its own hash function, which hashes the string directly without first creating a normalized string. In addition, for a Unicode 9.0 collation, the hash is computed without padding. NDB now takes advantage of this built-in function whenever hashing a string identified as using a Unicode 9.0 collation.

Since, for other collations, there are existing databases which are hash partitioned on the transformed string, NDB continues to employ the previous method for hashing strings that use these, to maintain compatibility. (Bug #89590, Bug #89604, Bug #89609, Bug #27515000, Bug #27523758, Bug #27522732)

RESET MASTER changes.  Because the MySQL Server now executes RESET MASTER with a global read lock, the behavior of this statement when used with NDB Cluster has changed in the following two respects:

ndb_restore option usage.  Beginning with NDB 8.0.16, the --nodeid and --backupid options are both required when invoking ndb_restore.

ndb_log_bin default.  Beginning with NDB 8.0.16, the default value of the ndb_log_bin system variable has changed from TRUE to FALSE.

Dynamic transactional resource allocation.  Allocation of resources in the transaction corrdinator (see The DBTC Block) is now performed using dynamic memory pools. This means that resource allocation determined by data node configuration parameters such as MaxDMLOperationsPerTransaction, MaxNoOfConcurrentIndexOperations, MaxNoOfConcurrentOperations, MaxNoOfConcurrentScans, MaxNoOfConcurrentTransactions, MaxNoOfFiredTriggers, MaxNoOfLocalScans, and TransactionBufferMemory is now done in such a way that, if the load represented by each of these parameters is within the target load for all such resources, others of these resources can be limited so as not to exceed the total resources available.

As part of this work, several new data node parameters controlling transactional resources in DBTC, listed here, have been added:

See the descriptions of the parameters just listed for further information.

Backups using multiple LDMs per data node.  NDB backups can now be performed in a parallel fashion on individual data nodes using multiple local data managers (LDMs). (Previously, backups were done in parallel across data nodes, but were always serial within data node processes.) No special syntax is required for the START BACKUP command in the ndb_mgm client to enable this feature, but all data nodes must be using multiple LDMs. This means that data nodes must be running ndbmtd (ndbd is single-threaded and thus always has only one LDM) and they must be configured to use multiple LDMs before taking the backup; you can do this by choosing an appropriate setting for one of the multi-threaded data node configuration parameters MaxNoOfExecutionThreads or ThreadConfig.

Backups using multiple LDMs create subdirectories, one per LDM, under the BACKUP/BACKUP-backup_id/ directory. ndb_restore now detects these subdirectories automatically, and if they exist, attempts to restore the backup in parallel; see Section 23.4.23.3, “Restoring from a backup taken in parallel”, for details. (Single-threaded backups are restored as in previous versions of NDB.) It is also possible to restore backups taken in parallel using an ndb_restore binary from a previous version of NDB Cluster by modifying the usual restore procedure; Section 23.4.23.3.2, “Restoring a parallel backup serially”, provides information on how to do this.

Binary configuration file enhancements.  Beginning with NDB 8.0.18, a new format is used for the management server's binary configuration file. Previously, a maximum of 16381 sections could appear in the cluster configuration file; now the maximum number of sections is 4G. This is intended to support larger numbers of nodes in a cluster than was possible before this change.

Upgrades to the new format are relatively seamless, and should seldom if ever require manual intervention, as the management server continues to be able to read the old format without issue. A downgrade from NDB 8.0.18 (or later) to an older version of the NDB Cluster software requires manual removal of any binary configuration files or, alternatively, starting the older management server binary with the --initial option.

For more information, see Section 23.2.7, “Upgrading and Downgrading NDB Cluster”.

Increased number of data nodes.  NDB 8.0.18 increases the maximum number of data nodes supported per cluster to 144 (previously, this was 48). Data nodes can now use node IDs in the range 1 to 144, inclusive.

Previously, the recommended node IDs for management nodes were 49 and 50. These are still supported for management nodes, but using them as such limits the maximum number of data nodes to 142; for this reason, it is now recommended that node IDs 145 and 146 are used for management nodes.

As part of this work, the format used for the data node sysfile has been updated to version 2. This file records information such as the last global checkpoint index, restart status, and node group membership of each node (see NDB Cluster Data Node File System Directory).

RedoOverCommitCounter and RedoOverCommitLimit changes.  Due to ambiguities in the semantics for setting them to 0, the minimum value for each of the data node configuration parameters RedoOverCommitCounter and RedoOverCommitLimit has been increased to 1, beginning with NDB 8.0.19.

ndb_autoincrement_prefetch_sz changes.  In NDB 8.0.19, the default value of the ndb_autoincrement_prefetch_sz server system variable is increased to 512.

Changes in parameter maxmimums and defaults.  NDB 8.0.19 makes the following changes in configuration parameter maximum and default values:

Disk Data checkpointing improvements.  NDB Cluster 8.0.19 provides a number of new enhancements which help to reduce the latency of checkpoints of Disk Data tables and tablespaces when using non-volatile memory devices such as solid-state drives and the NVMe specification for such devices. These improvements include those in the following list:

As part of this work, NDB 8.0.19 introduces two new data node configuration parameters. MaxDiskDataLatency places a ceiling on the degree of latency permitted for disk access and causes transactions taking longer than this length of time to be aborted. DiskDataUsingSameDisk makes it possible to take advantage of housing Disk Data tablespaces on separate disks by increasing the rate at which checkpoints of such tablespaces can be performed.

In addition, three new tables in the ndbinfo database, also added in NDB 8.0.19 and listed here, provide information about Disk Data performance:

Memory allocation and TransactionMemory.  NDB 8.0.19 introduces a new TransactionMemory parameter which simplifies allocation of data node memory for transactions as part of the work done to pool transactional and Local Data Manager (LDM) memory. This parameter is intended to replace several older transactional memory parameters which have been deprecated.

Transaction memory can now be set in any of the three ways listed here:

For more information, see the description of TransactionMemory, as well as Section 23.3.3.13, “Data Node Memory Management”.

Support for additional fragment replicas.  NDB 8.0.19 increases the maximum number of fragment replicas supported in production from two to four. (Previously, it was possible to set NoOfReplicas to 3 or 4, but this was not officially supported or verified in testing.)

Restoring by slices.  Beginning with NDB 8.0.20, it is possible to divide a backup into roughly equal portions (slices) and to restore these slices in parallel using two new options implemented for ndb_restore:

This makes it possible to employ multiple instances of ndb_restore to restore subsets of the backup in parallel, potentially reducing the amount of time required to perform the restore operation.

For more information, see the description of the ndb_restore --num-slices option.

Read from any fragment replica enabled.  Beginning with NDB 8.0.19, read from any fragment replica is enabled by default for all NDB tables. This means that the default value for the ndb_read_backup system variable is now ON, and that the value of the NDB_TABLE comment option READ_BACKUP is 1 when creating a new NDB table. Enabling read from any fragment replica significantly improves performance for reads from NDB tables, with minimal impact on writes.

For more information, see the description of the ndb_read_backup system variable, and Section 13.1.20.11, “Setting NDB_TABLE Options”.

ndb_blob_tool enhancements.  Beginning with NDB 8.0.20, the ndb_blob_tool utility can detect missing blob parts for which inline parts exist and replace these with placeholder blob parts (consisting of space characters) of the correct length. To check whether there are missing blob parts, use the --check-missing option with this program. To replace any missing blob parts with placeholders, use the --add-missing option.

For more information, see Section 23.4.6, “ndb_blob_tool — Check and Repair BLOB and TEXT columns of NDB Cluster Tables”.

ndbinfo versioning.  NDB 8.0.20 and later supports versioning for ndbinfo tables, and maintains the current definitions for its tables internally. At startup, NDB compares its supported ndbinfo version with the version stored in the data dictionary. If the versions differ, NDB drops any old ndbinfo tables and recreates them using the current definitions.

Support for Fedora Linux.  Beginning with NDB 8.0.20, Fedora Linux is a supported platform for NDB Cluster Community releases and can be installed using the RPMs supplied for this purpose by Oracle. These can be obtained from the NDB Cluster downloads page.

NDB programs—NDBT dependency removal.  The dependency of a number of NDB utility programs on the NDBT library has been removed. This library is used internally for development, and is not required for normal use; its inclusion in these programs could lead to unwanted issues when testing.

Affected programs are listed here, along with the NDB versions in which the dependency was removed:

The principal effect of this change for users is that these programs no longer print NDBT_ProgramExit - status following completion of a run. Applications that depend upon such behavior should be updated to reflect the change when upgrading to the indicated versions.

Pushdown of outer joins and semijoins.  Work done in NDB 8.0.20 allows many outer joins and semijoins, and not only those using a primary key or unique key lookup, to be pushed down to the data nodes (see Section 8.2.1.5, “Engine Condition Pushdown Optimization”).

Outer joins using scans which can now be pushed include those which meet the following conditions:

A semijoin that uses an index scan can now be pushed if it meets the the conditions just noted for a pushed outer join, and it uses the firstMatch strategy (see Section 8.2.2.1, “Optimizing IN and EXISTS Subquery Predicates with Semijoin Transformations”).

When a join cannot be pushed, EXPLAIN should provide the reason or reasons.

Foreign keys and lettercasing.  NDB stores the names of foreign keys using the case with which they were defined. Formerly, when the value of the lower_case_table_names system variable was set to 0, it performed case-sensitive comparisons of foreign key names as used in SELECT and other SQL statements with the names as stored. Beginning with NDB 8.0.20, such comparisons are now always performed in a case-insensitive fashion, regardless of the value of lower_case_table_names.

Multiple transporters.  NDB 8.0.20 introduces support for multiple transporters to handle node-to-node communication between pairs of data nodes. This facilitates higher rates of update operations for each node group in the cluster, and helps avoid constraints imposed by system or other limitations on inter-node communications using a single socket.

By default, NDB now uses a number of transporters based on the number of local data management (LDM) threads or the number of transaction coordinator (TC) threads, whichever is greater. By default, the number of transporters is equal to half of this number. While the default should perform well for most workloads, it is possible to adjust the number of transporters employed by each node group by setting the NodeGroupTransporters data node configuration parameter (also introduced in NDB 8.0.20), up a maximum of the greater of the number of LDM threads or the number of TC threads. Setting it to 0 causes the number of transporters to be the same as the number of LDM threads.

ndb_restore: primary key schema changes.  NDB 8.0.21 (and later) supports different primary key definitions for source and target tables when restoring an NDB native backup with ndb_restore when it is run with the --allow-pk-changes option. Both increasing and decreasing the number of columns making up the original primary key are supported.

When the primary key is extended with an additional column or columns, any columns added must be defined as NOT NULL, and no values in any such columns may be changed during the time that the backup is being taken. Because some applications set all column values in a row when updating it, whether or not all values are actually changed, this can cause a restore operation to fail even if no values in the column to be added to the primary key have changed. You can override this behavior using the --ignore-extended-pk-updates option also added in NDB 8.0.21; in this case, you must ensure that no such values are changed.

A column can be removed from the table's primary key whether or not this column remains part of the table.

For more information, see the description of the --allow-pk-changes option for ndb_restore.

Merging backups with ndb_restore.  In some cases, it may be desirable to consolidate data originally stored in different instances of NDB Cluster (all using the same schema) into a single target NDB Cluster. This is now supported when using backups created in the ndb_mgm client (see Section 23.5.8.2, “Using The NDB Cluster Management Client to Create a Backup”) and restoring them with ndb_restore, using the --remap-column option added in NDB 8.0.21 along with --restore-data (and possibly additional compatible options as needed or desired). --remap-column can be employed to handle cases in which primary and unique key values are overlapping between source clusters, and it is necessary that they do not overlap in the target cluster, as well as to preserve other relationships between tables such as foreign keys.

--remap-column takes as its argument a string having the format db.tbl.col:fn:args, where db, tbl, and col are, respectively, the names of the database, table, and column, fn is the name of a remapping function, and args is one or more arguments to fn. There is no default value. Only offset is supported as the function name, with args as the integer offset to be applied to the value of the column when inserting it into the target table from the backup. This column must be one of INT or BIGINT; the allowed range of the offset value is the same as the signed version of that type (this allows the offset to be negative if desired).

The new option can be used multiple times in the same invocation of ndb_restore, so that you can remap to new values multiple columns of the same table, different tables, or both. The offset value does not have to be the same for all instances of the option.

In addition, two new options are provided for ndb_desc, also beginning in NDB 8.0.21:

For more information and examples, see the description of the --remap-column option.

Send thread improvements.  As of NDB 8.0.20, each send thread now handles sends to a subset of transporters, and each block thread now assists only one send thread, resulting in more send threads, and thus better performance and data node scalability.

Adaptive spin control using SpinMethod.  NDB 8.0.20 introduces a simple interface for setting up adaptive CPU spin on platforms supporting it, using the SpinMethod data node parameter. This parameter has four settings, one each for static spinning, cost-based adaptive spinning, latency-optimized adaptive spinning, and adaptive spinning optimized for database machines on which each thread has its own CPU. Each of these settings causes the data node to use a set of predetermined values for one or more spin parameters which enable adaptive spinning, set spin timing, and set spin overhead, as appropriate to a given scenario, thus obviating the need to set these directly for common use cases.

For fine-tuning spin behavior, it is also possible to set these and additional spin parameters directly, using the existing SchedulerSpinTimer data node configuration parameter as well as the following DUMP commands in the ndb_mgm client:

NDB 8.0.20 also adds a new TCP configuration parameter TcpSpinTime which sets the time to spin for a given TCP connection.

The ndb_top tool is also enhanced to provide spin time information per thread.

For additional information, see the description of the SpinMethod parameter, the listed DUMP commands, and Section 23.4.29, “ndb_top — View CPU usage information for NDB threads”.

Disk Data and cluster restarts.  Beginning with NDB 8.0.21, an initial restart of the cluster forces the removal of all Disk Data objects such as tablespaces and log file groups, including any data files and undo log files associated with these objects.

See Section 23.5.10, “NDB Cluster Disk Data Tables”, for more information.

Disk Data extent allocation.  Beginning with NDB 8.0.20, allocation of extents in data files is done in a round-robin fashion among all data files used by a given tablespace. This is expected to improve distribution of data in cases where multiple storage devices are used for Disk Data storage.

For more information, see Section 23.5.10.1, “NDB Cluster Disk Data Objects”.

--ndb-log-fail-terminate option.  Beginning with NDB 8.0.21, you can cause the SQL node to terminate whenever it is unable to log all row events fully. This can be done by starting mysqld with the --ndb-log-fail-terminate option.

AllowUnresolvedHostNames parameter.  By default, a management node refuses to start when it cannot resolve a host name present in the global configuration file, which can be problematic in some environments such as Kubernetes. Beginning with NDB 8.0.22, it is possible to override this behavior by setting AllowUnresolvedHostNames to true in the [tcp default] section of the cluster global confugration file (config.ini file). Doing so causes such errors to be treated as warnings instead, and to permit ndb_mgmd to continue starting

Blob write performance enhancements.  NDB 8.0.22 implements a number of improvements which allow more efficient batching when modifying multiple blob columns in the same row, or when modifying multiple rows containing blob columns in the same statement, by reducing the number of round trips required between an SQL or other API node and the data nodes when applying these modifications. The performance of many INSERT, UPDATE, and DELETE statements can thus be improved. Examples of such statements are listed here, where table is an NDB table containing one or more Blob columns:

Other SQL statements may benefit from these improvements as well. These include LOAD DATA INFILE and CREATE TABLE ... SELECT .... In addition, ALTER TABLE table ENGINE = NDB, where table uses a storage engine other than NDB prior to execution of the statement, may also execute more efficiently.

This enhancement applies to statements affecting columns of MySQL type BLOB, MEDIUMBLOB, LONGBLOB, TEXT, MEDIUMTEXT, and LONGTEXT. Statements which update TINYBLOB or TINYTEXT columns (or both types) only are not affected by this work, and no changes in their performance should be expected.

The performance of some SQL statements is not noticeably improved by this enhancement, due to the fact that they require scans of table Blob columns, which breaks up batching. Such statements include those of the types listed here:

To take advantage of this improvement to its fullest extent, you may wish to increase the values used for the --ndb-batch-size and --ndb-blob-write-batch-bytes options for mysqld, to minimize the number of round trips required to modify Blobs. For replication, it is also recommended that you enable the slave_allow_batching system variable, which minimizes the number of round trips required by the replica cluster to apply epoch transactions.

Node.js update.  Beginning with with NDB 8.0.22, the NDB adapter for Node.js is built using version 12.18.3, and only that version (or a later version of Node.js) is now supported.

Encrypted backups.  NDB 8.0.22 adds support for backup files encrypted using AES-256-CBC; this is intended to protect against recovery of data from backups that have been accessed by unathorized parties. When encrypted, backup data is protected by a user-supplied password. The password can be any string consisting of up to 256 characters from the range of printable ASCII characters other than !, ', ", $, %, \, and ^. Retention of the password used to encrypt any given NDB Cluster backup must be performed by the user or application; NDB does not save the password. The password can be empty, although this is not recommended.

When taking an NDB Cluster backup, you can encrypt it by using ENCRYPT PASSWORD=password with the management client START BACKUP command. Users of the MGM API can also initiate an encrypted backup by calling ndb_mgm_start_backup4().

To restore from an encrypted backup, use ndb_restore with the options --decrypt and --backup-password. Both options are required, along with any others that would be needed to restore the same backup if it were not encrypted. ndb_print_backup_file and ndbxfrm can also read encrypted files using, respectively, -P password and --decrypt-password=password.

In all cases in which a password is supplied for encryption or decryption, it must be quoted; you can use either single or double quotation marks to delimit the password.

You can encrypt existing backup files using the ndbxfrm utility which is added to the NDB Cluster distribution in the 8.0.22 release; this program can also be employed for decrypting encrypted backup files. In addition, ndbxfrm can compress backup files and decompress compressed backup files using the same method that is employed by NDB Cluster for creating backups when the CompressedBackup configuration parameter is set to 1.

This feature also implements the ability to enforce encrypted backups by setting RequireEncryptedBackup=1 in the [ndbd default] section of the cluster global configuration file. When this is done, the ndb_mgm client rejects any attempt to perform a backup that is not encrypted.

IPv6 support.  Beginning with NDB 8.0.22, IPv6 addressing is supported for connections to management and data nodes; this includes connections between management and data nodes with SQL nodes. When configuring a cluster, you can use numeric IPv6 addresses, host names which resolve to IPv6 addresses or both.

For IPv6 addressing to work, the operating platform and network on which the cluster is deployed must support IPv6. As when using IPv4 addressing, hostname resolution to IPv6 addresses must be provided by the operating platform.

IPv4 addressing continues to be supported by NDB. Using IPv4 and IPv6 addresses concurrently is not recommended, but can be made to work in the following cases:

These cases work because ndb_mgmd does not bind to any IP address by default.

To perform an upgrade from a version of NDB that does not support IPv6 addressing to one that does, provided that the network supports IPv4 and IPv6, first perform the software upgrade; after this has been done, you can update IPv4 addresses used in the config.ini file with IPv6 addresses. After this, to cause the configuration changes to take effect and to make the cluster start using the IPv6 addresses, it is necessary to perform a system restart of the cluster.

Auto-Installer deprecation and removal.  The MySQL NDB Cluster Auto-Installer web-based installation tool (ndb_setup.py) is deprecated in NDB 8.0.22, and is removed in NDB 8.0.23 and later. It is no longer supported.

ndbmemcache deprecation and removal.  ndbmemcache is no longer supported. ndbmemcache was deprecated in NDB 8.0.22, and removed in NDB 8.0.23.

ndbinfo backup_id table.  NDB 8.0.24 adds a backup_id table to the ndbinfo information database. This is intended to serve as a replacement for obtaining this information by using ndb_select_all to dump the contents of the internal SYSTAB_0 tyable, which is error-prone and takes an excessively long time to perform.

This table has a single column and row containing the ID of the most recent backup of the cluster taken using the START BACKUP management client command. In the event that no backup of this cluster can be found, the table contains a single row whose column value is 0.

Table partitioning enhancements.  NDB 8.0.23 introduces a new method for handling table partitions and fragments, which can determine the number of local data managers (LDMs) for a given data node independently of the number of redo log parts. This means that the number of LDMs can now be highly variable. NDB can employ this method when the ClassicFragmentation data node configuration parameter, also implemented in NDB 8.0.23, is set to false; when this is the case, the number of LDMs is no longer used to determine how many partitions to create for a table per data node, and the value of the PartitionsPerNode parameter (also introduced in NDB 8.0.23) determines this number instead, which is also used for calculating the number of fragments used for a table.

When ClassicFragmentation has its default value true, then the traditional method of using the number of LDMs is used to determine the number of fragments that a table should have.

For more information, see the descriptions of the new parameters referenced previously, in Multi-Threading Configuration Parameters (ndbmtd).

Terminology updates.  To align with work begun in MySQL 8.0.21 and NDB 8.0.21, NDB 8.0.23 implements a number of changes in terminology, listed here:

ThreadConfig enhancements.  As of NDB 8.0.23, the configurability of the ThreadConfig parameter has been extended with two new thread types, listed here:

It is also possible to combine the existing main and rep threads in either of two ways:

In addition, the maximum numbers of a number of existing thread types have been increased. The new maximums, including those for query threads and recovery threads, are listed here:

Maximums for other thread types remain unchanged.

For more information, see the descriptions of the ThreadConfig parameter and the ndbinfo.threads table.

Also, as the result of work done relating to this task, NDB now employs mutexes to protect job buffers when using more than 32 block threads. While this can cause a slight decrease in performance (1 to 2 percent in most cases), it also significantly reduces the amount of memory required by very large configurations. For example, a setup with 64 threads which used 2 GB of job buffer memory prior to NDB 8.0.23 should require only about 1 GB instead in NDB 8.0.23 and later. In our testing this has resulted in an overall improvement on the order of 5 percent in the execution of very complex queries.

ndbmtd Thread Auto-Configuration.  Beginning with NDB 8.0.23, it is possible to employ automatic configuration of threads for multi-threaded data nodes using the ndbmtd configuration parameter AutomaticThreadConfig. When this parameter is set to 1, NDB sets up thread assignments automatically, based on the number of processors available to applications, for all thread supported thread types, including the new query and recover thread types described in the previous item. If the system does not limit the number of processors, you can do so if desired by setting NumCPUs (also added in NDB 8.0.23). Otherwise, automatic thread configuration accommodates up to 1024 CPUs.

Automatic thread configuration occurs regardless of any values set for ThreadConfig or MaxNoOfExecutionThreads in config.ini; this means that it is not necessary to set either of these parameters.

In addition, NDB 8.0.23 implements a number of new ndbinfo information database tables providing information about hardware and CPU availability, as well as CPU usage by NDB data nodes. These tables are listed here:

Some of these tables are not available on every platform supported by NDB Cluster; see the individual descriptions of them for more information.

Hierachical views of NDB database objects.  The dict_obj_tree table, added to the ndbinfo information database in NDB 8.0.24, can provide hierarchical and tree-like views of many NDB database objects, including the following:

For more information and examples, see Section 23.5.14.22, “The ndbinfo dict_obj_tree Table”.

Node Configuration Parameters Introduced in NDB 8.0

Node Configuration Parameters Deprecated in NDB 8.0

Node Configuration Parameters Removed in NDB 8.0

MySQL Server Options and Variables Introduced in NDB 8.0

MySQL Server Options and Variables Deprecated in NDB 8.0

MySQL Server Options and Variables Removed in NDB 8.0

NDB Cluster

Cluster Direct API (NDBAPI)

Cluster Disk Data

Cluster Replication

ClusterJ

Each data node or SQL node requires a my.cnf file that provides two pieces of information: a connection string that tells the node where to find the management node, and a line telling the MySQL server on this host (the machine hosting the data node) to enable the NDBCLUSTER storage engine.

For more information on connection strings, see Section 23.3.3.3, “NDB Cluster Connection Strings”.

The management node needs a config.ini file telling it how many fragment replicas to maintain, how much memory to allocate for data and indexes on each data node, where to find the data nodes, where to save data to disk on each data node, and where to find any SQL nodes.

For a table to be replicated in the cluster, it must use the NDBCLUSTER storage engine. To specify this, use the ENGINE=NDBCLUSTER or ENGINE=NDB option when creating the table:

CREATE TABLE tbl_name (col_name column_definitions) ENGINE=NDBCLUSTER;

Alternatively, for an existing table that uses a different storage engine, use ALTER TABLE to change the table to use NDBCLUSTER:

ALTER TABLE tbl_name ENGINE=NDBCLUSTER;

Every NDBCLUSTER table has a primary key. If no primary key is defined by the user when a table is created, the NDBCLUSTER storage engine automatically generates a hidden one. Such a key takes up space just as does any other table index. (It is not uncommon to encounter problems due to insufficient memory for accommodating these automatically created indexes.)

On the management host (198.51.100.10 in our example setup):

shell> ndb_mgmd -f /var/lib/mysql-cluster/config.ini

On each of the data node hosts (198.51.100.30 and 198.51.100.40):

shell> ndbd

Use the ndb_mgm client to verify that both data nodes have started successfully.

On the SQL host (198.51.100.20):

shell> mysqld_safe &

On the management host (198.51.100.10 in our example setup), execute the following command:

C:\> SC START ndb_mgmd

On each of the data node hosts (198.51.100.30 and 198.51.100.40), execute the following command:

C:\> SC START ndbd

On the management node host, use the ndb_mgm client to verify that the management node and both data nodes have started successfully (see Section 23.2.2.3, “Initial Startup of NDB Cluster on Windows”).

On the SQL node host (198.51.100.20), execute the following command:

C:\> SC START mysql

NDB Cluster 7.6: NDB 7.6.4 and later

NDB Cluster 7.5: NDB 7.5.4 and later

NDB Cluster 7.4: NDB 7.4.6 and later

Online downgrades from NDB 8.0.14 to previous releases are not supported. Tables created in NDB 8.0.14 are not backwards compatible with previous releases. This is due to a change in usage of the extra metadata property implemented by NDB tables to provide full support for the MySQL data dictionary.

For more information, see Changes in NDB table extra metadata. See also Chapter 14, MySQL Data Dictionary.

Distributed privileges shared between MySQL servers as implemented in prior release series (see Distributed Privileges Using Shared Grant Tables) are not supported in NDB Cluster 8.0. When started, the mysqld supplied with NDB 8.0.16 and later checks for the existence of any grant tables which use the NDB storage engine; if it finds any, it creates local copies (“shadow tables”) of these using InnoDB. This is true for each MySQL server connected to NDB Cluster. After this has been performed on all MySQL servers acting as NDB Cluster SQL nodes, the NDB grant tables may be safely removed using the ndb_drop_table utility supplied with the NDB Cluster distribution, like this:

ndb_drop_table -d mysql user db columns_priv tables_priv proxies_priv procs_priv

It is safe to retain the NDB grant tables, but they are not used for access control and are effectively ignored.

For more information about the MySQL privileges system used in NDB 8.0, see Section 23.5.12, “Distributed MySQL Privileges with NDB_STORED_USER”, as well as Section 6.2.3, “Grant Tables”.

In NDB 8.0.18, the binary configuration file format has been enhanced to provide support for greater numbers of nodes than in previous versions. The new format is not accessible to 8.0.17 and older nodes, although newer management servers can detect older nodes and communicate with them using the appropriate format.

Upgrades to NDB 8.0.18 or later from 8.0.17 and earlier should not be problematic in this regard. In the case of downgrades from NDB 8.0.18 or later to 8.0.17 or earlier, because older management servers cannot read the newer binary configuration file format, some manual intervention is required. When performing such a downgrade, it is necessary to remove any cached binary configuration files prior to starting the management using the older NDB software version, and to have the plaintext configuration file available for the management server to read. Alternatively, you can start the older management server using the --initial option (again, it is necessary to have the config.ini available). If the cluster uses multiple management servers, one of these two things must be done for each management server binary.

Also in connection with support for increased numbers of nodes, due to incompatible changes implemented in NDB 8.0.18 in the data node LCP Sysfile, it is necessary, when performing an online downgrade from NDB 8.0.18 (or later) to any prior release, to restart all data nodes using the --initial option.

Restarting the data nodes with --initial is also required when upgrading any release prior to NDB 7.6.4 to any NDB 8.0 release.

Direct downgrades of clusters running more than 48 data nodes, or with data nodes using node IDs greater than 48, to NDB versions 8.0.17 and earlier from NDB 8.0.18 or later are not supported. It is necessary in such cases to reduce the number of data nodes, change the configurations for all data nodes such that they use node IDs less than or equal to 48, or both, as required not to exceed the old maximums.

If you are downgrading from NDB 8.0 to NDB 7.5 or NDB 7.4, you must set an explicit value for IndexMemory in the cluster configuration file if none is already present. This is because NDB 8.0 does not use this parameter (which was removed in NDB 7.6) and sets it to 0 by default, whereas it is required in NDB 7.5 and NDB 7.4, in both of which the cluster refuses to start with Invalid configuration received from Management Server... if IndexMemory is not set to a nonzero value.

Setting IndexMemory is not required for downgrades from NDB 8.0 to NDB 7.6.

NDB 8.0.22 adds support for IPv6 addressing for management nodes and data nodes in the config.ini file. To begin using IPv6 addresses as part of an upgrade, perform the following steps: