Results 1 to 13 of 13
  1. #1
    Join Date
    Apr 2010
    Posts
    260

    High Server Load due to MySQL

    Hello,

    this is several days that my server load is too high because MySQL uses more than 120% of the CPU !

    I updated mysql and also restarted it several times but it doesn't fixed the issue.

    Someone told me I have to move to litespeed. But I'm not sure whether the litespeed could solve my problem or not

    Do you think so ?
    Please help me to make the best decision.

    My server details:
    Xeon X3440 2.53GHz + 8MB Cache
    8GB DDR3 RAM
    Centos 5.5 64bit
    Apache latest version
    Cpanel latest version

  2. #2
    Join Date
    Apr 2009
    Location
    inside wht
    Posts
    716
    What application you running in your server ? Can you show the my,cnf settings ?
    24x7 PRO-ACTIVE SERVER MANAGEMENT | OUTSOURCED WEB HOSTING SUPPORT
    Now Available The first Support Brand for Startup Hosting Companies

    Sales : sales @ syslint.com | Skype us : SyslintSkype | Call us : (+91)6007799

  3. #3
    Join Date
    Jul 2009
    Location
    UK
    Posts
    1,308
    Id also suggest maybe running something like mtop ( http://mtop.sourceforge.net/ )

    It can be as simple as a dodgy query taking too long to execute.. Or a bad index somewhere.
    Live Chat Support Software for your Business website - IMsupporting.com

  4. #4
    Join Date
    Nov 2009
    Location
    Cincinnati
    Posts
    1,583
    Grab mysql tuner and run it. You will most likely need to tune out your cache settings and tmp table settings.

    http://blog.mysqltuner.com/

    When the mysql load is high open the mysql prompt.

    mysql
    show processlist;

    If it says copying to temp table its having a hard time with ram allocation.

    Also, what is the drive configuration in the server? Could just be a heavy amount of IOWait.


    Like it matters what software hes running lol.
    'Ripcord'ing is the only way!

  5. #5
    Join Date
    Apr 2010
    Posts
    260
    Quote Originally Posted by Syslint View Post
    What application you running in your server ? Can you show the my,cnf settings ?
    It's a hosting server and a lot of applications are running on it. my.cnf content is as the below:
    Code:
    #DO NOT MODIFY THE FOLLOWING COMMENTED LINES!
    #Created with ELS from www.servermonkeys.com
    #els-build=5.0
    [mysqld]
    local-infile=0
    datadir=/var/lib/mysql
    skip-locking
    skip-innodb
    skip-networking
    safe-show-database
    query_cache_limit=1M
    query_cache_size=32M ## 32MB for every 1GB of RAM
    query_cache_type=1
    max_user_connections=200
    max_connections=500
    interactive_timeout=10
    wait_timeout=20
    connect_timeout=20
    thread_cache_size=128
    key_buffer=128M ## 128MB for every 1GB of RAM
    join_buffer=1M
    max_connect_errors=20
    max_allowed_packet=16M
    table_cache=1024
    record_buffer=1M
    sort_buffer_size=1M ## 1MB for every 1GB of RAM
    read_buffer_size=1M ## 1MB for every 1GB of RAM
    read_rnd_buffer_size=1M  ## 1MB for every 1GB of RAM
    thread_concurrency=16 ## Number of CPUs x 2
    myisam_sort_buffer_size=64M
    server-id=1
    collation-server=latin1_general_ci
    
    [mysql.server]
    user=mysql
    
    [safe_mysqld]
    err-log=/var/log/mysqld.log
    pid-file=/var/lib/mysql/mysql.pid
    open_files_limit=8192
    
    [mysqldump]
    quick
    max_allowed_packet=16M
    
    [mysql]
    no-auto-rehash
    #safe-updates
    
    [isamchk]
    key_buffer=64M
    sort_buffer=64M
    read_buffer=16M
    write_buffer=16M
    
    [myisamchk]
    key_buffer=64M
    sort_buffer=64M
    read_buffer=16M
    write_buffer=16M
    
    [mysqlhotcopy]
    interactive-timeout
    Quote Originally Posted by lynxus View Post
    Id also suggest maybe running something like mtop ( http://mtop.sourceforge.net/ )

    It can be as simple as a dodgy query taking too long to execute.. Or a bad index somewhere.
    I don't know what exactly it is !
    Quote Originally Posted by Visbits View Post
    Grab mysql tuner and run it. You will most likely need to tune out your cache settings and tmp table settings.

    http://blog.mysqltuner.com/

    When the mysql load is high open the mysql prompt.

    mysql
    show processlist;

    If it says copying to temp table its having a hard time with ram allocation.

    Also, what is the drive configuration in the server? Could just be a heavy amount of IOWait.


    Like it matters what software hes running lol.
    Can you tell me more about mysql tuner ?
    I/O wait is fine (less than 3)

  6. #6
    Join Date
    Apr 2007
    Posts
    3,513
    My guess would be that your disks are causing the issue.

    Whats your HDD spec?
    - Buying up websites, side-projects and companies - PM Me! -

  7. #7
    Join Date
    Apr 2010
    Posts
    260
    What specs you mean?
    I'm using 4x 500GB SATA2 with RAID 10

  8. #8
    Mysql tuner suggest you settings for your my.cnf file according to your server configuration. You have to make changes in that file. Did you try to install mytop and tried to know which database is causing load. You could try to optimize queries of that db.

  9. #9
    Join Date
    Apr 2009
    Location
    inside wht
    Posts
    716
    You can use mytop to see queries or use : mysqladmin proc stat " . Also try to decrease the value of sort_buffer_size to 512K or 256K to avoid the sort to death issue and eat up all memory ,

    Also you need to check you HDD as suggested by "iTom"
    24x7 PRO-ACTIVE SERVER MANAGEMENT | OUTSOURCED WEB HOSTING SUPPORT
    Now Available The first Support Brand for Startup Hosting Companies

    Sales : sales @ syslint.com | Skype us : SyslintSkype | Call us : (+91)6007799

  10. #10
    There is no 1 solution to this. It all depends on what MySQL is doing that is causing the high load.

    Look at the queries running at the time of high load using SHOW FULL PROCESSLIST. It's possible that adding indexes may solve this problem. If the queries causing high loads are SELECTS and the data is cache-able, look at caching that data using memcached or other query cache.

    You may also be disk constrained. Use iostat to determine utilization on the RAID 10 array. If this is the case, you may need to add more disks to the array and/or use faster disks like SAS 15K. If you have binary logging on, are you writing those log files to a separate set of disks?

    Best of luck

    Quote Originally Posted by Rezaa View Post
    Hello,

    this is several days that my server load is too high because MySQL uses more than 120% of the CPU !

    I updated mysql and also restarted it several times but it doesn't fixed the issue.

    Someone told me I have to move to litespeed. But I'm not sure whether the litespeed could solve my problem or not

    Do you think so ?
    Please help me to make the best decision.

    My server details:
    Xeon X3440 2.53GHz + 8MB Cache
    8GB DDR3 RAM
    Centos 5.5 64bit
    Apache latest version
    Cpanel latest version

  11. #11
    Join Date
    Aug 2005
    Location
    Durham, NC USA
    Posts
    195
    Also, what are you seeing in the log file. If this is something that has popped up recently, then, as mentioned above, it may well just be a broken table or query.

    If you're seeing a ton of errors in the log, at least you'll know where to start fixing things.
    Netmar Web Services - Reliable Web Hosting for 15 years
    Shared hosting, business hosting, domain name registration, and VMware virtual servers
    Specializing in Drupal hosting
    Call 866-363-8627 (toll-free) and ask about our Drupal accounts

  12. #12
    Join Date
    Mar 2003
    Location
    WebHostingTalk
    Posts
    16,960
    Moved > Hosting Security and Technology.
    Specially 4 You
    .
    JoneSolutions.Com ( Jones.Solutions ) is on the net 24/7 providing stable and reliable web hosting solutions and services since 2001

  13. #13
    Join Date
    Nov 2010
    Location
    Arizona
    Posts
    297
    You might want to consider same server MySQL replication:

    http://www.howtoforge.com/one_machine_mysql_replication

Similar Threads

  1. Wepapp down due to High cpu load on server
    By Steven314 in forum Dedicated Server
    Replies: 0
    Last Post: 11-08-2010, 06:07 AM
  2. High server load due to Search engine crawlers
    By powerplaybegins in forum Hosting Security and Technology
    Replies: 5
    Last Post: 06-30-2010, 05:22 PM
  3. May server crash due to High Load with low CPU and RAM usage?
    By Driimer in forum Hosting Security and Technology
    Replies: 9
    Last Post: 10-29-2009, 12:13 PM
  4. VPS server crashes due to high load - http connections?
    By ADD3AN in forum Hosting Security and Technology
    Replies: 10
    Last Post: 09-08-2009, 03:41 PM
  5. High server load due to I/O. What can I do?
    By Igotit in forum Hosting Security and Technology
    Replies: 14
    Last Post: 05-29-2006, 07:55 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •