如何快速定位到影响mysql cpu飙升的原因—

本文主要是介绍如何快速定位到影响mysql cpu飙升的原因——筑梦之路，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

通常我们只需要执行show processlist 进行查看，一般执行时间最长的SQL八九不离十就是罪魁祸首，但当show processlist的输出有近千条，那么很难第一眼就发现有问题的SQL，那么如何快速找到呢？其实也非常简单。我们知道mysqld是单进程多线程。那么我们可以使用top获取mysqld进程的各个线程的cpu使用情况。

top -H -p mysqld_pid

测试sql:

select * from cpu_h order by rand() limit 1;

可以看到是线程22682占用cpu比较高，接着通过查看performance_schema.threads表，找到相关SQL

select * from performance_schema.threads where thread_os_id=22682

再通过show processlist看

找到processlist id以后，就可以直接使用命令kill。

python脚本：

（统计活跃线程SQL执行的次数）

#!/usr/bin/python# -*- coding:utf-8 -*-import argparseimport MySQLdbimport argparseimport commandsimport sysimport MySQLdb.cursorsfrom warnings import filterwarningsfrom warnings import resetwarningsfilterwarnings('ignore', category = MySQLdb.Warning)reload(sys)sys.setdefaultencoding('utf8')def init_parse():parser = argparse.ArgumentParser(epilog='by yayun @2022',)parser.add_argument('-n','--num',required=False,default=1,help='获取多少条最耗费cpu的记录,默认1条')parser.add_argument('-a','--active',action='store_true',default=False,help='统计活跃线程各类SQL的条目数')return parserdef mysql_exec(sql):try:   conn=MySQLdb.connect(host='127.0.0.1',user='root',passwd='xx',port=3306,connect_timeout=15,charset='utf8')curs = conn.cursor()curs.execute(sql)conn.commit()curs.close()conn.close()except Exception,e:print "mysql execute: " + str(e)def mysql_query(sql):conn=MySQLdb.connect(host='127.0.0.1',user='root',passwd='xx',port=3306,connect_timeout=15,charset='utf8',cursorclass = MySQLdb.cursors.DictCursor)cursor = conn.cursor()count=cursor.execute(sql)if count == 0 :result=0else:result=cursor.fetchall()return resultcursor.close()conn.close()if __name__ == '__main__':parser = init_parse()args = parser.parse_args()slow_sql_numbers=args.numactive_thread_status=args.activemysqld_pid = commands.getoutput("cat /data/mysql/3306/pid_mysql.pid")get_mysql_thead_cmd="top -H -p %s  -n 1 | grep mysqld | head -n %s | awk '{print $1,$2}' | sed 's/mysql//g'" % (mysqld_pid,slow_sql_numbers)tmp_mysqld_thread=commands.getoutput(get_mysql_thead_cmd).split()mysqld_thread=[]for i in tmp_mysqld_thread:try:a=i.replace('\x1b[0;10m\x1b[0;10m','')a=i.replace('\x1b[0;10m','')mysqld_thread.append(int(a))except Exception,e:passactive_thread_sql="select  * from ( select max(user) as user,max(db) as db , max(info) as runningsql,count(*) as rungingsql_cnt from information_schema.processlist where db is not null  and info is not null and info like '%SELECT%' group by user, db, md5(info)   union all     select max(user) as user,max(db) as db ,max(info) as runningsql,count(*) as rungingsql_cnt from information_schema.processlist where db is not null  and info is not null and info like 'UPDATE%' group by user, db,md5(info)  ) a order by 4 desc limit 10"if active_thread_status:resut=mysql_query(active_thread_sql)if resut:for i in resut:print "运行条目统计: %s | 运行SQL: %s  | 运行用户:%s " % (i['rungingsql_cnt'],i['runningsql'],i['user'])print "============================================="    processlist_id=[]if len(mysqld_thread) >= 2:new_mysqld_thread=tuple(mysqld_thread)get_slow_sql="select PROCESSLIST_ID,PROCESSLIST_USER,PROCESSLIST_HOST,\PROCESSLIST_DB,PROCESSLIST_TIME,PROCESSLIST_INFO from performance_schema.threads where thread_os_id in %s" % (new_mysqld_thread,)else:new_mysqld_thread=mysqld_threadget_slow_sql="select PROCESSLIST_ID,PROCESSLIST_USER,PROCESSLIST_HOST,\PROCESSLIST_DB,PROCESSLIST_TIME,PROCESSLIST_INFO from performance_schema.threads where thread_os_id=%s" % (new_mysqld_thread[0])new_resut=mysql_query(get_slow_sql)for b in new_resut:if b['PROCESSLIST_ID']:processlist_id.append(b['PROCESSLIST_ID'])if b['PROCESSLIST_INFO'] != None:print "SQL已运行时间: %s秒|执行SQL用户: %s |执行SQL来源ip: %s |操作的库: %s |SQL详情: %s |PROCESSLIST ID: %s" % (b['PROCESSLIST_TIME'],b['PROCESSLIST_USER'],b['PROCESSLIST_HOST'],b['PROCESSLIST_DB'],b['PROCESSLIST_INFO'],b['PROCESSLIST_ID'])if processlist_id:print "============================================="print "以上耗费CPU资源的SQL是否需要杀掉? 请输入Y/N"while True:in_content = raw_input("请输入：")if in_content == "Y":for p in processlist_id:sql="kill %s" % (p) mysql_exec(sql)exit(0)elif in_content == "N":print("退出程序！")exit(0)else:print("输入有误，请重输入！")