Good configuration

Tweet I get the opportunity see a lot of different MySQL Cluster configurations and most of them does not configure the disk subsystem in a good way. E.g, the redo log is way to often not correctly dimensioned. Wrongly dimensioned buffers affects system stability!!

There are also some buffers related to communication and disk, that is important to setup. At the end here you will find a good configuration template that you can use, but first we will have a brief discussion about the disk


Configuring the disk

In MySQL Cluster, it is very important to dimension the redo log properly in order for System restart and Node restarts to work. In MySQL Cluster, the redo log must be large enough to accomodate changes that happens over three Local Checkpoints (LCP). When the third LCP has been written, then the redo log can be recycled. The LCP is the image of the DataMemory which is written to disk, so if you have filled up 8GB of DataMemory, then one LCP will take approx. 8GB of disk space. Writing the LCP takes time, and in 5.1 it is written with a speed determined by DiskCheckpointSpeed. DiskCheckpointSpeed is 10MB/s by default writing an LCP of 8GB will then take 8192MB/10MB/s = ~820seconds. MySQL Cluster wiill write three LCPs, and clearly we need to have a REDO log big enough to handle changes that happens over three LCPs, which is then 2460 seconds. However, we should have some head room in the redo log, so we pretend that we should save the redo log for four LCPs. That is 3280 seconds of changes!

Pretend that you write 1MB/s to a two node, two replica cluster (remember, that provisioning or restore data is often the heaviest write phase in a database). Then the size of the REDO log should be atleast:

Size of redo log = 1MB/s * 3280seconds = 3300MB

Moreover, the redo log file is segmented over a number of 64MB large segments on disk. How many segments you have is set by NoOfFragmentLogFiles! So the NoOfFragmentLogFiles determines the size of the REDO log.

NoOfFragmentLogFiles=3300MB/64MB=52

But, way to often the NoOfFragmentLogFiles is left to the default. Not good, and system stability is at stake.

A heuristic can also be used to determine the size of the redo log, if you don't have all data, or if you have ample of free disk space:

NoOfFragmentLogFiles= DataMemory (in MB) * 6 / 64 MB

If you have DataMemory=8G and want to use the heuristic, then:
NoOfFragmentLogFiles= 8192 * 6 / 64 MB = 768
are needed!

RedoBuffer, which sits in front of the disk should also be increased if you have the resources (highly recommended) from its default value to:
RedoBuffer=32M

Configuration template
Most things can be left with default values, but these things are worth considering:


[tcp default]
SendBufferMemory=2M

[ndbd default]
##avoid swapping, not good at all
LockPagesInMainMemory=1

## Very recommended to increase RedoBuffer:
RedoBuffer=32M


## Set the correct size of the redo log (heuristic):
NoOfFragmentLogFiles= 6 * / 64MB



In the my.cnf file, set:

[mysqld]
...
ndb-force-send=1
ndb-use-exact-count=0
engine-condition-pushdown=1
...

What's next?

If you have any questions, are interested in more info, please contact me at <> and I will try to address your inquiries.