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 load: mysql --log-error=/var/lib/mysql/...

Discussion in 'Database Discussions' started by amrkps, Apr 3, 2012.

  1. amrkps

    amrkps Registered

    Joined:
    Apr 3, 2012
    Messages:
    1
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Root Administrator
    I have vps,

    Everyday, I have high cpu usage from this :
    /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --log-error=/var/lib/mysql/my.hostname.net.err --pid-file=/var/lib/mysql/my.hostname.net.pid

    See my attachment picture:

    /https://lh5.googleusercontent.com/-0DU6ip2aYaI/T3rF0sb4qzI/AAAAAAAAAL4/EHrqz4G-XV4/s800/1333446023351.png

    It consume 30% - 50% of CPU, make my vps very slow.

    How to fix this case?

    I am not expert ini ssh command, please guide me step by step.


    Best regards,
    Amrkps
     
  2. cPanelTristan

    cPanelTristan Quality Assurance Analyst
    Staff Member

    Joined:
    Oct 2, 2010
    Messages:
    7,623
    Likes Received:
    21
    Trophy Points:
    38
    Location:
    somewhere over the rainbow
    cPanel Access Level:
    Root Administrator
    Hello,

    MySQL typically always uses high CPU, since all MySQL processes are listed under the main MySQL process. There are many existing forum threads discussing MySQL Optimization. The first suggestion is to run the following:

    Code:
    mysql_config --version
    cat /etc/my.cnf
    /usr/local/cpanel/3rdparty/mysqltuner/mysqltuner.pl
    Please post the output for all 3 commands.

    Thanks!
     
  3. mmx38

    mmx38 Member

    Joined:
    Apr 28, 2010
    Messages:
    8
    Likes Received:
    0
    Trophy Points:
    1
    I tried those commands in my box..

    What can you suggest me..? I think I have bigger problems than I thought.. The output scares me :)

    Let me mention that the server rebooted 6 hours ago.. so the recommendations may be inaccurate..


    Code:
    mysql_config --version
    5.0.95
    
    
    
    
    
    
    
    
    
    
    cat /etc/my.cnf
    
    [mysqld]
    set-variable = max_connections=500
    safe-show-database
    set-variable = max_allowed_packet=100M
    key_buffer = 512M
    table_cache = 2200
    #oti nanai read_buffer_size=400M
    #oti nanai read_rnd_buffer_size=400M
    
    
    
    
    
    
    
    
    /usr/local/cpanel/3rdparty/mysqltuner/mysqltuner.pl
    
    >>  MySQLTuner 1.1.2 - Major Hayden <major@mhtx.net>
     >>  Bug reports, feature requests, and downloads at [url]http://mysqltuner.com/[/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.0.95-community
    [OK] Operating on 64-bit architecture
    
    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
    [--] Data in MyISAM tables: 9G (Tables: 10832)
    [--] Data in InnoDB tables: 20M (Tables: 575)
    [--] Data in MEMORY tables: 0B (Tables: 7)
    [!!] Total fragmented tables: 638
    5.0.95
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 6h 58m 17s (857K q [34.166 qps], 12K conn, TX: 2B, RX: 225M)
    [--] Reads / Writes: 47% / 53%
    [--] Total buffers: 538.0M global + 2.7M per thread (500 max threads)
    [OK] Maximum possible memory usage: 1.9G (23% of installed RAM)
    [OK] Slow queries: 0% (12/857K)
    [OK] Highest usage of available connections: 2% (14/500)
    [OK] Key buffer size / total MyISAM indexes: 512.0M/2.5G
    [OK] Key buffer hit rate: 98.5% (23M cached / 351K reads)
    [!!] Query cache is disabled
    [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 28K sorts)
    [!!] Joins performed without indexes: 1543
    [!!] Temporary tables created on disk: 31% (4K on disk / 14K total)
    [!!] Thread cache is disabled
    [!!] Table cache hit rate: 8% (2K open / 25K opened)
    [!!] Open file limit used: 86% (4K/4K)
    [OK] Table locks acquired immediately: 99% (680K immediate / 680K locks)
    [!!] InnoDB data size / buffer pool: 20.2M/8.0M
    
    -------- 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_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)
        tmp_table_size (> 32M)
        max_heap_table_size (> 16M)
        thread_cache_size (start at 4)
        table_cache (> 2200)
        open_files_limit (> 4910)
        innodb_buffer_pool_size (>= 20M)
     
    #3 mmx38, Apr 5, 2012
    Last edited: Apr 5, 2012
  4. cPanelTristan

    cPanelTristan Quality Assurance Analyst
    Staff Member

    Joined:
    Oct 2, 2010
    Messages:
    7,623
    Likes Received:
    21
    Trophy Points:
    38
    Location:
    somewhere over the rainbow
    cPanel Access Level:
    Root Administrator
    Since you are using MySQL 5.0, please try the following values to replace your existing /etc/my.cnf file (which might not be accurate to use as MySQL had been restarted in less than 24 hours when mysqltuner.pl was run):

    Code:
    [mysqld]
    innodb_buffer_pool_size=20M
    key_buffer_size = 512M
    max_allowed_packet=100M
    max_connections=500
    max_heap_table_size=24M
    open_files_limit=5000
    query_cache_size=8M
    table_cache = 2200
    thread_cache_size=4
    tmp_table_size=40M
    log-slow-queries=/var/lib/mysql/slow.log
    After this, then create the slow query log using these commands:

    Code:
    cd /var/lib/mysql
    touch slow.log
    chmod 660 slow.log
    chown mysql:mysql slow.log
    After doing that, then restart MySQL:

    Code:
    /etc/init.d/mysql restart
    Thanks!
     
  5. lucian0308

    lucian0308 Registered

    Joined:
    Jul 3, 2012
    Messages:
    1
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    DataCenter Provider
    i have this problem myself

    mysql version 5.5.24
    with no connections to the sql server cpu to mysql is 65%

    how do you resolve that?
     
    #5 lucian0308, Jul 3, 2012
    Last edited: Jul 3, 2012
Loading...

Share This Page