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:
- Extents are allocated/assigned to a table
- Empty extents followed by
DELETEare not deallocated from the table.
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).
