27

python练手脚本-自动结束mysql慢查询会话

 4 years ago
source link: https://www.tuicool.com/articles/BZ3q6rA
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.

生产环境的有些sql查询写得太复杂，或是表很大，对应索引未建立或建立不合理，或是查询未充分使用索引等，就有可能出现慢查询，一些慢查询需要修改程序，可能没那么快能解决，这时如果有个脚本能自动检测符合条件的慢查询会话并结束，那么是很方便的，当然运维人员也可顺便弄个检测慢查询并告警的脚本。

涉及知识点

  1. mysql慢查询会话查询
  2. schedule定时任务调度
  3. pymysql执行sql

代码分解

mysql慢查询

#会话查询，只能查询所有会话，不能按条件过滤，不过比较好记
show PROCESSLIST;

#从information_schema中查询会话，可以按条件过滤
SELECT
	*
FROM
	information_schema.`PROCESSLIST`;

#查询符合条件的慢会话，id是会话ID，info是正在执行的sql，time是会话持续时间，杀会话时注意要做好过滤
SELECT
	id,
	info,
	time
FROM
	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 schedule
import time

def 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 time

import pymysql
import 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)

report

Recommend

About Joyk


Aggregate valuable and interesting links.
Joyk means Joy of geeK