mysql cpu 占比过高问题解决

已于 2022-06-16 10:48:38 修改
阅读量7.1k 收藏 36
点赞数 3
分类专栏： Linux mysql数据库 文章标签： mysql 数据库
于 2022-06-15 18:34:59 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/vipxiaowenbo/article/details/125302212
版权
  • 1 首先我们找到mysql占比高的进程号PID <ps 我这里已经优化过了 所以占比较低>
top

在这里插入图片描述

  • 2 根据PID找到mysql的os_thread_id
 top -H -p 2559

在这里插入图片描述
我们这里可以明显看到有个pid=16815的程序在占用cpu进程 那么我们找到这进行查询的慢sql
这里可以看到是哪个主机连接的信息

SELECT
	a. USER,
	a. HOST,
	a.db,
	b.thread_os_id,
	b.thread_id,
	a.id processlist_id,
	a.command,
	a.time,
	a.state,
	a.info
FROM
	information_schema. PROCESSLIST a,
	PERFORMANCE_SCHEMA .threads b
WHERE
	a.id = b.processlist_id
AND b.thread_os_id = 16804;

在这里插入图片描述

  • 我们最终要的是sql 那么执行下面找个就可以找到高id的进程所属的sql
SELECT
	*
FROM
	PERFORMANCE_SCHEMA.events_statements_current
WHERE
	thread_id = (
		SELECT
			thread_id
		FROM
			PERFORMANCE_SCHEMA .threads
		WHERE
			thread_os_id = 16804
	) ;

在这里插入图片描述
接下来优化sql就行了

这里我们随意找一处比较耗时的sql来做统计
这应该是一位非常懂这块业务的哥们写的 我们来看一下

EXPLAIN
SELECT g.*
FROM (
	SELECT DISTINCT ru.id AS unitId, ru.unit_name AS unitName, ru.create_time, ru.baseid, ru.entid
		, p.id AS postId, p.post_name AS postName, IFNULL(a.patrolNum, 0) AS patrolNum
		, IFNULL(ht.hiddenNum, 0) AS hiddenNum, b.postRiskLevel
		, b.postRiskLevelText, c.unitRiskLevel, c.unitRiskLevelText
	FROM drh_risk_unit_post rup
		LEFT JOIN drh_risk_unit ru ON ru.id = rup.risk_unit_id
		LEFT JOIN ent_post p ON p.id = rup.post_id
		LEFT JOIN (
			SELECT COUNT(1) AS patrolNum, pp.post_id
			FROM drh_post_patrol pp
			GROUP BY pp.post_id
		) a
		ON a.post_id = rup.post_id
		LEFT JOIN (
			SELECT COUNT(1) AS hiddenNum, h.post_id
			FROM drh_hidden h
			GROUP BY h.post_id
		) ht
		ON ht.post_id = rup.post_id
		LEFT JOIN (
			SELECT llt.*, dt.item_text AS postRiskLevelText
			FROM (
				SELECT ht1.*
				FROM (
					SELECT a.*, rup.risk_unit_id
					FROM drh_risk_unit_post rup
						LEFT JOIN (
							SELECT MIN(hft.risk_level) AS postRiskLevel, pr.post_id
							FROM (
								SELECT hf.fk_id, MIN(hf.risk_level) AS risk_level
								FROM drh_post_result_hazar_factors hf
								GROUP BY hf.fk_id
							) hft
								LEFT JOIN drh_post_result pr ON pr.id = hft.fk_id
							WHERE pr.post_id IS NOT NULL
							GROUP BY pr.post_id
						) a
						ON a.post_id = rup.post_id
						LEFT JOIN drh_risk_unit ru ON ru.id = rup.risk_unit_id
					WHERE ru.unit_source = '1'
				) ht1
				UNION ALL
				SELECT ht2.*
				FROM (
					SELECT a.*, rup.risk_unit_id
					FROM drh_risk_unit_post rup
						LEFT JOIN (
							SELECT MIN(hft.risk_level) AS postRiskLevel, sm.post_id
							FROM (
								SELECT hf.fk_id, MIN(hf.risk_level) AS risk_level
								FROM drh_post_result_hazar_factors hf
								GROUP BY hf.fk_id
							) hft
								LEFT JOIN drh_safety_matters sm ON sm.id = hft.fk_id
							WHERE sm.post_id IS NOT NULL
							GROUP BY sm.post_id
						) a
						ON a.post_id = rup.post_id
						LEFT JOIN drh_risk_unit ru ON ru.id = rup.risk_unit_id
					WHERE ru.unit_source = '2'
				) ht2
			) llt
				LEFT JOIN sys_dict_item dt ON llt.postRiskLevel = dt.item_value
				LEFT JOIN sys_dict d ON dt.dict_id = d.id
			WHERE d.dict_code = 'drh_inherent_risk_level'
		) b
		ON b.risk_unit_id = rup.risk_unit_id
			AND b.post_id = rup.post_id
		LEFT JOIN (
			SELECT tll.*, dt.item_text AS unitRiskLevelText
			FROM (
				SELECT ull.risk_unit_id, MIN(ull.postRiskLevel) AS unitRiskLevel
				FROM (
					SELECT ht1.*
					FROM (
						SELECT a.*, rup.risk_unit_id
						FROM drh_risk_unit_post rup
							LEFT JOIN (
								SELECT MIN(hft.risk_level) AS postRiskLevel, pr.post_id
								FROM (
									SELECT hf.fk_id, MIN(hf.risk_level) AS risk_level
									FROM drh_post_result_hazar_factors hf
									GROUP BY hf.fk_id
								) hft
									LEFT JOIN drh_post_result pr ON pr.id = hft.fk_id
								WHERE pr.post_id IS NOT NULL
								GROUP BY pr.post_id
							) a
							ON a.post_id = rup.post_id
							LEFT JOIN drh_risk_unit ru ON ru.id = rup.risk_unit_id
						WHERE ru.unit_source = '1'
					) ht1
					UNION ALL
					SELECT ht2.*
					FROM (
						SELECT a.*, rup.risk_unit_id
						FROM drh_risk_unit_post rup
							LEFT JOIN (
								SELECT MIN(hft.risk_level) AS postRiskLevel, sm.post_id
								FROM (
									SELECT hf.fk_id, MIN(hf.risk_level) AS risk_level
									FROM drh_post_result_hazar_factors hf
									GROUP BY hf.fk_id
								) hft
									LEFT JOIN drh_safety_matters sm ON sm.id = hft.fk_id
								WHERE sm.post_id IS NOT NULL
								GROUP BY sm.post_id
							) a
							ON a.post_id = rup.post_id
							LEFT JOIN drh_risk_unit ru ON ru.id = rup.risk_unit_id
						WHERE ru.unit_source = '2'
					) ht2
				) ull
				GROUP BY ull.risk_unit_id
			) tll
				LEFT JOIN sys_dict_item dt ON tll.unitRiskLevel = dt.item_value
				LEFT JOIN sys_dict d ON dt.dict_id = d.id
			WHERE d.dict_code = 'drh_inherent_risk_level'
		) c
		ON c.risk_unit_id = rup.risk_unit_id
) g
WHERE g.postRiskLevelText IS NOT NULL
	AND g.entid = 1
	AND g.baseid = 1
ORDER BY g.create_time DESC
LIMIT 10;

在这里插入图片描述

耗时22秒 接下来我们分析下
我们通过分析得出 这里面的tablederived2 的rows是一个笛卡尔积 他的数量等于id=2的所有操作 ，其实通过sql可以看出这里不难得出子查套子查询 导致查询效率低
在这里插入图片描述
一个主表 left 两个子查询后 外面又套了一个子查询
意思是 子查询 套子查询 导致查询结果比较慢，那么我们就这样优化 如下

SELECT DISTINCT ru.id AS unitId, ru.unit_name AS unitName, ru.create_time, ru.baseid, ru.entid
		, p.id AS postId, p.post_name AS postName, IFNULL(a.patrolNum, 0) AS patrolNum
		, IFNULL(ht.hiddenNum, 0) AS hiddenNum, b.postRiskLevel
		, b.postRiskLevelText, c.unitRiskLevel, c.unitRiskLevelText
	FROM drh_risk_unit_post rup
		LEFT JOIN drh_risk_unit ru ON ru.id = rup.risk_unit_id
		LEFT JOIN ent_post p ON p.id = rup.post_id
		LEFT JOIN (
			SELECT COUNT(1) AS patrolNum, pp.post_id
			FROM drh_post_patrol pp
			GROUP BY pp.post_id
		) a
		ON a.post_id = rup.post_id
		LEFT JOIN (
			SELECT COUNT(1) AS hiddenNum, h.post_id
			FROM drh_hidden h
			GROUP BY h.post_id
		) ht
		ON ht.post_id = rup.post_id
		LEFT JOIN  
     (
SELECT llt.*, dt.item_text AS postRiskLevelText
			FROM (
				SELECT ht1.*
				FROM (
					SELECT a.*, rup.risk_unit_id
					FROM drh_risk_unit_post rup
						LEFT JOIN (
							SELECT MIN(hft.risk_level) AS postRiskLevel, pr.post_id
							FROM (
								SELECT hf.fk_id, MIN(hf.risk_level) AS risk_level
								FROM drh_post_result_hazar_factors hf
								GROUP BY hf.fk_id
							) hft
								LEFT JOIN drh_post_result pr ON pr.id = hft.fk_id
							WHERE pr.post_id IS NOT NULL
							GROUP BY pr.post_id
						) a
						ON a.post_id = rup.post_id
						LEFT JOIN drh_risk_unit ru ON ru.id = rup.risk_unit_id
					WHERE ru.unit_source = '1'
				) ht1
				UNION ALL
				SELECT ht2.*
				FROM (
					SELECT a.*, rup.risk_unit_id
					FROM drh_risk_unit_post rup
						LEFT JOIN (
							SELECT MIN(hft.risk_level) AS postRiskLevel, sm.post_id
							FROM (
								SELECT hf.fk_id, MIN(hf.risk_level) AS risk_level
								FROM drh_post_result_hazar_factors hf
								GROUP BY hf.fk_id
							) hft
								LEFT JOIN drh_safety_matters sm ON sm.id = hft.fk_id
							WHERE sm.post_id IS NOT NULL
							GROUP BY sm.post_id
						) a
						ON a.post_id = rup.post_id
						LEFT JOIN drh_risk_unit ru ON ru.id = rup.risk_unit_id
					WHERE ru.unit_source = '2'
				) ht2
			) llt
				LEFT JOIN sys_dict_item dt ON llt.postRiskLevel = dt.item_value
				LEFT JOIN sys_dict d ON dt.dict_id = d.id
			WHERE d.dict_code = 'drh_inherent_risk_level'
     )
    b
		ON b.risk_unit_id = rup.risk_unit_id
			AND b.post_id = rup.post_id
		LEFT JOIN (
       SELECT tll.*, dt.item_text AS unitRiskLevelText
			FROM (
				SELECT ull.risk_unit_id, MIN(ull.postRiskLevel) AS unitRiskLevel
				FROM (
					SELECT ht1.*
					FROM (
						SELECT a.*, rup.risk_unit_id
						FROM drh_risk_unit_post rup
							LEFT JOIN (
								SELECT MIN(hft.risk_level) AS postRiskLevel, pr.post_id
								FROM (
									SELECT hf.fk_id, MIN(hf.risk_level) AS risk_level
									FROM drh_post_result_hazar_factors hf
									GROUP BY hf.fk_id
								) hft
									LEFT JOIN drh_post_result pr ON pr.id = hft.fk_id
								WHERE pr.post_id IS NOT NULL
								GROUP BY pr.post_id
							) a
							ON a.post_id = rup.post_id
							LEFT JOIN drh_risk_unit ru ON ru.id = rup.risk_unit_id
						WHERE ru.unit_source = '1'
					) ht1
					UNION ALL
					SELECT ht2.*
					FROM (
						SELECT a.*, rup.risk_unit_id
						FROM drh_risk_unit_post rup
							LEFT JOIN (
								SELECT MIN(hft.risk_level) AS postRiskLevel, sm.post_id
								FROM (
									SELECT hf.fk_id, MIN(hf.risk_level) AS risk_level
									FROM drh_post_result_hazar_factors hf
									GROUP BY hf.fk_id
								) hft
									LEFT JOIN drh_safety_matters sm ON sm.id = hft.fk_id
								WHERE sm.post_id IS NOT NULL
								GROUP BY sm.post_id
							) a
							ON a.post_id = rup.post_id
							LEFT JOIN drh_risk_unit ru ON ru.id = rup.risk_unit_id
						WHERE ru.unit_source = '2'
					) ht2
				) ull
				GROUP BY ull.risk_unit_id
			) tll
				LEFT JOIN sys_dict_item dt ON tll.unitRiskLevel = dt.item_value
				LEFT JOIN sys_dict d ON dt.dict_id = d.id
			WHERE d.dict_code = 'drh_inherent_risk_level'
     ) 
    c
		ON c.risk_unit_id = rup.risk_unit_id
    WHERE 
    b.postRiskLevelText IS NOT NULL
    and ru.entid = 1
    and ru.baseid = 1
    ORDER BY ru.create_time DESC
    LIMIT 10

我们这里验证一下是否是这样
通过验证我们发现1.多秒就执行完了 原先需要20秒
在这里插入图片描述

早cpu高峰的时候
1 通过top命令找到那台机器连接我们的mysql比较耗CPU
2 通过 top -H -p PID 找到我们mysql那个物理进程比较耗费cpu
然后根据sql找到当时执行的sql或者是
找到我们的后台程序对应的数据库监控工具进行查询哪些比较慢
我的web后台使用到了druid 通过druid监控也可以查询到一些慢查询的sql

参考：https://www.isolves.com/it/sjk/MYSQL/2022-03-11/51075.html
http://www.muzhuangnet.com/show/44458.html
http://t.zoukankan.com/wyy123-p-9258513.html
https://blog.csdn.net/asd051377305/article/details/113979657

vipxiaowenbo
关注
  • 3
    点赞
  • 踩
  • 36
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
mysql开启日志占cpu不_MySQL占用CPU过高 查找原因及解决 多种方式
weixin_27388739的博客
01-18 927
MySQL占用CPU过高 查找原因及解决 多种方式一、排查有没有地方占用SQL资源过多1、排查方法：>mysql-uroot-p#登陆数据库>********#输入数据库密码2、查看数据库show databases;3、use 数据库名; #切换到常用数据库4、show processlist; #显...
内存cpu占用不高但mysql很卡_解决Mysql占用cpu,内存高故障案例
weixin_42359255的博客
02-05 1163
故障：晚上大概7点钟左右，收到播放中心投诉，说视频播放很慢，加载很久不出来。一开始，哥以为是tomcat服务又挂了。所以到tomcat服务器上查看下catalina.out输出日志。却没发现任务错误信息。分析：想了想，视频加载慢，会不会是数据库问题呢？果断上mysql数据库(从库)看下top如下：PIDUSERPRNIVIRTRESSHRS%CPU%MEM...
mysql占用cpu超过100%怎么办？mysql占用cpu特别高的解决方法！
hhg2006的博客
09-07 3219
前段时间我的一个网站经常打不开，通过检查发现服务器cpu占用超过100%；通过top命令发现是占用cpu特别高导致的，于是优化了mysql语句，mysql升级到了mysql8最新版本等，但是并没有什么卵用。甚至以为是服务器配置太低了，准备升级配置。后面分析网站日志的时候发现有些ip请求次数特别高，有些ip请求几万次，一查ip还是境外的，于是通过防火墙把这些ip禁用掉。mysql占用cpu超过100%的现象就基本解决了。以上是我对mysql占用cpu超过100%的解决方法。3.排除网站被攻击的可能。
mysql占用CPU过高解决
qq_36378416的博客
08-10 7766
mysqladmin -uroot -proot -r -i 1 ext |\ awk -F"|" \ "BEGIN{ count=0; }"\ '{ if($2 ~ /Variable_name/ && ++count == 1){\ print "----------|---------|--- MySQL Command Status --|----- Innodb row operation ----|-- Buffer Pool Read --";\ print.
如何快速定位到影响mysql cpu飙升的原因——筑梦之路
最新发布
筑梦之路
06-03 480
通常我们只需要执行show processlist 进行查看，一般执行时间最长的SQL八九不离十就是罪魁祸首，但当show processlist的输出有近千条，那么很难第一眼就发现有问题的SQL，那么如何快速找到呢？那么我们可以使用top获取mysqld进程的各个线程的cpu使用情况。可以看到是线程22682占用cpu比较高，接着通过查看performance_schema.threads表，找到相关SQL。找到processlist id以后，就可以直接使用命令kill。
MySQL数据库CPU飙升到100%解决方案
清风朗月的博客
04-05 7316
当cpu飙升到100%时，先用操作系统命令top命令观察是不是mysqld占用导致的，如果不是，找出占用高的进程，并进行相关处理。
【mysql】MySQL CUP过高如何排查？
MS_SONG的博客
08-12 7290
MySQL CUP过高如何排查 ？
mysql巡检脚本(必看篇)
09-09
MySQL巡检是数据库管理员日常维护工作中的重要环节，它能够帮助我们及时发现并解决系统性能问题，确保数据库的稳定运行。下面将详细讲解这个MySQL巡检脚本的主要内容及其涉及的知识点。 首先，巡检脚本采用了Python...
MySQL数据库疑难杂症排查.pptx
10-14
MySQL数据库疑难杂症排查 MySQL 数据库是一种关系型数据库管理系统，广泛应用...我们可以通过确认问题的来源、确认 MySQL 存在瓶颈、优化 MySQL 性能和解决 Sending data 状況等步骤来解决 MySQL 数据库的常见问题。
使用手册1
08-08
- **CPU分析**：根据抓取的数据，对不同笔记本电脑的CPU性能进行统计和比较。 - **屏幕尺寸分析**：分析产品的屏幕尺寸分布，反映消费者对屏幕大小的需求偏好。 - **价格区间分析**：统计不同价格区间的笔记本...
CloudDBA简化数据库运维.pptx
10-12
该服务提供了全面的功能，包括实例管理、高可用性、备份调度、性能监控、任务流程和资源管理，支持MySQL、PostgreSQL和SQL Server等多种数据库系统。 CloudDBA 的核心功能之一是实时诊断。它依赖于一个知识库和规则...
linux中对于springboot程序生成火焰图，并对火焰图进行解析
01-26
颜色越深，表示时间占比越大，可能是潜在的性能问题。 火焰图中的`VM Periodic Task Thread`是由JVM创建的，用于执行周期性的任务，例如内存监控和GC状态检查。`Lettuce`是Spring Boot推荐的Redis高级Java客户端，...
ajaxMyTop-开源
05-03
综上所述，AjaxMyTop利用AJAX技术提供了实时、高效的MySQL服务器监控解决方案，并结合Solr增强了数据检索能力。作为一个开源项目，它集成了社区的力量，提供了可定制化和低成本的数据库监控服务。对于数据库管理员和...
初级-程序员.rar
02-23
7. **问题解决能力**：作为初级程序员，应具备一定的逻辑思维能力和问题解决技巧，能根据需求分析设计出合理的解决方案，并能在遇到问题时进行调试和修复。 8. **实战经验**：通过真题和视频教程的学习，考生可以...
性能测试之需求分析.pdf
11-16
性能测试是IT行业中确保软件系统在高负载下稳定运行的关键环节。在开始性能测试之前，首先需要进行性能需求分析，这是整个测试流程的基础。本文将详细介绍如何进行性能需求分析，以便为后续的测试活动提供清晰的指导...
Mysql占用CPU过高排查过程及可能优化方案
热门推荐
zhangdi10am的博客
03-25 1万+
1.查看生产DB服务器top列表， 执行 top 命令 2.使用root用户登录mysql 执行 show full processlist 查看慢查询，反复执行，如果发现一直有select 查询语句存在，为了缓解DB服务器压力，直接使用kill命令杀掉 kill 慢查询的id 如果服务器压力缓解，恢复正常。 3.查询是否有锁问题 show OPEN TABLES where In_use > 0; 查看正在锁的事务 SELECT * FROM INFORMATION_SCHEMA.
mysql CPU占用过高解决方案
feifeiyechuan的博客
01-29 1566
mysql CPU占用过高解决方案
接口压测时MySql对CPU占用过高
weixin_52116015的博客
04-21 2266
接口压测时MySql对CPU占用过高
解析MySQL生产环境CPU使用率过高的排查与解决方案
lucky_fd的博客
01-27 5188
在生产环境中，MySQL作为一个关键的数据库组件，其性能对整个系统的稳定性至关重要。然而，有时候我们可能会遇到MySQL CPU使用率过高的问题，这可能导致系统性能下降，应用页面访问减慢，甚至影响到用户体验。本文将详细介绍如何排查和解决MySQL CPU过高的问题，帮助您迅速恢复正常的数据库性能。首先我们要明白什么是CPU使用率：CPU使用率是指在单位时间内CPU处于非空闲状态的时间比，反映了CPU的繁忙程度。某个进程的CPU使用率就是这个进程在一段时间内占用的CPU时间占总的时间的百分比。
mysqlcpu占用过高 排查
04-30
当MySQL的CPU占用过高时，需要进行排查和解决。首先，可以通过检查MySQL的日志和慢查询日志来确定是否存在查询效率低下的问题。这些日志通常可以找到查询的异常，并可以进行相应的优化。 其次，检查服务器的硬件和系统配置是否满足MySQL的运行要求。如果硬件性能较低，可能会导致MySQL的运行速度变慢。在这种情况下，需要考虑升级硬件来提高性能。 还需要检查MySQL的配置文件，特别是缓存配置。一些设置不正确的缓存大小可能会导致CPU占用过高。在这种情况下，需要正确地配置缓存，以确保性能最优。 还可以使用一些工具来评估MySQL的性能和优化需求，例如使用Percona工具来评估和优化MySQL的设置和配置。 最后，还可以考虑使用新版本的MySQL或使用其他数据库系统来替代MySQL。这些解决方案可能会提供更好的性能和稳定性，可能会更适合特定的应用需求。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

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

余额充值