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.

help setting my.cnf, mysql 100% cpu usage

Discussion in 'Workarounds and Optimization' started by bou3lam, Apr 1, 2013.

  1. bou3lam

    bou3lam Active Member

    Joined:
    Sep 29, 2009
    Messages:
    36
    Likes Received:
    1
    Trophy Points:
    8
    hi
    please someone help set my.cnf for my box, currently its seems i dont have a correct one :
    Code:
    root@server [/]# cat /etc/my.cnf
    [mysqld]
    innodb_file_per_table=1
    local-infile=0
    open_files_limit=10192
    root@server 
    
    i have run the mysqltuner :

    Code:
    root@server [/usr/local/cpanel/3rdparty/mysqltuner]# /usr/local/cpanel/3rdparty/mysqltuner/mysqltuner.pl
    
     >>  MySQLTuner 1.2.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
    
    -------- General Statistics --------------------------------------------------
    [--] Skipped version check for MySQLTuner script
    [OK] Currently running supported MySQL version 5.1.68-cll
    [OK] Operating on 64-bit architecture
    
    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster 
    [--] Data in MyISAM tables: 381M (Tables: 1648)
    [--] Data in InnoDB tables: 224K (Tables: 14)
    [--] Data in MEMORY tables: 124K (Tables: 4)
    [!!] Total fragmented tables: 64
    
    -------- Security Recommendations  -------------------------------------------
    [OK] All database users have passwords assigned
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 6d 18h 23m 10s (143M q [246.019 qps], 1M conn, TX: 32B, RX: 14B)
    [--] Reads / Writes: 98% / 2%
    [--] Total buffers: 34.0M global + 2.7M per thread (151 max threads)
    [OK] Maximum possible memory usage: 449.2M (2% of installed RAM)
    [OK] Slow queries: 0% (95/143M)
    [OK] Highest usage of available connections: 43% (65/151)
    [OK] Key buffer size / total MyISAM indexes: 8.0M/223.1M
    [OK] Key buffer hit rate: 100.0% (301B cached / 52M reads)
    [!!] Query cache is disabled
    [OK] Sorts requiring temporary tables: 1% (53K temp sorts / 4M sorts)
    [!!] Joins performed without indexes: 12349
    [OK] Temporary tables created on disk: 1% (47K on disk / 2M total)
    [!!] Thread cache is disabled
    [!!] Table cache hit rate: 0% (64 open / 7M opened)
    [OK] Open file limit used: 1% (112/10K)
    [OK] Table locks acquired immediately: 99% (148M immediate / 148M locks)
    [OK] InnoDB data size / buffer pool: 224.0K/8.0M
    
    -------- Recommendations -----------------------------------------------------
    General recommendations:
        Run OPTIMIZE TABLE to defragment tables for better performance
        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_cache gradually to avoid file descriptor limits
    Variables to adjust:
        query_cache_size (>= 8M)
        join_buffer_size (> 128.0K, or always use indexes with joins)
        thread_cache_size (start at 4)
        table_cache (> 64)
    
    
    thank you
     
  2. bou3lam

    bou3lam Active Member

    Joined:
    Sep 29, 2009
    Messages:
    36
    Likes Received:
    1
    Trophy Points:
    8
    anyone please ? my cpu is always and ever 100%
     
  3. storminternet

    storminternet Well-Known Member

    Joined:
    Nov 2, 2011
    Messages:
    462
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    Set the value for query_cache_size is equal to 8 M or more than that. Run mysqladmin pr on shell and see which database is having more process list. Accordingly either optimize it or disable it and then monitor your server's performance.
     
  4. ES - George

    ES - George Well-Known Member
    PartnerNOC

    Joined:
    Jun 12, 2011
    Messages:
    142
    Likes Received:
    1
    Trophy Points:
    16
    Location:
    UK
    cPanel Access Level:
    Root Administrator
    [mysqld]
    innodb_file_per_table=1
    local-infile=0
    open_files_limit=10192
    query_cache_size=8M
    join_buffer_size=128K
    thread_cache_size=4
    table_cache=64

    Then restart MySQL

    I suggest running mysqlcheck -aor after to optimise and repair all tables
     
  5. bou3lam

    bou3lam Active Member

    Joined:
    Sep 29, 2009
    Messages:
    36
    Likes Received:
    1
    Trophy Points:
    8
    seems working :) thanks a lot, load of mysql now is never up to 5% cpu ! will check tomorrow
     
  6. ES - George

    ES - George Well-Known Member
    PartnerNOC

    Joined:
    Jun 12, 2011
    Messages:
    142
    Likes Received:
    1
    Trophy Points:
    16
    Location:
    UK
    cPanel Access Level:
    Root Administrator
    Great to hear. I suggest re-running the mysqltuner script after 24 hours of mysql uptime, and re-posting the results here so we can give you further advice.
     
  7. bou3lam

    bou3lam Active Member

    Joined:
    Sep 29, 2009
    Messages:
    36
    Likes Received:
    1
    Trophy Points:
    8
    hi . the load yesterday and today was 26% i guess its normal , here the mysql tuner stats and recommendations :

    Code:
     >>  MySQLTuner 1.2.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
    
    -------- General Statistics --------------------------------------------------
    [--] Skipped version check for MySQLTuner script
    [OK] Currently running supported MySQL version 5.1.68-cll
    [OK] Operating on 64-bit architecture
    
    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster 
    [--] Data in MyISAM tables: 391M (Tables: 1650)
    [--] Data in InnoDB tables: 5M (Tables: 332)
    [--] Data in MEMORY tables: 124K (Tables: 21)
    [!!] Total fragmented tables: 64
    
    -------- Security Recommendations  -------------------------------------------
    [OK] All database users have passwords assigned
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 2d 2h 33m 37s (22M q [125.373 qps], 555K conn, TX: 6B, RX: 2B)
    [--] Reads / Writes: 88% / 12%
    [--] Total buffers: 42.0M global + 2.7M per thread (151 max threads)
    [OK] Maximum possible memory usage: 457.2M (2% of installed RAM)
    [OK] Slow queries: 0% (0/22M)
    [OK] Highest usage of available connections: 15% (24/151)
    [OK] Key buffer size / total MyISAM indexes: 8.0M/229.0M
    [OK] Key buffer hit rate: 100.0% (47B cached / 16M reads)
    [OK] Query cache efficiency: 81.4% (16M cached / 19M selects)
    [!!] Query cache prunes per day: 422044
    [OK] Sorts requiring temporary tables: 0% (8K temp sorts / 883K sorts)
    [!!] Joins performed without indexes: 2767
    [OK] Temporary tables created on disk: 3% (12K on disk / 397K total)
    [OK] Thread cache hit rate: 99% (1K created / 555K connections)
    [!!] Table cache hit rate: 0% (64 open / 2M opened)
    [OK] Open file limit used: 0% (107/11K)
    [OK] Table locks acquired immediately: 99% (6M immediate / 6M locks)
    [OK] InnoDB data size / buffer pool: 5.3M/8.0M
    
    -------- Recommendations -----------------------------------------------------
    General recommendations:
        Run OPTIMIZE TABLE to defragment tables for better performance
        Enable the slow query log to troubleshoot bad queries
        Adjust your join queries to always utilize indexes
        Increase table_cache gradually to avoid file descriptor limits
    Variables to adjust:
        query_cache_size (> 8M)
        join_buffer_size (> 128.0K, or always use indexes with joins)
        table_cache (> 64)
    
    
    what do you think please
    many thanks :)
     
  8. bou3lam

    bou3lam Active Member

    Joined:
    Sep 29, 2009
    Messages:
    36
    Likes Received:
    1
    Trophy Points:
    8
    following :

    root@server [/]# /usr/local/cpanel/3rdparty/mysqltuner/mysqltuner.pl

    >> MySQLTuner 1.2.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

    -------- General Statistics --------------------------------------------------
    [--] Skipped version check for MySQLTuner script
    [OK] Currently running supported MySQL version 5.1.68-cll
    [OK] Operating on 64-bit architecture

    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
    [--] Data in MyISAM tables: 403M (Tables: 1769)
    [--] Data in InnoDB tables: 5M (Tables: 335)
    [--] Data in MEMORY tables: 124K (Tables: 23)
    [!!] Total fragmented tables: 71

    -------- Security Recommendations -------------------------------------------
    [OK] All database users have passwords assigned

    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 7d 20h 2m 27s (73M q [108.647 qps], 2M conn, TX: 23B, RX: 9B)
    [--] Reads / Writes: 88% / 12%
    [--] Total buffers: 42.0M global + 2.7M per thread (151 max threads)
    [OK] Maximum possible memory usage: 457.2M (2% of installed RAM)
    [OK] Slow queries: 0% (0/73M)
    [OK] Highest usage of available connections: 17% (26/151)
    [OK] Key buffer size / total MyISAM indexes: 8.0M/236.3M
    [OK] Key buffer hit rate: 100.0% (181B cached / 65M reads)
    [OK] Query cache efficiency: 75.7% (46M cached / 61M selects)
    [!!] Query cache prunes per day: 604669
    [OK] Sorts requiring temporary tables: 1% (34K temp sorts / 3M sorts)
    [!!] Joins performed without indexes: 12330
    [OK] Temporary tables created on disk: 3% (49K on disk / 1M total)
    [OK] Thread cache hit rate: 99% (10K created / 2M connections)
    [!!] Table cache hit rate: 0% (64 open / 9M opened)
    [OK] Open file limit used: 1% (127/11K)
    [OK] Table locks acquired immediately: 99% (26M immediate / 26M locks)
    [OK] InnoDB data size / buffer pool: 5.4M/8.0M

    -------- Recommendations -----------------------------------------------------
    General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    Enable the slow query log to troubleshoot bad queries
    Adjust your join queries to always utilize indexes
    Increase table_cache gradually to avoid file descriptor limits
    Variables to adjust:
    query_cache_size (> 8M)
    join_buffer_size (> 128.0K, or always use indexes with joins)
    table_cache (> 64)


    mysql load now is 27% , what do you think please
     
  9. ES - George

    ES - George Well-Known Member
    PartnerNOC

    Joined:
    Jun 12, 2011
    Messages:
    142
    Likes Received:
    1
    Trophy Points:
    16
    Location:
    UK
    cPanel Access Level:
    Root Administrator
    I'd suggest running mysqlcheck -Aor first to optimise and repair tables

    You might also like to increase the caching size. Change table cache from 64 to 1024
     
  10. bou3lam

    bou3lam Active Member

    Joined:
    Sep 29, 2009
    Messages:
    36
    Likes Received:
    1
    Trophy Points:
    8
    after icreasing the cache and restarting the mysql cpu usage jumped to 200% !!! this the current sql command with such high usage :
    /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --log-error=/var/lib/mysql/server.mydomain.com.err --open-files-limit=12276 --pid-file=/var/lib/mysql/server.mydomain.com.pid
     
  11. bou3lam

    bou3lam Active Member

    Joined:
    Sep 29, 2009
    Messages:
    36
    Likes Received:
    1
    Trophy Points:
    8
    re-modified it to 64, 25% again, guess this is fine :) thanks !
     
  12. bandungdown

    bandungdown Registered

    Joined:
    Nov 26, 2012
    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Root Administrator
    You can see poseses mysql

     
Loading...

Share This Page