2

We are currently create a Mysql Cluster with ndb distribution storage. We have a very large set of data so we want to config the ndb using the tablespace to store data in disk. I follow this link to config the tablespace and I check the ndb instance and I can see the data file is created.

https://dev.mysql.com/doc/refman/5.1/en/mysql-cluster-disk-data-objects.html

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

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

ALTER LOGFILE GROUP lg_1
    ADD UNDOFILE 'undo_3.log'
    INITIAL_SIZE 1M
    ENGINE NDBCLUSTER;

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

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

ALTER TABLESPACE ts_1
    ADD DATAFILE 'data_3.dat'
    INITIAL_SIZE 5G
    ENGINE NDBCLUSTER;

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

ALTER TABLESPACE ts_1
    ADD DATAFILE 'data_5.dat'
    INITIAL_SIZE 5G
    ENGINE NDBCLUSTER;

ALTER TABLESPACE ts_1
    ADD DATAFILE 'data_6.dat'
    INITIAL_SIZE 5G
    ENGINE NDBCLUSTER;

ALTER TABLESPACE ts_1
    ADD DATAFILE 'data_7.dat'
    INITIAL_SIZE 5G
    ENGINE NDBCLUSTER;

ALTER TABLESPACE ts_1
    ADD DATAFILE 'data_8.dat'
    INITIAL_SIZE 5G
    ENGINE NDBCLUSTER;

Then I create all table and I alter them to use the tablespace.

alter table AggregateNodeData TABLESPACE ts_1 STORAGE DISK;
alter table AlertEvent TABLESPACE ts_1 STORAGE DISK;
alter table AlertProfile TABLESPACE ts_1 STORAGE DISK;
alter table AreaType TABLESPACE ts_1 STORAGE DISK;
alter table BatteryReading TABLESPACE ts_1 STORAGE DISK;
alter table Building TABLESPACE ts_1 STORAGE DISK;
alter table CalculationRule TABLESPACE ts_1 STORAGE DISK;
alter table City TABLESPACE ts_1 STORAGE DISK;
alter table Country TABLESPACE ts_1 STORAGE DISK;
alter table Gateway TABLESPACE ts_1 STORAGE DISK;
alter table GatewayMapping TABLESPACE ts_1 STORAGE DISK;
alter table HistoryChange TABLESPACE ts_1 STORAGE DISK;
alter table HistoryRecord TABLESPACE ts_1 STORAGE DISK;
alter table HistoryRecordDetails TABLESPACE ts_1 STORAGE DISK;
alter table Log TABLESPACE ts_1 STORAGE DISK;
alter table LogDetails TABLESPACE ts_1 STORAGE DISK;
alter table Node TABLESPACE ts_1 STORAGE DISK;
alter table NodeData TABLESPACE ts_1 STORAGE DISK;
alter table Notification TABLESPACE ts_1 STORAGE DISK;
alter table NotificationTimetable TABLESPACE ts_1 STORAGE DISK;
alter table Organization TABLESPACE ts_1 STORAGE DISK;
alter table Point TABLESPACE ts_1 STORAGE DISK;
alter table PointAlertProfile TABLESPACE ts_1 STORAGE DISK;
alter table PointAlertStatus TABLESPACE ts_1 STORAGE DISK;
alter table PointData TABLESPACE ts_1 STORAGE DISK;
alter table PointSensor TABLESPACE ts_1 STORAGE DISK;
alter table PointType TABLESPACE ts_1 STORAGE DISK;
alter table RawData TABLESPACE ts_1 STORAGE DISK;
alter table Role TABLESPACE ts_1 STORAGE DISK;
alter table RoleFunction TABLESPACE ts_1 STORAGE DISK;
alter table RoleFunctionMapping TABLESPACE ts_1 STORAGE DISK;
alter table SendingTo TABLESPACE ts_1 STORAGE DISK;
alter table Sensor TABLESPACE ts_1 STORAGE DISK;
alter table SensorData TABLESPACE ts_1 STORAGE DISK;
alter table SensorType TABLESPACE ts_1 STORAGE DISK;
alter table SetupRequest TABLESPACE ts_1 STORAGE DISK;
alter table Site TABLESPACE ts_1 STORAGE DISK;
alter table SitePermit TABLESPACE ts_1 STORAGE DISK;
alter table User TABLESPACE ts_1 STORAGE DISK;
alter table UserDevice TABLESPACE ts_1 STORAGE DISK;
alter table UserRole TABLESPACE ts_1 STORAGE DISK;

Then I run the application overnight to see how the data stack.

I run the following query to see how is the table usage.

    SELECT sum(round(((data_length + index_length) / 1024 / 1024), 2)) `Size in MB` 
FROM information_schema.TABLES 
ORDER BY (data_length + index_length) DESC;

It show is 1579MB

Then I calculate the In-memory usage of the data node.

In ndb_mgm, i use all report mem

Node 11: Data usage is 41%(31826 32K pages of total 76800)
Node 11: Index usage is 47%(36148 8K pages of total 76832)
Node 12: Data usage is 41%(31822 32K pages of total 76800)
Node 12: Index usage is 47%(36123 8K pages of total 76832)

31826 * 32 /1024 + 31822 * 32 / 1024 = 1986MB

Seem the data is not storing in the tablespace. Do anyone know? Thanks.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.