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.

Extreme disk writes and server IO load from mysqld.

Discussion in 'Workarounds and Optimization' started by Sir William, Feb 16, 2011.

  1. Sir William

    Sir William Member

    Joined:
    Jul 24, 2006
    Messages:
    8
    Likes Received:
    0
    Trophy Points:
    1
    Greetings. I have a recently setup install running on my new dedicated server. The server is a Xeon 3470 2.93GHz, 8GB Ram, dual 500GB SAS drives in RAID 1. I'm running VMWare ESXi 4.1 on it, but am only running two virtual servers. Each has 4 cores, 4GB ram and 250GB drive space allocated (90GB actually used).

    I migrated 67 domains to it from my old server which was a lot slower but ran basically fine overall. For some reason that I can't find, the mysqld process is writing HUGE amounts of data to the drive which pegs IOWaits and keeps the load up from 3-8 constantly. I can monitor the writes via the vSphere management tool and it's averaging over 10,000KB/s spiking to over 35,000KB/s. I can restart the machine and it's fine for a while but then starts going nuts. I can't seem to track it down to any particular query or database. I've been tuning MySQL for the past couple of days and don't see anything out of the ordinary, but something is forcing the MySQL Daemon to just go nuts writing to disk. I can stop Apache and MySQL, and the load dies off almost instantly, but that's obviously not a solution. Does anybody have any ideas?

    WHM/cPanel: WHM 11.28.83 - CENTOS 5.5 x86_64 vmware
    Apache: 2.2.7
    PHP: 5.2.16
    MySQL: 5.1.54-log
     
  2. LinuxTechie

    LinuxTechie Well-Known Member

    Joined:
    Jan 22, 2011
    Messages:
    502
    Likes Received:
    2
    Trophy Points:
    18
    cPanel Access Level:
    Root Administrator
    Hello Sir,

    It seems a fine mysql tweaking will resolve the load issue.

    You could try running the following script to see the recommendations it provides:

    Code:
    /usr/local/cpanel/3rdparty/mysqltuner/mysqltuner.pl
    You need to modify your /etc/my.cnf according to the mysqltuner results.

    Next, you can optimize all databases with this command:

    Code:
    mysqlcheck -u root -p --auto-repair --check --optimize --all-databases
    Restart MySQL after you've done the above:

    Code:
    /etc/init.d/mysql restart
    Please wait 24 hours and then re-run mysqltuner.pl again on the machine to see any other variable changes to make.
     
  3. Sir William

    Sir William Member

    Joined:
    Jul 24, 2006
    Messages:
    8
    Likes Received:
    0
    Trophy Points:
    1
    I appreciate the recommendations and should have probably said I've done all that already. This isn't my first rodeo, and unlike many others, posting for help is usually my last resort. I've been searching, reading and tweaking on this for days trying to arrive at a solution.
     
  4. 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
    When you note it is writing huge amounts of data to the drive, have you tried enabling the general log for MySQL to see what data it is writing? If not, I suggest doing that, then I suggest using mysqlsla (mysqlsla :: Parse, filter and sort MySQL slow, general and binary logs) to go through the general log to determine the 10 highest database users in it.

    You might also want to enable the slow query log to see what might be logging there as well.

    If you aren't certain how to enable the general log or slow query log for your version of MySQL, please let us know the MySQL version and I can provide the details on how to accomplish that task.
     
  5. Sir William

    Sir William Member

    Joined:
    Jul 24, 2006
    Messages:
    8
    Likes Received:
    0
    Trophy Points:
    1
    Ok, in experimenting today, I came to an odd realization. The server runs great for a while after a reboot but then starts with the extreme IO load. I installed atop to monitor what each process was writing to the drive and mysqld is not writing more after the load goes nuts than before. After a LOT more reading today, it came to me that as the OS cache fills up, the performance starts dropping. So any time the load goes nuts, I can tell the OS to sync then clear the cache and the heavy write problems go away completely and everything comes back to normal. I've played with the 'swappiness' setting, but am unsure if that's going to be the solution.

    I've also read some things that indicate that CentOS can be finicky running under VMWare...this may be my issue. However I'm still not 100% sure how to make it behave consistently.
     
Loading...

Share This Page