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.

Very high mysql load

Discussion in 'Workarounds and Optimization' started by laobuod, Aug 20, 2015.

  1. laobuod

    laobuod Registered

    Joined:
    Aug 20, 2015
    Messages:
    1
    Likes Received:
    0
    Trophy Points:
    1
    Location:
    Cyprus
    cPanel Access Level:
    Website Owner
    I have a dedicated server 32 GB DDR3 and Quad-Core, 4x 3,7 Ghz. I am running 5 domains. The mysql load is very high. When I am using the top command it shows more than 90 of %CPU, sometimes even more than 100. Here is the output of mysqltuner:

    Code:
     >>  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] Logged in using credentials passed on the command line
    [OK] Currently running supported MySQL version 5.5.42-cll
    [OK] Operating on 64-bit architecture
    
    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MRG_MYISAM
    [--] Data in MyISAM tables: 38M (Tables: 26)
    [--] Data in InnoDB tables: 1G (Tables: 42)
    [!!] Total fragmented tables: 9
    
    -------- Security Recommendations  -------------------------------------------
    [OK] All database users have passwords assigned
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 9m 0s (36K q [67.120 qps], 409 conn, TX: 1B, RX: 6M)
    [--] Reads / Writes: 91% / 9%
    [--] Total buffers: 2.4G global + 6.0M per thread (160 max threads)
    [!!] Maximum possible memory usage: 3.3G (108% of installed RAM)
    [OK] Slow queries: 0% (8/36K)
    [OK] Highest usage of available connections: 6% (11/160)
    [OK] Key buffer size / total MyISAM indexes: 22.0M/21.7M
    [OK] Key buffer hit rate: 99.6% (27M cached / 124K reads)
    [OK] Query cache efficiency: 49.7% (16K cached / 32K selects)
    [OK] Query cache prunes per day: 0
    [OK] Sorts requiring temporary tables: 1% (75 temp sorts / 5K sorts)
    [OK] Temporary tables created on disk: 15% (820 on disk / 5K total)
    [OK] Thread cache hit rate: 97% (11 created / 409 connections)
    [OK] Table cache hit rate: 96% (187 open / 194 opened)
    [OK] Open file limit used: 1% (174/10K)
    [OK] Table locks acquired immediately: 99% (28K immediate / 28K locks)
    [OK] InnoDB buffer pool / data size: 2.0G/1.8G
    [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
        Reduce your overall MySQL memory footprint for system stability
    
    It seems strange that I have 32G RAM and I get a message:
    [!!] Maximum possible memory usage: 3.3G (108% of installed RAM)

    How can I correct this?

    My my.cnf file is the following:

    Code:
    [mysqld]
    bind-address=127.0.0.1
    default-storage-engine=MyISAM
    innodb_file_per_table=1
    max_allowed_packet=1024M
    open_files_limit=10000
    query_cache_size=75M
    query_cache_limit=4M
    tmp_table_size=300M
    max_heap_table_size=300M
    table_open_cache=500
    innodb_buffer_pool_size=2048M
    thread_cache_size=10
    key_buffer_size=22M
    sort_buffer_size=5M
    read_rnd_buffer_size=512K
    max_connections=160
    wait_timeout=27000
    interactive_timeout=27000
    log-slow-queries=/var/lib/mysql/slow.log
    join_buffer_size=130kb
    
    Thanks
     
  2. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

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

Share This Page