From: Josh Chamas (joshchamas.com)
Date: Thu Jul 01 2004 - 22:12:29 CDT

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]

Hi, ( hopefully a MySQL developer sees this at some point! )

I am giving InnoDB a good workout before rolling it out onto
production systems, and found a bug in the way the tablespace gets fragmented
when doing basic add/drop of indexes. Below my sig is a series of SQL
commands I used to replicate the problem.

Basically, when doing a drop index, add index, drop index, one would
expect the tablespace to look more or less how it looked after the first
drop index since the add index should just reuse what gets reclaimed
during the 1st drop. What I am finding however is that this sequence will
perpetually grow the tablespace, both on disk, and according to InnoDB.

The really interesting thing about this issue is that the tablespace
data file grows on disk at the "drop index" time, not during the "add index".
I could not believe it when I saw it at first, but I repeated the
procedure and confirmed this aspect of this bug a couple times.

Note that I am using the "autoextend" feature with a basic innodb config of:

# Uncomment the following if you are using InnoDB tables
innodb_data_home_dir = /usr/local/mysql4/innodb
innodb_data_file_path = ibdata1:100M:autoextend
innodb_log_group_home_dir = /usr/local/mysql4/innodb
innodb_log_arch_dir = /usr/local/mysql4/innodb
transaction-isolation = READ-COMMITTED

and the innodb files end up looking like this:

]$ ls -allg /usr/local/mysql4/innodb/
total 504352
drwxr-xr-x 2 mysql 4096 Jun 21 00:50 .
drwxr-xr-x 12 root 4096 Jun 21 00:17 ..
-rw-rw---- 1 mysql 25088 Mar 22 22:00 ib_arch_log_0000000000
-rw-rw---- 1 mysql 2560 Jun 21 00:50 ib_arch_log_0000000002
-rw-rw---- 1 mysql 499122176 Jul 1 19:45 ibdata1
-rw-rw---- 1 mysql 8388608 Jul 1 19:45 ib_logfile0
-rw-rw---- 1 mysql 8388608 Jul 1 19:45 ib_logfile1

and just before the last "drop index" the ibdata1 file looked like:

]$ ls -allg /usr/local/mysql4/innodb/
-rw-rw---- 1 mysql 490733568 Jul 1 19:44 ibdata1

Finally, I call this a bug because it seems that if one is doing no more
than routine maintenance on tables by adding/dropping indexes, one will
eventually run out of disk space regardless of whether one is actually
using that disk space!

Also, I have a linux 2.4 kernel that this is running on, with mysql
compiled with gcc 3.2.2.

Thanks,

Josh
________________________________________________________________________
Josh Chamas, Founder | NodeWorks - http://www.nodeworks.com
Chamas Enterprises Inc. | NodeWorks Directory - http://dir.nodeworks.com
http://www.chamas.com | Apache::ASP - http://www.apache-asp.org

mysql> alter table clicks drop index idx_test;
Query OK, 891450 rows affected (57.83 sec)
Records: 891450 Duplicates: 0 Warnings: 0

mysql> show table status like 'clicks';
+--------+--------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+-------------
+-------------+------------+----------------+------------------------+
| Name | Type | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time
| Update_time | Check_time | Create_options | Comment |
+--------+--------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+-------------
+-------------+------------+----------------+------------------------+
| clicks | InnoDB | Dynamic | 891651 | 95 | 85590016 | NULL | 58458112 | 0 | NULL | NULL
| NULL | NULL | | InnoDB free: 323584 kB |
+--------+--------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+-------------
+-------------+------------+----------------+------------------------+
1 row in set (0.00 sec)

mysql> alter table clicks add index idx_test (client_id);
Query OK, 891450 rows affected (1 min 4.73 sec)
Records: 891450 Duplicates: 0 Warnings: 0

mysql> show table status like 'clicks';
+--------+--------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+-------------
+-------------+------------+----------------+------------------------+
| Name | Type | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time
| Update_time | Check_time | Create_options | Comment |
+--------+--------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+-------------
+-------------+------------+----------------+------------------------+
| clicks | InnoDB | Dynamic | 891651 | 95 | 85590016 | NULL | 91602944 | 0 | NULL | NULL
| NULL | NULL | | InnoDB free: 291840 kB |
+--------+--------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+-------------
+-------------+------------+----------------+------------------------+
1 row in set (0.01 sec)

mysql> alter table clicks drop index idx_test;
Query OK, 891450 rows affected (1 min 9.88 sec)
Records: 891450 Duplicates: 0 Warnings: 0

mysql> show table status like 'clicks';
+--------+--------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+-------------
+-------------+------------+----------------+------------------------+
| Name | Type | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time
| Update_time | Check_time | Create_options | Comment |
+--------+--------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+-------------
+-------------+------------+----------------+------------------------+
| clicks | InnoDB | Dynamic | 891651 | 95 | 85590016 | NULL | 58458112 | 0 | NULL | NULL
| NULL | NULL | | InnoDB free: 331776 kB |
+--------+--------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+-------------
+-------------+------------+----------------+------------------------+
1 row in set (0.01 sec)

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]