Table full errors in populating a table with disk columns

1

Created a table with columns other than primary key configured to be stored in disk.

CREATE LOGFILE GROUP lg_1
    ADD UNDOFILE 'undo_1.log'
    INITIAL_SIZE 128M
    UNDO_BUFFER_SIZE 16M
    ENGINE NDBCLUSTER;

ALTER LOGFILE GROUP lg_1
    ADD UNDOFILE 'undo_2.log'
    INITIAL_SIZE 128M
    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;

According to MySQL NDB docs, default initial size of UNDOFILE and DATAFILE is 128M. Is that applicable in RonDB as well?

Table definition is

CREATE TABLE TEST_TABLE_DISK (ID INT PRIMARY KEY, COLUMN_1 INT, COLUMN_2 DOUBLE, COLUMN_3 BIGINT, COLUMN_4 VARCHAR(255), COLUMN_5 TIMESTAMP, COLUMN_6 DECIMAL(10,2), COLUMN_7 TINYINT, COLUMN_8 VARCHAR(255), COLUMN_9 INT, COLUMN_10 DOUBLE, 
COLUMN_11 INT, COLUMN_12 DOUBLE, COLUMN_13 BIGINT, COLUMN_14 VARCHAR(255), COLUMN_15 TIMESTAMP, COLUMN_16 DECIMAL(10,2), COLUMN_17 TINYINT, COLUMN_18 VARCHAR(255), COLUMN_19 INT, COLUMN_20 DOUBLE,
COLUMN_21 INT, COLUMN_22 DOUBLE, COLUMN_23 BIGINT, COLUMN_24 VARCHAR(255), COLUMN_25 TIMESTAMP, COLUMN_26 DECIMAL(10,2), COLUMN_27 TINYINT, COLUMN_28 VARCHAR(255), COLUMN_29 INT, COLUMN_30 DOUBLE
) TABLESPACE ts_1 STORAGE DISK ENGINE NDBCLUSTER;

Here first 10 columns after primary key are repeated. We observed that we were unable to create a table more than 38 columns (excluding primary key). Whereas we could create a table with 100 such columns without using disk storage clause.

We started to populate some random data to above table using a stored procedure (inserting data in batch of 1000 rows) and observed that unable to insert over 38000 rows. Post that it gave ERROR 1114 (HY000): The table 'TEST_TABLE_DISK' is full.

Are we missing something here? Should we allocate a bigger size than 128M to UNDOFILE and DATAFILE ?

2

The numbers look reasonable. Most of the space is consumed by the 6 VARCHAR(255) columns. RonDB defaults to using UTF-8 for VARCHAR, this requires 4 bytes per character. So each of those columns use 1 kBytes of space.

There is a limit on the row size for disk columns, the total row size can at most be 30000 bytes. Disk data columns are currently stored as fixed size columns, this is limited to 8052 bytes. So this is the limit that you see. One can define one very large column beyond that, don’t exactly recall from the top of my head the rules for that.

Default size of UNDOFILE and DATAFILE is the same in RonDB as in NDB.

You can add more tablespace files as online operations and as well for the UNDO logs.

I have tested adding tablespace files up to 30+ TBytes per data node.

I would recommend using batches of around 100-200 rather than 1000 for best performance.

3

Okay, so to store more number of rows, I need to increase tablespace size right?

4

Yep, correct, 256MByte isn’t that much of space.