0

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?

0

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 the REPAIR automatically.

You really should consider changing to InnoDB which will mostly eliminate the need for OPTIMIZE and REPAIR. 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.

  • Thanks Rick! Yes the first optimize did help. It had a pretty big impact. The tables had not been previously optimized for over a year, and there were multiple TEXT/BLOB fields. That cut it down considerably. However, when I ran the REPAIR, the tables broke. In addition, I was able to get into a state where the CPU was maxed out and that continued for over 24 hours. After the repair 7 tables were unusable, a number which I managed to get down to a few over time by more REPAIR operations. – azoundria Apr 29 '17 at 1:17
  • What finally appeared to fix the CPU problem and bring that to reasonable level was avoiding using the large tables with tons of entries (largest had 34 million) and changing the cPanel password to close down the PHPMyAdmin processes that were still running and maxing out the CPU. The large tables were all recreated, with the old ones renamed. One of the tables was critical because it tracks errors, so each time there was a problem with another table it would try to INSERT there. – azoundria Apr 29 '17 at 1:17
  • From there, I copied data from one table I use regularly into a new paritioning system, with one table for each quarter, and it switches tables automatically every 3 months. However, I in the end needed to get support to prioritize my ticket for one of the tables, which was accomplished by requesting the .frm files so I could extract the data and restore myself. (I didn't get the files, but they bumped the request to higher priority.) – azoundria Apr 29 '17 at 1:17
  • In the end, I'm still convinced that the issue is as I originally understood it to be. The REPAIR operation locks the table while it is in progress. However, before it can finish, the MySQL process was terminated for using too much CPU on a shared hosting environment. This left the table in an unusable state, which required someone with admin-level access to resolve. So the moral of the story is - don't try to REPAIR large tables unless there's actually a problem. – azoundria Apr 29 '17 at 1:18
  • I do currently use indexing extensively, even fields I don't think I'll need to search often I index. I ran into that roadblock previously, and indexes extended the lifespan of this database from weeks into multi-year territory. I've been looking into InnoDB quite a bit and thinking of using it for certain tables at least as a start. As I understand, you can mix and match and have some tables InnoDB and others MyISAM, which should help that transition. – azoundria Apr 29 '17 at 1:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.