Max DATAFILE size in NDB engine

Question

My database setup is using NDB cluster with 2-3 nodes, on CentOS. While trying to test out the performance, i tried to store ~7mil records (around 2GB size) into one of the table. at around ~200k, it failed with notification:

ERROR 1114 (HY000): The table 'my_request' is full

These are the LOGFILE GROUP and TABLESPACE:

CREATE LOGFILE GROUP lg_1
    ADD UNDOFILE 'undo_1.log'
    ENGINE NDBCLUSTER;

ALTER LOGFILE GROUP lg_1
    ADD UNDOFILE 'undo_2.log'
    ENGINE NDBCLUSTER;

CREATE TABLESPACE ts_1
    ADD DATAFILE 'data_1.dat'
    USE LOGFILE GROUP lg_1
    ENGINE NDBCLUSTER;

ALTER TABLESPACE ts_1
    ADD DATAFILE 'data_2.dat'
    ENGINE NDBCLUSTER;

Suspecting the problem lay in DATAFILE, i added 2 more into it: data_3.dat and data_4.dat, which solve the problem.

So according to this page, AUTOEXTEND_SIZE and MAX_SIZE are ignored in my current version. That table is expected to hold 700GB+ data before getting dumped into disk after pre-determined time. Therefore, Are there any way to setup the size of this DATADISK to calculate the future usage of it? Where is this data stored? (I tried to check in datadir, exec find, but found nothing). Would be really grateful if you can share some good information/strategy which explain how to work with NDB.

Note: Not an expert in NDB, please assume i know nothing when explaining. big ty.

Answer 1

To get undo/data file size:

SELECT TABLESPACE_NAME, LOGFILE_GROUP_NAME, FILE_NAME, FILE_TYPE, EXTRA, 
INITIAL_SIZE/1000000 size_init_mb, 
MAXIMUM_SIZE/1000000 size_max_mb, 
TOTAL_EXTENTS - FREE_EXTENTS AS extents_used, 
(TOTAL_EXTENTS - FREE_EXTENTS) * EXTENT_SIZE/1000000 AS mb_used, 
FREE_EXTENTS * EXTENT_SIZE/1000000 AS mb_free
FROM INFORMATION_SCHEMA.FILES WHERE EXTRA IS NOT NULL;

To change MAX_SIZE of datafile, all it need is to set the INITIAL_SIZE

ALTER TABLESPACE ts_1
ADD DATAFILE 'data_4.dat'
INITIAL_SIZE 1G
ENGINE=NDBCLUSTER;

Even though the documentations are pointing that this datafile can be located by checking the datadir in config, but somehow i can't find it in my system (by default it is at /var/lib/mysql/).

Answer 2

Here is a cut from the manual:

The optional INITIAL_SIZE parameter sets the UNDO file's initial size; if not specified, it defaults to 128M (128 megabytes). The optional UNDO_BUFFER_SIZE parameter sets the size used by the UNDO buffer for the log file group; The default value for UNDO_BUFFER_SIZE is 8M (eight megabytes); this value cannot exceed the amount of system memory available. Both of these parameters are specified in bytes. You may optionally follow either or both of these with a one-letter abbreviation for an order of magnitude, similar to those used in my.cnf. Generally, this is one of the letters M (for megabytes) or G (for gigabytes).

So size of UNDO buffer is 8M by default and size of UNDO log is 128M by default. It is possible to extend the size of the UNDO log by adding more UNDO log files.

Similarly for DATAFILE the size of the default data file size is 128 MB. And as specified in the earlier answer one sets the size of the data file by setting INITIAL_SIZE. It is possible to extend tablespace size by adding more data files.

A data file also needs a setting of EXTENT_SIZE (default 1MByte). This specifies how much space is allocated when a table fragment needs to allocate disk space in the tablespace.