Skip to main content

High CPU Load

zlati
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:
>> MySQLTuner 1.6.9 - Major Hayden >> 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 :
[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:
total used free shared buffers cached Mem: 31995 27091 4904 704 741 22025 -/+ buffers/cache: 4324 27671 Swap: 16071 1419 14652
php -v
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...

Comments

3 comments

Date Votes
  • SysSachin
    First you can check which process causing high load on the server. You can check process using the "top" command. The following thread is useful : Troubleshooting high server loads on Linux servers
    0
  • zlati
    Here is top(now it's not much traffic but still over 5 load)
    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'
    0
  • cPanelMichael
    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.
    0

Please sign in to leave a comment.

Didn't find what you were looking for?

New post