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.

High CPU usage mysql 5.6

Discussion in 'Workarounds and Optimization' started by jkassem, Mar 31, 2014.

  1. jkassem

    jkassem Registered

    Joined:
    Mar 7, 2014
    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Root Administrator
    Hello,

    Recently i upgraded from mysql 5.5 to mysql 5.6 and now i am facing a very high cpu usage.

    Please find below the my.cnf configuration that worked on mysql 5.5 but its seems its not working on 5.6

    Code:
    innodb_buffer_pool_size=2g
    open_files_limit=3692
    tmpdir="/mysqltmp"
    concurrent_insert=ALWAYS
    join_buffer_size=3M
    table_open_cache=2k
    max_heap_table_size=512M
    query_cache_size=128M
    tmp_table_size=512M
    low_priority_updates=1
    max_connections=500
    key_buffer_size=256M
    thread_cache_size=16k
    table_definition_cache=8k
    key_buffer=64M
    query_cache_limit=4M
    innodb_file_per_table=1
    long_query_time=5
    
    interactive_timeout=30
    low_priority_updates=1
    max_connections=500
    key_buffer_size=256M
    thread_cache_size=16k
    table_definition_cache=8k
    key_buffer=64M
    query_cache_limit=4M
    innodb_file_per_table=1
    long_query_time=5
    interactive_timeout=30
    wait_timeout=30
    default-storage-engine=MyISAM
    query_cache_size=8M
    local-infile=0
    
     
  2. thinkbot

    thinkbot Well-Known Member

    Joined:
    Oct 30, 2012
    Messages:
    326
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    run mysqltuner.pl and post the result
     
  3. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    30,852
    Likes Received:
    675
    Trophy Points:
    113
    cPanel Access Level:
    Root Administrator
  4. jkassem

    jkassem Registered

    Joined:
    Mar 7, 2014
    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Root Administrator
    Hello Please find the below:

    Code:
     >>  MySQLTuner 1.2.0 - Major Hayden <major@mhtx.net>
     >>  Bug reports, feature requests, and downloads at [url=http://mysqltuner.com/]MySQLTuner-perl by major[/url]
     >>  Run with '--help' for additional options and output filtering
    
    -------- General Statistics --------------------------------------------------
    [--] Skipped version check for MySQLTuner script
    [OK] Currently running supported MySQL version 5.6.16
    [OK] Operating on 64-bit architecture
    
    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
    [--] Data in MyISAM tables: 1G (Tables: 407)
    [--] Data in InnoDB tables: 9M (Tables: 177)
    [--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 52)
    [!!] Total fragmented tables: 35
    
    -------- Security Recommendations  -------------------------------------------
    [OK] All database users have passwords assigned
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 1m 3s (37K q [599.889 qps], 812 conn, TX: 903M, RX: 5M)
    [--] Reads / Writes: 83% / 17%
    [--] Total buffers: 2.6G global + 4.9M per thread (1500 max threads)
    [!!] Maximum possible memory usage: 9.7G (127% of installed RAM)
    [OK] Slow queries: 0% (0/37K)
    [OK] Highest usage of available connections: 1% (22/1500)
    [OK] Key buffer size / total MyISAM indexes: 64.0M/260.4M
    [OK] Key buffer hit rate: 99.9% (10M cached / 11K reads)
    [OK] Query cache efficiency: 70.8% (23K cached / 33K selects)
    [!!] Query cache prunes per day: 4749257
    [OK] Sorts requiring temporary tables: 2% (112 temp sorts / 3K sorts)
    [!!] Temporary tables created on disk: 33% (427 on disk / 1K total)
    [OK] Thread cache hit rate: 97% (22 created / 812 connections)
    [OK] Table cache hit rate: 94% (132 open / 139 opened)
    [OK] Open file limit used: 0% (130/30K)
    [OK] Table locks acquired immediately: 99% (12K immediate / 12K locks)
    [OK] InnoDB data size / buffer pool: 9.2M/2.0G
    
    -------- Recommendations -----------------------------------------------------
    General recommendations:
        Run OPTIMIZE TABLE to defragment tables for better performance
        MySQL started within last 24 hours - recommendations may be inaccurate
        Reduce your overall MySQL memory footprint for system stability
        Temporary table size is already large - reduce result set size
        Reduce your SELECT DISTINCT queries without LIMIT clauses
    Variables to adjust:
      *** MySQL's maximum memory usage is dangerously high ***
      *** Add RAM before increasing MySQL buffer variables ***
        query_cache_size (> 8M)
    [/QUOTE]
    
    [QUOTE]
    [mysqld]
    max_connect_errors=0
    max_allowed_packet=1MB
    host_cache_size=128
    performance_schema=ON
    table_definition_cache=400
    innodb_buffer_pool_size=2g
    open_files_limit=30000
    tmpdir="/mysqltmp"
    concurrent_insert=ALWAYS
    join_buffer_size=4M
    table_open_cache=2k
    max_heap_table_size=512M
    query_cache_size=128M
    tmp_table_size=512M
    low_priority_updates=1
    max_connections=500
    key_buffer_size=256M
    thread_cache_size=50
    table_definition_cache=8k
    table_open_cache=10000
    key_buffer=64M
    query_cache_limit=4M
    innodb_file_per_table=1
    long_query_time=5
    interactive_timeout=30
    low_priority_updates=1
    max_connections=1500
    key_buffer_size=256M
    table_definition_cache=400
    key_buffer=64M
    query_cache_limit=4M
    innodb_file_per_table=1
    long_query_time=5
    interactive_timeout=30
    wait_timeout=30
    default-storage-engine=MyISAM
    query_cache_size=8M
    local-infile=0
    query_cache_type=ON
    max_allowed_packet=1MB
    
     
  5. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    30,852
    Likes Received:
    675
    Trophy Points:
    113
    cPanel Access Level:
    Root Administrator
    That output suggests MySQL was only running for a little over a minute. Let it run for at least 24 hours before running the tuner for more accurate results.

    Thank you.
     
  6. Archmactrix

    Archmactrix Well-Known Member

    Joined:
    Jan 20, 2012
    Messages:
    132
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    You have many duplicated variables and some of them conflicting, like these (conflicts):

    Code:
    max_connections=1500
    max_connections=500
    query_cache_size=128M
    query_cache_size=8M
    table_definition_cache=400
    table_definition_cache=8k
    table_open_cache=10000
    table_open_cache=2k
    Is the first one of these two, key_buffer, a variable?

    Code:
    key_buffer=64M
    key_buffer_size=256M
     
  7. jkassem

    jkassem Registered

    Joined:
    Mar 7, 2014
    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Root Administrator
    Hello,

    Actually i am not a professional with this, can u help me to delete and fix what its need to be fixed?

    Thank you.
     
  8. jkassem

    jkassem Registered

    Joined:
    Mar 7, 2014
    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Root Administrator
    Just fixed and used the below:

    Code:
    innodb_file_per_table=1
    innodb_buffer_pool_size=2g
    default-storage-engine=MyISAM
    max_connect_errors=0
    max_allowed_packet=1MB
    max_connections=1500
    host_cache_size=128
    thread_cache_size=50
    table_open_cache=2k
    table_definition_cache=8k
    open_files_limit=30000
    long_query_time=5
    query_cache_size=128M
    query_cache_limit=4M
    key_buffer_size=256M
    key_buffer=64M
    join_buffer_size=4M
    max_heap_table_size=512M
    tmp_table_size=512M
    low_priority_updates=1
    interactive_timeout=30
    wait_timeout=30
    local-infile=0
    query_cache_type=ON
    performance_schema=ON
    concurrent_insert=ALWAYS
     
Loading...

Share This Page