Mysql – extrapolate from the progress reporting in MyISAM ALTER

myisamMySQLperformance

I'm migrating a 300 million row MyISAM table to add some indexes.

Just because it might be relevant, here's my ALTER statement:

ALTER TABLE my_table
    ADD UNIQUE INDEX my_table_abcd (a, b, c, d), 
    ADD INDEX my_table_abc (a, b, c), 
    ADD INDEX my_table_c (c), 
    ADD INDEX my_table_ce (c, e), 
    ADD INDEX my_table_d (a);

It currently says 

Stage: 1 of 2 'copy to tmp table'   74.8% of stage done

and the processlist says that the progress of the ALTER statement is 37.440.

I don't remember the exact timings, but the 'Stage 1' value jumped up to 60% in the first hour or so, and has been I crawling up to 70% in the last 12 hours. In the lat 3 hours it has increased 1.5%.

I have a decision to make about whether I abort this and try changing some configuration settings or let it run. It's not linear based on previous observations. If it's slowing down exponentially then it's a waste to let it continue when I could be trying another approach.

Any ideas for bases on which to make a decision?

NB this question is relevant but not a duplicate. I'm not asking about predicting the time, I'm asking about whether the progress report has a linear relationship with time.

Best Answer

You have redundant indexes

Instead of your ALTER TABLE

ALTER TABLE my_table
    ADD UNIQUE INDEX my_table_abcd (a, b, c, d), 
    ADD INDEX my_table_abc (a, b, c), 
    ADD INDEX my_table_c (c), 
    ADD INDEX my_table_ce (c, e), 
    ADD INDEX my_table_d (a);

you only need to two of the indexes

ALTER TABLE my_table
    ADD UNIQUE INDEX my_table_abcd (a, b, c, d), 
    ADD INDEX my_table_ce (c, e);

To verify this, download pt-duplicate-key-checker and run it against your table, and you will see its recommendation to remove those three redundant indexes. See my post Mysql might have too many indexes on how to do this.

BTW, that old post of mine you cited no longer applies because MySQL now applies all structural changes into a single temp table before loading the data into it.

As for the operation itself, please abort it and start over with my shortened version.

Related Question