22.5.13.1 NDB Cluster Disk Data Objects

NDB Cluster Disk Data storage is implemented using the following objects:

Undo log files and data files are actual files in the file system of each data node; by default they are placed in ndb_node_id_fs in the DataDir specified in the NDB Cluster config.ini file, and where node_id is the data node's node ID. It is possible to place these elsewhere by specifying either an absolute or relative path as part of the filename when creating the undo log or data file. Statements that create these files are shown later in this section.

Undo log files are used only by Disk Data tables, and are not needed or used by NDB tables that are stored in memory only.

NDB Cluster tablespaces and log file groups are not implemented as files.

Although not all Disk Data objects are implemented as files, they all share the same namespace. This means that each Disk Data object must be uniquely named (and not merely each Disk Data object of a given type). For example, you cannot have a tablespace and a log file group both named dd1.

Assuming that you have already set up an NDB Cluster with all nodes (including management and SQL nodes), the basic steps for creating an NDB Cluster table on disk are as follows:

Each of these tasks can be accomplished using SQL statements in the mysql client or other MySQL client application, as shown in the example that follows.

You can obtain information about the NDB disk data files and undo log files just created by querying the FILES table in the INFORMATION_SCHEMA database, as shown here:

For more information and examples, see Section 25.11, “The INFORMATION_SCHEMA FILES Table”.

Indexing of columns implicitly stored on disk.  For table dt_1 as defined in the example just shown, only the dob and joined columns are stored on disk. This is because there are indexes on the id, last_name, and first_name columns, and so data belonging to these columns is stored in RAM. Only nonindexed columns can be held on disk; indexes and indexed column data continue to be stored in memory. This tradeoff between the use of indexes and conservation of RAM is something you must keep in mind as you design Disk Data tables.

You cannot add an index to a column that has been explicitly declared STORAGE DISK, without first changing its storage type to MEMORY; any attempt to do so fails with an error. A column which implicitly uses disk storage can be indexed; when this is done, the column's storage type is changed to MEMORY automatically. By “implicitly”, we mean a column whose storage type is not declared, but which is which inherited from the parent table. In the following CREATE TABLE statement (using the tablespace ts_1 defined previously), columns c2 and c3 use disk storage implicitly:

Because c2, c3, and c4 are themselves not declared with STORAGE DISK, it is possible to index them. Here, we add indexes to c2 and c3, using, respectively, CREATE INDEX and ALTER TABLE:

SHOW CREATE TABLE confirms that the indexes were added.

You can see using ndb_desc that the indexed columns (emphasized text) now use in-memory rather than on-disk storage:

shell> ./ndb_desc -d test t1
-- t1 --
Version: 33554433
Fragment type: HashMapPartition
K Value: 6
Min load factor: 78
Max load factor: 80
Temporary table: no
Number of attributes: 4
Number of primary keys: 1
Length of frm data: 317
Max Rows: 0
Row Checksum: 1
Row GCI: 1
SingleUserMode: 0
ForceVarPart: 1
PartitionCount: 4
FragmentCount: 4
PartitionBalance: FOR_RP_BY_LDM
ExtraRowGciBits: 0
ExtraRowAuthorBits: 0
TableStatus: Retrieved
Table options:
HashMap: DEFAULT-HASHMAP-3840-4
-- Attributes --
c1 Int PRIMARY KEY DISTRIBUTION KEY AT=FIXED ST=MEMORY
c2 Int NULL AT=FIXED ST=MEMORY
c3 Int NULL AT=FIXED ST=MEMORY
c4 Int NULL AT=FIXED ST=DISK
-- Indexes -- 
PRIMARY KEY(c1) - UniqueHashIndex
i2(c3) - OrderedIndex
PRIMARY(c1) - OrderedIndex
i1(c2) - OrderedIndex

NDBT_ProgramExit: 0 - OK

Performance note.  The performance of a cluster using Disk Data storage is greatly improved if Disk Data files are kept on a separate physical disk from the data node file system. This must be done for each data node in the cluster to derive any noticeable benefit.

You can use absolute and relative file system paths with ADD UNDOFILE and ADD DATAFILE; relative paths are calculated with respect to the data node's data directory. You can also use symbolic links to point to a directory on a different disk; see Section 22.5.13.2, “Using Symbolic Links with Disk Data Objects”, for more information and examples.

A log file group, a tablespace, and any Disk Data tables using these must be created in a particular order. This is also true for dropping these objects, sibject to the following constraints:

For example, to drop all the objects created so far in this section, you can use the following statements:

These statements must be performed in the order shown, except that the two ALTER TABLESPACE ... DROP DATAFILE statements may be executed in either order.