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.

MYSQL High CPU Usage

Discussion in 'Workarounds and Optimization' started by singh9211, Jan 27, 2015.

  1. singh9211

    singh9211 Member

    Joined:
    Jan 18, 2011
    Messages:
    15
    Likes Received:
    0
    Trophy Points:
    1
    Hello,

    I am in great trouble, my cpu usage is very high because of mysql, average load remains at 6.71 5.91 5.23
    There is not much traffic on my websites, i have recently migrated the websites to this new server from ovh, it has 6 cores and 32 gigs of ram, so ample of resources. Due this mysql high CPU usages my websites are opening very slow.

    I don't know what to do.

    Here is my /etc/my.cnf file content, i have tweaked it a little:

    Code:
    [mysqld]
    innodb_file_per_table=1
    open_files_limit=1000000
    local-infile=0
    #bind-address=127.0.0.1
    max_connections=160
    query_cache_size=512M
    join_buffer_size=1028K
    tmp_table_size=32M
    max_heap_table_size=32M
    table_open_cache=2000
    innodb_buffer_pool_size=500217728
    
    max_allowed_packet=268435456

    I also ran the mysqltunner and below is the output:

    Code:
    root@server3 [~]# perl mysqltuner.pl 
    
     >>  MySQLTuner 1.4.0 - Major Hayden <major@mhtx.net>
     >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
     >>  Run with '--help' for additional options and output filtering
    [OK] Currently running supported MySQL version 5.5.40-cll
    [OK] Operating on 64-bit architecture
    
    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MRG_MYISAM 
    [--] Data in MyISAM tables: 5G (Tables: 4859)
    [--] Data in InnoDB tables: 251M (Tables: 832)
    [--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
    [!!] Total fragmented tables: 54
    
    -------- Security Recommendations  -------------------------------------------
    [OK] All database users have passwords assigned
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 23h 21m 39s (11M q [131.578 qps], 115K conn, TX: 9B, RX: 2B)
    [--] Reads / Writes: 88% / 12%
    [--] Total buffers: 1.0G global + 3.6M per thread (160 max threads)
    [OK] Maximum possible memory usage: 1.6G (5% of installed RAM)
    [OK] Slow queries: 0% (3/11M)
    [OK] Highest usage of available connections: 62% (100/160)
    [OK] Key buffer size / total MyISAM indexes: 8.0M/684.0M
    [OK] Key buffer hit rate: 100.0% (45B cached / 136K reads)
    [OK] Query cache efficiency: 77.0% (7M cached / 10M selects)
    [OK] Query cache prunes per day: 0
    [OK] Sorts requiring temporary tables: 0% (7 temp sorts / 248K sorts)
    [!!] Joins performed without indexes: 15135
    [OK] Temporary tables created on disk: 0% (14K on disk / 1M total)
    [!!] Thread cache is disabled
    [!!] Table cache hit rate: 5% (2K open / 38K opened)
    [OK] Open file limit used: 0% (3K/1M)
    [OK] Table locks acquired immediately: 99% (11M immediate / 11M locks)
    [OK] InnoDB buffer pool / data size: 477.0M/251.3M
    [OK] InnoDB log waits: 0
    -------- Recommendations -----------------------------------------------------
    General recommendations:
        Run OPTIMIZE TABLE to defragment tables for better performance
        MySQL started within last 24 hours - recommendations may be inaccurate
        Enable the slow query log to troubleshoot bad queries
        Adjust your join queries to always utilize indexes
        Set thread_cache_size to 4 as a starting value
        Increase table_open_cache gradually to avoid file descriptor limits
        Read this before increasing table_open_cache over 64: http://bit.ly/1mi7c4C
    Variables to adjust:
        join_buffer_size (> 1.0M, or always use indexes with joins)
        thread_cache_size (start at 4)
        table_open_cache (> 2000)
    
    But still no success. So its affecting my bussiness a lot, i am loosing lot of customers, so any help is greatly apprectiated.

    Regards.
     
  2. wrender

    wrender Well-Known Member

    Joined:
    Sep 29, 2007
    Messages:
    69
    Likes Received:
    3
    Trophy Points:
    8
    I think setting your query cache too high like you have might cause problems. Maybe try these settings (below)? Or also try disabling query_cache by setting it to "query_cache_size=0". This seem to work for us with MySQL 5.6 with cPanel 11.46.x From a lot of blogs I read apparently query_cache can cause more performance problems than it is worth. Not sure how true this is.

    I am not sure about how high you can set the table_open_cache. I have read that there can be negative scalability, but so far increasing it for us seems to have improved performance greatly. I have opened another thread for this, and hoping someone provides some advice on this soon.

    Also, this script seems to also be helpful : https://launchpad.net/mysql-tuning-primer

    Code:
    [mysqld]
    open_files_limit=1000000
    local-infile=0
    #bind-address=127.0.0.1
    max_connections=160
    query_cache_size=16M
    join_buffer_size=1M
    
    tmp_table_size=64M
    max_heap_table_size=64M
    table_open_cache=5000
    table_definition_cache=3000
    
    innodb_file_per_table=1
    innodb_buffer_pool_size=3G
    innodb_flush_method=O_DIRECT
    
    max_allowed_packet=268435456
    
     
  3. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    30,854
    Likes Received:
    676
    Trophy Points:
    113
    cPanel Access Level:
    Root Administrator
    Hello :)

    Feel free to let us know if the advice from the previous thread was helpful. You may want to let MySQL run for another 24 hours and run the tuner again.

    Thank you.
     
Loading...

Share This Page