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 cpu consumtion is 95%+

Discussion in 'Workarounds and Optimization' started by ashiftoday, Feb 3, 2015.

  1. ashiftoday

    ashiftoday Registered

    Joined:
    Feb 3, 2015
    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Root Administrator
    Hi,
    my VPS has very limited sites not more than 10, VPS has 4 cores and 1GB RAM and 1GB Swap.
    my average load values (from last several days) is 2+ several times a day and it shows mysql service being consuming the most.

    what configurations are advised for that,
    and what information you require to see in order to optimize the mysql configurations.

    thaks.
     
  2. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    30,762
    Likes Received:
    662
    Trophy Points:
    113
    cPanel Access Level:
    Root Administrator
  3. ashiftoday

    ashiftoday Registered

    Joined:
    Feb 3, 2015
    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Root Administrator
    below is the mysqltuner output.
    Code:
    >>  MySQLTuner 1.4.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
    [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: 578M (Tables: 455)
    [--] Data in InnoDB tables: 990M (Tables: 411)
    [--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
    [--] Data in MEMORY tables: 0B (Tables: 4)
    [!!] Total fragmented tables: 24
    
    -------- Security Recommendations  -------------------------------------------
    [OK] All database users have passwords assigned
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 1h 8m 48s (341K q [82.675 qps], 2K conn, TX: 2B, RX: 91M)
    [--] Reads / Writes: 94% / 6%
    [--] Total buffers: 168.0M global + 2.8M per thread (151 max threads)
    [OK] Maximum possible memory usage: 583.2M (56% of installed RAM)
    [OK] Slow queries: 0% (3/341K)
    [OK] Highest usage of available connections: 9% (15/151)
    [OK] Key buffer size / total MyISAM indexes: 8.0M/94.5M
    [OK] Key buffer hit rate: 99.6% (1M cached / 7K reads)
    [!!] Query cache is disabled
    [OK] Sorts requiring temporary tables: 0% (7 temp sorts / 42K sorts)
    [!!] Joins performed without indexes: 4197
    [!!] Temporary tables created on disk: 62% (13K on disk / 20K total)
    [!!] Thread cache is disabled
    [!!] Table cache hit rate: 4% (400 open / 8K opened)
    [OK] Open file limit used: 0% (414/50K)
    [OK] Table locks acquired immediately: 99% (388K immediate / 388K locks)
    [!!] InnoDB  buffer pool / data size: 128.0M/990.8M
    [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
        When making adjustments, make tmp_table_size/max_heap_table_size equal
        Reduce your SELECT DISTINCT queries without LIMIT clauses
        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: [url=http://bit.ly/1mi7c4C]table_cache negative scalability - MySQL Performance Blog[/url]
    Variables to adjust:
        query_cache_size (>= 8M)
        join_buffer_size (> 128.0K, or always use indexes with joins)
        tmp_table_size (> 16M)
        max_heap_table_size (> 16M)
        thread_cache_size (start at 4)
        table_open_cache (> 400)
        innodb_buffer_pool_size (>= 990M)
    
     
  4. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    30,762
    Likes Received:
    662
    Trophy Points:
    113
    cPanel Access Level:
    Root Administrator
Loading...

Share This Page