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:
.