Re: Optimize Table Taking Forever
Hi Rick,
Thanks for your help.
I was keeping a fairly close eye on the disk space and I don't think it ever went below 10 gigs of space free.
I didn't check the data_free value for the table, but I had deleted more than half of the records in the table and the free disk space for that server was unchanged, so I assume that it was about 50% of the data_length.
I have today realised that after I killed it, the optimize table query went into the binary log so now my replicant is doing the same thing! It shows the same state (Repair by sorting) and this is 35 hours since it started according to the time value in the show processlist. This of course means that my replicant is 35 hours behind on other data.
I ran a "kill query <num>" about 25 minutes ago, but it's still running. I used "kill query" instead of a normal kill because I thought that might break the replication. Would you suggest I try a normal kill, or just give it more time for the kill query to take effect?
I know that kill sets a flag that is only checked periodically, but I expected the query to be killed by now.
I didn't realise it used files with '.' so I didn't use the -a flag on the ls to see hidden files. I've just had a look on my replicant (with -a), and the files I have with the table name are .frm, .MYD, .TMD, .MYI and a .BAK.
I am on a slightly old version (5.1.55) so I'll have to look into upgrading. I would also like to switch the tables to innodb as this would hopefully reduce problems like this, but unfortunately there are hundreds of them with a lot of data, so that's no easy task.
Subject
Views
Written By
Posted
17651
April 27, 2012 03:03AM
12321
April 28, 2012 04:17PM
8815
April 30, 2012 12:32AM
Re: Optimize Table Taking Forever
7904
April 30, 2012 03:52AM
6362
April 30, 2012 10:12AM
5585
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.