Re: Optimize Table Taking Forever
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?
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.