Adding an index very slow...is there a mysql cmd to get an ETA or show progress?

Question

I'm currently running an alter query on my table (20M entries) in order to add an index. it's running for more than 3 days already (stuck on 'copy to tmp table').

is there a way I can see the progress of the query or in other word is there a way I can get an estimation time of completion ?

thanks.

Answer 1

When mysql alters a table, it essentially makes a copy of of it and then swaps the copy in. This way if you cancel the update in the middle, the table is still in a stable state. So you can look in the mysql data directory (/var/lib/mysql/ ?) to see how large the new file is, that will tell you how far along it is. This is a bit more difficult with Innodb, but there is a tmp table being created somewhere.

You can significantly reduce the amount of a time an index takes by increasing your sort buffer variables (myisam_sort_buffer_size, sort_buffer_size). Make those as large as you can with memory you have. You could cut a few days off of the modification time, even get it down to a few hours depending on how much memory you have. I've done a 150M record table in about 3 hours.

Answer 2

Since innodb_fle_per_table is OFF, you cannot see the table and measure it progress.

I made an earlier post about how to do this for MyISAM. You can do this for InnoDB if and only if innodb_file_per_table was enabled and you rearchitect the InnoDB infrastructure. It still requires looking in the operating system at the sizes of the files in question.

Once you have fully implemented the InnoDB Cleanup and you have enabled innodb_file_per_table, you may want to perform the index updating as follows:

EXAMPLE you have the following

• MySQL Instance with /var/lib/mysql as datadir
• InnoDB table called db.lotsofdata with 20 million names:

The table looks like this:

CREATE TABLE db.lotsofdata
(
    id INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(30),
    PRIMARY KEY (id)
) ENGINE=InnoDB;

Suppose you want to create a name index. You can do this:

CREATE TABLE db.lotsofdata_new LIKE db.lotsofdata;
ALTER TABLE db.lotsofdata_new ADD INDEX (name);
INSERT INTO db.lotsofdata_new SELECT * db.lotsofdata;
ALTER TABLE db.lotsofdata RENAME db.lotsofdata_old;
ALTER TABLE db.lotsofdata_new RENAME db.lotsofdata;
TRUNCATE TABLE db.lotsofdata_old;
ALTER TABLE db.lotsofdata_old ENGINE=InnoDB;
DROP TABLE db.lotsofdata_old;

While the INSERT is running, you go in the operating system and run this:

cd /var/lib/mysql/db
watch ls -l lotsofda*.ibd

This will give you a listing of the current size of lotsofdata_new.ibd. When it gets bigger than lotsofdata.ibd, then you know you are close to completion.

BTW MariaDB has a Progress Status implemented internally.

Answer 3

There is no reliable way to see progress of an index creation. If you had innodb-file-per-table on, you could get some indication by looking at the temporary .ibd file that is being created in the data directory and compare it to the size of the current file, but that isn't very reliable, as your current datafile could be bloated due to deletes/fragmentation and the new data file will have an additional index that your previous one didn't.

It sounds like you are using a version of MySQL without InnoDB plugin installed. InnoDB plugin has fast index creation which does not require a complete rebuild of the table for index additions and deletions. InnoDB plugin is enabled by default in MySQL 5.5 and available with a configuration setting in 5.1.38 and later.

• What version of MySQL are you using?

Percona Server back quite a ways (pre 5.5) has InnoDB Plugin enabled by default.

Answer 4

pt-online-schema-change by Percona shows remaining time estimate. By default it prints out the remaining time estimate and progress percentage every 30 seconds.

It also has additional functions compared to just running the ALTER command by itself.

http://www.percona.com/doc/percona-toolkit/2.1/pt-online-schema-change.html