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