MySQL Bugs: #29647: Falcon does not reuse empty space in tablespace

Bug #29647	Falcon does not reuse empty space in tablespace
Submitted:	9 Jul 2007 13:26	Modified:	9 Jul 2007 15:32
Reporter:	Robin Schumacher	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: Falcon storage engine	Severity:	S2 (Serious)
Version:	6.0	OS:	Windows
Assigned to:		CPU Architecture:	Any

Description:
When multiple objects are in a tablespace, Falcon does not reuse empty space in tablespace after object(s) are deleted. 

How to repeat:
mysql> create tablespace gimdata
    -> add datafile 'c:/dev/falcon/gimdata.fts' engine=falcon;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from information_schema.falcon_tables;
+-------------+------------+------------+
| SCHEMA_NAME | TABLE_NAME | TABLESPACE |
+-------------+------------+------------+
| GIMF        | FTEST      | GIMDATA    |
+-------------+------------+------------+

<push 1.8 million rows into ftest>

size is now 175MB

mysql> create table t1 (c1 int) engine=falcon tablespace gimdata;
Query OK, 0 rows affected (0.06 sec)

mysql> insert into t1 values (1);
Query OK, 1 row affected (0.03 sec)

mysql> select * from information_schema.falcon_tables;
+-------------+------------+------------+
| SCHEMA_NAME | TABLE_NAME | TABLESPACE |
+-------------+------------+------------+
| GIMF        | FTEST      | GIMDATA    |
| GIMF        | T1         | GIMDATA    |
+-------------+------------+------------+

mysql> drop table ftest;
Query OK, 0 rows affected (0.05 sec)

mysql> create table ftest like rms2;
Query OK, 0 rows affected (0.05 sec)

mysql> alter table ftest engine=falcon;
Query OK, 0 rows affected (0.17 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table ftest tablespace gimdata;
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select * from information_schema.falcon_tables;
+-------------+------------+------------+
| SCHEMA_NAME | TABLE_NAME | TABLESPACE |
+-------------+------------+------------+
| GIMF        | T1         | GIMDATA    |
| GIMF        | FTEST      | GIMDATA    |
+-------------+------------+------------+

<push 1.8 million rows into ftest>

size is now 348MB

Thank you for the bug report. I was not able to repeat this issue with latest
source server:

c:\dev\6.0>bin\mysqladmin -uroot create db7

c:\dev\6.0>bin\mysql -uroot db7
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 6.0.1-alpha-nt Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create tablespace gimdata
    -> add datafile 'c:/dev/falcon/gimdata.fts' engine=falcon;
Query OK, 0 rows affected (0.13 sec)

mysql> create table ftest (id serial, col1 varchar(500)) engine=falcon tablespace gimdata;
Query OK, 0 rows affected (0.16 sec)

mysql> insert into ftest values (null,repeat("a",500));
Query OK, 1 row affected (0.01 sec)

mysql> insert into ftest (col1) select col1 from ftest;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

<cut>

mysql> insert into ftest (col1) select col1 from ftest;
Query OK, 524288 rows affected (47.11 sec)
Records: 524288  Duplicates: 0  Warnings: 0

mysql> insert into ftest (col1) select col1 from ftest;
Query OK, 1048576 rows affected (4 min 17.40 sec)
Records: 1048576  Duplicates: 0  Warnings: 0

mysql> select count(*) from ftest;
+----------+
| count(*) |
+----------+
|  2097152 |
+----------+
1 row in set (6 min 58.49 sec)

mysql> create table t1 (c1 int) engine=falcon tablespace gimdata;
Query OK, 0 rows affected (0.47 sec)

mysql> insert into t1 values (1);
Query OK, 1 row affected (0.14 sec)

mysql>  select * from information_schema.falcon_tables;
+-------------+------------+------------+
| SCHEMA_NAME | TABLE_NAME | TABLESPACE |
+-------------+------------+------------+
| DB7         | FTEST      | GIMDATA    |
| DB7         | T1         | GIMDATA    |
+-------------+------------+------------+
2 rows in set (0.09 sec)

mysql>

c:\dev\6.0>dir c:\dev\falcon\
 O volume na unidade C não tem nome.
 O Número de Série do Volume é 6418-7E81

 Pasta de c:\dev\falcon

09/07/2007  11:45    <DIR>          .
09/07/2007  11:45    <DIR>          ..
09/07/2007  11:45     1.260.355.584 gimdata.fts
               1 arquivo(s)  1.260.355.584 bytes
               2 pasta(s)   65.497.653.248 bytes disponíveis

c:\dev\6.0>bin\mysql -uroot db7
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 6.0.1-alpha-nt Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql>  drop table ftest;
Query OK, 0 rows affected (1.59 sec)

mysql>  select * from information_schema.falcon_tables;
+-------------+------------+------------+
| SCHEMA_NAME | TABLE_NAME | TABLESPACE |
+-------------+------------+------------+
| DB7         | T1         | GIMDATA    |
+-------------+------------+------------+
1 row in set (0.08 sec)

mysql> create table ftest (id serial, col1 varchar(500)) engine=falcon tablespace gimdata;
Query OK, 0 rows affected (0.14 sec)

mysql> insert into ftest values (null,repeat("a",500));
Query OK, 1 row affected (0.00 sec)

mysql> insert into ftest (col1) select col1 from ftest;
Query OK, 1 row affected (0.05 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> insert into ftest (col1) select col1 from ftest;
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> insert into ftest (col1) select col1 from ftest;
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql>  select * from information_schema.falcon_tables;
+-------------+------------+------------+
| SCHEMA_NAME | TABLE_NAME | TABLESPACE |
+-------------+------------+------------+
| DB7         | T1         | GIMDATA    |
| DB7         | FTEST      | GIMDATA    |
+-------------+------------+------------+
2 rows in set (0.05 sec)

mysql> insert into ftest (col1) select col1 from ftest;
Query OK, 524288 rows affected (58.63 sec)
Records: 524288  Duplicates: 0  Warnings: 0

mysql> insert into ftest (col1) select col1 from ftest;
Query OK, 1048576 rows affected (2 min 40.62 sec)
Records: 1048576  Duplicates: 0  Warnings: 0

mysql> select count(*) from ftest;
+----------+
| count(*) |
+----------+
|  2097152 |
+----------+
1 row in set (5 min 28.08 sec)

mysql> exit
Bye

c:\dev\6.0>dir c:\dev\falcon\
 O volume na unidade C não tem nome.
 O Número de Série do Volume é 6418-7E81

 Pasta de c:\dev\falcon

09/07/2007  11:45    <DIR>          .
09/07/2007  11:45    <DIR>          ..
09/07/2007  11:45     1.260.355.584 gimdata.fts
               1 arquivo(s)  1.260.355.584 bytes
               2 pasta(s)   63.975.186.432 bytes disponíveis

c:\dev\6.0>