Hi Jonas, When ever an alter table command is executed on mysql tables, mysql creates a temp file with all the data + the current table. Once the alter table command is done, it will drop the current table and rename the new temp file to the current table, that the reason the disk usage goes high.
Since u have cancled the job, those in-complete temp files can be deleted from the file system. Alter when adding indexes use set sort_buffer_size=100*1024*1024; so that index creation uses more memory and the execution is faster. regards anandkl On 12/4/08, Jonas Genannt <[EMAIL PROTECTED]> wrote: > > Hello, > > we having an 60 GB InnoDB database. The table with the problem is about > 12GB. > > On of our scripts has got a problem and run 60 times an alter table: > > ALTER TABLE `foo` ADD INDEX ( `bar` ) ; > > We had to kill the alter table commands with kill on the mysql console. > Now we have one index on the column, but this index is broken. We have > to delete this index. > > But the main problem is the disk usage: When this problem occur the > disk usage of the ibdata file bumps up to 85 GB. > > I think after I have killed the alter table commands mysql does not > delete the temp tables from the alter table command. > > How I can remove the temp tables? - I have also read at some bug > reports if I now restart the database mysql performs an InnoDB recovery > and restores an old temp table to the current table! Is this correct? > > Thanks, > Jonas > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > >
