Thursday, February 16, 2006

How to define a table that uses disk data in MySQL Cluster

At first before creating a table that uses non-indexed fields on
disk in MySQL Cluster, it is necessary to create a LOGFILE
GROUP and a TABLESPACE.

CREATE LOGFILE GROUP lg1
ADD UNDOFILE 'undofile.dat'
INITIAL_SIZE 16M
UNDO_BUFFER_SIZE = 1M
ENGINE = NDB;

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

CREATE TABLESPACE ts1
ADD DATAFILE 'datafile.dat'
USE LOGFILE GROUP lg1
INITIAL_SIZE 12M
ENGINE NDB;

This creates a file 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.

Now we are ready to define 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 4M
ENGINE=NDB;

and similarly can be done for the LOGFILE GROUP
ALTER LOGFILE GROUP lg1
ADD UNDOFILE 'undofile02.dat'
INITIAL_SIZE = 4M
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 'datafile.dat'
ENGINE=NDB;
ALTER TABLESPACE ts1
DROP DATAFILE 'datafile02.dat'
ENGINE=NDB;
DROP TABLESPACE ts1
ENGINE=NDB;
DROP LOGFILE GROUP lg1
ENGINE=NDB;

4 comments:

Kris Buytert said...

From which version on is this supposed to work ?

Mikael Ronstrom said...

From version 5.1.6.

Rgrds Mikael

Ferdi said...

Hi Mikael,

I just tried out this new feature but i am wondering why some columns are still stored in memory (even tough they are not part of the index)

I asked my question here: http://www.mysqltalk.org/question-about-tablespace-fields-on-disk-with-517-vt190493.html

Thanks!

Mikael Ronstrom said...

Looks like there is some bug around this with
TEXT columns.