Disk data extent (de)allocation caveats

Tweet Here is a discussion about some caveats with allocation and deallocation of extents in MySQL Cluster (disk data).

Let's assume we have created a table space with one datafile having 5 extents (i.e the datafile is 5MB in since, since the size of each extent is 1MB).

Using the information_schema we can figure out how many free extents we have by executing the following query:

mysql> select free_extents, total_extents from information_schema.files where file_type='datafile';
+--------------+---------------+
| free_extents | total_extents |
+--------------+---------------+
| 5 | 5 |
| 5 | 5 |
+--------------+---------------+

The figure below shows a number of extents that are all free.
We then create a table using the table space and start populating it with data:

mysql> create table t1(a char(255)) engine=ndb tablespace ts1 storage disk;
mysql> insert into t1(a) values ('a');
mysql> insert into t1(a) select a from t1 limit 4096;



After inserting for while you can the table space is full (all extents have been filled):


mysql> select free_extents, total_extents from information_schema.files where file_type='datafile';
+--------------+---------------+
| free_extents | total_extents |
+--------------+---------------+
| 0 | 5 |
| 0 | 5 |
+--------------+---------------+

If I try to insert more data, then I will get:
ERROR 1114 (HY000): The table 't1' is full and I would have to add another datafile to my table space.

Now I decide to delete records:
mysql> delete from t1 limit 4096;

Eventually extents will become empty:

But there are a couple caveats to be aware of!!
Caveat one is that the extents that are free up by the deletion does not show up in the information schema. I still have zero free extents!!:

mysql> select free_extents, total_extents from information_schema.files where file_type='datafile';
+--------------+---------------+
| free_extents | total_extents |
+--------------+---------------+
| 0 | 5 |
| 0 | 5 |
+--------------+---------------+

However, I can insert more records , since there are holes in the extents that can be used.

Now I delete ALL (or almost all, it doesn't really matter) records in t1, so that table is completely empty!!

Caveat two comes when I try to create a new table using the same tablespace:

mysql> create table t2(a char(255)) engine=ndb tablespace ts1 storage disk;
Query OK, 0 rows affected (1.03 sec)

mysql> insert into t2 (a) values ('aaaa');
ERROR 1114 (HY000): The table 't2' is full

The problem is that:

1. Extents are allocated/assigned to a table
   
2. Empty extents followed by DELETE are not deallocated from the table.

Only DROP TABLE deallocate/deassociate the extents making them available to another table!!!

Thus I cannot do:

mysql> insert into t2 (a) values ('aaa')
ERROR 1114 (HY000): The table 't2' is full

But inserting into t1 is still possible:

mysql> insert into t1 (a) select a from t1 limit 4096;
Query OK, 4096 rows affected (0.17 sec)
Records: 4096 Duplicates: 0 Warnings: 0

mysql> insert into t1 (a) select a from t1 limit 4096;
Query OK, 4096 rows affected (0.16 sec)
Records: 4096 Duplicates: 0 Warnings: 0


I hope this will be fixed one day!

(btw, the cmon trunk on http://launchpad.net/cmon monitors the table spaces from today. I will release source distro shortly, but in the time being you can get the source using bazaar-vcs and do bzr branch lp:cmon).