How Disk Data Tables work

A set of UNDO logs and data files can be created which are on every data node in the cluster. The UNDO logs bring the disk data back to a consistent point so that during node restart the REDO logs can be replayed when a node is started.

Not all data in a disk table is actually stored on disk, the main two things that are not are indexed columns (the entire column is in main memory, not just the index) and the first 256 bytes of a BLOB/TEXT column. Also columns on disk are fixed length, so your varchar(255) will be the equivalent of a char(255). There are plans to change this in future releases. How to create Disk Data Tables

To create a Disk Data Table you first need two things, a Logfile Group containing UNDO files and a Tablespace containing data files. Each cluster can only have one Logfile Group but can have multiple Tablespaces. There are in fact two ways to creates Logfile Groups and Tablespaces. The first is using config.ini, if you use this method the files are created upon an initial start of a data node automatically. To do this you need to add the following to the [ndbd default] section of this file:

InitialLogFileGroup = name=lg_1; undo_buffer_size=64M; undo1.log=150M; undo2.log=200M InitialTablespace = name=ts_1; extent_size=1M; data1.dat=1G; data2.dat=2G

These examples show two files in each grouping but you can have one or more in each group. The extent_size is optional and defaults to 1M if omitted.

The second way of creating these is to use the MySQL client, the following example will create the same as the config.ini settings above:

mysql> CREATE LOGFILE GROUP LG1

  ADD UNDOFILE 'undo1.log'
  INITIAL_SIZE 150M
  UNDO_BUFFER_SIZE 164M
  ENGINE NDBCLUSTER;

mysql> ALTER LOGFILE GROUP LG1

  ADD UNDOFILE 'undo2.log'
  INITIAL_SIZE 200M
  ENGINE NDBCLUSTER;

mysql> CREATE TABLESPACE ts_1

  ADD DATAFILE 'data1.dat'
  EXTENT_SIZE 1M
  INITIAL_SIZE 1G
  ENGINE NDBCLUSTER;

mysql> ALTER TABLESPACE ts_1

  ADD UNDOFILE 'data2.dat'
  INITIAL_SIZE 2G
  ENGINE NDBCLUSTER;

The ENGINE NDBCLUSTER is required, this is because it is possible in the future to have Logfile Groups and Tablespaces for other engines. It is worth noting here that the undo_buffer_size cannot be increased with an alter table. The only way of doing this is dropping all the Tablespaces and the Logfile Group (and therefore all your disk data) first, this is something else we are working on improving.

Finally to create a disk table you need to do:

CREATE TABLE table_1 (

  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  a VARCHAR(50) NOT NULL,
  b DATE NOT NULL,
  )
  TABLESPACE ts_1 STORAGE DISK
  ENGINE NDBCLUSTER;

Or if the table already exists it can be moved into a tablespace using:

ALTER TABLE table_1 TABLESPACE ts_1 STORAGE DISK ENGINE NDBCLUSTER;