MySQL Forums
Forum List  »  MyISAM

Re: Optimize Table Taking Forever
Posted by: Thomas Cleveland
Date: April 28, 2012 04:17PM

Well after 47 hours of waiting, and no advice from here, the situation stayed the same so I had to sort it out. But I'll update this in-case anyone with the same problem ever comes across it.

I did some research and found out there is no proper way to check the status of an optimize table. You can look at the files on the disk for a clue, since it actually copies the data files into a new file, but the files on the disk didn't seem to be changing in size and the CPU and disk IO were still low so I don't think it was doing anything.

I issued a kill command in the mysql console which has stopped the optimize and, as the mysql documentation says it would, crashed the table. The 'show table status' shows everything as NULL except comment which is:

Table './<dbname>/<table name>' is marked as crashed and last (automatic?) r

I don't know what 'and last (automatic?) r' means. Fixing it would give the same problem of not knowing what the heck it's doing or how long it's going to take, so I'm not going to bother. Fortunately my replicant is still OK so I still have the data.

Next time I want to free up space after deleting lots of records in a big table, I am not going to follow the manual. 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!

Options: ReplyQuote

Written By
April 27, 2012 03:03AM
Re: Optimize Table Taking Forever
April 28, 2012 04:17PM
April 30, 2012 12:32AM
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.