Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am trying to do some tests on MySQL Cluster.
I am creating my database using following commands:

CREATE DATABASE test1;
use test1;
CREATE LOGFILE GROUP lg1
ADD UNDOFILE 'undofile.dat'
ENGINE = NDB;
CREATE TABLESPACE ts1
ADD DATAFILE 'datafile.dat'
USE LOGFILE GROUP lg1
ENGINE = NDB;
CREATE TABLE `dane` (
  `id` int(11) NOT NULL,
  `data` blob NOT NULL,
  PRIMARY KEY (`id`)
)
TABLESPACE ts1 STORAGE DISK
engine=ndb;

Then I populate table with some data and random blobs 50KB each. But I am getting following error:

mysql> INSERT INTO dane(id, data) VALUES(3790, randomBlob(51200));
ERROR 1114 (HY000): The table 'dane' is full
mysql> show warnings;
+---------+------+--------------------------------------------------------+
| Level   | Code | Message                                                |
+---------+------+--------------------------------------------------------+
| Warning | 1296 | Got error 1601 'Out extents, tablespace full' from NDB |
| Error   | 1114 | The table 'dane' is full                               |
+---------+------+--------------------------------------------------------+
2 rows in set (0,00 sec)

mysql> select count(*) from dane;
+----------+
| count(*) |
+----------+
|     2507 |
+----------+
1 row in set (0,03 sec)

It is not cluster memory usage problem:

ndb_mgm> ALL REPORT MEMORYUSAGE
Connected to Management Server at: localhost:1186
Node 3: Data usage is 6%(164 32K pages of total 2560)
Node 3: Index usage is 7%(178 8K pages of total 2432)

2500*50KB ~= 1GB
I have this error any time I am trying to insert more than 1GB of blobs.
How I can extend size of this tablespace?

share|improve this question

2 Answers 2

up vote 3 down vote accepted
+25

First 2500*50kB ~= 128MB. Then if you will check CREATE TABLESPACE syntax default INITIAL_SIZE for the datafile is 128MB!

So if you want to store more data you can either specify INITIAL_SIZE you need while creating tablespace or you can ALTER TABLESPACE ADD DATAFILE.

By specifying STORAGE DISK in CREATE TABLE statement you stated that you want on disk table not in memory. With such tables ALL REPORT MEMORYUSAGE shows nothing meaningful for that table.

If you want "classic" NDB table which is stored in memory just skip TABLESPACE ts1 STORAGE DISK modifiers and then ALL REPORT MEMORYUSAGE will show you how much memory is used for the data and indexes.

share|improve this answer
    
So tablespace is limited by initial size of file? Does is mean, that space on disk will be allocated for whole tablespace on creation time? (even if tablespace is empty) –  Michał Herman Oct 18 '13 at 7:24
1  
Yes all INITIAL_SIZE is allocated on tablespace creation. Later you can extend tablespace with ADD DATAFILE statement. AUTOEXTEND_SIZE for now is silently ignored. Once again - tablespaces are needed just for on disk tables. In memory tables do not need tablespaces at all. –  Mindaugas Riauba Oct 18 '13 at 7:30

The keyword INITIAL_SIZE is misleading for both CREATE TABLESPACE and CREATE LOGFILE GROUP. It suggests that the file may autoextend on demand, but actually not.So INITIAL_SIZE is rather just SIZE.

There is nothing to do with ALL REPORT MEMORY USAGE here, since your table is created on disk (because of CREATE TABLE .. TABLESPACE ts1 STORAGE DISK). Only the id field you is stored in the memory (since PRIMARY KEY). I you are interested of the bytes free in tablespace, use INFORMATION_SCHEMA.FILES, like

mysql> select FILE_NAME, FREE_EXTENTS*EXTENT_SIZE as bytes_free, extra  from   information_schema.FILES where TABLESPACE_NAME='ts_1' and    FILE_TYPE="DATAFILE";
+-----------+------------+----------------+
| FILE_NAME | bytes_free | extra          |
+-----------+------------+----------------+
| data1     |  132120576 | CLUSTER_NODE=3 |
| data1     |  132120576 | CLUSTER_NODE=4 |
+-----------+------------+----------------+
2 rows in set (0.01 sec)

Note: Bytes_free is not increases if you DELETE from the table. The disk space is reclaimed only if doing OPTIMIZE TABLE after DELETE or if performing TRUNCATE TABLE.

Note2: For disk tables MySQL Cluster uses fixed row format, so VARCHAR(10000) is handled as CHAR(10000). Using BLOB or TEXT is even more complicated: the first 256 bytes stored in the DataMemory and the rest is divided into 2k chunks, and stored in a helper table. So inserting 5000 1k records into the above table, have a result like that

Before:

+-----------+------------+----------------+------------+
| FILE_NAME | bytes_free | extra          | row_format |
+-----------+------------+----------------+------------+
| data1     |  134217728 | CLUSTER_NODE=4 | FIXED      |
| data1     |  134217728 | CLUSTER_NODE=3 | FIXED      |
+-----------+------------+----------------+------------+

ndb_mgm> all report memory usage;
Node 3: Data usage is 2%(59 32K pages of total 2560)
Node 3: Index usage is 1%(26 8K pages of total 2592)
Node 4: Data usage is 2%(59 32K pages of total 2560)
Node 4: Index usage is 1%(26 8K pages of total 2592)

After:

+-----------+------------+----------------+------------+
| FILE_NAME | bytes_free | extra          | row_format |
+-----------+------------+----------------+------------+
| data1     |  123731968 | CLUSTER_NODE=4 | FIXED      |
| data1     |  123731968 | CLUSTER_NODE=3 | FIXED      |
+-----------+------------+----------------+------------+

ndb_mgm> all report memory usage;
Node 3: Data usage is 4%(120 32K pages of total 2560) 
Node 3: Index usage is 1%(42 8K pages of total 2592)
Node 4: Data usage is 4%(120 32K pages of total 2560)
Node 4: Index usage is 1%(42 8K pages of total 2592)

Disk usage

$> echo $(((134217728-123731968)/5000))
2097
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.