深圳全飞鸿

标题: mysql的CPU高的一次排查过程，QPS/TPS/ [打印本页]

---

作者: zhgc    时间: 2020-10-28 21:40
标题: mysql的CPU高的一次排查过程，QPS/TPS/


mysql的CPU持续占用较高的排查方法

从两个方面去排查：

1. 应用负载（QPS）高
2. 查询执行成本高


第二种情况，通过processlist和slow_log基本可以定位问题

本次出现的问题是QPS高导致的

看COM参数：
show global status like 'com_select %'

select (563449524 -81055264)/14.96  

com_select  =   1551
com_insert  =    38
com_delete  =    2.25
com_update  =  31.6

基本正常！

看全局参数：

show global status like 'Questions%'
show global status like 'Queries%'

select (88556393034-88555538113) /13.60
Questions=  1879.7459 /秒
Queries=  51038.1851 /秒

每秒执行查询5万次，这就有很大的问题

Questions与Queries的差异：


说明问题出在SP里。

要定位问题，只能开启general_log:
set global general_log=0;
truncate table mysql.general_log ;
ALTER TABLE mysql.general_log ENGINE = MYISAM;
set global log_output='TABLE';
set global general_log=1;


然后用以下SQL统计出最多的SQL：
select * from (select argument, count(*) as number from mysql.general_log  group by argument)t
     order by number DESC;

---

作者: zhgc    时间: 2020-12-2 09:23
第二次的数据：




select group_name,wip_group ,count(1) from sfc.r107 where  rework_no='FFFBBB12899'
group by group_name,wip_group


select distinct special_route from sfc.r107 where  rework_no='FFFBBB12899'

select * from sfc.c_route_control_t where route_code in (360,478)  and state_flag=0 and group_next='TEST2'


SELECT * FROM ZJU.zj_sn_t

show global status like 'com_update%'

select (3120579541 -3120552173)/14.50   1887  select
select (200886149 -200885024)/22.67   49  insert
select (9664667 -9664624)/12.79   3.362  delete
select (139105588 -139105102)/14.59   33.31  update


show global status like 'Questions%'
show global status like 'Queries%'


select (2052361970 -2052343439)/14.01   1322.69  Questions
select (125716072948 -125715676658)/14.04  28225.7835  Questions

select count(1) from mysql.general_log  limit 1

select * from mysql.general_log



CALL SYANT.SESSION_START_6P('SMTLoading.exe', '1.0.1.5', 'C:\\SFIS_AMS\\SMTLoading.exe', '192.168.3.136', '94:C6:913:BC:68', 0, @RES); SELECT @RES AS '@RES'

set global general_log=0;
truncate table mysql.general_log ;

set global general_log=1;


然后用以下SQL统计出最多的SQL：
select * from (select argument, count(*) as number from mysql.general_log  group by argument)t
     order by number DESC;


select * from syant.z_session  where exe='SMTLoading.exe' and ip='192.168.3.136'
192.168.3.136

kill 53770644;
kill 54295063;
kill 54295308;
kill 54295334;
kill 54295355;
kill 54295927;
kill 54296036;
kill 54296669;

---

欢迎光临 深圳全飞鸿 (http://www.nagomes.com/disc/)

Powered by Discuz! X3.2