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 Load

Discussion in 'Workarounds and Optimization' started by zlati, Mar 25, 2016.

  1. zlati

    zlati Registered

    Joined:
    Jan 31, 2016
    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    1
    Location:
    Romania
    cPanel Access Level:
    Root Administrator
    Hello,

    Few days ago my dedicated server it's going nuts, have high cpu load and working very slow.

    Server is using: cloudlinux, cPanel and litespeed.

    21:08:32 up 8 days, 2:28, 1 user, load average: 9.06, 7.58, 7.72

    mysqltuner log:

    Code:
     >>  MySQLTuner 1.6.9 - Major Hayden <major@mhtx.net>
    >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
    >>  Run with '--help' for additional options and output filtering
    
    [--] Skipped version check for MySQLTuner script
    [OK] Currently running supported MySQL version 5.5.45-cll-lve
    [OK] Operating on 64-bit architecture
    
    -------- System Linux Recommendations  ---------------------------------------
    Look for related Linux system recommandations
    [--] CloudLinux Server release 6.7 (Aleksei Gubarev)
    [--] Internet              : Connected
    [--] Operating System Type : GNU/Linux
    [--] Kernel Release        : 2.6.32-673.8.1.lve1.4.3.el6.x86_64
    [--] Name Servers          : 8.8.8.8, 8.8.4.4
    [--] Logged In users       :
    [--]    root     pts/0        2016-03-25 20:23
    [--]Intel(R) Xeon(R) CPU E3-1230 V2 @ 3.30GHz
    [--] Ram Usages            :
    [--]                 total       used       free     shared    buffers     cached
    [--]    Mem:           31G        26G       5.0G       711M       722M        21G
    [--]    Swap:          15G       1.4G        14G
    [--] Load Average          :
    [--]    top - 20:54:10 up 8 days,  2:14,  1 user,  load average: 6.94, 8.27, 8.50
    [--] User process except mysqld used 2G RAM.
    [--] Other user process except mysqld used less than 15% of total physical memory 7.55% (2G / 31G)
    [--] mount point /dev/shm is using 1 % of total space
    [--] mount point /boot is using 19 % of total space
    [--] mount point /home is using 48 % of total space
    [--] mount point / is using 14 % of max allowed inodes
    [--] mount point /dev/shm is using 1 % of max allowed inodes
    [--] mount point /boot is using 1 % of max allowed inodes
    [--] mount point /home is using 2 % of max allowed inodes
    
    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MEMORY +MRG_MYISAM +MyISAM +PERFORMANCE_SCHEMA
    [--] Data in MyISAM tables: 3G (Tables: 35312)
    [--] Data in InnoDB tables: 1G (Tables: 4588)
    [--] Data in MEMORY tables: 10M (Tables: 63)
    [!!] Total fragmented tables: 7261
    
    -------- Security Recommendations  -------------------------------------------
    [OK] There are no anonymous accounts for any database users
    [OK] All database users have passwords assigned
    [!!] User 'munin@localhost' has user name as password.
    [--] There are 605 basic passwords in the list.
    
    -------- CVE Security Recommendations  ---------------------------------------
    [--] Skipped due to --cvefile option undefined
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 8d 2h 14m 1s (1B q [2K qps], 15M conn, TX: 4080G, RX: 98G)
    [--] Reads / Writes: 81% / 19%
    [--] Binary logging is disabled
    [--] Total buffers: 912.0M global + 4.1M per thread (600 max threads)
    [OK] Maximum reached memory usage: 1.5G (4.94% of installed RAM)
    [OK] Maximum possible memory usage: 3.3G (10.59% of installed RAM)
    [OK] Slow queries: 0% (1K/1B)
    [OK] Highest usage of available connections: 27% (162/600)
    [!!] Aborted connections: 14.64%  (2213263/15117534)
    [!!] Query cache should be disabled by default due to mutex contention.
    [OK] Sorts requiring temporary tables: 0% (60K temp sorts / 13M sorts)
    [!!] Joins performed without indexes: 127586
    [!!] Temporary tables created on disk: 27% (2M on disk / 10M total)
    [OK] Thread cache hit rate: 99% (162 created / 15M connections)
    [!!] Table cache hit rate: 0% (5K open / 99M opened)
    [OK] Open file limit used: 16% (8K/50K)
    [OK] Table locks acquired immediately: 99% (886M immediate / 887M locks)
    
    -------- ThreadPool Metrics --------------------------------------------------
    [--] ThreadPool stat is disabled.
    
    -------- Performance schema --------------------------------------------------
    [--] Performance schema is disabled.
    
    -------- MyISAM Metrics ------------------------------------------------------
    [!!] Key buffer used: 22.0% (118M used / 536M cache)
    [OK] Key buffer size / total MyISAM indexes: 512.0M/669.4M
    [OK] Read Key buffer hit rate: 98.9% (3B cached / 35M reads)
    [!!] Write Key buffer hit rate: 65.7% (48M cached / 16M writes)
    
    -------- AriaDB Metrics ------------------------------------------------------
    [--] AriaDB is disabled.
    
    -------- InnoDB Metrics ------------------------------------------------------
    [--] InnoDB is enabled.
    [!!] InnoDB buffer pool / data size: 128.0M/1.1G
    [OK] InnoDB buffer pool instances: 1
    [OK] InnoDB Used buffer: 100.00% (8191 used/ 8191 total)
    [OK] InnoDB Read buffer efficiency: 99.95% (88610431066 hits/ 88653649327 total)
    [!!] InnoDB Write Log efficiency: 77.92% (1650374 hits/ 2117974 total)
    [OK] InnoDB log waits: 0.00% (0 waits / 467600 writes)
    
    -------- TokuDB Metrics ------------------------------------------------------
    [--] TokuDB is disabled.
    
    -------- Galera Metrics ------------------------------------------------------
    [--] Galera is disabled.
    
    -------- Replication Metrics -------------------------------------------------
    [--] No replication slave(s) for this server.
    [--] This is a standalone server..
    
    -------- Recommendations -----------------------------------------------------
    General recommendations:
        Run OPTIMIZE TABLE to defragment tables for better performance
        Set up a Secure Password for user@host ( SET PASSWORD FOR 'user'@'SpecificDNSorIp' = PASSWORD('secure_password'); )
        Reduce or eliminate unclosed connections and network issues
        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 which have no LIMIT clause
        Increase table_open_cache gradually to avoid file descriptor limits
        Read this before increasing table_open_cache over 64: http://bit.ly/1mi7c4C
        Beware that open_files_limit (50000) variable
        should be greater than table_open_cache ( 5000)
    Variables to adjust:
        query_cache_type (=0)
        join_buffer_size (> 128.0K, or always use indexes with joins)
        tmp_table_size (> 256M)
        max_heap_table_size (> 128M)
        table_open_cache (> 5000)
        innodb_buffer_pool_size (>= 1G) if possible.
    
    my.cnf :

    Code:
    [mysqld]
    local-infile=0
    max_connections = 600
    max_user_connections=1000
    key_buffer_size = 512M
    myisam_sort_buffer_size = 64M
    read_buffer_size = 1M
    table_open_cache = 5000
    thread_cache_size = 384
    wait_timeout = 20
    connect_timeout = 10
    tmp_table_size = 256M
    max_heap_table_size = 128M
    max_allowed_packet = 64M
    net_buffer_length = 16384
    max_connect_errors = 10
    concurrent_insert = 2
    read_rnd_buffer_size = 786432
    bulk_insert_buffer_size = 8M
    query_cache_limit = 5M
    query_cache_size = 128M
    query_cache_type = 1
    query_prealloc_size = 262144
    query_alloc_block_size = 65535
    transaction_alloc_block_size = 8192
    transaction_prealloc_size = 4096
    max_write_lock_count = 8
    slow_query_log
    log-error
    external-locking=FALSE
    open_files_limit=50000
    
    [mysqld_safe]
    
    [mysqldump]
    quick
    max_allowed_packet = 16M
    
    [isamchk]
    key_buffer = 384M
    sort_buffer = 384M
    read_buffer = 256M
    write_buffer = 256M
    
    [myisamchk]
    key_buffer = 384M
    sort_buffer = 384M
    read_buffer = 256M
    write_buffer = 256M
    
    #### Per connection configuration ####
    sort_buffer_size = 1M
    join_buffer_size = 1M
    thread_stack = 192K
    
    memory usage:

    Code:
                 total       used       free     shared    buffers     cached
    Mem:         31995      27091       4904        704        741      22025
    -/+ buffers/cache:       4324      27671
    Swap:        16071       1419      14652
    
    php -v

    Code:
    PHP 5.6.18 (cli) (built: Mar 21 2016 01:25:56)
    Copyright (c) 1997-2016 The PHP Group
    Zend Engine v2.6.0, Copyright (c) 1998-2016 Zend Technologies
        with the ionCube PHP Loader v4.7.5, Copyright (c) 2002-2014, by ionCube Ltd., and
        with Zend OPcache v7.0.6-dev, Copyright (c) 1999-2016, by Zend Technologies
        with Zend Guard Loader v3.3, Copyright (c) 1998-2014, by Zend Technologies
        with Suhosin v0.9.36, Copyright (c) 2007-2014, by SektionEins GmbH
    


    I have on the server 138 websites (wordpres, magento, joomla, etc)
    Any suggestion / recommendation for optimizing mysql, i think that is the problem...
     
  2. SysSachin

    SysSachin Well-Known Member

    Joined:
    Aug 23, 2015
    Messages:
    319
    Likes Received:
    23
    Trophy Points:
    18
    Location:
    India
    cPanel Access Level:
    Root Administrator
    Twitter:
  3. zlati

    zlati Registered

    Joined:
    Jan 31, 2016
    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    1
    Location:
    Romania
    cPanel Access Level:
    Root Administrator
    Here is top(now it's not much traffic but still over 5 load)

    Code:
    top - 14:04:06 up 8 days, 19:24,  1 user,  load average: 6.71, 6.33, 5.67
    Tasks: 331 total,   4 running, 324 sleeping,   1 stopped,   2 zombie
    Cpu(s): 42.5%us,  5.7%sy,  0.0%ni, 51.0%id,  0.6%wa,  0.0%hi,  0.1%si,  0.0%st
    Mem:  32763392k total, 32096768k used,   666624k free,   716844k buffers
    Swap: 16457724k total,  1717160k used, 14740564k free, 26744896k cached
    
        PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
       2558 mysql     20   0 6138m 2.0g 5948 S 298.3  6.5  21275:17 mysqld
    610565 hidden   20   0  205m  19m  13m R 19.9  0.1   0:11.72 lsphp
    611074 nobody    20   0  159m  48m 5148 S  7.6  0.2   0:02.30 litespeed
    611416 hidden 20   0  205m  25m  17m S  7.0  0.1   0:00.31 lsphp
    611232 hidden  20   0  323m  25m 9984 S  6.6  0.1   0:00.82 lsphp
    611116 hidden 20   0  208m  30m  21m R  6.3  0.1   0:00.76 lsphp
    611368 hidden 20   0  205m  29m  20m S  5.3  0.1   0:00.36 lsphp
    611192 hidden 20   0  207m  30m  22m R  3.7  0.1   0:00.72 lsphp
    611378 hidden 20   0  207m  25m  17m S  2.7  0.1   0:00.28 lsphp
       3855 root      20   0  414m 8404 1588 S  2.3  0.0 409:49.86 db_governor
    611426 hidden 20   0  383m  24m  12m S  2.3  0.1   0:00.10 lsphp
    611092 hidden 20   0  462m  27m  18m S  2.0  0.1   0:00.62 lsphp
    611161 hidden 20   0  380m  25m  14m S  2.0  0.1   0:00.46 lsphp
    611431 hidden 20   0  205m  20m  14m S  1.7  0.1   0:00.05 lsphp
    611124 hidden 20   0  335m  19m  10m S  1.3  0.1   0:00.49 lsphp
    611231 hidden 20   0  209m  27m  17m S  1.3  0.1   0:00.17 lsphp
    611241 hidden 20   0  387m  30m  16m S  1.3  0.1   0:00.39 lsphp
    611350 hidden 20   0  317m  22m  10m S  1.3  0.1   0:00.15 lsphp
    611430 hidden 20   0  205m  19m  13m S  1.3  0.1   0:00.04 lsphp
    611434 hidden 20   0  339m  19m  10m S  1.3  0.1   0:00.04 lsphp
    611366 hidden 20   0  205m  23m  14m S  1.0  0.1   0:00.25 lsphp
    611385 hidden 20   0  204m  22m  15m S  1.0  0.1   0:00.18 lsphp
       1992 root      20   0  154m 5856 1952 S  0.7  0.0  43:15.93 lvestats-server
    611267 hidden 20   0  385m  22m  11m S  0.7  0.1   0:00.12 lsphp
    611407 hidden 20   0  386m  29m  16m S  0.7  0.1   0:00.21 lsphp
         33 root      20   0     0    0    0 S  0.3  0.0   0:26.25 ksoftirqd/7
       2573 root      20   0  139m  632  500 S  0.3  0.0   7:58.13 avgtcpd
    190596 root      20   0  160m 9036 1700 S  0.3  0.0   0:26.67 lfd - sleeping
    595573 nobody    23   3  178m  63m 5160 S  0.3  0.2   2:19.38 litespeed
    611078 hidden 20   0  206m  21m  14m S  0.3  0.1   0:00.21 lsphp
    611107 hidden 20   0  386m  31m  19m S  0.3  0.1   0:00.56 lsphp
    611113 hidden 20   0  207m  23m  15m S  0.3  0.1   0:00.27 lsphp
    611128 hidden   20   0  204m  20m  14m S  0.3  0.1   0:00.17 lsphp
    611133 hidden 20   0  206m  21m  14m S  0.3  0.1   0:00.22 lsphp
    611250 hidden 20   0  206m  21m  14m S  0.3  0.1   0:00.15 lsphp
    611353 hidden 20   0  484m  26m  13m S  0.3  0.1   0:00.13 lsphp
    611387 hidden 20   0  323m  25m 9892 S  0.3  0.1   0:00.30 lsphp
    611418 hidden 20   0  365m  16m  10m S  0.3  0.1   0:00.03 lsphp
    611422 root      20   0 13268 1472  952 R  0.3  0.0   0:00.01 top
    611424 hidden 20   0  383m  25m  16m S  0.3  0.1   0:00.11 lsphp
          1 root      20   0 19236 1116  908 S  0.0  0.0   0:06.65 init
          2 root      20   0     0    0    0 S  0.0  0.0   0:00.00 kthreadd
          3 root      RT   0     0    0    0 S  0.0  0.0   0:27.07 migration/0
          4 root      20   0     0    0    0 S  0.0  0.0   0:43.74 ksoftirqd/0
          5 root      RT   0     0    0    0 S  0.0  0.0   0:00.00 stopper/0
          6 root      RT   0     0    0    0 S  0.0  0.0   0:00.64 watchdog/0
          7 root      RT   0     0    0    0 S  0.0  0.0   0:11.53 migration/1
          8 root      RT   0     0    0    0 S  0.0  0.0   0:00.00 stopper/1
          9 root      20   0     0    0    0 S  0.0  0.0   0:21.06 ksoftirqd/1
         10 root      RT   0     0    0    0 S  0.0  0.0   0:00.28 watchdog/1
    
    PS: i replaced the username with 'hidden'
     
  4. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    30,678
    Likes Received:
    648
    Trophy Points:
    113
    cPanel Access Level:
    Root Administrator
    Hello :)

    You can try making changes based on the "Variables to adjust" output from the MySQL tuner. Let MySQL run an additional 24 hours after making the changes and then run the tuner again to see if the output has changed.

    Thank you.
     
Loading...

Share This Page