MySQL高CPU消耗(600%)

Question

我们有一个运行mysql的高流量服务器。多年来，这个数据库运行良好，流量没有明显增加，数据库也没有发生任何变化，但最近Mysql的消耗增加到了600%。

我们没有看到任何缓慢的查询被记录，但我们得到以下建议。

我们对MySQL不是很了解，我们试图修复CPU消耗的一切都没有起作用。

如果有人可以提供一些建议的设置，将不胜感激。

top - 08:08:59 up 313 days, 12:47,  2 users,  load average: 7.21, 6.48, 6.08
Tasks: 294 total,   1 running, 293 sleeping,   0 stopped,   0 zombie
Cpu(s): 29.9%us, 10.6%sy,  3.9%ni, 47.1%id,  3.1%wa,  0.0%hi,  5.5%si,  0.0%st
Mem:   8018016k total,  7869300k used,   148716k free,   587632k buffers
Swap:        0k total,        0k used,        0k free,  5894136k cached

[mysqld]
# skip-networking
#tmpdir=/dev/shm
general_log = 1
slow_query_log = 1
log-slow-queries = /var/log/mysql-slow.log
slow-query_log_file = /var/log/mysql-slow.log
long_query_time = 2
sort_buffer_size = 4M
max_connections = 151
max_allowed_packet = 100M
query_cache_size = 128M
query_cache_limit = 5M
join_buffer_size = 10M
tmp_table_size = 64M
max_heap_table_size = 64M
thread_cache_size = 4
table_cache = 20k
innodb_buffer_pool_size = 512M
key_buffer_size = 128M
local-infile=0
open_files_limit=2058
wait_timeout = 500
connect_timeout = 500
interactive_timeout = 500
performance_schema = on


 >>  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.50-log
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MRG_MYISAM 
[--] Data in MyISAM tables: 453M (Tables: 169)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[!!] InnoDB is enabled but isn't being used
[!!] Total fragmented tables: 5

-------- Security Recommendations  -------------------------------------------
[OK] All database users have passwords assigned

-------- Performance Metrics -------------------------------------------------
[--] Up for: 22m 39s (763K q [561.834 qps], 22K conn, TX: 506M, RX: 95M)
[--] Reads / Writes: 71% / 29%
[--] Total buffers: 848.0M global + 14.6M per thread (151 max threads)
[OK] Maximum possible memory usage: 3.0G (39% of installed RAM)
[OK] Slow queries: 0% (1/763K)
[OK] Highest usage of available connections: 52% (80/151)
[OK] Key buffer size / total MyISAM indexes: 128.0M/273.3M
[OK] Key buffer hit rate: 100.0% (72M cached / 29K reads)
[OK] Query cache efficiency: 79.1% (472K cached / 596K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (8 temp sorts / 43K sorts)
[OK] Temporary tables created on disk: 0% (37 on disk / 10K total)
[!!] Thread cache hit rate: 47% (12K created / 22K connections)
[OK] Table cache hit rate: 97% (249 open / 256 opened)
[OK] Open file limit used: 0% (277/41K)
[!!] Table locks acquired immediately: 53%

-------- Recommendations -----------------------------------------------------
General recommendations:
    Add skip-innodb to MySQL configuration to disable InnoDB
    Run OPTIMIZE TABLE to defragment tables for better performance
    MySQL started within last 24 hours - recommendations may be inaccurate
    Optimize queries and/or use InnoDB to reduce lock wait
Variables to adjust:
    thread_cache_size (> 4)

次优缓存方法.

您正在使用具有相当高流量数据库的MySQL查询缓存。可能值得考虑使用memcached而不是MySQL查询缓存，特别是在有多个从站的情况下。

使用的查询缓存不足80%。

这可能是由于query_cache_limit太低造成的。刷新查询缓存也可能有帮助。

查询缓存相当分散。

严重的碎裂很可能(进一步)增加Qcache_lowmem_prunes。这可能是由于query_cache_size太小而导致许多查询缓存内存修剪过低造成的。对于即时但短暂的修复，您可以刷新查询缓存(可能会锁定查询缓存很长时间)。小心地将query_cache_min_res_unit调整为较低的值也会有帮助，例如可以使用以下公式将其设置为缓存中查询的平均大小：(query_cache_size - qcache_free_memory) / qcache_queries_in_cache

有很多行正在排序.

尽管大量行排序没有什么问题，但您可能希望确保需要大量排序的查询使用ORDER子句中的索引列，因为这将导致更快的排序。

没有索引的联接太多了.

这意味着联接正在执行完整的表扫描。为联接条件中使用的列添加索引将大大加快表联接的速度。

第一个索引条目的读取率很高。

这通常表示频繁的全索引扫描。全索引扫描比表扫描快，但在大表中需要大量的CPU周期，如果那些具有或具有大量更新和删除量的表，运行“优化表”可能会减少和/或加快全索引扫描的数量。除此之外，只有通过重写查询才能减少完整的索引扫描。

从固定位置读取数据的速率很高。

这表明许多查询需要对结果进行排序和/或进行全表扫描，包括不使用索引的联接查询。在适用的情况下添加索引。

下一个表行的读取率很高。

这表明许多查询正在执行全表扫描。在适用的情况下添加索引。

许多临时表正在写入磁盘，而不是保存在内存中。

增加max_heap_table_size和tmp_table_size可能有帮助。但是，有些临时表总是被写入磁盘，与这些变量的值无关。要消除这些问题，您必须重写查询以避免这些情况(在临时表中:存在BLOB或文本列或存在大于512个字节的列)，如MySQL文档中提到的那样。

使用的MyISAM密钥缓冲区(索引缓存)%低。

您可能需要减小key_buffer_size的大小，重新检查您的表以查看索引是否已被删除，或者检查有关使用哪些索引的查询和期望。

开桌率很高.

打开表需要磁盘I/O，这是昂贵的。增加table_open_cache可能会避免这种情况。

文件的打开率很高.

考虑增加open_files_limit，并在更改open_files_limit后重新启动时检查错误日志。

太多的表锁没有立即被授予.

优化查询和/或使用InnoDB来减少锁等待。

太多的表锁没有立即被授予.

优化查询和/或使用InnoDB来减少锁等待。

太多的连接被中止了.

当无法授权连接时，通常会中止连接。这篇文章可能会帮助您找到源代码。

相对于InnoDB缓冲池.，InnoDB日志文件大小不是合适的大小。

特别是在有大量写入InnoDB表的系统上，您应该将innodb_log_file_size设置为innodb_buffer_pool_size的25%。但是，当数据库崩溃时，该值越大，恢复时间就越长，因此这个值不应该设置得远远高于256个MiB。但是，请注意，您不能简单地更改此变量的值。您需要关闭服务器，删除InnoDB日志文件，在my.cnf中设置新值，启动服务器，如果一切顺利，则检查错误日志。还请参阅此博客条目

编辑

    -------- Storage Engine Statistics -------------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MRG_MYISAM 
[--] Data in MyISAM tables: 461M (Tables: 168)
[--] Data in InnoDB tables: 16K (Tables: 1)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[!!] Total fragmented tables: 8

-------- Security Recommendations  -------------------------------------------
[OK] All database users have passwords assigned

-------- Performance Metrics -------------------------------------------------
[--] Up for: 25d 22h 33m 27s (1B q [533.080 qps], 35M conn, TX: 801B, RX: 148B)
[--] Reads / Writes: 71% / 29%
[--] Total buffers: 1020.0M global + 14.6M per thread (151 max threads)
[OK] Maximum possible memory usage: 3.2G (41% of installed RAM)
[OK] Slow queries: 0% (7/1B)
[OK] Highest usage of available connections: 55% (84/151)
[OK] Key buffer size / total MyISAM indexes: 300.0M/317.3M
[OK] Key buffer hit rate: 100.0% (35B cached / 18K reads)
[OK] Query cache efficiency: 79.6% (743M cached / 933M selects)
[!!] Query cache prunes per day: 34376
[OK] Sorts requiring temporary tables: 0% (48K temp sorts / 68M sorts)
[OK] Temporary tables created on disk: 0% (1K on disk / 17M total)
[OK] Thread cache hit rate: 76% (8M created / 35M connections)
[OK] Table cache hit rate: 98% (504 open / 511 opened)
[OK] Open file limit used: 1% (678/41K)
[!!] Table locks acquired immediately: 74%
[OK] InnoDB buffer pool / data size: 512.0M/16.0K
[OK] InnoDB log waits: 0
-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    Optimize queries and/or use InnoDB to reduce lock wait
Variables to adjust:
    query_cache_size (> 128M)

Answer 1

升级-- 5.5的支持在大约7年前结束。

提示建议:将表从MyISAM切换到InnoDB。一定要把key_buffer_size调低到20M，innodb_buffer_pool_size也要高一些，但不要太高以至于会导致交换。

最重要的建议:使用您一直捕获的。理解它，让我们讨论“最糟糕”的几个查询。

嗯，也许在慢速日志中没有什么东西(“慢速查询: 0% (1/763 k)”)。所以把long_query_time降低到0.4

查询缓存的伤害可能比它所起的作用更大。很难说。

8MB中没有多少是由MySQL占用的；您是否有其他应用程序运行在同一台机器上？改变内存大小不会缓解CPU问题。使用更多的内存来提高table_open_cache、open_files_limit等，可能对CPU有所帮助。

如果不使用MyISAM，请将key_buffer_size降为20M。

忽略关于“分段表”的注释。

关闭general_log；它会迅速填充磁盘。( CPU影响不大。)

"Up : 22m 39s“--分析主要集中在”启动“活动上，因此没有充分解决您的问题。等24小时。

更深入的分析：http://mysql.rjweb.org/doc.php/mysql_analysis#tuning

Answer 2

在我开始之前，我将提到推荐列表中的所有内容都是值得一看的，我强烈同意另一个答案，即5.5是疯狂的--旧的--甚至连安全补丁都没有。升级是个很好的主意。

我想把重点放在以下几个方面：

此数据库多年来运行良好，流量或对数据库的更改都没有显著增加。

考虑到这一点，我知道以下两个常见的原因：

1. 磁盘处于失败的边缘，因此某些磁盘I/O操作是缓慢或重复的。如果这是您的问题，您可能会在核心服务器(而不是MySql)日志中找到其他证据.但不总是这样！

正如您所看到的数据增长--不仅是随着时间的推移，而且在业务增长方面(希望业务拥有比安装服务器时更多的客户)--与以前相比，需要更多的内存来保持在内存中缓冲的活动记录，而不是频繁地从(慢得多的)磁盘中读取。您可以到达一个临界点，在这个临界点上，您以前完全在内存中完成的许多查询现在需要溢出到磁盘上。

是的，是的，我知道这两个问题都是磁盘问题，而问题是CPU的使用。但是，磁盘I/O问题通常会显示为CPU负载问题，进程在磁盘上等待完成。

这两个问题的解决方案通常涉及硬件升级.但如果这是你问题中的第二个原因，你可以推迟。

Answer 3

Master00，由于您需要并希望继续使用MyISAM，请考虑以下建议，以提高您使用MyISAM的响应时间。

更正变量名为key_cache_age_threshold -我谦卑的道歉。威尔逊

key_cache_block_size=16384  # from 1024 to reduce overhead managing key_buffer data
key_cache_division_limit=50  # from 100 percent to enable Hot / Warm cache separation
key_cache_age_threshold=7200  # from 300 seconds before AGE_OUT causes another READ

这三个变化将显著降低您的key_reads每秒速率。

要获得更多有用的提示，请查看我的个人资料以获取联系信息并与之保持联系。