面试官:如果MySQL引起CPU消耗过大,你会怎么优化?

如果MySQL引起CPU消耗过大,祸首是谁?欢迎大家讨论

附言 1  ·  2年前

感谢大家的解决思路,使用盖伦终极奥义“百度一下”,这其实就是mysql优化题,总结如下:

谁在消耗cpu

用户+系统+IO等待+软硬中断+空闲

日志存储

祸首是谁?

用户

用户空间CPU消耗,各种逻辑运算

正在进行大量tps

函数/排序/类型转化/逻辑IO访问…

用户空间消耗大量cpu,产生的系统调用是什么?哪些函数使用了cpu周期?

IO等待

等待IO请求的完成

此时CPU实际上空闲

产生影响

用户和IO等待消耗了大部分cpu

  • 吞吐量下降(tps)

  • 查询响应时间增加

  • 慢查询数增加

  • 对mysql的并发陡增,也会产生上诉影响

解决方案

减少等待

减少IO量

SQL/index,使用合适的索引减少扫描的行数(需平衡索引的正收益和维护开销,空间换时间)

提升IO处理能力

加cache/加磁盘/SSD

减少计算

减少逻辑运算量

  • 避免使用函数,将运算转移至易扩展的应用服务器中 如substr等字符运算,dateadd/datesub等日期运算,abs等数学函数

  • 减少排序,利用索引取得有序数据或避免不必要排序 如union all代替 union,order by 索引字段等

  • 禁止类型转换,使用合适类型并保证传入参数类型与数据库字段类型绝对一致 如数字用tiny/int/bigint等,必需转换的在传入数据库之前在应用中转好

  • 简单类型,尽量避免复杂类型,降低由于复杂类型带来的附加运算。更小的数据类型占用更少的磁盘、内存、cpu缓存和cpu周期

减少逻辑IO量

  • index,优化索引,减少不必要的表扫描 如增加索引,调整组合索引字段顺序,去除选择性很差的索引字段等等

  • table,合理拆分,适度冗余 如将很少使用的大字段拆分到独立表,非常频繁的小字段冗余到“引用表”

  • SQL,调整SQL写法,充分利用现有索引,避免不必要的扫描,排序及其他操作 如减少复杂join,减少order by,尽量union all,避免子查询等

  • 数据类型,够用就好,减少不必要使用大字段 如tinyint够用就别总是int,int够用也别老bigint,date够用也别总是timestamp

减少query请求量(非数据库本身

  • 适当缓存,降低缓存数据粒度,对静态并被频繁请求的数据进行适当的缓存 如用户信息,商品信息等

  • 优化实现,尽量去除不必要的重复请求 如禁止同一页面多次重复请求相同数据的问题,通过跨页面参数传递减少访问等

  • 合理需求,评估需求产出比,对产出比极端底下的需求合理去除

升级cpu若经过减少计算和减少等待后还不能满足需求,cpu利用率还高T_T 是时候拿出最后的杀手锏了,升级cpu,是选择更快的cpu还是更多的cpu了?

  • 低延迟(快速响应),需要更快的cpu(每个查询只能使用一个cpu)

  • 高吞吐,同时运行很多查询语句,能从多个cpu处理查询中收益

Luson
《L01 基础入门》
我们将带你从零开发一个项目并部署到线上,本课程教授 Web 开发中专业、实用的技能,如 Git 工作流、Laravel Mix 前端工作流等。
《G01 Go 实战入门》
从零开始带你一步步开发一个 Go 博客项目,让你在最短的时间内学会使用 Go 进行编码。项目结构很大程度上参考了 Laravel。
讨论数量: 10
Luson

脑壳疼

2年前 评论

慢sql,大规模更新索引,锁过多,巨量事务,CLI批量任务,可能性太多了

2年前 评论
Luson (楼主) 2年前
Imuyu (作者) 2年前
Luson (楼主) 2年前
QIN秦同学

题目是CPU飙升,莫非面试官 想听优化系统配置参数?【前提:Innodb引擎】

  • 日志(redo log 和 binlog)两阶段提交,参数 InnoDB_flush_log_at_trx_commit ,
    • 默认值 1,意思是每次提交事务的时候,都把数据写入日志,并把日志写入磁盘
    • 0 表示每隔 1 秒将数据写入日志,并将日志写入磁盘;
    • 2 表示,每次提交事务的时候都将数据写入日志,但是日志每间隔 1 秒写入磁盘。
  • 提高缓存区容量,减少IO。参数 InnoDB_buffer_pool_size
  • 修改缓存区个数,提高并行能力。参数 InnoDB_buffer_pool_instances
  • 搞个监控,监听事件,诊断信息。

其他想不到了

2年前 评论
Luson (楼主) 2年前
PHPer技术栈 2年前

一般情况下CPU突然升高就是'慢查询'集中发生在某一时刻,祸首就是某程序员了

2年前 评论
Imuyu 2年前

首先执行show processlist,CPU飙升肯定有正在执行的SQL语句,查看并保存一下打印信息,然后具体问题具体分析优化,慢sql,索引,事务,还是其它问题要根据具体的信息决定。一般情况下都是sql语句查询慢或并发高导致cpu飙升,面试官可能是想听排查问题的步骤,个人感觉答出show processlist 能对一半。

2年前 评论

一直高,那就是mysql一直在连接吧,连接数和查询数高

2年前 评论

面试官可能想听的是排查思路,没建好索引导致全表查询容易引起.. 其他不知了,mysql配置一般不轻易动

2年前 评论
陈先生

show full processlist ,自行排查吧

2年前 评论

mysql存储引擎属于或支持MVCC,支持并发和I/O多路复用。以为客户端是以TCP连接的形式跟mysql客户端通信,正常情况下客户端会使用已经建立好的连接不会每次执行sql都新建一个连接(从资源和效率角度考虑)。如果瞬时流量很大客户端应该会新建多条连接以这种形式实现异步非阻塞来处理请求,每个TCP连接都会一个断开等待时间,如果到了这个时间还没有被使用过它就会被销毁,show full processlist看到的其实就是这些连接

2年前 评论
aa24615

杀了面试官祭天

2年前 评论

讨论应以学习和精进为目的。请勿发布不友善或者负能量的内容,与人为善,比聪明更重要!