Disk data - a summary

Tweet Here is a summary about Disk Data tables in MySQL Cluster.

Data node configuration:

• SharedGlobalMemory=384M
  RAM from the shared global memory is used for the UNDO_BUFFER when you create the log file group.
  In the configuration generated by severalnines.com/config then you have to uncomment the SharedGlobalMemory in mysqlcluster-XYZ/cluster/config/config.ini before you start the cluster.
  
• DiskPageBufferMemory=3072MB
  If you are relying a lot on the disk data, we recommend to set this to as much as possible.
  In the configuration generated by severalnines.com/config then you have to uncomment the DiskPageBufferMemory in mysqlcluster-63/cluster/config/config.ini before you start the cluster.
  The DiskPageBufferMemory should be set to:
  DiskPageBufferMemory=TOTAL_RAM - 1GB (OS) - 1200MB (approx size used for buffers etc in the data nodes) - DataMemory - IndexMemory
  Expect to do some trial and terror before getting this correct.
  

Creating the storage:

• LOGFILE GROUP
CREATE LOGFILE GROUP lg ADD UNDOFILE 'undo1.dat' INITIAL_SIZE=20G UNDO_BUFFER_SIZE=128M ENGINE=NDB;
  You can only have one LOGFILE GROUP. 20GB undo log is just an example, but don't be shy with it if you have space. Theoretically, you could need as much UNDO as you have TABLE SPACE size. If you have 10GB table space, then start with an UNDO log that is 10% of that. Add more UNDO LOG FILES if you need, i.e, run into problems. UNDO_BUFFER_SIZE=128M. should be sufficient for most applications (but you must have set SharedMemoryGlobal=384M). This buffer cannot grow dynamically or be extended later :(
• UNDO LOG FILES
  You can add more undo files (online) by doing:
  ALTER LOGFILE GROUP lg ADD UNDOFILE 'undo_2.dat' INITIAL_SIZE 256M ENGINE NDB;
  
• TABLESPACE
  CREATE TABLESPACE ts_1 ADD DATAFILE 'data1.dat' USE LOGFILE GROUP lg INITIAL_SIZE=256M ENGINE=NDB;
• DATAFILES
  In 7.0 you can use DiskIOThreadPool which makes it possible to have X threads operating on one file, so you don't have to create many data files. Of course for some applications it may be good to have the more files if you want to spread out over several physical disks.
  In 6.3 it is recommended to have many but smaller data files. Here is a script that generates SQL (ALTER TABLE ADD ... ) to create number of datafiles. Run it as:
  sh ts-gen.sh 100 > alter_add_ts.sql
  and you will get a file with 99 SQL statements to add table spaces.
  From the mysql client you can then do:
  mysql> source alter_add_ts.sql
  and a lot of datafiles will be added to the ts_1 tablespace.
  Please note that you can change filesize and path if you wish.
  
• Having more than 50-60 data files does not really give anything. The reason for having several data files instead of one is that more internal file system threads can operate in "parallel" on the data files, instead of having to be synchronized on one.
  
• You grow the table space by adding data files.
  
• Adding data files is an online operation if you wish to extend your table space.
• There is no auto-extend yet of table spaces.
  
• You can create many tablespaces, each having many data files.
• Leave EXTENT_SIZE as it is. We have found no reason yet to change it.
• Example CREATE TABLE:
  CREATE TABLE t1 ( a INTEGER PRIMARY KEY, b VARBINARY(2048)) engine=ndb TABLESPACE ts_1 STORAGE DISK;
  The column 'b' will be stored on disk. The column 'a' will be stored in DataMemory together with the Ordered Index on 'a' (which comes from the PRIMARY KEY). The PRIMARY KEY hash index on 'a' will use IndexMemory.
  
  

Disk space and disk utilization:

• Indexed attributes are stored in RAM (DataMemory). However, you can specify for the non-indexed if they should be in RAM or DISK when you create the table (see ref manual, about column definition STORAGE DISK or STORAGE MEMORY).
  
• In MySQL Cluster 6.2 , 6.3, and 7.0 disk data attributes are always stored as fixed size (e.g a VARCHAR(256) will always use 256B on disk, even if it is NULL - this will change in 7.1, hopefully).
  
• BLOB/TEXTs are special and stored in segments. The first 256B will be stored in RAM, the rest will be stored in segments (depending if it is a MEDIUMBLOB/BLOB/LONGBLOB then the segment size varies (from 2048B up to 8192B iirc).
• NULLs stored in a BLOB/TEXT will occupy 0B on disk, but 2B overhead of DataMemory.
  
• The data files are divided into extents. Each extent is 1024K (1MB). There is no reason to change the extent size. Many records may reside in one extent.
• If you write something to a disk data table, then the data will be written in as many table spaces as you have NoOfReplicas set to. Thus, typically you will have two copies of the data (one on each node in a node group).
  
• The information_schema contains information how much of the table space and undo files that are used. E.g
  

  select free_extents, total_extents from information_schema.files where file_type='datafile'
  
  

Short on disk data internals :

Thanks jonas for your input on this!

• The DiskPageBufferMemory is a LIRS cache (variant of the LRU cache)
  
• Dirty pages are flushed to the table space during an LCP (local checkpoint), but also if they have been inactive for some time (i have no info yet on the inactive time)
  
• Changes to Disk data is Redo logged (same ways as with in-memory tables).
• Changes to Disk data is written to a circular UNDO log (using WAL). This is so that we can undo all the way back to the start of the last completed LCP. This way, we can get to a state where memory and disk are in sync (i.e. references between disk data and in-memory storage are correct) before we start running the REDO log.
• If a record exists in the DiskPageBufferMemory, it will be returned from there to the application.
• If a record does not exists in the DiskPageBufferMemory, it will have to be fetched from disk. In case there are no empty pages in the DiskPageBufferMemory, the LRU algorithm will sync dirty pages to disk in order to free up empty pages.
  

Performance

• Disk data counters
• Disk data counters (more)
  

What to use disk data for?:

• Storing e.g inactive subscribers in a subscriber database (when activated they you can move them into RAM). If an activated user has been inactive for too long it can be moved back to the disk data storage. This is something you have to write in the application logic.
  
• Storing e.g SMS/MMS
  
• Logging data
• Large feeds coming in that you need to persist, but not necessarily access in real-time (i.e<3ms).>
• Probably more..
  

Suitable access patterns:

• You should not use data stored in disk data tables to realtime applications. If you do random io, then you applications will be as fast as the disk.
• Use simple access patterns - avoid joins on disk data. See it as an extension to RAM where you store data that you don't have to have that frequent access to.
• If you want frequent access to the data you should have a BIG DiskPageBufferMemory (like in innodb, the innodb_buffer_pool is very important to performance). It is the same here.
  
• Range searches will be expensive as disk data is not stored according to the order of a particular index. It is more or less random where data is stored. I will get back on numbers of this.
  
• If you use SSD for disk data, then the above might be more or less true. Disk data has not been tested with SSDs yet.

Caveats:

• Check my earlier blog post for caveats regarding deallocation/allocation of extents.
  
• If you restart the cluster with --intial you have to manually delete all the data files and undo log files that was created. These are not cleared even if you start the cluster with --initial.