Shulou(Shulou.com)06/01 Report--

The audit platform developed by the group recently launched the function of slow SQL optimization, and topN slow SQL can be obtained through mysqldumpslow, but because the output information of mysqldumpslow does not contain the database, it makes the subsequent automatic optimization of the program a bit tricky. After observing the MySQL slow log structure, I decided to write a python parser myself, and the result returned is more than the database name column on the mysqldumpslow resolution result:

Count: 15 Time=0.002961s (0.034505s) Lock=8.8e-05s (0.000767s) Rows=446 (6690), dbmgr [dbmgr] @ 10.33.46.179 mysql-- database SHOW GLOBAL VARIABLES to which SQL belongs

Python version: 2.7

File name: slowParse.py-currently, only topN can be fetched by query time

Code content:

Import osimport sysdef get_sql (slowlog, topN): # directory where Slow log is located Please replace F1 = open ("/ var/mysql/data3306/" + slowlog) yourself "r") flag1 = 1 flag2 = 0 sqltext = "" slow_sql_all = {} sql_info = [] queryTime_list = [] locksTime_list = [] rows_list = [] logonInfo_list = [] db = "None" rownum = 0 while 1: line = f1.readline () rownum + = 1 if not line: break Elif "use" in line and len (line) < 30: db = getDB (line.strip ()) elif "# User@Host" in line and flag1 = = 1: flag2 = 1 userAndHost = getUserAndHost (line.strip ()) logonInfo = userAndHost [0] + "+ userAndHost [0] +" @ "+ userAndHost [1] logonInfo_list.append (logonInfo) Elif "# Query_time" in line and flag1 = = 1 and flag2 = = 1: execInfo = getExecInfo (line.strip ()) queryTime_list.append (execInfo [0]) locksTime_list.append (execInfo [1]) rows_list.append (execInfo [2]) elif " "in line and" SET timestamp "not in line and flag1 = = 1 and flag2 = 1: flag2 = 0 sqltext = sqltext + line.strip () if slow_sql_all.has_key (sqltext): tmp = slow_sql_ all [sqltext] tmp [0] .append (queryTime_list [0]) tmp [1] .append (locksTime_) List [0]) tmp [2] .append (rows_list [0]) tmp [3] .append (logonInfo_list [0]) else: # count = 1 # sql_info.append (count) sql_info.append (queryTime_list) sql_info.append (locksTime_ List) sql_info.append (rows_list) sql_info.append (logonInfo_list) sql_info.append (db) slow_sql_ all [sqltext] = sql_info queryTime_list = [] locksTime_list = [] rows_list = [] logonInfo_list = [ ] sqltext = "" sql_info = [] elif flag1 = = 1 and flag2 = = 1 and "# User@Host" not in line and "# Query_time" not in line and "# Time" not in line and "SET timestamp" not in line: sqltext = sqltext + line.strip () + "f1.close () sqlCombined = {} sqlTmp = {} for i in slow_sql_all: # print I Slow_sql_ [I] count = len (slow_sql_ all [I] [0]) totalQueryTime = 0 totalLocksTime = 0 totalRows = 0 for j in slow_sql_ all [I]: totalQueryTime + = float (j) maxQueryTime = float (max (slow_sql_ all [I] [0])) for k in slow_sql_ all [I] [1]: TotalLocksTime + = float (k) maxLocksTime = float (max (slow_sql_ all [I])) for l in slow_sql_ all [2]: totalRows + = int (l) maxRows = int (max (slow_sql_ all [2])) logonInfo = slow_sql_ all [I] [3] [0] db = slow_sql_all [ I] [4] sqlCombined [I] = (count MaxQueryTime, totalQueryTime, maxLocksTime, totalLocksTime, maxRows, totalRows, logonInfo, db) sqlTmp [I] = maxQueryTime sqlTopN = sorted (sqlTmp.items (), key=lambda x: X [1], reverse=True) [: topN] # Please replace the generated file directory f2 = open ("/ var/mysql/data3306/" + slowlog [:-4] + "- top" + str (topN) + ".txt" "w") for i in sqlTopN: sqltext = I [0] count_str = "Count:" + str (sqlCombined [sqltext] [0]) queryTime_str = "Time=" + str (sqlCombined [sqltext] [1]) + "s (" + str (sqlCombined [sqltext] [2]) + "s)" locksTime_str = "Lock=" + str (sqlCombinded [sqltext] [3]) + "s (" + str (sqlCombined)) [sqltext] [4]) + "s)" rows_str = "Rows=" + str (sqlCombined [sqltext] [5]) + "(" + str (sqlCombined [sqltext] [6]) + ") "logonInfo_str = sqlCombined [sqltext] [7] db_str = sqlCombined [sqltext] [8] f2.write (count_str +"+ queryTime_str +" + locksTime_str + "+ rows_str +"+ logonInfo_str +" + db_str + "\ n" + sqltext + "\ n") f2.close () def getDB (line): info = line .split ("") db = info [1] [:-1] return dbdef getUserAndHost (line): info_list = line.split ("") User = info_list [2] .split ("[") [0] idx = info_list.index ("@") hostInfo = info_ ListListIDX + 2] if hostInfo = "[]": Host = "localhost" else: Host = hostInfo [1:-1] return User Hostdef getExecInfo (line): info_list = line.split ("") Query_time = info_list [0] .split ("") [2] Lock_time = info_list [1] .split ("") [1] Rows_sent = info_list [1] .split (") [3] return Query_time, Lock_time Rows_sentif _ _ name__ = ='_ main__': filename = str (sys.argv [1]) topN = int (sys.argv [2]) get_sql (filename, topN)

Use:

Python slowParse.py slow.log 5-- take top 5

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.