mysql内存突然增高分析_mysql 资源占用过高分析和解决方法

分析和解决方案

1,情况描述:网站发标后,出现卡顿,最后出错。

2,问题排查:最后一个版本的更新是在两个星期前,照理来说,应该不是代码的问题。但是一时找不到问题所在,还是先对代码进行测试。使用排除法,修改数据后,在测试环境,生产环境测试,均正常。既然测试正常,那么排除代码的问题。那怀疑可能是服务器的问题,接下来查看Linux环境的运行情况。

3,linux系统,命令top可以查看系统资源使用情况。最近两次查看 ,如图一,图二。

afb03dc7efe74d9f463879f24c5c1d91.png

图一

71ecb6381156c5c4d7760e1c97af5ccb.png

图二

由这两个图,可以看出来,mysql的服务几乎把资源耗尽。

ps:发标前的两三分钟,CPU就已经高达140%,在60 - 140震荡。

第一行的 load average 表示系统负载,即任务队列的平均长度,3个数值分别统计最近1,5,15分钟的系统平均负载

系统平均负载:单核CPU情况下,0.00 表示没有任何负荷,1.00表示刚好满负荷,超过1侧表示超负荷,理想值是0.7;

多核CPU负载:CPU核数 * 理想值0.7 = 理想负荷,例如:4核CPU负载不超过2.8何表示没有出现高负载。

系统是2核,理想值是2 * 0.7 =1.4 。图一的3.46,图二的7.69,明显大于理想值。

进程信息

K: %CPU = (CPU usage) 上次更新到现在的CPU时间占用百分比

图二的将近200%,两核的最高是200%,因此系统CPU是满负载运行。

因此在图二的时候,系统卡顿了一段时间。如果再多些访问量,系统应该就会出错。

常见原因

系统执行应用提交查询(包括数据修改操作)时需要大量的逻辑读(逻辑 IO,执行查询所需访问的表的数据行数),所以系统需要消耗大量的 CPU 资源以维护从存储系统读取到内存中的数据一致性。

4,mysql分析数据运行状态:show processlist;

在数据库,执行上面语句,如图

a23a5e8727a9dd4d362ba0e047b79910.png

可以看出,这个查询语句有问题。state为sending data。单独拿出来在数据库执行,时间有点久。当集中访问时,数据库占用资源飙升。

5,查看NGINX日志,访问量并不多,访问的比较多的链接和show processlist;查询的情况基本吻合。

6,查看系统日志。图二的时候,自动投标部分用了14秒。而测试的时候是2秒(测试时候,只是单独发标)。真实发标时,访问比较多,两种情况的结合,影响变大了。单独发标,或者单独访问多时,影响比较小。

7,解决方案

1,优化数据库语句

2,增加资源,采用缓存,分布式等。这个方案保留。

7.1,优化数据库语句

7.1.1 查看查找项目的查询语句,投标表关联的项目表的项目ID,即投标表的项目ID,pid加上索引(index)。

80303ce62353ab69c8d586a5e2d47994.png

图:没有索引时

a019e4a56681d03da270749e4e7a74a6.png

图:加上索引时

由上面两个图可以看出来,加上索引后,访问的时间变为几十毫秒。时间快了15- 20倍。

这样访问首页和“我要投资”页面的时候,系统资源压力大大降低。

7.1.2 查询公告

首页只要显示5条数据,现在是查询出了全部。在数据库执行语句,查询全部时,是100多ms,查询限制5条时,是10多ms,时间快了10倍。

89cb031bd62e1e5f9a4c8184e2ae2465.png

图:content查询修改前。

db30ced05a8fa3f6811d5083f22ba3c8.png

图:content查询修改后。

运行时间快了好几倍。

7.1.3 查询投资排行榜

换种查询方式,在数据库里执行,由80ms到40ms,时间提高一倍。

SELECT sum(money)

as investSum ,(select lname from P_user p1 where p1.id =userId ) aslname

FROMpersonTransactionDetail

where type = 4 and state in (1,3)

GROUP BY userId,lname ORDER BY sum(money)

DESC limit 5

改为:

SELECT sum(money)

asinvestSum ,lname

FROMpersonTransactionDetail a, P_user b

where a.userId = b.id andtype = 4 and state in (1,3)

GROUP BY userId,lname ORDER BY sum(money)

DESC limit 5

a0ccbabb923270c683b4c4ba9746aa45.png

图:投资排行榜修改前

d3ed0cecd7a4334d7870ce01d182155b.png

图:投资排行榜修改后

由200多ms到70ms,时间快了3倍。

连接查询的效率比子查询的高。

执行子查询时,MYSQL需要创建临时表,查询完毕后再删除这些临时表,所以,子查询的速度会受到一定的影响,这里多了一个创建和销毁临时表的过程。

因此,尽量用连接查询。

结合自己的实验,和别人的经历,确实是连接查询的效率比子查询的高。

7.1.4 自动投标插入方面,先保留修改,看后面执行情况。

后续

修改后的运行情况:发标前,mysql服务,cpu的占用2%左右。发标中,不超过10%。自动投标部分,用时2m,正常了。

经过上述调整后,系统运行正常了。

对于访问比较频繁的首页的每个连接做了数据库查询优化后,访问数据都有所提升,因此效率大大提高了。

现在的网站主要的压力都来自于数据库,频繁的数据库访问经常会使服务器死机。良好的数据库设计和优化查询语句是很重要的。

网站架构先是小型架构,逐渐演变成大型架构的。大型网站由小型网站演化而来,随着业务发展,逐渐发展。

25723064参考:大型网站技术架构 ,个人GitHub笔记

  • 0
    点赞
  • 4
    收藏
  • 打赏
    打赏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
©️2022 CSDN 皮肤主题:游动-白 设计师:我叫白小胖 返回首页
评论

打赏作者

知乎科技

你的鼓励将是我创作的最大动力

¥2 ¥4 ¥6 ¥10 ¥20
输入1-500的整数
余额支付 (余额:-- )
扫码支付
扫码支付:¥2
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值