0

I have a problem with MySQL Cluster.

I created a table in order to be stored in the disk but i think it's always stored in memory.

I have have created a table with the query below:

   CREATE TABLE IF NOT EXISTS user (
  `FIELD_KEY` varchar(255) NOT NULL,
  `FIELD0` text,
  `FIELD1` text,
  `FIELD2` text,
  `FIELD3` text,
  `FIELD4` text,
  `FIELD5` text,
  `FIELD6` text,
  `FIELD7` text,
  `FIELD8` text,
  `FIELD9` text,
  PRIMARY KEY (FIELD_KEY)
) ENGINE=ndbcluster DEFAULT CHARSET=utf8 max_rows=40000000 
TABLESPACE ts_1 STORAGE DISK;


ALTER TABLE usertable4M PARTITION BY KEY() PARTITIONS 4

My Environment (3 nodes in the same VM):

MySQL Cluster 7.5

  • management nodes: 1
  • data node: 1
  • sql nodes: 1

VM Config : 16GB RAM, 100GB HDD

MySQL Cluster settings (config.ini) are set to:

[ndbd default]
noofreplicas=1
DataMemory=2048M
# Memory to allocate for index storage
IndexMemory=1024M
# To support multi-thread processors
MaxNoOfExecutionThreads=2
FragmentLogFileSize=256M
NoOfFragmentLogFiles=12
#-------
RedoBuffer=32M
SharedGlobalMemory=256M
DiskPageBufferMemory=4096M
NoOfFragmentLogParts = 4
#-------
[ndb_mgmd]
HostName=192.168.1.10
DataDir=/var/lib/mysql-cluster

[ndbd]
HostName=192.168.1.10
NodeId=2
DataDir=/usr/local/mysql/data

[mysqld]
HostName=192.168.1.10

In order to store data in Disk i created a LOGFILE and TABLESPACE with the queries below:

CREATE LOGFILE GROUP lg ADD UNDOFILE 'undo1.dat'
INITIAL_SIZE=12288M 
UNDO_BUFFER_SIZE=32M
ENGINE=NDB;

CREATE TABLESPACE ts_1
ADD DATAFILE 'data1.dat'
USE LOGFILE GROUP lg
INITIAL_SIZE=256M
ENGINE=NDB;

ALTER TABLESPACE ts_1 ADD DATAFILE 'data2.dat' INITIAL_SIZE=256M ENGINE=NDB;
.
.
.
ALTER TABLESPACE ts_1 ADD DATAFILE 'data24.dat' INITIAL_SIZE=256M ENGINE=NDB;

MemoryUsage report

$ sudo ndb_mgm -e 'all report MemoryUsage'
Connected to Management Server at: 192.168.1.10:1186
Node 2: Data usage is 94%(62259 32K pages of total 65536)
Node 2: Index usage is 2%(2801 8K pages of total 131104)

When I ran a query to insert 4000000 records (4GB) to my table I get the error (The table 'user' is full) and can not insert data any more to the table and only 1611787 records has been inserted . I don't understand why it is so.

Could somebody explain me what is the situation. And how can I resolve the problem.

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.