【MySQL cluster】using disk based table in NDB

====================================================================
Beginning with MySQL Cluster NDB 6.2.5, table can be stored in disk other than memory by using:
TABLESPACE ts_name STORAGE disk

There are 2 ways:
1st mothod:
CREATE TABLE t1 (a int, b int, c int, d int, e int,
primary key(a), index(a,b))
TABLESPACE ts1 STORAGE DISK
engine=ndb;
none-indexed columns will be stored in ts1;

2nd method, choose your own columns
CREATE TABLE t1 (
c1 INT STORAGE DISK,
c2 INT STORAGE MEMORY
) TABLESPACE ts_1 ENGINE NDB;


====================================================================
How to define a table that uses disk data in MySQL Cluster
====================================================================
At first, you have to create a LOGFILE GROUP and a TABLESPACE.

1. create logfile group
CREATE LOGFILE GROUP lg1
ADD UNDOFILE 'undofile01.dat'
INITIAL_SIZE = 1g
UNDO_BUFFER_SIZE = 8M
ReDO_BUFFER_SIZE = 8M
ENGINE = NDB;


This creates a same file name on each node for storing UNDO log records.

2. create tablespace
CREATE TABLESPACE ts1
ADD DATAFILE 'datafile01.dat'
USE LOGFILE GROUP lg1
INITIAL_SIZE 1g ENGINE NDB;

This creates a same file name on each node for storing the disk data parts
of a disk-based table. All tables in the TABLESPACE is connected
to the LOGFILE GROUP previously defined.

Crrrently, it is just a fixed size, we can only add new files to add more space.


3. create the actual disk-based table.
CREATE TABLE t1 (a int, b int, c int, d int, e int,
primary key(a), index(a,b))
TABLESPACE ts1 STORAGE DISK
engine=ndb;

This defines a table where a and b will be in memory since they are
part of indexes. c,d and e will be stored as they are not part of
indexes.

If later one wants to add a new file to the tablespace one does this
by:
ALTER TABLESPACE ts1
ADD DATAFILE 'datafile02.dat'
INITIAL_SIZE 4G
ENGINE=NDB;

and similarly can be done for the LOGFILE GROUP
ALTER LOGFILE GROUP lg1
ADD UNDOFILE 'undofile02.dat'
INITIAL_SIZE = 2G
ENGINE=NDB;



Tablespaces and logfile groups can be dropped as well but only when
all objects in them are dropped. So to drop everything after the above
changes one does.

DROP TABLE t1;
ALTER TABLESPACE ts1
DROP DATAFILE 'datafile01.dat'
ENGINE=NDB;

ALTER TABLESPACE ts1
DROP DATAFILE 'datafile02.dat'
ENGINE=NDB;

DROP TABLESPACE ts1
ENGINE=NDB;

DROP LOGFILE GROUP lg1
ENGINE=NDB;


====================================================================
monitor the usage

check undo usage
-------------------------------------------------------
SELECT node_id,ROUND(total/1024/1024) total_MB,
ROUND(used*100/total,2) used_pct
FROM ndbinfo.logspaces WHERE log_type='DD-UNDO';

check tablespace usage
-------------------------------------------------------
SELECT TABLESPACE_NAME, extra,
ROUND(SUM(total_extents*EXTENT_SIZE)/1024/1024) total_MB,
ROUND(SUM((total_extents-free_extents)*EXTENT_SIZE)/1024/1024) used_MB
FROM INFORMATION_SCHEMA.FILES
WHERE file_type = 'DATAFILE'
GROUP BY tablespace_name,extra;


check the ndb memory usage
-------------------------------------------------------
SELECT node_id,memory_type,
ROUND(total/1024/1024) total_mb,
ROUND(used/1024/1023) used_MB
FROM ndbinfo.memoryusage
GROUP BY node_id, memory_type;


check the table_size and rows
-------------------------------------------------------
SELECT table_name,ROUND(data_length/1024/1024) data_MB,
ROUND(index_length/1024/1024) index_MB,
table_rows,AVG_ROW_LENGTH
FROM information_schema.tables
WHERE table_type='BASE TABLE'
AND table_schema='co_test';