List:General Discussion« Previous MessageNext Message »
From:Ananda Kumar Date:December 4 2008 10:03am
Subject:Re: Alter Table - InnoDB
View as plain text  
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 <jonasge@stripped> 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/mysql?unsub=1
>
>

Thread
Alter Table - InnoDBJonas Genannt4 Dec
  • Re: Alter Table - InnoDBAnanda Kumar4 Dec
    • Re: Alter Table - InnoDBJonas Genannt4 Dec
      • Re: Alter Table - InnoDBAnanda Kumar4 Dec
        • Re: Alter Table - InnoDBShachi Govil4 Dec
          • Re: Alter Table - InnoDBJonas Genannt4 Dec
          • Re: Alter Table - InnoDBChandru4 Dec
            • RE: Alter Table - InnoDBRolando Edwards4 Dec
        • Re: Alter Table - InnoDBJonas Genannt4 Dec
  • Re: Alter Table - InnoDBMartijn Tonies4 Dec