I was recently involved with a client that has to deal with a very large dataset in MySQL NDB cluster. The schema was very simple, a few (one or two) key columns and a large varbinary column. The cluster was large with 14 data nodes having each close to 60 GB devoted to DataMemory (DM) for a total combine size of
The main problem we had with the cluster during this engagement was the lack of disk space and the main cause of this, was the Redo log files. The cluster has been configured according to Johan best practices of 6xDM (http://johanandersson.blogspot.com/2008/04/great-configini.htm) forFragmentLogFileSize and NoOfFragmentLogFiles and, adding the 2 Local CheckPoint (LCP), this mean 8xDM or 480 GB, hard to fit on a pair or 146 GB drives. The purpose for 6xDM is to make sure write transaction are never rejected by the cluster because of a lack of redo log space. The redo log space basically hold changes to the NDB database between 2 LCP. LCP are memory dumps of the DM. In restore mode, after a cluster shutdown, the most recent LCP is read from disk and missing entries from the redo log applied in order for the cluster to recovery to the most recent state possible. By default, redo log space can be reused only after 3 LCP so setting the total size of the redo log to 6xDM insure that you never run out of redo log space.
This is very important for interactive write access but what if the cluster is used for read only operations and is batch updated once in a while? In such cases, where you have no SLA on the updates, you can use much smaller values for FragmentLogFileSize and NoOfFragmentLogFiles leading 1xDM or even less. The only important aspect will be a proper handling of the error 410 “Out of log file space temporarily” by the batch update process. When the error is catched, the process needs to sleep for a few seconds and retry the rejected transaction. Once the ongoing LCP completes, part of the redo log space will be made available and the transaction will succeed.