List:General Discussion« Previous MessageNext Message »
From:Xn Nooby Date:August 18 2010 8:57pm
Subject:Re: Slow ALTER TABLE on 70M row InnoDB table
View as plain text  
Hi Mike, my comments are below:

On Wed, Aug 18, 2010 at 4:50 PM, mos <mos99@stripped> wrote:
> At 02:52 PM 8/18/2010, Xn Nooby wrote:
>>
>> Below is a generic version of the code I am trying.  It does copy the
>> rows very quickly, but I will have to test to see how quickly the
>> indices are built.  Is the below code what you were suggesting?  I had
>> a little trouble dropping and later adding the primary index, but I
>> think I got it figured out.
>>
>> Below I basically do this:
>>  make the_table_clone from the the_table
>>  drop the indices on the_table_clone
>>  copy the row from the_table to the_table_clone
>>  add the indices back to the_table_clone
>>
>> If this runs fast enough, I will then drop the_table, and rename
>> the_table_clone to the_table
>>
>>
>> USE the_database;
>>
>>
>> DROP TABLE IF EXISTS the_table_clone;
>>
>> CREATE TABLE the_table_clone LIKE the_table;
>
> Or you can try something like:
>
> create table the_table_clone engine=innodb select * from the_table limit=0;
> This will create the same table structure but not the indexes so you don't
> have to drop the indexes below.


That is good to know.  I did not mind dropping the indices in this
case, because the table was still empty.


>
>
>
>> # drop minor indices on clone
>>
>> ALTER TABLE the_table_clone DROP INDEX IX_the_table_on_col2_col3;
>>
>> ALTER TABLE the_table_clone DROP INDEX IX_the_table_on_col4_col6;
>>
>> ALTER TABLE the_table_clone DROP INDEX IX_the_table_on_col5_col2;
>>
>>
>> # drop primary index on clone
>>
>> ALTER TABLE the_table_clone CHANGE id id INT UNSIGNED;
>
> You still need the statement above to change the autoinc to integer if you
> use my Create Table... statement above.
>
>
>
>> ALTER TABLE the_table_clone DROP PRIMARY KEY;
>>
>>
>> # add 2 new columns to clone
>>
>> ALTER TABLE the_table_clone ADD flag_field int(11) DEFAULT '0', ADD
>> price_amount float DEFAULT '0';
>>
>>
>> # copy rows
>>
>> INSERT the_table_clone SELECT *, NULL, NULL FROM the_table LIMIT
>> 0,10000000;
>>
>> #INSERT the_table_clone SELECT *, NULL, NULL FROM the_table;
>
> Why do you have two insert statements? If you are inserting a group of
> records at a time then you need a limit statement on each, and increment the
> offset by the number of rows that have been added.


The 2nd INSERT is disabled with the # character.  I am using the
statement with the LIMIT for testing, and will switch to the other
command when I want to process all the records.


>
> I would explicitly specify the column list for both the Insert and the
> Select to make sure they match up. There is no point going through all this
> if it inserts the data into the wrong columns!
> Check the data before creating the indexes to make sure the same number of
> rows have been copied over and the data is in the correct columns.


Okay.  I thought it was safe to assume that the new columns would
appear on the "right-side" of the column list.


>
>
>
>> # Add back indices in one command (for max speed)
>>
>> ALTER TABLE the_table_clone \
>>  ADD INDEX IX_the_table_on_col2_col3 (col2,col3),\
>>  ADD INDEX IX_the_table_on_col4_col6 (col4,col6),\
>>  ADD INDEX IX_the_table_on_col5_col2 (col5,col2),\
>>  MODIFY id INT SIGNED AUTO_INCREMENT,\
>>  ADD PRIMARY KEY(col1);
>
> Correct.


The insert took 7 minutes on 10M rows, and that ALTER command took
another 46 minutes.


>
> Mike
>
>
>
Thread
Slow ALTER TABLE on 70M row InnoDB tableXn Nooby18 Aug
  • Re: Slow ALTER TABLE on 70M row InnoDB tablemos18 Aug
    • Re: Slow ALTER TABLE on 70M row InnoDB tableXn Nooby18 Aug
      • Re: Slow ALTER TABLE on 70M row InnoDB tablemos18 Aug
        • Re: Slow ALTER TABLE on 70M row InnoDB tableXn Nooby18 Aug
        • Re: Slow ALTER TABLE on 70M row InnoDB tableXn Nooby18 Aug
        • Re: Slow ALTER TABLE on 70M row InnoDB tableXn Nooby18 Aug
          • Re: Slow ALTER TABLE on 70M row InnoDB tableEric Bergen21 Aug
  • RE: Slow ALTER TABLE on 70M row InnoDB tableTravis Ard18 Aug