MySQL 5.7定位消耗CPU高的SQL

MySQL 5.7.23
1.先查看CPU飙高的线程,top -H -p <mysqld进程id>
top - 15:09:26 up 135 days, 7:11, 2 users, LOAD average: 0.12, 0.08, 0.02
Tasks: 34 total, 1 running, 33 sleeping, 0 stopped, 0 zombie
Cpu(s): 12.7%us, 0.0%sy, 0.0%ni, 87.3%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st
Mem: 32880780k total, 17577884k used, 15302896k free, 601816k buffers
Swap: 4128760k total, 3916k used, 4124844k free, 7478260k cached

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
23974 mysql 20 0 1658m 358m 12m R 99.9 1.1 0:05.52 mysqld
12295 mysql 20 0 1658m 358m 12m S 0.3 1.1 0:02.44 mysqld
12283 mysql 20 0 1658m 358m 12m S 0.0 1.1 0:01.60 mysqld
12284 mysql 20 0 1658m 358m 12m S 0.0 1.1 0:00.00 mysqld
12285 mysql 20 0 1658m 358m 12m S 0.0 1.1 0:02.63 mysqld
12286 mysql 20 0 1658m 358m 12m S 0.0 1.1 0:02.24 mysqld
12287 mysql 20 0 1658m 358m 12m S 0.0 1.1 0:01.68 mysqld
12288 mysql 20 0 1658m 358m 12m S 0.0 1.1 0:01.77 mysqld
12289 mysql 20 0 1658m 358m 12m S 0.0 1.1 0:01.64 mysqld
12290 mysql 20 0 1658m 358m 12m S 0.0 1.1 0:01.80 mysqld
12291 mysql 20 0 1658m 358m 12m S 0.0 1.1 0:02.37 mysqld

2.使用MySQL内部的表定位问题SQL。
SELECT a.THREAD_OS_ID,b.user,b.host,b.db,b.command,b.time,b.state,b.info
FROM performance_schema.threads a,information_schema.processlist b
WHERE b.id = a.processlist_id;
THREAD_OS_ID USER HOST db command TIME state info


   20891  root    10.10.18.153:1499   jira    SLEEP       345                (NULL)                                                                    
   20892  root    10.10.18.153:1500   jira    SLEEP       345                (NULL)                                                                    
   23651  root    10.10.18.201:19387  (NULL)  SLEEP      2198                (NULL)                                                                    
   23650  root    10.10.18.201:21445  (NULL)  QUERY         0  executing     SELECT a.THREAD_OS_ID,b.user,b.host,b.db,b.command,b.time,b.state,b.info  
                                                                             FROM performance_schema.threads a,information_schema.processlist b        
                                                                              WHERE b.id = a.processlist_id LIMIT 0, 1000                              
   23970  root    10.10.18.201:21446  (NULL)  SLEEP       199                (NULL)                                                                    
   23974  root    10.10.18.201:21466  sys     QUERY        29  Sending DATA  SELECT a.* FROM test a,test b,test c ,test d                              
                                                                             ORDER BY a.value                                                          
                                                                              LIMIT 0, 1000                                                            
<think>嗯,用户问的是在MySQL 5.7.35中如何确定当前会话中哪个用了CPU。首先,我需要回忆一下MySQL的性能监控方法。通常,CPU使用可能由长时间运行的查询或者大量复杂计算导致。 首先想到的是,应该查看当前正在运行的进程。MySQL的SHOW PROCESSLIST命令可以显示当前的所有连接和它们的状态。这个命令能快速查看哪些线程在执行,是否有长时间运行的查询。不过,SHOW PROCESSLIST可能不会直接显示CPU使用情况,但可以通过查看执行时间和状态来推断。 然后,考虑使用性能模式(Performance Schema)。MySQL 5.7默认启用了Performance Schema,可以用来收集更详细的信息。比如,查看events_statements_current表,找到当前执行的语句,分析其消耗的资源。可能需要检查相关的表,如threads、events_statements_current等,关联线程和查询信息。 另外,系统工具如top或htop可能帮助定位CPUMySQL线程。通过top命令查看MySQL进程的CPU使用情况,记录下对应的线程ID,再在MySQL中根据线程ID查找对应的会话。这里要注意将操作系统的线程ID与MySQL的线程ID进行转换,可能需要使用PERFORMANCE_SCHEMA中的THREAD_OS_ID字段。 还有,慢查询日志也可能有用。虽然慢查询日志通常记录执行时间超过阈值的查询,但CPU的查询可能也会被记录,尤其是当它们执行时间长的时候。可以检查慢查询日志,看看是否有频繁出现的消耗查询。 不过,用户可能没有开启慢查询日志,或者需要实时分析。这时候,使用information_schema中的PROCESSLIST表可能更直接。例如,查询SELECT * FROM information_schema.PROCESSLIST WHERE COMMAND != 'Sleep' ORDER BY TIME DESC; 找出运行时间长的查询。 另外,可能需要考虑查询的执行计划,分析是否缺少索引或者有全表扫描的情况,这些都会导致CPU。可以用EXPLAIN命令来检查查询的执行计划。 还有,MySQL的内置工具如mysqladmin processlist或者第三方监控工具如Percona Toolkit中的pt-query-digest也可能有帮助,但用户可能更希望使用原生的方法。 综合这些方法,分步骤给出解决方案:首先使用SHOW PROCESSLIST或查询information_schema.PROCESSLIST找出活动会话,然后结合Performance Schema或操作系统工具定位CPU线程,最后分析具体查询优化。</think>在 MySQL 5.7.35定位 CPU 用的会话,可以通过以下步骤实现: --- ### **方法 1:通过 `SHOW PROCESSLIST` 快速诊断** ```sql SHOW FULL PROCESSLIST; ``` - **观察字段**: - `Time`:查询已执行的时间(秒) - `State`:线程状态(如 `Sending data`、`Sorting result` 可能负载) - `Info`:正在执行的 SQL 语句 - **重点关注**:长时间运行(`Time` 值大)或状态异常的查询。 --- ### **方法 2:通过 `performance_schema` 深度分析** 1. **查看活跃线程的 CPU 消耗**: ```sql SELECT THREAD_ID, EVENT_NAME, SUM_TIMER_WAIT/1e12 AS CPU_SECONDS FROM performance_schema.events_waits_summary_by_thread_by_event_name WHERE EVENT_NAME LIKE 'wait/io/file/%' OR EVENT_NAME LIKE 'wait/io/table/%' OR EVENT_NAME LIKE 'wait/synch/%' GROUP BY THREAD_ID ORDER BY CPU_SECONDS DESC LIMIT 10; ``` 2. **关联线程与 SQL 语句**: ```sql SELECT t.THREAD_OS_ID, p.USER, p.HOST, p.DB, p.TIME, p.STATE, LEFT(p.INFO, 200) AS SQL_Snippet FROM performance_schema.threads t JOIN information_schema.PROCESSLIST p ON t.PROCESSLIST_ID = p.ID WHERE t.THREAD_OS_ID = [操作系统线程ID]; ``` --- ### **方法 3:操作系统级定位** 1. 通过 `top` 或 `htop` 找到 CPUMySQL 线程: ```bash top -H -p $(pgrep mysqld) ``` - 记录 `PID`(操作系统线程 ID)。 2. **将 OS 线程 ID 转换为 MySQL 线程 ID**: ```sql SELECT THREAD_ID, PROCESSLIST_ID, THREAD_OS_ID FROM performance_schema.threads WHERE THREAD_OS_ID = [OS_PID]; ``` --- ### **方法 4:慢查询日志辅助** 1. **启用慢查询日志**(临时): ```sql SET GLOBAL slow_query_log = 1; SET GLOBAL long_query_time = 1; -- 记录超过 1 秒的查询 SET GLOBAL log_queries_not_using_indexes = 1; ``` 2. **分析日志**: ```bash mysqldumpslow -s t /path/to/slow.log ``` --- ### **优化建议** 1. CPU 查询使用 `EXPLAIN` 分析执行计划: ```sql EXPLAIN [SQL语句]; ``` 2. 检查索引有效性,避免全表扫描。 3. 监控 `CPU_USAGE` 工具(如 `sys` 库): ```sql SELECT * FROM sys.session WHERE cpu_time > 1000; ``` --- ### **总结步骤** 1. 用 `SHOW PROCESSLIST` 快速定位可疑会话。 2. 通过 `performance_schema` 或操作系统工具确认 CPU 线程。 3. 关联线程 ID 到具体 SQL 语句。 4. 分析并优化对应查询。
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

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

抵扣说明:

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

余额充值