Bug #17605 MySQL Cluster not cleaning up / freeing tablespaces after delete
Submitted: 21 Feb 2006 8:21 Modified: 28 Dec 2006 14:01
Reporter: Kris Buytaert (Candidate Quality Contributor) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Cluster: Disk Data Severity:S2 (Serious)
Version:5.1.6 OS:Linux (Linux)
Assigned to: Jonas Oreland CPU Architecture:Any

[21 Feb 2006 8:21] Kris Buytaert 
Description:

Short summary .. I created tables and tablespaces as documented on 
http://mikaelronstrom.blogspot.com/2006/02/how-to-define-table-that-uses-disk.html

I filled up all my tablespaces with dummy data, then
dropped the table and according to the  files table the space was
available again, so I recreated my table and tried to insert stuff into
which failed and kept failing till I added yet another datafile.  

Should I have flushed my tables or so ? 
Here's the output.

How to repeat:

mysql> select file_id, file_name , tablespace_name , engine,
free_extents, total_extents, data_free, status from files;
+---------+----------------+-----------------+------------+--------------+---------------+-----------+--------+
| file_id | file_name      | tablespace_name | engine     | free_extents
| total_extents | data_free | status |
+---------+----------------+-----------------+------------+--------------+---------------+-----------+--------+
|       0 | datafile02.dat | ts1             | ndbcluster |            4
|             4 |      NULL | NORMAL |
|       0 | datafile02.dat | ts1             | ndbcluster |            4
|             4 |      NULL | NORMAL |
|       0 | datafile.dat   | ts1             | ndbcluster |           12
|            12 |      NULL | NORMAL |
|       0 | datafile.dat   | ts1             | ndbcluster |           12
|            12 |      NULL | NORMAL |
|       0 | datafile03.dat | ts1             | ndbcluster |            1
|            40 |      NULL | NORMAL |
|       0 | datafile03.dat | ts1             | ndbcluster |            1
|            40 |      NULL | NORMAL |
|       0 | datafile04.dat | ts1             | ndbcluster |            0
|           400 |      NULL | NORMAL |
|       0 | datafile04.dat | ts1             | ndbcluster |           10
|           400 |      NULL | NORMAL |
|       0 | undofile.dat   | ts1             | ndbcluster |      4159504
|       1048576 |      NULL | NORMAL |
|       0 | undofile.dat   | ts1             | ndbcluster |      4159504
|       1048576 |      NULL | NORMAL |
+---------+----------------+-----------------+------------+--------------+---------------+-----------+--------+
10 rows in set (0.01 sec)

mysql> drop table test.t2;
Query OK, 0 rows affected (0.81 sec)

mysql> select file_id, file_name , tablespace_name , engine,
free_extents, total_extents, data_free, status from files;
+---------+----------------+-----------------+------------+--------------+---------------+-----------+--------+
| file_id | file_name      | tablespace_name | engine     | free_extents
| total_extents | data_free | status |
+---------+----------------+-----------------+------------+--------------+---------------+-----------+--------+
|       0 | datafile02.dat | ts1             | ndbcluster |            4
|             4 |      NULL | NORMAL |
|       0 | datafile02.dat | ts1             | ndbcluster |            4
|             4 |      NULL | NORMAL |
|       0 | datafile.dat   | ts1             | ndbcluster |           12
|            12 |      NULL | NORMAL |
|       0 | datafile.dat   | ts1             | ndbcluster |           12
|            12 |      NULL | NORMAL |
|       0 | datafile03.dat | ts1             | ndbcluster |           40
|            40 |      NULL | NORMAL |
|       0 | datafile03.dat | ts1             | ndbcluster |           40
|            40 |      NULL | NORMAL |
|       0 | datafile04.dat | ts1             | ndbcluster |          400
|           400 |      NULL | NORMAL |
|       0 | datafile04.dat | ts1             | ndbcluster |          400
|           400 |      NULL | NORMAL |
|       0 | undofile.dat   | ts1             | ndbcluster |      4159504
|       1048576 |      NULL | NORMAL |
|       0 | undofile.dat   | ts1             | ndbcluster |      4159504
|       1048576 |      NULL | NORMAL |
+---------+----------------+-----------------+------------+--------------+---------------+-----------+--------+
10 rows in set (0.02 sec)

mysql> CREATE TABLE t2 (a int, b int, text varchar(255), primary key
(a), index(a,b)) TABLESPACE ts1 STORAGE DISK engine=ndb;
Query OK, 0 rows affected (1.24 sec)

mysql> select * from t2;
Empty set (0.02 sec)

mysql> select file_id, file_name , tablespace_name , engine,
free_extents, total_extents, data_free, status from
information_schema.files;
+---------+----------------+-----------------+------------+--------------+---------------+-----------+--------+
| file_id | file_name      | tablespace_name | engine     | free_extents
| total_extents | data_free | status |
+---------+----------------+-----------------+------------+--------------+---------------+-----------+--------+
|       0 | datafile02.dat | ts1             | ndbcluster |            4
|             4 |      NULL | NORMAL |
|       0 | datafile02.dat | ts1             | ndbcluster |            4
|             4 |      NULL | NORMAL |
|       0 | datafile.dat   | ts1             | ndbcluster |           12
|            12 |      NULL | NORMAL |
|       0 | datafile.dat   | ts1             | ndbcluster |           12
|            12 |      NULL | NORMAL |
|       0 | datafile03.dat | ts1             | ndbcluster |           40
|            40 |      NULL | NORMAL |
|       0 | datafile03.dat | ts1             | ndbcluster |           40
|            40 |      NULL | NORMAL |
|       0 | datafile04.dat | ts1             | ndbcluster |          400
|           400 |      NULL | NORMAL |
|       0 | datafile04.dat | ts1             | ndbcluster |          399
|           400 |      NULL | NORMAL |
|       0 | undofile.dat   | ts1             | ndbcluster |      4159502
|       1048576 |      NULL | NORMAL |
|       0 | undofile.dat   | ts1             | ndbcluster |      4159502
|       1048576 |      NULL | NORMAL |
+---------+----------------+-----------------+------------+--------------+---------------+-----------+--------+
10 rows in set (0.01 sec)

mysql> insert into t2 values (11,0,"lotsta text in here that doesnt
really matter 1");
ERROR 1114 (HY000): The table 't2' is full
mysql> select * from t2;
Empty set (0.00 sec)

mysql> ALTER TABLESPACE ts1 ADD DATAFILE 'datafile05.dat' INITIAL_SIZE
400M ENGINE=NDB;
Query OK, 0 rows affected (7.54 sec)

mysql> insert into t2 values (11,0,"lotsta text in here that doesnt
really matter 1");
Query OK, 1 row affected (0.01 sec)

mysql> select file_id, file_name , tablespace_name , engine,
free_extents, total_extents, data_free, status from
information_schema.files;
+---------+----------------+-----------------+------------+--------------+---------------+-----------+--------+
| file_id | file_name      | tablespace_name | engine     | free_extents
| total_extents | data_free | status |
+---------+----------------+-----------------+------------+--------------+---------------+-----------+--------+
|       0 | datafile02.dat | ts1             | ndbcluster |            4
|             4 |      NULL | NORMAL |
|       0 | datafile02.dat | ts1             | ndbcluster |            4
|             4 |      NULL | NORMAL |
|       0 | datafile.dat   | ts1             | ndbcluster |           12
|            12 |      NULL | NORMAL |
|       0 | datafile.dat   | ts1             | ndbcluster |           12
|            12 |      NULL | NORMAL |
|       0 | datafile03.dat | ts1             | ndbcluster |           40
|            40 |      NULL | NORMAL |
|       0 | datafile03.dat | ts1             | ndbcluster |           40
|            40 |      NULL | NORMAL |
|       0 | datafile04.dat | ts1             | ndbcluster |          400
|           400 |      NULL | NORMAL |
|       0 | datafile04.dat | ts1             | ndbcluster |          399
|           400 |      NULL | NORMAL |
|       0 | datafile05.dat | ts1             | ndbcluster |          399
|           400 |      NULL | NORMAL |
|       0 | datafile05.dat | ts1             | ndbcluster |          400
|           400 |      NULL | NORMAL |
|       0 | undofile.dat   | ts1             | ndbcluster |      4151311
|       1048576 |      NULL | NORMAL |
|       0 | undofile.dat   | ts1             | ndbcluster |      4151311
|       1048576 |      NULL | NORMAL |
+---------+----------------+-----------------+------------+--------------+---------------+-----------+--------+
12 rows in set (0.01 sec)
[4 Mar 2006 7:18] Jonas Oreland 
Hi Kris,

Sorry that this response is somewhat late.

This is a know limitation.
An extent can't be reused to another table until a LCP has been performed.
You can force a LCP by using 'ndb_mgm -e "all dump 7099"'

I'm not really sure how to fix this now...but will have it in mind...

Let me know how it goes

/Jonas
[4 Apr 2006 23:00] Bugs System 
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".
[5 Apr 2006 15:15] Kris Buytaert 
The above mentionnned  "all dump 7099' trick indeed frees the allocated space.
I however consider it a workaround,  not a bugfix.
[5 Apr 2006 21:47] Jonas Oreland 
Agree
[7 Dec 2006 14:25] Bugs System 
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/16593

ChangeSet@1.2346, 2006-12-07 15:25:02+01:00, jonas@perch.ndb.mysql.com +12 -0
  ndb - bug#21948 & bug#17605
    fix alloc/free extent in undo log
    allow extent to be reused once a lcp is finished (instead of when next lcp starts)
[28 Dec 2006 14:01] Jon Stephens 
Thank you for your bug report. This issue has been committed to our source repository of that product and will be incorporated into the next release.

If necessary, you can access the source repository and build the latest available version, including the bug fix. More information about accessing the source trees is available at

    http://dev.mysql.com/doc/en/installing-source.html

Documented bugfix in 5.1.15 changelog.