The Community Forums

Interact with an entire community of cPanel & WHM users!
  1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

How do you properly repair mysql database?

Discussion in 'Workarounds and Optimization' started by PvUtrix, Apr 7, 2012.

  1. PvUtrix

    PvUtrix Well-Known Member

    Joined:
    Mar 12, 2005
    Messages:
    57
    Likes Received:
    0
    Trophy Points:
    6
    Location:
    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?
     
    #1 PvUtrix, Apr 7, 2012
    Last edited: Apr 8, 2012
  2. PvUtrix

    PvUtrix Well-Known Member

    Joined:
    Mar 12, 2005
    Messages:
    57
    Likes Received:
    0
    Trophy Points:
    6
    Location:
    Ekaterinburg - Russia
    cPanel Access Level:
    Root Administrator
    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?
     
  3. cPanelTristan

    cPanelTristan Quality Assurance Analyst
    Staff Member

    Joined:
    Oct 2, 2010
    Messages:
    7,623
    Likes Received:
    21
    Trophy Points:
    38
    Location:
    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!
     

Share This Page