[MySQL优化案例]系列 — 典型性索引引发CPU负载飙升问题
1.停掉慢查询
2.停掉无关的服务
3.停掉从库
1.在刚开始的时间，导出show full processlist;
mysql -uroot -p'xx' -Ne "show full processlist;" > /root/process.txt
2.pt-ioprofile --profile-pid=3946 --cell=sizes
2017年 09月 21日 星期四 15:23:38 CST
114688 114688 0 0 0 0 0 0 0 a表
53098 49152 0 0 0 0 3946 0 0 b表
3.比对这两者，注意pread和Sending data
明显是查询慢，以及并发查询导致，io升高
4.最后测试一下这台机器的io
time dd if=/dev/sdb of=/testrw.dbf bs=4k
5.后期，数据库剥离，方便排错
6.set profiling=1;
show profile cpu,block io for query 1;
可能的原因
1.是经常执行的sql没有加索引，导致执行时间有时候非常长。导致cpu占有到700%
2.pt工具引起的，因为使用pt工具处理慢查询，如果pt和mysql运行同一个磁盘上，也可能产生io瓶颈
分析的过程
用show processlist 看进程是查询慢还是update慢
用show OPEN TABLES where In_use > 0; 查询锁表
最后用lepus查询下，以下sql语句有可能是错的
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
|
AAAAA BBBBB (为慢查询用到的库名)
select t2.checksum,t2.sample,t2.last_seen,t2.ts_cnt,
t2.Query_time_avg,t2.Query_time_max,t2.Query_time_sum,t2.Lock_time_max,t2.Lock_time_sum
from
(select a.checksum,a.fingerprint,a.sample,a.first_seen,a.last_seen,b.serverid_max,
b.db_max,b.user_max,b.ts_min,b.ts_max,
sum
(b.ts_cnt) as ts_cnt,
sum
(b.Query_time_sum)
/
sum
(b.ts_cnt) as Query_time_avg,
max
(b.Query_time_max) as Query_time_max,
min
(b.Query_time_min) as Query_time_min,
b.Query_time_sum as Query_time_sum,
max
(b.Lock_time_max) as Lock_time_max,
min
(b.Lock_time_min) as Lock_time_min,
sum
(b.Lock_time_sum) as Lock_time_sum
from
mysql_slow_query_review a join mysql_slow_query_review_history b
where a.checksum
=
b.checksum
and
b.serverid_max
=
'275'
group by a.checksum ) t2 where
-
-
t2.Query_time_avg>
4
and
t2.ts_cnt>
1000
t2.sample like
'%AAAAA%'
and
t2.sample like
'%BBBBB%'
and
t2.last_seen > date_sub(now(),interval
2
week)
and
t2.sample like
'%count(1)%'
order by t2.ts_cnt desc limit
1
-
-
order by Query_time_sum desc
|
或者
select * from mysql_slow_query_review_history
where sample like '%count(1)%'
and ts_max > '2017-09-21 00:00:00' and ts_max > '2017-09-21 15:00:00'
and db_max = 'db'
show processlist 里面组好能用explain 解析下，这是最靠谱的办法。
还有几个误区
1.Query_time: 4173.803182 Lock_time: 0.000081
2.show processlist 比 直接看binlog执行时间要差点
总结
query time 很严重，要特别注意
binlog执行时间
mysql优化无非是内存，cpu，io，目前看来cpu高，就是慢查询太多，好多sql未加索引
本文转自 liqius 51CTO博客，原文链接：http://blog.51cto.com/szgb17/1967526，如需转载请自行联系原作者
