Mysql占用CPU过高如何优化

Question

如题所述

Answer 1

CPU占用过高诊断思路

mpstat -P ALL 1，查看cpu使用情况，主要消耗在sys即os系统调用上

perf top，cpu主要消耗在_spin_lock

生成perf report查看详细情况

CPU主要消耗在mutex争用上，说明有锁热点。

采用pt-pmp跟踪mysqld执行情况，热点主要集中在mem_heap_alloc和mem_heap_free上。

Pstack提供更详细的API调用栈

Innodb在读取数据记录时的API路径为

row_search_for_mysql --》row_vers_build_for_consistent_read --》mem_heap_create_block_func --》mem_area_alloc --》malloc --》  _L_unlock_10151 --》__lll_unlock_wait_private

row_vers_build_for_consistent_read会陷入一个死循环，跳出条件是该条记录不需要快照读或者已经从undo中找出对应的快照版本，每次循环都会调用mem_heap_alloc/free。

而该表的记录更改很频繁，导致其undo history list比较长，搜索快照版本的代价更大，就会频繁的申请和释放堆内存。

Linux原生的内存库函数为ptmalloc，malloc/free调用过多时很容易产生锁热点。

当多条 SQL 并发执行时，会最终触发os层面的spinlock，导致上述情形。

解决方案

将mysqld的内存库函数替换成tcmalloc，相比ptmalloc，tcmalloc可以更好的支持高并发调用。

修改my.cnf，添加如下参数并重启

[mysqld_safe]malloc-lib=tcmalloc

上周五早上7点执行的操作，到现在超过72小时，期间该实例没有再出现cpu长期飙高的情形。

以下是修改前后cpu使用率对比

Answer 2

insert delayed 会让insert优先级变低，查询优先。
把每个页面的无数条insert语句合并成一句，能让插入时间减少一个数量级（10倍或更少），用括号把每条语句的数据分组即可。
不会的话，用mysqldump导出一个表，然后看看生成的SQL就知道了。
您也可以试试搭载了第六代智能英特尔酷睿处理器的产品，创新性的使用模式，如实感技术，姿势控制，语音识别，2D/3D影像，突破传统PC使用体验，无论办公学习、畅玩游戏或者观看超高清影像播放，均得心应手，引领产品创新。

Answer 3

优化的情况分为以下几点：
1、一次生产DB服务器的 超负荷运行问题解决：
1.查看生产DB服务器top列表，执行 top 命令
查看Cpu(s) 参数一直处于 98% 状态 ，load average达到了 5 (4核服务器)，可见 DB已经超负荷运行了
2.使用root用户登录mysql

执行 show full processlist 查看慢查询，反复执行，发现一直有3个select 查询语句存 在，为了缓解DB服务器压力，直接使用kill命令杀掉， 慢查询的id，服务器压力缓解， 恢复正常。
使用这条SQL语句去项目中找到对应功能，经查是没用的功能，直接隐藏这个功能，问 题解决。
MySQL处在高负载环境下，磁盘IO读写过多，肯定会占用很多资源，必然CP会U占用 过高。
2、占用CPU过高，可以做如下考虑：
1.打开慢查询日志，查询是否是某个SQL语句占用过多资源，如果是的话，可以对SQL语 句进行优化，比如优化 insert 语句、优化 group by 语句、优化 order by 语句、优化 join 语句等等；
2.考虑索引问题；
3.定期分析表，使用optimize table；
4.优化数据库对象；
5.考虑是否是锁问题；
6.调整一些MySQL Server参数，比如key_buffer_size、table_cache、 innodb_buffer_pool_size、innodb_log_file_size等等；
7.如果数据量过大，可以考虑使用MySQL集群或者搭建高可用环境。

Answer 4