Mysql显示100%的CPU使用率

我在安装Cpanel的MYSQL服务器时遇到了一些问题。 我的服务器显示100%的MySQL使用率,即使我没有太多的networkingstream量。

我正在运行CentOS 6.5×64,带有4CPU内核的80GB SSD和8GB RAM。

my.cnf文件:

[mysqld] max_connections=500 key_buffer=128M myisam_sort_buffer_size=35M join_buffer_size=1M read_buffer_size=1M sort_buffer_size=2M table_cache=4000 thread_cache_size=286 interactive_timeout=25 wait_timeout=7000 connect_timeout=10 max_allowed_packet=268435456 max_connect_errors=10 query_cache_limit=2M query_cache_size=256M query_cache_type=1 tmp_table_size=16M innodb_buffer_pool_size=134217728 open_files_limit=10000 [mysqld_safe] [mysqldump] quick max_allowed_packet=16M [myisamchk] key_buffer=64M sort_buffer=64M read_buffer=16M write_buffer=16M 

这里是MySQLTuner的输出:

 -------- Storage Engine Statistics ------------------------------------------- [--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MRG_MYISAM [--] Data in MyISAM tables: 164M (Tables: 88) [--] Data in InnoDB tables: 33M (Tables: 239) [--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17) [!!] Total fragmented tables: 129 -------- Security Recommendations ------------------------------------------- [OK] All database users have passwords assigned -------- Performance Metrics ------------------------------------------------- [--] Up for: 15m 2s (83K q [92.549 qps], 13K conn, TX: 44M, RX: 5M) [--] Reads / Writes: 61% / 39% [--] Total buffers: 544.0M global + 4.5M per thread (500 max threads) [OK] Maximum possible memory usage: 2.7G (34% of installed RAM) [OK] Slow queries: 0% (0/83K) [OK] Highest usage of available connections: 23% (116/500) [OK] Key buffer size / total MyISAM indexes: 128.0M/59.2M [OK] Key buffer hit rate: 97.9% (24K cached / 527 reads) [OK] Query cache efficiency: 54.3% (18K cached / 33K selects) [OK] Query cache prunes per day: 0 [OK] Sorts requiring temporary tables: 1% (5 temp sorts / 388 sorts) [OK] Temporary tables created on disk: 0% (0 on disk / 374 total) [OK] Thread cache hit rate: 99% (116 created / 13K connections) [OK] Table cache hit rate: 98% (465 open / 474 opened) [OK] Open file limit used: 3% (311/10K) [!!] Table locks acquired immediately: 58% [OK] InnoDB buffer pool / data size: 128.0M/33.6M [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 Enable the slow query log to troubleshoot bad queries Optimize queries and/or use InnoDB to reduce lock wait 

这是我的TOP的输出:

 top - 12:01:06 up 1 day, 1:02, 1 user, load average: 10.37, 9.22, 8.61 Tasks: 399 total, 1 running, 397 sleeping, 0 stopped, 1 zombie Cpu(s): 84.1%us, 15.1%sy, 0.0%ni, 0.0%id, 0.0%wa, 0.0%hi, 0.2%si, 0.7%st Mem: 8191528k total, 6042780k used, 2148748k free, 242064k buffers Swap: 0k total, 0k used, 0k free, 4785012k cached PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND 24515 mysql 20 0 2732m 165m 6264 S 303.8 2.1 42:43.18 mysqld 19471 root 20 0 174m 10m 6148 S 1.0 0.1 0:00.03 php 19523 root 20 0 172m 10m 5864 S 1.0 0.1 0:00.03 php 19533 root 20 0 172m 9m 5804 S 0.7 0.1 0:00.02 php 19535 root 20 0 172m 10m 5864 S 0.7 0.1 0:00.02 php 

造成这个问题的原因是什么?
我是否必须对my.cnf进行更改以防止服务器挂起?
或者我需要购买一台16GB内存的新服务器?

看看你的细节,我们可以总结出这些一般的build议:

  1. 你的READ:WRITE比率(61:39)给出了你的引擎types应该改为Innodb的线索。 在此之前检查输出

    SHOW ENGINE INNODB STATUS\G; SHOW FULL PROCESSLIST;

    在繁忙时间。 很可能你会看到处于LOCK状态的查询。 表锁是MyISAM的一大局限。 如果您有大量的并发写入和select,并且查询性能必须始终如一, Innodb是唯一的select,因为更好的locking机制( row-level

  2. 碎片总数:129 。 运行OPTIMIZE TABLE并进行碎片整理以获得更好的性能

  3. 看看你的slow_query.log ,检查Query_time,Lock_time,Rows_sent,Rows_examined。 如果您发现查询Rows Sent / Rows Examined数比例较高的查询,那么这些查询就可以optimization 。 slow_query.log中的一些查询可能会利用大量的CPU资源。

  4. 检查有关RAM和可用磁盘空间的服务器状态: free -m , df -h 。 如果你有可用的内存,增加innodb_buffer_pool_size以便有更多的caching。

  5. 总是很好的调整这些参数在MySQLconfiguration

    innodb_fast_shutdown=0 innodb_log_buffer_size innodb_log_file_size innodb_flush_method=O_DIRECT query_cache_size=0