Disk data tables

Tweet I got a few questions about how to configure the cluster for disk data.
The new version of the Dimensioning Toolkit does a better job calculating:

• UNDO LOG file size
• TABLESPACE size
• UNDO Buffer size

What I write about below is taken into account in the Dimensioning Toolkit. Moreover, the things below only applies to disk data tables (read about the generic stuff about them in the reference manual).

UNDO LOG and UNDO BUFFER
Disk data tables make use of an UNDO log and an UNDO buffer.
The size of the undo log and the undo buffer is specified when you create the logfile group.

I recommend setting:

• UNDO log size = 4 to 6 times the DataMemory, thus the same size as for the REDO log.
  
• UNDO buffer size= 32M
  

The memory for the UNDO buffer is allocated from the SharedGlobalMemory, which is a parameter in the [ndbd default] section.
In order to have an UNDO buffer that is 32M, you need to set:

[ndbd default]
...
SharedGlobalMemory=256M
...

Current version of the Configuration Tool sets this value for you, and the Dimensioning Toolkit calculates the size of the UNDO log file for you.

In order to create a log file group with the specified values you then do:

CREATE LOGFILE GROUP lg ADD UNDOFILE 'undo1.dat'
INITIAL_SIZE=[4-6]*DataMemory (in MB) ##e.g DataMemory=1024M --> INITIAL_SIZE=6144M
UNDO_BUFFER_SIZE=32M
ENGINE=NDB;

For MySQL Cluster 6.2, it should be 6xDataMemory. For MySQL Cluster 6.3 it should be 4xDataMemory (this because 6.3 writes only two LCPs)



TABLESPACE
Next thing is to create a table space. A table space is a collection of one or more data files.
Benchmarks indicates it is better to have many smaller data files than one giant data file. This has to do with how the data nodes are handling open files (one thread for each open file). More files --> more threads...

Again, the Dimensioning Toolkit will calculate the total size you need for the table space, but you can later on add more data files online if you wish.

If you want to create a table space that is 1GB in size I would do like:

CREATE TABLESPACE ts_1
ADD DATAFILE 'data1.dat'
USE LOGFILE GROUP lg
INITIAL_SIZE=128M ##one data (data1.dat) file with size = 128M
ENGINE=NDB;

Then add more data files:

ALTER TABLESPACE ts_1 ADD DATAFILE 'data2.dat' INITIAL_SIZE=128M ENGINE=NDB;
...
ALTER TABLESPACE ts_1 ADD DATAFILE 'data10.dat' INITIAL_SIZE=128M ENGINE=NDB;


I intentionally over-allocated the size of the table space (10 x 128M).
Moreover, you can have one log file group, but one log file group can contain many undo files, and you can have many table spaces.

Recommendations
My recommendations are:

• Use the Configuration Tool to generate a good config.ini. Even though you don't want to use the scripts, you can still use the config.ini. The config.ini it generates is state of the art and a great boiler plate!
  
• Use the Dimensioning Toolkit to scope out how much RAM, Disk etc you need.
  
• Use the resulting DataMemory/IndexMemory etc you got from the Dimensioning Toolkit in the Configuration Tool to nail a configuration that suits you.
  

Good luck!