A great config.ini

Tweet After having to the UC and seen some of the talks with people using MySQL Cluster it seems that it is time to hand out a good configuration template.

Unfortunately, configuring cluster is too difficult and a lot of things could be done to ease the pain. One of the problematic things customers and users always forget is to dimension the redo log properly.

However, the template below allows you to:

• Load any mysqldump i have seen to date (if you have enough DataMemory/IndexMemory to support the data set).
• ndb_restore on any data (I strongly recommend you run ndb_restore with a less degree of parallelism than what is defaut). This is regulated with the -p option. Run ndb_restore with ndb_restore -p 32 .... You may in some rare cases need to lower the degree of parallelism somewhat.
• Recover in case of node failure/cluster failure. Often the things preventing you from recovery is that you have a too short redo log. If you have a well-sized (6xDataMemory in order to be safe) one then it is likely that you have hit an actual bug rather than a configuration issue.
  

The below template is geared towards MySQL Cluster 6.2 (CGE6.2) and MySQL Cluster 6.3 (CGE6.3).

If you are using 5.0 or regular 5.1 (i do recommend you upgrade to CGE6.x), then you might have to change some things and read an earlier post on how to get going with it. The template only shows the "default" sections, so you need to add your actual [ndb_mgmd] , [ndbd] and [mysqld] processes to it. Moreover, the template is not taken into account disk data tables and configuration for those.

REMEMBER: If you change the DataMemory (in the config template below it is 2048M), then you must update the NoOfFragmentLogfiles (why this is not handled automatically inside cluster is a valid question...) with the correct value according to the formula below! Also, don't forget to update the IndexMemory as well.

This configuration will require (not including the IndexMemory or DataMemory) about 900MB of RAM for each data node, but is very solid. If you run into a problem now, then it is a bug, and not a configuration issue. Good luck.


[tcp default]
SendBufferMemory=2M
ReceiveBufferMemory=2M

[ndb_mgmd default]
datadir=X


[ndbd default]
#redundancy:
NoOfReplicas=2
#avoid swapping:
LockPagesInMainMemory=1

#Bypass FS cache (you should test if this works for you or not)
Odirect=1

#DataMemory (memory for records and ordered indexes)
DataMemory=2048M
#IndexMemory (memory for Primary key hash index and unique hash index)
#Usually between 1/6 or 1/8 of the DataMemory is enough, but depends on the
#number of unique hash indexes (UNIQUE in table def)
IndexMemory=256M

#Redolog
#size of each redo log fragment, 4 redo log fragment makes up on fragment log file.
# A bigger Fragment log file size thatn the default 16M works better with high write load
# and is strongly recommended!!
FragmentLogFileSize=256M

#Set NoOfFragmentLogFiles to 6xDataMemory [in MB]/(4 *FragmentLogFileSize [in MB]
#Thus, NoOfFragmentLogFiles=6*2048/1024=12
#The "6xDataMemory" is a good heuristic and is STRONGLY recommended.
NoOfFragmentLogFiles=12

#RedoBuffer of 32M should let you restore/provisiong quite a lot of data in parallel.
#If you still have problems ("out of redobuffer"), then you probably have to slow disks and
#increasing this will not help, but only postpone the inevitable.
RedoBuffer=32M

#table related things
MaxNoOfTables=4096
MaxNoOfAttributes=24756
MaxNoOfOrderedIndexes=2048
MaxNoOfUniqueHashIndexes=512

#Operation records
#MaxNoOfConcurrentOperations=100000 means that you can load any mysqldump file into cluster.
MaxNoOfConcurrentOperations=100000

#Checkpointing...
Diskcheckpointspeed=10M
Diskcheckpointspeedinrestart=100M
TimeBetweenGlobalCheckpoints=1000
#the default value for TimeBetweenLocalCheckpoints is very good
TimeBetweenLocalCheckpoints=20

#Realtime extensions (only in MySQL Cluster 6.3 (CGE 6.3) , read this how to use this)
#SchedulerSpinTimer=400
#SchedulerExecutionTimer=100
#RealTimeScheduler=1
#LockMaintThreadsToCPU=[cpuid]
#LockExecuteThreadToCPU=[cpuid]

#If you use MySQL Cluster 6.3 (CGE 6.3) and are tight on disk space, e.g ATCA.
#You should also then lock cpu's to a particular core.
#CompressedLCP=1
#CompressedBackup=1

datadir=X

[ndb_mgmd]
hostname=...

#second management server for redundancy
#[ndb_mgmd]
#hostname=...


[ndbd]
hostname=...

[ndbd]
hostname=...

[mysqld]

...

[mysqld]




Please note that this config.ini template is not for disk data tables, but I will update it soon with those configuration parameters.