Friday, November 28, 2008

Disk data extent (de)allocation caveats

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

3 comments:

Matthew Montgomery said...

Johan, It's good to note that this only applieds to on disk tablespaces and that OPTIMIZE TABLE deallocates empty extents from the in-memory tables from ndb-6.3 onward.

Seems, you're looking for http://forge.mysql.com/worklog/task.php?id=3192 to be implemented.

Though I can't think how it would affect "Connector/.NET-5.2" as the description shows.

Johan Andersson said...

Hello, good to stress that - in-memory tables does proper allocation/deallocation ! So yeah, this only applies to dd!

-johan

111 said...

WoW shares many wow gold of its features with previously launched games. Essentially, you battle with Cheapest wow gold monsters and traverse the countryside, by yourself or as a buy cheap wow gold team, find challenging tasks, and go on to higher Cheap Wow Gold levels as you gain skill and experience. In the course of your journey, you will be gaining new powers that are increased as your skill rating goes up. All the same, in terms of its features and quality, that is a ture stroy for this.WoW is far ahead of all other games of the genre the wow power leveling game undoubtedly is in a league of its own and cheapest wow gold playing it is another experience altogether.
Even though WoW is a wow gold cheap rather complicated game, the controls and interface are done in buy warhammer gold such a way that you don't feel the complexity. A good feature of the game is that it buy wow items does not put off people with lengthy manuals. The instructions cannot be simpler and the pop up tips can help you start playing the game World Of Warcraft Gold immediately. If on the other hand, you need a detailed manual, the instructions are there for you to access. Buy wow gold in this site,good for you ,WoW Gold, BUY WOW GOLD.