So I've been building up over time a database, and unfortunately I was using MyISAM which didn't have any problems until the database became huge.
After massive CPU spikes, I created a script to OPTIMIZE all the tables, and ran it. And everything was fixed and working fine and not using nearly as much CPU. This was done through PHP. I also have a jailed shell that I can use to access the database.
The next thing I did was really stupid. I took this working database and decided to run a REPAIR operation on all the tables. For most tables the operation was successful and returned a success status, however it timed out and broke several of the tables. The remaining tables that weren't working show as 'in use' in PHPMyAdmin and when I try to perform any operation on those tables it says 'table is marked as crashed and last (automatic?) repair failed'.
A couple of the tables recovered fully by simply repeating the REPAIR operation. Over time by running REPAIR operations over and over I managed to recover most of those tables. However, there is one table which is still timing out always. I have access to run queries via PHP, PHPMyAdmin, or the jailed shell, all of which time out, as well as cPanel. The CHECK operation also times out. I am certain that the actual data is fine, and that the operations are simply being killed before they can finish by some higher-level restriction. (All of which I do not have access to change.) I'm pretty sure the data is probably even just fine and it's an in-use flag somewhere which was set because the operation didn't finish. Obviously the export function of PHPMyAdmin does not work in this scenario. What can I do to get this data off the server and attempt a proper recovery?
Best Answer
First, I need to ask "Does the
OPTIMIZE
actually help?" I ask because it is very rare that it makes enough difference to bother with. Please describe the activity on the table. One case that might need the optimize is a huge amount of deletes and/or updates (with different lengths). If the deletes are of "old" data, then we should discuss partitioning as a much better way.What setting do you have for
myisam_recover_options
? That would let the server deal with theREPAIR
automatically.You really should consider changing to InnoDB which will mostly eliminate the need for
OPTIMIZE
andREPAIR
. Also MyISAM is going away.On another topic,... Have you identified the query/queries that are involved in the CPU spike? It could be that we can speed them up enough to avoid the problem. Sometimes it is as simple as adding a suitable composite index.