How do you properly repair mysql database?

PvUtrix

Well-Known Member
Mar 12, 2005
58
1
156
Ekaterinburg - Russia
cPanel Access Level
Root Administrator
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.
apache_processes-day.png
mysql_threads-day.png

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?
 
Last edited:

cPanelTristan

Quality Assurance Analyst
Staff member
Oct 2, 2010
7,607
40
248
somewhere over the rainbow
cPanel Access Level
Root Administrator
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:

Code:
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!
 
Thread starter Similar threads Forum Replies Date
cPanelRyanR Workarounds and Optimization 8