How do you properly repair mysql database?

[PvUtrix]

Hello everyone!

A table about 750mb in size became in need of repairs. I have started the process, it's a lengthy one. A query "Repair by sorting" is running for many hours. The table is from a phpBB forum, I have disabled the forum from it's CP, but then a lot of mysql and apache processes started appearing waiting for this table indefinetly.
They went out of control as you can see from the attached pictures. To resolve this I just renamed the table which needed to be repaired which is casing ugly errors for phpBB but there are no hung processes any more.



My apache Timeout was set to 300, but the apache processes were not killed after 5 minutes for sure, they just kept pilling up, eating up all the resources. I've played around with some settings and restarted the process a few times, only after I have changed the table name did the server return to normal.

Is there some way to configure apache or MySQL to automatically kill processes which are waiting for a table after some time to avoid the uncontroller process growth?

 

[PvUtrix]

Also I have noticed that my /tmp is maxed out. Repair query seems to be still going? How can I check that it's really working and not hung because of insufficient space available in /tmp?

 

[cPanelTristan]

Hello,

If the reason everything was maxed out was due to space in /tmp filling up, then Apache will hang along with MySQL. You can grep for this in the error log for MySQL to see if that was happening:

grep -i "incorrect key" /var/lib/mysql/*.err

The best way to prevent this from happening would be to move the tmpdir location for MySQL to somewhere else as discussed in this post:

http://forums.cpanel.net/f5/drive-critical-dev-loop0-var-tmp-97-full-190772.html#post799162

Thanks!