MySQL Cluster: Disk Data Config

Tweet

If you are using the Disk Data feature of MySQL Cluster you may find this useful. As you may know the default way of storing data in MySQL Cluster is in-memory. However, if you have tables that don't require realtime access and/or are growing larger than your available memory then you can use disk data tables. I have written a bunch of posts about disk data tables before:

• http://johanandersson.blogspot.se/2008/06/disk-data-tables.html
• http://johanandersson.blogspot.se/2008/11/disk-data-extent-deallocation-caveats.html
• http://johanandersson.blogspot.se/2008/12/disk-data-summary.html
• http://johanandersson.blogspot.se/2010/11/diskpagebuffermemory-tuning-and-disk.html

However, it is quite hard to configure Disk Data tables correctly. There are some parameters to keep an eye on:

• UNDO LOG - MySQL Cluster undo logs every write access to the disk data tables. You can grow this resource online (ALTER .. ADD ..)
• REDO LOG - as for in-memory tables  every write is REDO logged. You can grow the REDO log with a  Rolling restart (NoOfFragmentLogFiles). The REDO LOG is shared between the disk data tables and the in-memory tables.
• REDO BUFFER - specified as RedoBuffer=32M or higher in config.ini.  The REDO BUFFER is shared between the disk data tables and the in-memory tables.
• UNDO BUFFER -  created from SharedGlobalMemory resource and is set at LOGFILE GROUP creation. WARNING!! Can not be changed at runtime! The LOGFILE GROUP must be recreated, and it requires that the table space (and all its data files) is dropped before.
• TABLESPACE - can be extended at runtime.  ALTER .. ADD..
• DiskPageBufferMemory - like the innodb_buffer_pool. The bigger the better. Can be tuned with a Rolling Restart.

For stability the most common mistakes are to set UNDO LOG and UNDO BUFFER too small.

What can happen then?

Stability and Recovery

If UNDO BUFFER is too small symptoms are:

• Failed writes to the Disk Data tables. 
• In worst case you can hit crashing bugs.

If UNDO LOG is too small symptoms are:

• Failed writes to the Disk Data tables. In worst case you can hit crashing bugs.
•  Node recovery may not be possible - the recovering node will crash.
  Severalnines has assisted a number of client to perform  a number of recovery when this has been the case.
• If there is a Cluster crash due to a too small UNDO LOG you will most likely have to restore from backup.

How can it be possible for MySQL Cluster to fail to recover the node, when it was possible to load it with data?

The problem is that your write pattern when writing data at runtime into MySQL Cluster is different from the write pattern at recovery.

At recovery the recovering data node will copy data (over the network) from the other node in the node group. It will do this as fast as it can, table by table.

Thus, it will be a lot of data coming into the recovering data node (like a bulk write) and in this case then the UNDO LOG may be too small to handle the write log (you can argue why UNDO and REDO log while doing recovery but but..). If the UNDO LOG is over run, then the node will crash.

Configuration

As usual this depends a lot  on the access patterns you have so it is hard to give exact numbers but if you rely on disk data tables you should have:

• [NDBD DEFAULT] SharedGlobalMemory=1024M
  Disk operation records and other meta data is allocated from this buffer. The more records you have in the disk data tables the bigger this buffer must be. 1024M is a good start.
• [NDBD DEFAULT] DiskPageBufferMemory=4096M
  As large as you can (remember that in-memory tables, and for disk data tables, indexed attributes resides in DataMemory so you need to have enough DataMemory.
  The important when tuning the DiskPageBufferMemory is to get a good hit ratio. If you use ClusterControl you will see the hit ratio (picture coming soon).
  4096M is a good start.
• UNDO_BUFFER_SIZE=32M  or 64M (same size as you have for the RedoBuffer)
  This is specified in the CREATE LOGFILE GROUP ... 
• UNDO LOG space should be 0.5 x NoOfFragmentLogFileSize x 4 x FragmentLogFileSize (this may take it to the extreme but better safe than sorry).
• [NDBD DEFAULT]  DiskIoThreadPool=8    (2 is default)   
• UndoIndexBuffer/UndoDataBuffer  - those parameters are deprecated but still in the manual (I have grepped the 7.2.4 source and find no trace of it. It was a long time those were used. When LCP was done different in Cluster. So no need to set these.

Please not that you can always add more UNDO LOG files online by issuing

 ALTER LOGFILE GROUP  ADD UNDOFILE 'undo_X.dat INITIAL_SIZE=1024M ENGINE=NDBCLUSTER;

However, if the Cluster crashes, then it is too late.

Disk Layout

• Put the Redo log + LCP  on separate disk spindles ( A)
• Put the Undo log on separate disk spindles  (FileSystemPathUndoFiles) (B)
• Put the Data files (table space) on separate disk spindles (FileSystemPathDataFiles) (C)

In many cases you can combine B and C on the same disks, and if you have a good disk array, you can of course combine A,B and C. 

Bottom line is, watch your disks and utilized they are. That will determine if you have to make disk reconfigurations. Also, beware of trial and error before getting the DiskPageBufferMemory and SharedGlobalMemory right.