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.

abouyahya85
  • 115
  • 9

1 Answers1

0

"In a Disk Data table, the first 256 bytes of a TEXT or BLOB column are stored in memory; only the remainder is stored on disk."

You can check this in detail.

Jinho Choi
  • 11
  • 1