Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

migrated from stackoverflow.com Jan 16 '12 at 15:32

This question came from our site for professional and enthusiast programmers.

4  
MyISAM? InnoDB? If InnoDB, is file-per-table turned on? –  Charles Jan 16 '12 at 1:03
    
Great question - best thing I can think of instantly is to make a rough guess using the size of the raw temporary and actual files - will do some more research though –  Adrian Cornish Jan 16 '12 at 1:04
    
Please SHOW CREATE TABLE tblname\G on the table and tell us the index you want to make. –  RolandoMySQLDBA Jan 16 '12 at 21:02

4 Answers 4

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.

share|improve this answer
    
Unfortunatly I could not found any tmp table. I use innoDB, with innodb_file_per_table set to OFF. –  Atai Voltaire Jan 16 '12 at 7:59

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.

share|improve this answer

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.

share|improve this answer
    
thanks for your feedbacks. for now, I decided to kill the query and try again after upgrading to the last mysql version. hopefully it will go more smoothly that way. –  Atai Voltaire Jan 17 '12 at 22:48

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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.