分析mysqlslow 的python脚本|能根据时间以及慢执行时间定义查询统计

2018-01-16 23:28:14  阅读 682 次 评论 0 条

分析mysqlslow 的python脚本

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

MAX_QUERY_TIME_SECONDS = 10START_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 = 0def 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 Nonedef 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) + linedef 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 Falsedef 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()

脚本调用方式如:

python python_slow.py --slow_log="mysql.slow" --long_query_seconds=10 --start_datetime="2018-01-16 01:00:00" --end_datetime="2018-01-16 23:50:00"


执行结果

mysqlslow.png

本文地址:http://blog.ailinux.net/post/37.html
版权声明:本文为原创文章,版权归 admin 所有,欢迎分享本文,转载请保留出处!

发表评论


表情

还没有留言,还不快点抢沙发?