Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I'm trying to optimize a table that hasn't been optimized in a long time. It has around 2.5 million rows with 1.3 GB of data and a 152 MB index. I started optimizing it about 15 minutes ago and I have no idea how long it will take.

The server is reasonably robust (quad xeon with 4GB ram) and has a 500MB innodb buffer pool size.

Should I expect this to take minutes, hours or days?

share|improve this question
add comment

3 Answers

It depends a lot on what the status of the data in the table is and your hardware, but a typical upper bound on innodb alter table operations is the time needed to copy the whole table + the time needed to sort the index.

Have you made sure you are not running out of disk space?

share|improve this answer
add comment

The answer, apparently, was 1 hour, 20 minutes. I'm not sure if that's a "normal" time or if it was delayed by some inefficiency in my setup.

share|improve this answer
    
Seems slow to me. What's the disk config? –  James Apr 10 '10 at 0:02
add comment

I am not yet sure why, but sometimes such operations take hours. I witnessed a 2-hour-long run and heard about an 8-hour-long one. To alter a table, MySQL has not only to copy it to a new one, but also to re-index it. This is where it usually spends most of the time. If you check PROCESSLIST, it will most probably say it's creating index or sorting index.

It's not normal, but it happens, unfortunately.

share|improve this answer
add comment

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.