Chapter 13. MySQL Cluster Development Roadmap

Integration of MySQL Cluster into MySQL Replication.  MySQL Cluster Replication makes it possible to replicate from one MySQL Cluster to another. Updates on any SQL node (MySQL server) in the cluster acting as the master are replicated to the slave cluster; the state of the slave side remains consistent with the cluster acting as the master. This is sometimes referred to as asynchronous replication between clusters, providing geographic redundancy. It is also possible to replicate from a MySQL Cluster acting as the master to a standalone MySQL server acting as the slave, or from a standalone MySQL master server to to a slave cluster; in either of these cases, the standalone MySQL server uses a storage engine other than NDBCLUSTER. Multi-master replication setups such as circular replication are also supported.

See Chapter 9, MySQL Cluster Replication.

Support for storage of rows on disk.  Storage of NDBCLUSTER table data on disk is now supported. Indexed columns, including the primary key hash index, must still be stored in RAM; however, all other columns can be stored on disk.

See Chapter 10, MySQL Cluster Disk Data Tables.

Variable-size columns.  In MySQL 5.0, an NDBCLUSTER table column defined as VARCHAR(255) used 260 bytes of storage independent of what was stored in any particular record. In MySQL 5.1 Cluster tables, only the portion of the column actually taken up by the record is stored. This makes possible a significant reduction in space requirements for such columns as compared to previous release series — by a factor of up to 5 in many cases.

User-defined partitioning.  Users can define partitions based on columns that are part of the primary key. It is possible to partition NDB tables based on KEY and LINEAR KEY schemes. This feature is also available for many other MySQL storage engines, which support additional partitioning types that are not available with NDBCLUSTER tables.

For additional general information about user-defined partitioning in MySQL 5.1, see Partitioning. Specifics of partitioning types are discussed in Partition Types.

The MySQL Server can also determine whether it is possible to “prune away” some of the partitions from the WHERE clause, which can greatly speed up some queries. See Partition Pruning, for information about designing tables and queries to take advantage of partition pruning.

Autodiscovery of table schema changes.  In MySQL 5.0, it was necessary to issue a FLUSH TABLES statement or a “dummy” SELECT in order for new NDBCLUSTER tables or changes made to schemas of existing NDBCLUSTER tables on one SQL node to be visible on the cluster's other SQL nodes. In MySQL 5.1, this is no longer necessary; new Cluster tables and changes in the definitions of existing NDBCLUSTER tables made on one SQL node are immediately visible to all SQL nodes connected to the cluster.

Distribution awareness (NDB API).  Distribution awareness is a mechanism by which the best data node is automatically selected to be queried for information. (Conceptually, it is similar in some ways to partition pruning (see Partition Pruning). To take advantage of distribution awareness, you should do the following:

Following these steps causes records with the same value for the partitioning column to be stored on the same partition (that is, in the same node group). When reading data, transactions are begun on the data node actually having the desired rows instead of this node being determined by the usual round-robin mechanism.

Using distribution awareness can yield performance increase of as great as 45% when using four data nodes, and possibly more when using a greater number of data nodes.

Increased number of cluster nodes.  The maximum number of all nodes in a MySQL Cluster has been increased to 255. For more information, see Section 12.10, “Limitations Relating to Multiple MySQL Cluster Nodes”.

Disabling arbitration.  It is now possible to disable arbitration by setting ArbitrationRank=0 on all cluster management and SQL nodes. For more information, see Defining the Management Server: ArbitrationRank and Defining SQL and Other API Nodes: ArbitrationRank.

Additional DUMP commands.  New management client DUMP commands provide help with tracking transactions, scan operations, and locks. See Section 7.2, “Commands in the MySQL Cluster Management Client”, and DUMP Commands, for more information.

Faster Disk Data backups.  Improvements in backups of Disk Data tables can yield a 10 to 15% increase in backup speed of Disk Data tables.

Batched slave updates.  Batching of updates on cluster replication slaves, enabled using the --slave-allow-batching option for mysqld, increases replication efficiency. For more information, see Section 9.6, “Starting MySQL Cluster Replication (Single Replication Channel)”.

Enhanced backup status reporting.  Backup status reporting has been improved, aided in part by the introduction of a BackupReportFrequency configuration parameter; see Defining Data Nodes: BackupReportFrequency, for more information.

Multiple cluster connections per SQL node.  A single MySQL server acting as a MySQL Cluster SQL node can employ multiple connections to the cluster using the --ndb-cluster-connection-pool startup option for mysqld. This option is described in MySQL Cluster-Related Command Options for mysqld: --ndb-cluster-connection-pool option.

New data access interface.  The NdbRecord interface provides a new and simplified data handler for use in NDB API applications. See The NdbRecord Interface, for more information.

New reporting commands.  The new management client REPORT BackupStatus and REPORT MemoryUsage commands provide better access to information about the status of MySQL Cluster backups and how much memory is being used by MySQL Cluster for data and index storage. See Section 7.2, “Commands in the MySQL Cluster Management Client”, for more information about the REPORT commands. In addition, in-progress status reporting is provided by the ndb_restore utility; see Section 6.17, “ndb_restore — Restore a MySQL Cluster Backup”.

Improved memory allocation and configuration.  Memory is now allocated by the NDB kernel to tables on a page-by-page basis, which significantly reduces the memory overhead required for maintaining NDBCLUSTER tables. In addition, the MaxAllocate configuration parameter now makes it possible to set the maximum size of the allocation unit used for table memory; for more information about this configuration parameter, see Defining Data Nodes: MaxAllocate.

Choice of fixed-width or variable-width columns.  You can control whether fixed-width or variable-width storage is used for a given column of an NDB table by employing of the COLUMN_FORMAT specifier as part of the column's definition in a CREATE TABLE or ALTER TABLE statement. In addition, the ability to control whether a given column of an NDB table is stored in memory or on disk, using the STORAGE specifier as part of the column's definition in a CREATE TABLE or ALTER TABLE statement. For more information, see CREATE TABLE Syntax, and ALTER TABLE Syntax.

Controlling management client connections.  The --bind-address cluster management server startup option makes it possible to restrict management client connections to ndb_mgmd to a single host (IP address or host name) and port, which can make MySQL Cluster management operations more secure. For more information about this option, see Section 6.24.3, “Program Options for ndb_mgmd”.

Micro-GCPs.  Due to a change in the protocol for handling of global checkpoints (GCPs handled in this manner sometimes being referred to as “micro-GCPs”), it is now possible to control how often the GCI number is updated, and how often global checkpoints are written to disk, using the TimeBetweenEpochs configuration parameter. This improves the reliability and performance of MySQL Cluster Replication. For more information, see Defining Data Nodes: TimeBetweenEpochs and Defining Data Nodes: TimeBetweenEpochsTimeout.

Core online schema change support.  Support for the online ALTER TABLE operations ADD COLUMN, ADD INDEX, and DROP INDEX is available. When the ONLINE keyword is used, the ALTER TABLE is noncopying, which means that indexes do not have to be re-created, which provides these benefits:

Online CREATE INDEX and DROP INDEX statements are also supported. Online changes can be suppressed using the OFFLINE key word. See ALTER TABLE Syntax, CREATE INDEX Syntax, and DROP INDEX Syntax, for more detailed information.

mysql.ndb_binlog_index improvements.  More information has been added to the mysql.ndb_binlog_index table so that it is possible to determine which originating epochs have been applied inside an epoch. This is particularly useful for 3-way replication. See Section 9.4, “MySQL Cluster Replication Schema and Tables”, for more information.

Epoch lag control.  The MaxBufferedEpochs data node configuration parameter provides a means to control the maximum number of unprocessed epochs by which a subscribing node can lag. Subscribers which exceed this number are disconnected and forced to reconnect. For a discussion of this configuration parameter, see Defining Data Nodes: MaxBufferedEpochs.

Fully automatic database discovery.  It is no longer a requirement for database autodiscovery that an SQL node already be connected to the cluster at the time that a database is created on another SQL node, or for a CREATE DATABASE or CREATE SCHEMA statement to be issued on the new SQL node after it joins the cluster.

Multiple data node processes per host.  In earlier MySQL Cluster release series, we did not support MySQL Cluster deployments in production where more than one ndbd process was run on a single physical machine. However, beginning with MySQL Cluster NDB 6.2.0, you can use multiple data node processes on a single host.

Improved Disk Data filesystem configuration.  As of MySQL Cluster NDB 6.2.17, you can specify default locations for MySQL Cluster Disk Data data files and undo log files using the data node configuration parameters FileSystemPathDD, FileSystemPathDataFiles, and FileSystemPathUndoFiles. For more information, see Disk Data filesystem parameters.

Automatic creation of Disk Data log file groups and tablespaces.  Beginning with MySQL Cluster NDB 6.2.17, using the data node configuration parameters InitialLogFileGroup and InitialTablespace, you can cause the creation of a MySQL Cluster Disk Data log file group, tablespace, or both, when the cluster is first started. When using these parameters, no SQL statements are required to create these Disk Data objects. For more information, see Disk Data object creation parameters.

Conflict detection and resolution.  It is now possible to detect and resolve conflicts that arise in multi-master replication scenarios, such as circular replication, when different masters may try to update the same row on the slave with different data. Both “greatest timestamp wins” and “same timestamp wins” scenarios are supported. For more information, see Section 9.11, “MySQL Cluster Replication Conflict Resolution”.

Recovery of “one master, many slaves” replication setups.  Recovery of multi-way replication setups (“one master, many slaves”) is now supported via the --ndb-log-orig server option and changes in the mysql.ndb_binlog_index table. See Section 9.4, “MySQL Cluster Replication Schema and Tables”, for more information.

Enhanced selection options for transaction coordinator.  New values and behaviors are introduced for --ndb_optimized_node_selection allowing for greater flexibility when an SQL node chooses a transaction coordinator. For more information, see the description of ndb_optimized_node_selection in Section 4.3, “MySQL Cluster System Variables”.

Replication heartbeats.  Replication heartbeats facilitate the task of monitoring and detecting failures in master-slave connections in real time. This feature is implemented via a new MASTER_HEARTBEAT_PERIOD = value clause for the CHANGE MASTER TO statement and the addition of two status variables Slave_heartbeat_period and Slave_received_heartbeats. For more information, see CHANGE MASTER TO Syntax.

NDB thread locks.  It is possible to lock NDB execution threads and maintenance threads (such as file system and other operating system threads) to specific CPUs on multiprocessor data node hosts, and to leverage real-time scheduling.

Improved performance of updates using primary keys or unique keys.  The number of unnecessary reads when performing a primary key or unique key update has been greatly reduced. Since it is seldom necessary to read a record prior to an update, this can yield a considerable improvement in performance. In addition, primary key columns are no longer written to when not needed during update operations.

Batching improvements.  Support of batched DELETE and UPDATE operations has been significantly improved. Batching of UPDATE WHERE... and multiple DELETE operations is also now implemented.

Improved SQL statement performance metrics.  The Ndb_execute_count system status variable measures the number of round trips made by SQL statements to the NDB kernel, providing an improved metric for determining efficiency with which statements are excuted. For more information, see MySQL Cluster Status Variables: Ndb_execute_count.

Compressed LCPs and backups.  Compressed local checkpoints and backups can save 50% or more of the disk space used by uncompressed LCPs and backups. These can be enabled using the two new data node configuration parameters CompressedLCP and CompressedBackup, respectively. See MySQL Cluster Status Variables: CompressedBackup, and MySQL Cluster Status Variables: CompressedLCP, for more information about these parameters.

OPTIMIZE TABLE support with NDBCLUSTER tables.  OPTIMIZE TABLE is supported for dynamic columns of in-memory NDB tables. In such cases, it is no longer necessary to drop (and possibly to re-create) a table, or to perform a rolling restart, in order to recover memory from deleted rows for general re-use by Cluster. The performance of OPTIMIZE on Cluster tables can be tuned by adjusting the value of the ndb_optimization_delay system variable, which controls the number of milliseconds to wait between processing batches of rows by OPTIMIZE TABLE. In addition, OPTIMIZE TABLE on an NDBCLUSTER table can be interrupted by, for example, killing the SQL thread performing the OPTIMIZE operation.

Batching of transactions.  It is possible to cause statements occurring within the same transaction to be run as a batch by setting the session variable transaction_allow_batching to 1 or ON. To use this feature, autocommit must be set to 0 or OFF. Batch sizes can be controlled using the --ndb-batch-size option for mysqld. For more information, see Section 4.2, “mysqld Command Options for MySQL Cluster”, and Section 4.3, “MySQL Cluster System Variables”.

Attribute promotion with ndb_restore.  It is possible using ndb_restore to restore data reliably from a column of a given type to a column that uses a “larger” type. This is sometimes referred to as attribute promotion. For example, MySQL Cluster backup data that originated in a SMALLINT column can be restored to a MEDIUMINT, INT, or BIGINT column. See Section 6.17, “ndb_restore — Restore a MySQL Cluster Backup”, for more information.

Parallel data node recovery.  Recovery of multiple data nodes can now be done in parallel, rather than sequentially. In other words, several data nodes can be restored concurrently, which can often result in much faster recovery times than when they are restored one at a time.

Increased local checkpoint efficiency.  Only 2 local checkpoints are stored, rather than 3, lowering disk space requirements and the size and number of redo log files.

NDBCLUSTER table persistence control.  Persistence of NDB tables can be controlled using the session variables ndb_table_temporary and ndb_table_no_logging. ndb_table_no_logging causes NDB tables not to be checkpointed to disk; ndb_table_temporary does the same, and in addition, no schema files are created. See Section 4.1, “MySQL Cluster Server Option and Variable Reference”.

Epoll support (Linux only).  Epoll is an improved method for handling file descriptors, which is more efficient than scanning to determine whether a file descriptor has data to be read. (The term epoll is specific to Linux and equivalent functionality is known by other names on other platforms such as Solaris and FreeBSD.) Currently, MySQL Cluster supports this functionality on Linux only.

Distribution awareness (SQL nodes).  In MySQL Cluster NDB 6.3, SQL nodes can take advantage of distribution awareness. Here we provide a brief example showing how to design a table to make a given class of queries distrubtion-aware. Suppose an NDBCLUSTER table t1 has the following schema:

CREATE TABLE t1 (
    userid INT NOT NULL,
    serviceid INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    data VARCHAR(255)
)   ENGINE=NDBCLUSTER;

Suppose further that most of the queries to be used in our application test values of the userid column of this table. The form of such a query looks something like this:

SELECT columns FROM t1
    WHERE userid relation value;

In this query, relation represents some relational operator, such as =, <, >, and so on. Queries using IN and a list of values can also be used:

SELECT columns FROM t1
    WHERE userid IN value_list;

In order to make use of distribution awareness, we need to make the userid column part of the table's primary key, then explicitly partition the table with this column being used as the partitioning key. (Recall that for a partitioned table having one or more unique keys, all columns of the table's partitioning key must also be part of all of the unique keys — for more information and examples, see Partitioning Keys, Primary Keys, and Unique Keys.) In other words, the table schema should be equivalent to the following CREATE TABLE statement:

CREATE TABLE t1 (
    userid INT NOT NULL,
    serviceid INT NOT NULL AUTO_INCREMENT,
    data VARCHAR(255),
    PRIMARY KEY p (userid,serviceid)
)   ENGINE=NDBCLUSTER
    PARTITION BY KEY(userid);

When the table is partitioned in this way, all rows having the same userid value are found on the same node group, and the MySQL Server can immediately select the optimal node to use as the transaction coordinator.

Realtime extensions for multiple CPUs.  When running MySQL Cluster data nodes on hosts with multiple processors, the realtime extensions make it possible to give priority to the data node process and control on which CPU cores it should operate. This can be done using the data node configuration parameters RealtimeScheduler, SchedulerExecutionTimer and SchedulerSpinTimer. Doing so properly can significantly lower response times and make them much more predictable response. For more information about using these parameters, see Defining Data Nodes: Realtime Performance Parameters

Fully automatic database discovery.  It is no longer a requirement for database autodiscovery that an SQL node already be connected to the cluster at the time that a database is created on another SQL node, or for a CREATE DATABASE or CREATE SCHEMA statement to be issued on the new SQL node after it joins the cluster.

Restoring specific tables and databases from a MySQL Cluster backup.  It is now possible exercise more fine-grained control when restoring a MySQL Cluster from backup using ndb_restore. You can restore only specified tables or databases, or exclude specific tables or databases from being restored, using the new ndb_restore options --include-tables, --include-databases, --exclude-tables, and --exclude-databases. For more information about these options, see Section 6.17, “ndb_restore — Restore a MySQL Cluster Backup”.

Improved Disk Data filesystem configuration.  As of MySQL Cluster NDB 6.3.22, you can specify default locations for MySQL Cluster Disk Data data files and undo log files using the data node configuration parameters FileSystemPathDD, FileSystemPathDataFiles, and FileSystemPathUndoFiles. For more information, see Disk Data filesystem parameters.

Automatic creation of Disk Data log file groups and tablespaces.  Beginning with MySQL Cluster NDB 6.3.22, using the data node configuration parameters InitialLogFileGroup and InitialTablespace, you can cause the creation of a MySQL Cluster Disk Data log file group, tablespace, or both, when the cluster is first started. When using these parameters, no SQL statements are required to create these Disk Data objects. For more information, see Disk Data object creation parameters.

Configuration parameter data dumps.  Starting with MySQL Cluster NDB 6.3.25, the ndb_config utility supports a --configinfo option that causes it to dump a list of all configuration parameters supported by the cluster, along with brief descriptions, information about the parameters' default and allowed values, and the sections of the config.ini file in which the parameters apply. An additional --xml switch causes ndb_config to use XML rather than plaintext output. Using ndb_config --configinfo or ndb_config --configinfo --xml requires no access to a running MySQL Cluster, any other programs, or any files. For more information and examples, see Section 6.6, “ndb_config — Extract MySQL Cluster Configuration Information”.

MySQL Cluster on Windows (alpha).  MySQL Cluster is now available on an experimental basis for Windows operating systems. Features and behavior comparable to those found on platforms that are already supported — such as Linux and Solaris — are planned for MySQL Cluster on Windows. Currently, you must build from source, although we intend to start making Windows binaries available in the near future. To enable MySQL Cluster support on Windows, you must configure the build using the WITH_NDBCLUSTER_STORAGE_ENGINE option. For more information, see Installing MySQL from Source on Windows.

Ability to add nodes and node groups online.  Beginning with MySQL Cluster NDB 6.4.0, it is possible to add new node groups (and thus new data nodes) to a running MySQL Cluster without shutting down and reloading the cluster. As part of enabling this feature, a new command CREATE NODEGROUP has been added to the cluster management client and the functionality of the ALTER ONLINE TABLE ... REORGANIZE PARTITION SQL statement has been extended. For more information, see Section 7.8, “Adding MySQL Cluster Data Nodes Online”.

Data node multithreading support.  Beginning with MySQL Cluster NDB 6.4.0, a multithreaded version of the data node daemon, named ndbmtd, is available for use on data node hosts with multiple CPU cores. This binary is built automatically when compiling with MySQL Cluster support; no additional options other than those needed to provide MySQL Cluster support are needed when configuring the build. In most respects, ndbmtd functions in the same way as ndbd, and can use the same command-line options and configuration parameters. In addition, the new MaxNoOfExecutionThreads configuration parameter can be used to determine the number of data node process threads for ndbmtd. For more information, see Section 6.3, “ndbmtd — The MySQL Cluster Data Node Daemon (Multi-Threaded)”.

Configuration cache.  Formerly, MySQL Cluster configuration was stateless — that is, configuration information was reloaded from the cluster's global configuration file (usually config.ini) each time ndb_mgmd was started. Beginning with MySQL Cluster NDB 6.4.0, the cluster's configuration is cached internally, and the global configuration file is no longer automatically re-read when the management server is restarted. This behavior can be controlled via the three new management server options --configdir, --initial, and --reload. For more information about this change, see Section 3.4, “MySQL Cluster Configuration Files”. For more information about the new management server options, see Section 6.24.3, “Program Options for ndb_mgmd”.

Snapshot options for backups.  Beginning with MySQL Cluster NDB 6.4.0, you can determine when performing a cluster backup whether the backup matches the state of the data when the backup was started or when it was completed, using the new options SNAPSHOTSTART and SNAPSHOTEND for the management client's START BACKUP command. See Section 7.3.2, “Using The MySQL Cluster Management Client to Create a Backup”, for more information.

Dynamic NDB transporter send buffer memory allocation.  Previously, the NDB kernel used a fixed-size send buffer for every data node in the cluster, which was allocated when the node started. Because the size of this buffer could not be changed after the cluster was started, it was necessary to make it large enough in advance to accomodate the maximum possible load on any transporter socket. However, this was an inefficient use of memory, since much of it often went unused. Beginning with MySQL Cluster NDB 6.4.0, send buffer memory is allocated dynamically from a memory pool shared between all transporters, which means that the size of the send buffer can be adjusted as necessary. This change is reflected by the addition of the configuration parameters TotalSendBufferMemory, ReservedSendBufferMemory, and OverLoadLimit, as well as a change in how the existing SendBufferMemory configuration parameter is used. For more information, see Section 3.7, “Configuring MySQL Cluster Send Buffer Parameters”.

Robust DDL operations.  Beginning with MySQL Cluster NDB 6.4.0, DDL operations (such as CREATE TABLE or ALTER TABLE) are protected from data node failures; in the event of a data node failure, such operations are now rolled back gracefully. Previously, if a data node failed while trying to perform a DDL operation, the MySQL Cluster data dictionary became locked and no further DDL statements could be executed without restarting the cluster.

IPv6 support in MySQL Cluster Replication.  Beginning with MySQL Cluster NDB 6.4.1, IPv6 networking is supported between MySQL Cluster SQL nodes, which makes it possible to replicate between instances of MySQL Cluster using IPv6 addresses. However, IPv6 is supported only for direct connections between MySQL servers; all connections within an individual MySQL Cluster must use IPv4. For more information, see Section 9.3, “Known Issues in MySQL Cluster Replication”.

Restoring specific tables and databases from a MySQL Cluster backup.  It is now possible exercise more fine-grained control when restoring a MySQL Cluster from backup using ndb_restore. You can restore only specified tables or databases, or exclude specific tables or databases from being restored, using the new ndb_restore options --include-tables, --include-databases, --exclude-tables, and --exclude-databases. For more information about these options, see Section 6.17, “ndb_restore — Restore a MySQL Cluster Backup”.

Improved Disk Data filesystem configuration.  As of MySQL Cluster NDB 6.4.3, you can specify default locations for MySQL Cluster Disk Data data files and undo log files using the data node configuration parameters FileSystemPathDD, FileSystemPathDataFiles, and FileSystemPathUndoFiles. For more information, see Disk Data filesystem parameters.

Automatic creation of Disk Data log file groups and tablespaces.  Beginning with MySQL Cluster NDB 6.4.3, using the data node configuration parameters InitialLogFileGroup and InitialTablespace, you can cause the creation of a MySQL Cluster Disk Data log file group, tablespace, or both, when the cluster is first started. When using these parameters, no SQL statements are required to create these Disk Data objects. For more information, see Disk Data object creation parameters.

Improved internal message passing and record handling.  MySQL Cluster NDB 7.0 contains 2 changes that optimize the use of network connections by addressing the size and number of messages passed between data nodes, and between data nodes and API nodes, which can increase MySQL Cluster and application performance:

Both of these optimizations are internal to the NDB API, and so is transparent to applications; this is true whether an application uses the NDB API directly or does so indirectly through an SQL node.

Configuration parameter data dumps.  Starting with MySQL Cluster NDB 7.0.6, the ndb_config utility supports a --configinfo option that causes it to dump a list of all configuration parameters supported by the cluster, along with brief descriptions, information about the parameters' default and allowed values, and the sections of the config.ini file in which the parameters apply. An additional --xml switch causes ndb_config to use XML rather than plaintext output. Using ndb_config --configinfo or ndb_config --configinfo --xml requires no access to a running MySQL Cluster, any other programs, or any files. For more information and examples, see Section 6.6, “ndb_config — Extract MySQL Cluster Configuration Information”.

NDB$INFO meta-information database.  This feature is intended to make it possible to obtain real-time characteristics of a MySQL Cluster by issuing queries from the mysql client or other MySQL client applications. NDB$INFO is a database which is planned for MySQL Cluster NDB 7.1 to provide metadata specific to MySQL Cluster similar to that provided by the INFORMATION_SCHEMA database for the standard MySQL Server. This would eliminate much of the need to read log files, issue DUMP commands, or parse the output of ndb_config in order to get configuration and status information from a running MySQL Cluster.