首页 > > 网络编程 > Mysql >

Python--过滤Mysql慢日志

2018-06-17 23:08:35来源：未知阅读 ()

##================================================================##

先来个笑话：

刚交的女朋友让我去他家玩，我觉得空手不好，于是告诉她我买点东西就过去，谁知道她突然来一句:不用买，家里还有一盒！

##================================================================##

昨天在折腾一台磁盘出现性能问题的MySQL时，根据监控去慢日志里找，发现短短10分钟有好几万行慢日志，由于对慢日志结构不熟悉，打开一个600+MB的文件拖着滚动条看，好多慢SQL一条SQL就有几百行，看来看去都没找到问题最根本原因，总算功夫不负有心人，总算在不经意间找到，要不然让我在无知中慢慢老去？？？

问题SQL如下：

造成的影响：

怀疑造成问题的原因：

由于子查询中没有任何过滤条件，进行全表扫描，而服务器内存不够，导致扫描出来的数据有写到临时文件中，同时服务器上还有其他业务运行，服务器无法及时处理完所有请求，造成积压，而积压请求又增加服务器压力，最终导致相互影响恶性循环。

也不知道研发为啥蛋疼地这么套一下，反正Mysql语句优化处理能力这么差，大家尽可能简化SQL吧

##===================================================##

言归正传，由于这一个SQL执行频率较低，使用pt工具分析不能准确定位到该SQL，而使用肉眼看又比较蛋疼，因此写个小工具来处理，同时还可以解决慢日志较大阅读不便的问题。

python脚本如下：

# coding: utf-8
import datetime
from optparse import OptionParser

MAX_QUERY_TIME_SECONDS = 10
START_DATETIME = datetime.datetime.min
END_DATETIME = datetime.datetime.max
SLOW_LOG_PATH = ""


class EventInfo(object):
    def __init__(self):
        self.exec_time = None
        self.login_user = ""
        self.query_time_seconds = 0
        self.lock_time_seconds = 0
        self.rows_sent = 0
        self.rows_examined = 0
        self.command_text = ""
        self.file_line_num = 0


def get_datetime_by_time_line(time_line):
    if time_line.startswith("# Time:"):
        time_str = "20" + time_line.replace("# Time:", "").replace("  ", " ").strip()
        return datetime.datetime.strptime(time_str, "%Y%m%d %H:%M:%S")
    else:
        return None


def set_query_info(event_info, query_info_line):
    line = query_info_line
    if line.startswith("# Query_time:"):
        tmp_line = line.replace("#", "").replace(": ", ":").replace("  ", " ").replace("  ", " ")
        tmp_items = tmp_line.split(" ")
        for tmp_item in tmp_items:
            # print(tmp_item)
            if tmp_item.strip().startswith("Query_time:"):
                event_info.query_time_seconds = float(tmp_item.replace("Query_time:", ""))
            if tmp_item.strip().startswith("Lock_time:"):
                event_info.lock_time_seconds = float(tmp_item.replace("Lock_time:", ""))
            if tmp_item.strip().startswith("Rows_sent:"):
                event_info.rows_sent = float(tmp_item.replace("Rows_sent:", ""))
            if tmp_item.strip().startswith("Rows_examined:"):
                event_info.rows_examined = float(tmp_item.replace("Rows_examined:", ""))


def set_login_user(event_info, login_user_line):
    line = login_user_line
    if line.startswith("# User@Host:"):
        event_info.login_user = line.replace("# User@Host:", "").strip()


def set_command_text(event_info, command_text_line):
    line = command_text_line
    event_info.command_text += chr(13) + line


def print_event_info(event_info):
    print_info = """
file_line_num:      {7}
Time:               {0}
User@Host:          {1}
Query_time:         {2:.5f} seconds
Lock_time:          {3:.5f} seconds
Rows_sent:          {4}
Rows_examined:      {5}
command_text:
{6}
""".format(event_info.exec_time,
           event_info.login_user,
           event_info.query_time_seconds,
           event_info.lock_time_seconds,
           event_info.rows_sent,
           event_info.rows_examined,
           event_info.command_text,
           event_info.file_line_num)
    print("*" * 70)
    print(print_info)


def get_user_paras():
    try:
        opt = OptionParser()
        opt.add_option('--slow_log',
                       dest='slow_log',
                       type=str,
                       help='the file path of the slow log ')
        opt.add_option('--long_query_seconds',
                       dest='long_query_seconds',
                       type=float,
                       help='long query seconds')
        opt.add_option('--start_datetime',
                       dest="start_datetime",
                       type=str,
                       help="start datetime, format is %Y-%m-%d %H:%M:%S")
        opt.add_option('--end_datetime',
                       dest="end_datetime",
                       type=str,
                       help="end datetime,  format is %Y-%m-%d %H:%M:%S")
        (options, args) = opt.parse_args()
        error_messages = []
        slow_log = options.slow_log
        start_datetime = options.start_datetime
        long_query_seconds = options.long_query_seconds
        end_datetime = options.end_datetime
        if not slow_log:
            error_messages.append("slow_log must be set;")
        else:
            global SLOW_LOG_PATH
            SLOW_LOG_PATH = slow_log
        if not long_query_seconds:
            error_messages.append("long_query_seconds must be set;")
        else:
            global MAX_QUERY_TIME_SECONDS
            MAX_QUERY_TIME_SECONDS = long_query_seconds
        try:
            if start_datetime is not None:
                tmp_start_datetime = datetime.datetime.strptime(start_datetime, "%Y-%m-%d %H:%M:%S")
                global START_DATETIME
                START_DATETIME = tmp_start_datetime
        except:
            error_messages.append("the format of start_datetime was wrong ;")
        try:
            if end_datetime is not None:
                tmp_end_datetime = datetime.datetime.strptime(end_datetime, "%Y-%m-%d %H:%M:%S")
                global END_DATETIME
                END_DATETIME = tmp_end_datetime
        except:
            error_messages.append("the format of end_datetime was wrong ;")

        if len(error_messages) > 0:
            opt.print_help()
            for error_message in error_messages:
                print(error_message)
            return False
        else:
            return True
    except Exception as ex:
        print("exception :{0}".format(str(ex)))
        return False


def read_log():
    global MAX_QUERY_TIME_SECONDS
    file_path = SLOW_LOG_PATH
    file_handler = open(file_path)
    line = file_handler.readline()
    line_num = 1
    find_event_count = 0
    current_event_info = EventInfo()
    current_time = ""
    has_time = False
    while line:
        if line.startswith("# Time:"):
            current_time = get_datetime_by_time_line(line)
            if current_time < START_DATETIME:
                line = file_handler.readline()
                line_num += 1
                continue
            if current_time > END_DATETIME:
                break

            line = file_handler.readline()
            line_num += 1
            has_time = True
            continue
        if has_time:
            if line.startswith("# User@Host:"):
                if current_event_info.query_time_seconds > MAX_QUERY_TIME_SECONDS:
                    print_event_info(event_info=current_event_info)
                    find_event_count += 1
                current_event_info = EventInfo()
                current_event_info.file_line_num = line_num
                current_event_info.exec_time = current_time
                set_login_user(event_info=current_event_info,
                               login_user_line=line)
            elif line.startswith("# Query_time:"):
                set_query_info(event_info=current_event_info,
                               query_info_line=line)
            else:
                set_command_text(event_info=current_event_info,
                                 command_text_line=line)
        line = file_handler.readline()
        line_num += 1
    print("*" * 70)
    print("parse completely")
    print("find {0} events".format(find_event_count))


def main():
    if get_user_paras():
        read_log()


if __name__ == '__main__':
    main()

脚本调用方式如：

C:\Python27\python.exe D:/GGA/Code/slow_query_find/find_slow_quer.py --slow_log="D:\slow.log.2016010900" --long_query_seconds=4 --start_datetime="2016-01-08 15:46:00" --end_datetime="2016-01-08 15:50:00"

显示结果如：