Description:
In information_schema.files the number of free_extents are decremented when inserting records. This is correct.
When deleting records, the number of free_extents are never incremented...that is wrong!
I have to drop the table to get back the free_extents==total_extents., just deleting the records does not help.
This makes it impossible to monitor the amount of free table space that is truly available.
How to repeat:
CREATE TABLESPACE ts1
ADD DATAFILE 'datafile.dat'
USE LOGFILE GROUP lg1
INITIAL_SIZE 12M
ENGINE NDB;
ALTER TABLESPACE ts1
ADD DATAFILE 'datafile02.dat'
INITIAL_SIZE = 4M
ENGINE=NDB;
create table t1(a char(255)) engine=ndb tablespace ts1 storage disk;
insert into t1 (a) values('aa');
## repeat this a number of times
insert into t1 (a) select a from t1 limit 2048;
select free_extents, total_extents from information_schema.files where file_type='datafile';
gives:
+--------------+---------------+
| free_extents | total_extents |
+--------------+---------------+
| 8 | 12 |
| 8 | 12 |
+--------------+---------------+
mysql> select free_extents, total_extents from information_schema.files where file_type='datafile';
+--------------+---------------+
| free_extents | total_extents |
+--------------+---------------+
| 5 | 12 |
| 5 | 12 |
| 12 | 12 |
| 12 | 12 |
+--------------+---------------+
4 rows in set (0.00 sec)
delete from t1 limit 10000;
Query OK, 10000 rows affected (0.38 sec)
delete from t1 limit 10000;
Query OK, 10000 rows affected (0.38 sec)
delete from t1 limit 10000;
Query OK, 10000 rows affected (0.38 sec)
mysql> select free_extents, total_extents from information_schema.files where file_type='datafile';
+--------------+---------------+
| free_extents | total_extents |
+--------------+---------------+
| 5 | 12 |
| 5 | 12 |
| 12 | 12 |
| 12 | 12 |
+--------------+---------------+
4 rows in set (0.01 sec)
mysql>
No extents has been freed up!!!
coffee break
mysql> delete from t1 limit 10000;
Query OK, 1200 rows affected (0.06 sec)
mysql> delete from t1 limit 10000;
Query OK, 0 rows affected (0.00 sec)
mysql> select free_extents, total_extents from information_schema.files where file_type='datafile';
+--------------+---------------+
| free_extents | total_extents |
+--------------+---------------+
| 5 | 12 |
| 5 | 12 |
| 12 | 12 |
| 12 | 12 |
+--------------+---------------+
4 rows in set (0.01 sec)
mysql>
But I have to drop the table to get the free extents incremented..
mysql> drop table t1;
Query OK, 0 rows affected (0.62 sec)
mysql> select free_extents, total_extents from information_schema.files where file_type='datafile';
+--------------+---------------+
| free_extents | total_extents |
+--------------+---------------+
| 12 | 12 |
| 12 | 12 |
| 12 | 12 |
| 12 | 12 |
+--------------+---------------+
4 rows in set (0.00 sec)
mysql>
Suggested fix:
.
Description: In information_schema.files the number of free_extents are decremented when inserting records. This is correct. When deleting records, the number of free_extents are never incremented...that is wrong! I have to drop the table to get back the free_extents==total_extents., just deleting the records does not help. This makes it impossible to monitor the amount of free table space that is truly available. How to repeat: CREATE TABLESPACE ts1 ADD DATAFILE 'datafile.dat' USE LOGFILE GROUP lg1 INITIAL_SIZE 12M ENGINE NDB; ALTER TABLESPACE ts1 ADD DATAFILE 'datafile02.dat' INITIAL_SIZE = 4M ENGINE=NDB; create table t1(a char(255)) engine=ndb tablespace ts1 storage disk; insert into t1 (a) values('aa'); ## repeat this a number of times insert into t1 (a) select a from t1 limit 2048; select free_extents, total_extents from information_schema.files where file_type='datafile'; gives: +--------------+---------------+ | free_extents | total_extents | +--------------+---------------+ | 8 | 12 | | 8 | 12 | +--------------+---------------+ mysql> select free_extents, total_extents from information_schema.files where file_type='datafile'; +--------------+---------------+ | free_extents | total_extents | +--------------+---------------+ | 5 | 12 | | 5 | 12 | | 12 | 12 | | 12 | 12 | +--------------+---------------+ 4 rows in set (0.00 sec) delete from t1 limit 10000; Query OK, 10000 rows affected (0.38 sec) delete from t1 limit 10000; Query OK, 10000 rows affected (0.38 sec) delete from t1 limit 10000; Query OK, 10000 rows affected (0.38 sec) mysql> select free_extents, total_extents from information_schema.files where file_type='datafile'; +--------------+---------------+ | free_extents | total_extents | +--------------+---------------+ | 5 | 12 | | 5 | 12 | | 12 | 12 | | 12 | 12 | +--------------+---------------+ 4 rows in set (0.01 sec) mysql> No extents has been freed up!!! coffee break mysql> delete from t1 limit 10000; Query OK, 1200 rows affected (0.06 sec) mysql> delete from t1 limit 10000; Query OK, 0 rows affected (0.00 sec) mysql> select free_extents, total_extents from information_schema.files where file_type='datafile'; +--------------+---------------+ | free_extents | total_extents | +--------------+---------------+ | 5 | 12 | | 5 | 12 | | 12 | 12 | | 12 | 12 | +--------------+---------------+ 4 rows in set (0.01 sec) mysql> But I have to drop the table to get the free extents incremented.. mysql> drop table t1; Query OK, 0 rows affected (0.62 sec) mysql> select free_extents, total_extents from information_schema.files where file_type='datafile'; +--------------+---------------+ | free_extents | total_extents | +--------------+---------------+ | 12 | 12 | | 12 | 12 | | 12 | 12 | | 12 | 12 | +--------------+---------------+ 4 rows in set (0.00 sec) mysql> Suggested fix: .