生产环境的有些sql查询写得太复杂,或是表很大,对应索引未建立或建立不合理,或是查询未充分使用索引等,就有可能出现慢查询,一些慢查询需要修改程序,可能没那么快能解决,这时如果有个脚本能自动检测符合条件的慢查询会话并结束,那么是很方便的,当然运维人员也可顺便弄个检测慢查询并告警的脚本。
涉及知识点
- mysql慢查询会话查询
- schedule定时任务调度
- pymysql执行sql
代码分解
mysql慢查询
#会话查询,只能查询所有会话,不能按条件过滤,不过比较好记show PROCESSLIST;#从information_schema中查询会话,可以按条件过滤SELECT *FROM information_schema.`PROCESSLIST`;#查询符合条件的慢会话,id是会话ID,info是正在执行的sql,time是会话持续时间,杀会话时注意要做好过滤SELECT id, info, timeFROM information_schema.`PROCESSLIST`WHERE info LIKE '%select * from table%'AND time > 10;#直接使用sql批量杀会话,拼接kill xxx;后,拷贝了在控制台执行SELECT concat('KILL ', id, ';')FROM information_schema.`PROCESSLIST`WHERE info LIKE '%select * from table%'AND time > 10;脚本主入口
if __name__ == '__main__': #每5秒执行检查任务 schedule.every(5).seconds.do(kill_slow) #此处固定写法,意思是每秒钟schedule看下是否有pending的任务,有就执行 while True: schedule.run_pending() time.sleep(1)schedule的其它示例import scheduleimport timedef job(message='stuff'): print("I'm working on:", message)#每10分钟schedule.every(10).minutes.do(job)#每小时schedule.every().hour.do(job, message='things')#每天10点30分schedule.every().day.at("10:30").do(job)while True: schedule.run_pending() time.sleep(1)pymysql使用
# 连接数据库,设置结果集用dict返回,autocommit自动提交事务db = pymysql.connect(host='localhost', db='dbname', user='root', passwd='admin', port=3306, charset='utf8', cursorclass=pymysql.cursors.DictCursor, autocommit=True)cursor = db.cursor()查询符合条件的慢会话并结束
def kill_slow(): cursor.execute( """ SELECT id, info, time FROM information_schema.`PROCESSLIST` WHERE info LIKE '%select * from table%' AND time > 10; """) slow_sessions = cursor.fetchall() for slow_session in slow_sessions: print("slow session detected, kill it:\n id:%s\nsql:%s" % ( slow_session[0], slow_session[1])) cursor.execute("kill %s", slow_session[0])完整代码
import timeimport pymysqlimport schedule# 连接数据库,设置结果集用dict返回,autocommit自动提交事务db = pymysql.connect(host='localhost', db='dbname', user='root', passwd='admin', port=3306, charset='utf8', cursorclass=pymysql.cursors.DictCursor, autocommit=True)cursor = db.cursor()def kill_slow(): cursor.execute( """ SELECT id, info, time FROM information_schema.`PROCESSLIST` WHERE info LIKE '%select * from table%' AND time > 10; """) slow_sessions = cursor.fetchall() for slow_session in slow_sessions: print("slow session detected, kill it:\n id:%s\nsql:%s" % ( slow_session[0], slow_session[1])) cursor.execute("kill %s", slow_session[0])if __name__ == '__main__': # 每5秒执行检查任务 schedule.every(5).seconds.do(kill_slow) # 此处固定写法,意思是每秒钟schedule看下是否有pending的任务,有就执行 while True: schedule.run_pending() time.sleep(1)总结
以上所述是小编给大家介绍的python自动结束mysql慢查询会话的实例代码,希望对大家有所帮助!