Thursday, April 24, 2008

A great config.ini

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.

2 comments:

Nguyen said...

Hi,

I have managed to setup a Cluster with 1 Management. 2 Data Nodes and 2 mySQL Nodes. It worked. However I ran into problem with table full and I have edited config.ini file as below to increase parameters and restarted all, but non of the table-related things affacted. For example MaxNoOfOrderedIndexes=10000 but at Data Node after restarted even with --initial, command ndb_config -q MaxNoOfOrderedIndexes still show "128 128"

Mine: Nodes 128G RAM/each. mySQL 64G RAM/each same for Management node.

Can you hepl please. Thanks.
---------------------------

[ndbd default]
NoOfReplicas=2
MaxNoOfTables=10000
MaxNoOfAttributes=24756
MaxNoOfConcurrentOperations=10000
# MaxNoOfLocalOperations=2400000000
# MaxNoOfConcurrentTransactions=2000000000
MaxNoOfOrderedIndexes=10000
MaxNoOfUniqueHashIndexes=10000

DataMemory=96000M
# Memory to allocate for index storage
IndexMemory=10240M
# Lock table in memory to increase speed
LockPagesInMainMemory=1
# Use O_DIRECT write
ODirect=1
FragmentLogFileSize=256M
# 6*DataMemory/4/FragmentLogFileSize
NoOfFragmentLogFiles=560
RedoBuffer=32M

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

Johan Andersson said...

Hi,
You need to restart the management server also:

1) stop the management server
2) start the management server, but add the --reload option
Then restart each data node one by one.

BR
johan