MySQL Forums
Forum List  »  NDB clusters

table not stored on disk when "alter table" command doesn't specify storage engine ndb
Posted by: Marcel Kolsteren
Date: July 25, 2015 03:08PM

If I move an empty NDB table from memory to disk with the following command, and then insert rows, the rows end up in memory instead of disk:

alter table test tablespace ts_1 storage disk;

However, if I add the engine to the alter table command the rows correctly end up on disk:

alter table test tablespace ts_1 storage disk engine ndb;

This looks like a bug in MySQL Cluster 7.4.7. As an illustration of the problem, I include a sequence of commands below, and the result when I execute them (compare the disk file & memory usage before and after insertion of a row).

==========
COMMANDS
==========

select version();

CREATE LOGFILE GROUP lg_1 ADD UNDOFILE 'undo_1.log' INITIAL_SIZE 16M UNDO_BUFFER_SIZE 2M ENGINE NDBCLUSTER;
CREATE TABLESPACE ts_1 ADD DATAFILE 'data_1.dat' USE LOGFILE GROUP lg_1 INITIAL_SIZE 200M ENGINE NDBCLUSTER;

create database test;

use test;

CREATE TABLE test (
`id` int(11) NOT NULL,
`contents` longblob NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=NDB DEFAULT CHARSET=latin1;

alter table test TABLESPACE ts_1 STORAGE DISK;

select FILE_NAME, FREE_EXTENTS*EXTENT_SIZE as bytes_free, extra from information_schema.FILES where TABLESPACE_NAME='ts_1' and FILE_TYPE="DATAFILE"; select * from ndbinfo.memoryusage;

insert into `test` (id, contents) values (1, repeat(' ', 15000000));

select FILE_NAME, FREE_EXTENTS*EXTENT_SIZE as bytes_free, extra from information_schema.FILES where TABLESPACE_NAME='ts_1' and FILE_TYPE="DATAFILE"; select * from ndbinfo.memoryusage;

drop table test;

CREATE TABLE test (
`id` int(11) NOT NULL,
`contents` longblob NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=NDB DEFAULT CHARSET=latin1;

alter table test TABLESPACE ts_1 STORAGE DISK engine NDB;

select FILE_NAME, FREE_EXTENTS*EXTENT_SIZE as bytes_free, extra from information_schema.FILES where TABLESPACE_NAME='ts_1' and FILE_TYPE="DATAFILE"; select * from ndbinfo.memoryusage;

insert into `test` (id, contents) values (10006, repeat('f', 15000000));

select FILE_NAME, FREE_EXTENTS*EXTENT_SIZE as bytes_free, extra from information_schema.FILES where TABLESPACE_NAME='ts_1' and FILE_TYPE="DATAFILE"; select * from ndbinfo.memoryusage;


==========
RESULT
==========

mysql> select version();
+------------------------------+
| version() |
+------------------------------+
| 5.6.25-ndb-7.4.7-cluster-gpl |
+------------------------------+
1 row in set (0.00 sec)

mysql> CREATE LOGFILE GROUP lg_1 ADD UNDOFILE 'undo_1.log' INITIAL_SIZE 16M UNDO_BUFFER_SIZE 2M ENGINE NDBCLUSTER;
Query OK, 0 rows affected (0.40 sec)

mysql> CREATE TABLESPACE ts_1 ADD DATAFILE 'data_1.dat' USE LOGFILE GROUP lg_1 INITIAL_SIZE 200M ENGINE NDBCLUSTER;
Query OK, 0 rows affected (2.32 sec)

mysql> create database test;
Query OK, 1 row affected (0.06 sec)

mysql> use test;
Database changed

mysql> CREATE TABLE test (
-> `id` int(11) NOT NULL,
-> `contents` longblob NOT NULL,
-> PRIMARY KEY (`id`)
-> ) ENGINE=NDB DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.42 sec)

mysql> alter table test TABLESPACE ts_1 STORAGE DISK;
Query OK, 0 rows affected (0.24 sec)
Records: 0 Duplicates: 0 Warnings: 0

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"; select * from ndbinfo.memoryusage;
+------------+------------+----------------+
| FILE_NAME | bytes_free | extra |
+------------+------------+----------------+
| data_1.dat | 209715200 | CLUSTER_NODE=2 |
| data_1.dat | 209715200 | CLUSTER_NODE=3 |
+------------+------------+----------------+
2 rows in set (0.00 sec)

+---------+---------------------+---------+------------+-----------+-------------+
| node_id | memory_type | used | used_pages | total | total_pages |
+---------+---------------------+---------+------------+-----------+-------------+
| 2 | Data memory | 2097152 | 64 | 524288000 | 16000 |
| 2 | Index memory | 262144 | 32 | 262406144 | 32032 |
| 2 | Long message buffer | 7168 | 28 | 67108864 | 262144 |
| 3 | Data memory | 2097152 | 64 | 524288000 | 16000 |
| 3 | Index memory | 262144 | 32 | 262406144 | 32032 |
| 3 | Long message buffer | 7168 | 28 | 67108864 | 262144 |
+---------+---------------------+---------+------------+-----------+-------------+
6 rows in set (0.00 sec)

mysql> insert into `test` (id, contents) values (1, repeat(' ', 15000000));
Query OK, 1 row affected (0.23 sec)

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"; select * from ndbinfo.memoryusage;
+------------+------------+----------------+
| FILE_NAME | bytes_free | extra |
+------------+------------+----------------+
| data_1.dat | 209715200 | CLUSTER_NODE=2 |
| data_1.dat | 209715200 | CLUSTER_NODE=3 |
+------------+------------+----------------+
2 rows in set (0.00 sec)

+---------+---------------------+----------+------------+-----------+-------------+
| node_id | memory_type | used | used_pages | total | total_pages |
+---------+---------------------+----------+------------+-----------+-------------+
| 2 | Data memory | 19890176 | 607 | 524288000 | 16000 |
| 2 | Index memory | 286720 | 35 | 262406144 | 32032 |
| 2 | Long message buffer | 7168 | 28 | 67108864 | 262144 |
| 3 | Data memory | 19890176 | 607 | 524288000 | 16000 |
| 3 | Index memory | 286720 | 35 | 262406144 | 32032 |
| 3 | Long message buffer | 7168 | 28 | 67108864 | 262144 |
+---------+---------------------+----------+------------+-----------+-------------+
6 rows in set (0.01 sec)

mysql> drop table test;
Query OK, 0 rows affected (0.18 sec)

mysql> CREATE TABLE test (
-> `id` int(11) NOT NULL,
-> `contents` longblob NOT NULL,
-> PRIMARY KEY (`id`)
-> ) ENGINE=NDB DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.51 sec)

mysql> alter table test TABLESPACE ts_1 STORAGE DISK engine NDB;
Query OK, 0 rows affected (0.67 sec)
Records: 0 Duplicates: 0 Warnings: 0

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"; select * from ndbinfo.memoryusage;
+------------+------------+----------------+
| FILE_NAME | bytes_free | extra |
+------------+------------+----------------+
| data_1.dat | 209715200 | CLUSTER_NODE=2 |
| data_1.dat | 209715200 | CLUSTER_NODE=3 |
+------------+------------+----------------+
2 rows in set (0.01 sec)

+---------+---------------------+---------+------------+-----------+-------------+
| node_id | memory_type | used | used_pages | total | total_pages |
+---------+---------------------+---------+------------+-----------+-------------+
| 2 | Data memory | 2097152 | 64 | 524288000 | 16000 |
| 2 | Index memory | 262144 | 32 | 262406144 | 32032 |
| 2 | Long message buffer | 7168 | 28 | 67108864 | 262144 |
| 3 | Data memory | 2097152 | 64 | 524288000 | 16000 |
| 3 | Index memory | 262144 | 32 | 262406144 | 32032 |
| 3 | Long message buffer | 7168 | 28 | 67108864 | 262144 |
+---------+---------------------+---------+------------+-----------+-------------+
6 rows in set (0.00 sec)

mysql> insert into `test` (id, contents) values (10006, repeat('f', 15000000));
Query OK, 1 row affected (0.23 sec)

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"; select * from ndbinfo.memoryusage;
+------------+------------+----------------+
| FILE_NAME | bytes_free | extra |
+------------+------------+----------------+
| data_1.dat | 191889408 | CLUSTER_NODE=2 |
| data_1.dat | 191889408 | CLUSTER_NODE=3 |
+------------+------------+----------------+
2 rows in set (0.00 sec)

+---------+---------------------+---------+------------+-----------+-------------+
| node_id | memory_type | used | used_pages | total | total_pages |
+---------+---------------------+---------+------------+-----------+-------------+
| 2 | Data memory | 2260992 | 69 | 524288000 | 16000 |
| 2 | Index memory | 286720 | 35 | 262406144 | 32032 |
| 2 | Long message buffer | 7168 | 28 | 67108864 | 262144 |
| 3 | Data memory | 2260992 | 69 | 524288000 | 16000 |
| 3 | Index memory | 286720 | 35 | 262406144 | 32032 |
| 3 | Long message buffer | 7168 | 28 | 67108864 | 262144 |
+---------+---------------------+---------+------------+-----------+-------------+
6 rows in set (0.00 sec)

Options: ReplyQuote


Subject
Views
Written By
Posted
table not stored on disk when "alter table" command doesn't specify storage engine ndb
1975
July 25, 2015 03:08PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.