性能文章>【全网首发】记一次MySQL CPU被打满的SQL优化案例分析>

【全网首发】记一次MySQL CPU被打满的SQL优化案例分析原创

4393106

背景介绍

系统中有个公告模块,当用户登录后,根据用户所属机构查询公告列表,同时公告列表中需要展示出该用户对公告的阅读状态及阅读时间。公告(bulletin)、公告接收者(bulletin_receiver)、公告阅读者(bulletin_reader)定义及关联关系如下:
image.png
应用使用的数据库连接池是druid,数据库是阿里云RDS MySQL 5.6(16c64g)

问题梳理

经过梳理,事件时间线大概是这样的:
image.png

14:45 GetConnectionTimeoutException

image.png
image.png

14:47 钉钉报警

image.png

14:59 CommunicationsException

image.png

问题分析

应用分析

GetConnectionTimeoutException

image.png

arthas vmtool

我们分析问题,通常会碰到日志记录不够排查问题的情况,此时可以通过arthas相关命令进行分析。比如我们想看一下druid连接池统计信息。
image.png
image.png

CommunicationsException

出现该异常几个典型场景是:

  • 数据库连接空闲时间超过了MySQL服务器配置的wait_timeout
  • MySQL server versions like 5.6.25 and earlier or 5.7.5 and earlier,客户端连接属性useSSL默认是false;MySQL server versions like 5.6.25+ or 5.7.5+,客户端连接属性useSSL默认是true。默认useSSL=true的MySQL server版本,客户端连接属性还需要配置其他额外的连接属性,如果没有配置会抛出_CommunicationsException异常。_
  • 客户端发送请求后,服务端比较忙,一直没有回复客户端的请求导致超时。

从上面时间线看,当出现该异常的时候,MySQL CPU已经被打满,无法及时处理客户端请求,导致客户端请求超时。
image.png

系统负载

image.png

业务指标

  • 业务入口请求量比较平缓,没有波动
  • 系统GC情况正常… …

数据库分析

数据库侧没有打开performance_schema,主要排查操作:

  • show processlist:查看数据库会话信息
  • select * from information_schema.innodb_trx
  • 查询慢SQL:没有慢SQL

可疑SQL:

SELECT A.ID AS ID,
	 A.BULLETIN_TYPE AS BULLETIN_TYPE,
	 A.BULLETIN_CONTENT AS BULLETIN_CONTENT,
	 A.STATUS_CODE AS REMARKS,
	 B.READ_STATUS AS STATUS_CODE,
	 B.READ_TIME AS GMT_MODIFIED
FROM(
		SELECT C.*
			FROM BULLETIN C
		 WHERE C.ID IN(
								SELECT D.BULLETIN_ID
									FROM BULLETIN_RECEIVER AS D
								 WHERE D.RECV_CODE IN('45346600', '45302600')
								 GROUP BY D.BULLETIN_ID)
 AND C.STATUS_CODE= 'RELEASE'
 AND C.BULLETIN_TYPE= 2
 AND C.VALID_DATE_END> '2022-11-16 00:00:00'
 AND C.VALID_DATE_BEGIN<= '2022-11-17 00:00:00') AS A
LEFT JOIN BULLETIN_READER AS B ON A.ID= B.BULLETIN_ID
 AND B.READER_ID= 1990234
WHERE B.IS_DELETED IS NULL OR B.IS_DELETED= '0'
LIMIT 0,10

explain 执行计划:
image.png
似乎没有啥问题,而且SQL执行的还可以。
一时没有好的办法快速解决,扩容了两台从库用来分担读的流量,从库上线后一切还算正常,于是恢复了部分数据,哪知过了两天CPU再次被打满… …
问题分析到这里,似乎已经不是资源的问题了… …

解决办法

SQL优化

话说SQL里尽量不要使用IN,可以使用EXISTS或JOIN替代,于是我们改了下SQL。

EXISTS

SELECT A.ID AS ID,
	 A.BULLETIN_TYPE AS BULLETIN_TYPE,
	 A.BULLETIN_CONTENT AS BULLETIN_CONTENT,
	 A.STATUS_CODE AS REMARKS,
	 B.READ_STATUS AS STATUS_CODE,
	 B.READ_TIME AS GMT_MODIFIED
FROM(
		SELECT C.*
			FROM BULLETIN C
		 WHERE EXISTS (
								SELECT 1
									FROM BULLETIN_RECEIVER AS D
								 WHERE D.BULLETIN_ID=C.ID AND D.RECV_CODE IN('45346600', '45302600')
								 )
 AND C.STATUS_CODE= 'RELEASE'
 AND C.BULLETIN_TYPE= 2
 AND C.VALID_DATE_END> '2022-11-16 00:00:00'
 AND C.VALID_DATE_BEGIN<= '2022-11-17 00:00:00') AS A
LEFT JOIN BULLETIN_READER AS B ON A.ID= B.BULLETIN_ID
 AND B.READER_ID= 1990234
WHERE B.IS_DELETED IS NULL OR B.IS_DELETED= '0'
LIMIT 0,10

image.png

JOIN

除了将IN改为JOIN,同时将select中的公告内容字段去掉,因为多数场景下公告列表不需要内容字段。

SELECT A.ID AS ID,
	 A.BULLETIN_TYPE AS BULLETIN_TYPE,
	 A.STATUS_CODE AS REMARKS,
	 B.READ_STATUS AS STATUS_CODE,
	 B.READ_TIME AS GMT_MODIFIED
FROM(
		SELECT C.ID AS ID,C.BULLETIN_TYPE AS BULLETIN_TYPE,C.STATUS_CODE AS STATUS_CODE
			FROM BULLETIN C
			JOIN (
							SELECT D.BULLETIN_ID AS ID
								FROM POR_BULLETIN_RECEIVER AS D
							 WHERE D.RECV_CODE IN('45346600', '45302600') 
							 GROUP BY D.BULLETIN_ID
							) AS E ON C.ID=E.ID
 AND C.STATUS_CODE= 'RELEASE'
 AND C.BULLETIN_TYPE= 2
 AND C.VALID_DATE_END> '2022-11-16 00:00:00'
 AND C.VALID_DATE_BEGIN<= '2022-11-17 00:00:00') AS A
LEFT JOIN BULLETIN_READER AS B ON A.ID= B.BULLETIN_ID
 AND B.READER_ID= 1990234
WHERE B.IS_DELETED IS NULL OR B.IS_DELETED= '0'
LIMIT 0,10

image.png

验证

无论执行计划怎么样,还是实际验证下比较放心。
image.png

结论

IN、EXISTS性能几乎无差别,每次请求平均耗时56ms,JOIN每次请求平均耗时1ms。

服务拆分

公告模块目前与核心模块耦合有点紧,当公告模块出现问题的时候会直接影响到核心模块,需要将公告模块进行拆分。

公告内容字段存储优化

方案一

将公告内容字段独立到一个表中

方案二

目前公告字段是存储在MySQL中,大小从60B 到 25KB不等,从上面分析看这个字段对系统的伤害非常大,可以考虑将该字段放到OSS存储,数据库中保存OSS路径的方式。
image.png

完善监控

当发现我们掌握的信息不足以分析问题的时候,说明需要完善现有的监控指标

数据清理

根据数据保存规定,将过期数据及时清理出去。

点赞收藏
大禹的足迹

在阿里搬了几年砖的大龄码农,头条号:大禹的足迹

请先登录,查看10条精彩评论吧
快去登录吧,你将获得
  • 浏览更多精彩评论
  • 和开发者讨论交流,共同进步

为你推荐

嵌入式linux-CPU性能优化小记

嵌入式linux-CPU性能优化小记

【全网首发】MQ-消息堆积-JDK Bug导致线程阻塞案例分析

【全网首发】MQ-消息堆积-JDK Bug导致线程阻塞案例分析

FullGC没及时处理,差点造成P0事故

FullGC没及时处理,差点造成P0事故

【全网首发】不经意的两行代码把CPU使用率干到了90%+

【全网首发】不经意的两行代码把CPU使用率干到了90%+

如何修改 Nginx 源码实现 worker 进程隔离

如何修改 Nginx 源码实现 worker 进程隔离

关于SimpleDateFormat线程安全问题根因探究

关于SimpleDateFormat线程安全问题根因探究

6
10