MySQL Forums :: MyISAM :: Optimize Table Taking Forever

Advanced Search

Re: Optimize Table Taking Forever
Posted by: Rick James ()
Date: April 30, 2012 12:32AM

> I did some research and found out there is no proper way to check the status of an optimize table.
Newer versions of MySQL/MariaDB have a "% finished" indicator in SHOW PROCESSLIST. However, it is of limited use because the progress is not necessarily linear.

Note: OPTIMIZE creates a new set of files, with names beginning with ".". "ls -al" lets you see such. Those files should have been growing. At the end, there would have been a very quick rename to put the new files in place.

OPTIMIZE, ALTER, and REPAIR each take about the same amount of time. Since it is "crashed", you need REPAIR.

> Instead, I will create a new table and copy across the data, then drop the previous one. Maybe that will work out better since the proper way of doing it doesn't seem to work!
That's essentially what the "proper way" does.

I cannot explain why it went bad. The kill should have been safe.

Is there any chance you ran out of disk space?

> free up space after deleting lots of records in a big table
Did SHOW TABLE STATUS (beforehand) indicate that Data_free was a large percentage of Data_length?

Options: ReplyQuote

Subject Views Written By Posted
Optimize Table Taking Forever 8301 Thomas Cleveland 04/27/2012 03:03AM
Re: Optimize Table Taking Forever 7448 Thomas Cleveland 04/28/2012 04:17PM
Re: Optimize Table Taking Forever 5039 Rick James 04/30/2012 12:32AM
Re: Optimize Table Taking Forever 4804 Thomas Cleveland 04/30/2012 03:52AM
Re: Optimize Table Taking Forever 3870 Thomas Cleveland 04/30/2012 10:12AM
Re: Optimize Table Taking Forever 3242 Rick James 05/01/2012 10:14AM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.