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;

6 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.

Ali Raza said...

I created table with following command on ndb
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;
Query OK, 0 rows affected (3.90 sec)

new if I check the table create command it show as follows

CREATE TABLE `table_1` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`a` varchar(50) NOT NULL,
`b` date NOT NULL,
PRIMARY KEY (`id`)
) /*!50100 TABLESPACE ts_1 STORAGE DISK */ ENGINE=ndbcluster DEFAULT CHARSET=latin1

kindly let me know what does "/*!50100 TABLESPACE ts_1 STORAGE DISK */" means.
and how I can check wich table is stored on the disk.

Best Regards,
Ali Raza

Mikael Ronstrom said...

/*!50100 ... */
This means that the section will be used if MySQL version is above
5.1.0. So if you try to restore this table in 5.0 or older version the
tablespace part will not be used since the 5.0 version of MySQL Cluster
didn't support table on disk.

So for all practical matters nowadays it simply means that the
TABLESPACE ts_1 DISK is there since I assume you are not running
on such old versions as 5.0.