MySQL Cluster Disk Data Tables
By LinuxJedi on Feb 21, 2010
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' USE LOGFILE GROUP LG1 EXTENT_SIZE 1M INITIAL_SIZE 1G ENGINE NDBCLUSTER; mysql> ALTER TABLESPACE ts_1 ADD DATAFILE '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;
There is a very easy way to see usage statistics for the data and UNDO files, this information is all in the INFORMATION_SCHEMA.FILES table. This lists the details for each group and each file for each node so you many want to use an SQL query to limit this information. Some great examples can be seen in the manual page.
Things to watch for
The data node has the config.ini option DiskPageBufferMemory, this acts very much like InnoDB's innodb_buffer_pool_size setting. So it is best to set this as large as possible for optimal performance. If it is too low and the disk data is on the same file system as the REDO logs this can lead to GCP Stop.
So, to prevent GCP Stop in this case you can put the disk data on a different file system to REDO logs, this can be done with the FileSystemPathDD, FileSystemPathDataFiles and FileSystemPathUndoFiles configuration options. Alternatively a path can be provided when you add the files.
The undo_buffer_size is taken out of SharedGlobalMemory, so this setting will typically need to be increased to accommodate the UNDO buffer. If this happens when creating Logfile Group in the MySQL client you will see the following error:
ERROR 1528 (HY000): Failed to create LOGFILE GROUP
With SHOW WARNINGS you will then see:
Got error 1504 'Out of logbuffer memory' from NDB
This is also a good point with many errors that occur in MySQL Cluster, the SHOW WARNINGS command will often give more details on the error which can help figure out the cause.