Can I extrapolate from the progress reporting in MyISAM ALTER?

Question

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.

Answer 1

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.

Answer 2

The progress report is very likely to be non-linear. This is because of the way caching and sorting work.

Initially, everything can happen in RAM, and this is much faster than on disk. As things progress, I/O needs to be performed. Then the I/O gets more involved.

Also, there is an issue of whether it decides to build the index(es) in RAM (in the key_buffer) versus throwing things on disk and using the OS's sort command ("Repair by sorting"). (The latter is faster for huge tables.) Either way, the progress is non-linear. The key_buffer eventually fills up and thrashes. The sort is Order(N * log N).

I don't think there is much parallelism when adding multiple indexes in a single ALTER. However, batching the ADD INDEXes saves reading the data multiple times. So, you were right to do a single ALTER. Newer versions of InnoDB have algorithm=online wherein a single INDEX can be added with much less impact on the table. In this case, doing the ADDs separately may be better.

I agree with Rolando on getting rid of the redundant indexes. INDEX(c,e) can handle "WHERE c=12" (and no mention of e).