Very high mysql load

laobuod

• August 20, 2015 08:26

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:
>> MySQLTuner 1.4.0 - Major Hayden >> 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:
[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

• 

  cPanelMichael

  • August 20, 2015 14:54

  Hello :) Please ensure you let MySQL run for at least 24 hours to see accurate tuner results. The RAM issue is discussed at:

  0

Please sign in to leave a comment.