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?
Subject
Views
Written By
Posted
17592
April 27, 2012 03:03AM
12305
April 28, 2012 04:17PM
Re: Optimize Table Taking Forever
8800
April 30, 2012 12:32AM
7889
April 30, 2012 03:52AM
6351
April 30, 2012 10:12AM
5570
May 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.