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 To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
