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

Master Han Feng shared a mysql performance optimization tool, a python script that can automatically collect all kinds of information related to SQL optimization, which can save a lot of trouble for optimizers.

After trying it for a while, I found that it could not run under mysql5.7, but there were still several holes in the details. After some trouble, I finally stepped on the pit. Now explain the details and share the revised SQL.

Problem 1: when calling a script, if the SQL is enclosed in single quotation marks (as Mr. Han demonstrated: python mysql_tuning.py-p tuning_sql.ini-s' select xxx), but there will be an error. The solution: separate the script with double quotation marks, such as: python mysql_tuning.py-p tuning_sql.ini-s "select * from employees.dept_emp" so that there is no problem.

Problem 2: there is no reference to the string unit, and an error will be reported when using string.atoi. Solution: import string

After the problem 3:mysql5.7, several tables INFORMATION_SCHEMA.GLOBAL_VARIABLES, INFORMATION_SCHEMA.SESSION_VARIABLES and INFORMATION_SCHEMA.SESSION_STATUS of infomation_schema should be replaced with those under performance_schema

Question 4: when displaying the execution plan, table and type may also be NULL. You need to handle the null value, and the partitions field is not displayed.

Question 5 is probably a decimal, so using int to convert will report an error, and you need to use float

The problem 6:db_name shows up as user_pwd. This may not be a problem.

The modified script is as follows:

Click (here) to collapse or open

#! / usr/local/bin/python

Import datetime

Import getopt

Import sys

Import string

Import pprint

From warnings import filterwarnings

Import MySQLdb

Import ConfigParser

Import sqlparse

From sqlparse.sql import IdentifierList, Identifier

From sqlparse.tokens import Keyword, DML

Filterwarnings ('ignore', category = MySQLdb.Warning)

Seq1= "+"

Seq2= "-"

Seq3= "|"

SYS_PARM_FILTER = (

'BINLOG_CACHE_SIZE'

'BULK_INSERT_BUFFER_SIZE'

'HAVE_PARTITION_ENGINE'

'HAVE_QUERY_CACHE'

'INTERACTIVE_TIMEOUT'

'JOIN_BUFFER_SIZE'

'KEY_BUFFER_SIZE'

'KEY_CACHE_AGE_THRESHOLD'

'KEY_CACHE_BLOCK_SIZE'

'KEY_CACHE_DIVISION_LIMIT'

'LARGE_PAGES'

'LOCKED_IN_MEMORY'

'LONG_QUERY_TIME'

'MAX_ALLOWED_PACKET'

'MAX_BINLOG_CACHE_SIZE'

'MAX_BINLOG_SIZE'

'MAX_CONNECT_ERRORS'

'MAX_CONNECTIONS'

'MAX_JOIN_SIZE'

'MAX_LENGTH_FOR_SORT_DATA'

'MAX_SEEKS_FOR_KEY'

'MAX_SORT_LENGTH'

'MAX_TMP_TABLES'

'MAX_USER_CONNECTIONS'

'OPTIMIZER_PRUNE_LEVEL'

'OPTIMIZER_SEARCH_DEPTH'

'QUERY_CACHE_SIZE'

'QUERY_CACHE_TYPE'

'QUERY_PREALLOC_SIZE'

'RANGE_ALLOC_BLOCK_SIZE'

'READ_BUFFER_SIZE'

'READ_RND_BUFFER_SIZE'

'SORT_BUFFER_SIZE'

'SQL_MODE'

'TABLE_CACHE'

'THREAD_CACHE_SIZE'

'TMP_TABLE_SIZE'

'WAIT_TIMEOUT'

)

Def is_subselect (parsed):

If not parsed.is_group ():

Return False

For item in parsed.tokens:

If item.ttype is DML and item.value.upper () = = 'SELECT':

Return True

Return False

Def extract_from_part (parsed):

From_seen = False

For item in parsed.tokens:

# print item.ttype,item.value

If from_seen:

If is_subselect (item):

For x in extract_from_part (item):

Yield x

Elif item.ttype is Keyword:

Raise StopIteration

Else:

Yield item

Elif item.ttype is Keyword and item.value.upper () = = 'FROM':

From_seen = True

Def extract_table_identifiers (token_stream):

For item in token_stream:

If isinstance (item, IdentifierList):

For identifier in item.get_identifiers ():

Yield identifier.get_real_name ()

Elif isinstance (item, Identifier):

Yield item.get_real_name ()

# It's a bug to check for Keyword here, but in the example

# above some tables names are identified as keywords...

Elif item.ttype is Keyword:

Yield item.value

Def extract_tables (p_sqltext):

Stream = extract_from_part (sqlparse.parse (p_sqltext) [0])

Return list (extract_table_identifiers (stream))

Def f_find_in_list (myList,value):

Try:

For v in range (0jinlen (myList)):

If value==myList [v]:

Return 1

Return 0

Except:

Return 0

Def f_get_parm (p_dbinfo):

Conn = MySQLdb.connect (host=p_dbinfo [0], user=p_dbinfo [1], passwd=p_dbinfo [2], db=p_dbinfo [3])

Cursor = conn.cursor ()

Cursor.execute ("select lower (variable_name), variable_value from performance_schema.global_variables where upper (variable_name) in ('" + ",'" .join (list (SYS_PARM_FILTER)) + ") order by variable_name")

Records = cursor.fetchall ()

Cursor.close ()

Conn.close ()

Return records

Def f_print_parm (p_parm_result):

Print "= SYSTEM PARAMETER ="

Status_title= ('parameter_name','value')

Print "+-- +-- +"

Print seq3,status_title [0] .center (30)

Print seq3,status_title [1] .center (58), seq3

Print "+-- +-- +"

For row in p_parm_result:

Print seq3,row [0] .ljust (30)

If 'size' in row [0]:

If string.atoi (row [1]) > = 10240241024:

Print seq3, (str (round (string.atoi (row [1]) / 1024 plus 1024)) +'G'). Rjust (58), seq3

Elif string.atoi (row [1]) > = 1024024:

Print seq3, (str (round (string.atoi (row [1]) / 1024 + 1024) +'M'). Rjust (58), seq3

Elif string.atoi (row [1]) > = 1024:

Print seq3, (str (round (string.atoi (row [1]) / 1024 (2) +'K'). Rjust (58), seq3

Else:

Print seq3, (row [1] +'B'). Rjust (58), seq3

Else:

Print seq3,row [1] .rjust (58), seq3

Print "+-- +-- +"

Print

Def f_print_optimizer_switch (p_dbinfo):

Print "= OPTIMIZER SWITCH ="

Db= MySQLdb.connect (host=p_dbinfo [0], user=p_dbinfo [1], passwd=p_dbinfo [2], db=p_dbinfo [3])

Cursor = db.cursor ()

Cursor.execute ("select variable_value from performance_schema.global_variables where upper (variable_name) = 'OPTIMIZER_SWITCH'")

Rows = cursor.fetchall ()

Print "+-+-+"

Print seq3,'switch_name'.center (40)

Print seq3,'value'.center (10), seq3

Print "+-+-+"

For row in rows [0] [0] .split (','):

Print seq3,row.split ('=') [0] .ljust (40)

Print seq3,row.split ('=') [1] .rjust (10), seq3

Print "+-+-+"

Cursor.close ()

Db.close ()

Print

Def f_exec_sql (pairdbinfo.pamphsqltextrep option):

Results= {}

Conn = MySQLdb.connect (host=p_dbinfo [0], user=p_dbinfo [1], passwd=p_dbinfo [2], db=p_dbinfo [3])

Cursor = conn.cursor ()

If f_find_in_list (pumped option.PROFILING'):

Cursor.execute ("set profiling=1")

Cursor.execute ("select ifnull (max (query_id), 0) from INFORMATION_SCHEMA.PROFILING")

Records = cursor.fetchall ()

Query_id=records [0] [0] + 2 # skip next sql

If f_find_in_list (pumped optionthecomplete statuses):

# cursor.execute ("select concat (upper (left (variable_name,1)), substring (lower (variable_name), 2, (length (variable_name)-1) var_name,variable_value var_value from performance_schema.session_status where variable_name in ('" + ", '.join (tuple (SES_STATUS_ITEM)) +") order by 1 ")

Cursor.execute ("select concat (upper (left (variable_name,1)), substring (lower (variable_name), 2, (length (variable_name)-1)) var_name,variable_value var_value from performance_schema.session_status order by 1")

Records = cursor.fetchall ()

Results ['BEFORE_STATUS'] = dict (records)

Cursor.execute (p_sqltext)

If f_find_in_list (pumped optionthecomplete statuses):

Cursor.execute ("select concat (upper (left (variable_name,1)), substring (lower (variable_name), 2, (length (variable_name)-1)) var_name,variable_value var_value from performance_schema.session_status order by 1")

Records = cursor.fetchall ()

Results ['AFTER_STATUS'] = dict (records)

If f_find_in_list (pumped option.PROFILING'):

Cursor.execute ("select STATE,DURATION,CPU_USER,CPU_SYSTEM,BLOCK_OPS_IN,BLOCK_OPS_OUT, MESSAGES_SENT, MESSAGES_RECEIVED, PAGE_FAULTS_MAJOR, PAGE_FAULTS_MINOR, SWAPS from INFORMATION_SCHEMA.PROFILING where query_id=" + str (query_id) + "order by seq")

Records = cursor.fetchall ()

Results ['PROFILING_DETAIL'] = records

Cursor.execute ("SELECT STATE,SUM (DURATION) AS Total_R,ROUND (100*SUM (DURATION) / (SELECT SUM (DURATION) FROM INFORMATION_SCHEMA.PROFILING WHERE QUERY_ID=" + str (query_id) + "), 2) AS Pct_R,COUNT (*) AS Calls,SUM (DURATION) / COUNT (*) AS R_Call FROM INFORMATION_SCHEMA.PROFILING WHERE QUERY_ID=" + str (query_id) + "GROUP BY STATE ORDER BY Total_R DESC")

Records = cursor.fetchall ()

Results ['PROFILING_SUMMARY'] = records

Cursor.close ()

Conn.close ()

Return results

Def f_print_status (packs beforehand status status):

Print "= SESSION STATUS (DIFFERENT) ="

Status_title= ('status_name','before','after','diff')

Print "+-+"

Print seq3,status_title [0] .center (35)

Print seq3,status_title [1] .center (15)

Print seq3,status_title [2] .center (15)

Print seq3,status_title [3] .center (15), seq3

Print "+-+"

For key in sorted (p_before_status.keys ()):

If p_before_ status[key] p_after_status [key]:

Print seq3,key.ljust (35)

Print seq3,p_before_ status [key] .rjust (15)

Print seq3,p_after_ status [key] .rjust (15)

Print seq3,str (float (paired status [key])-float (paired before.status [key]) .rjust (15), seq3

Print "+-+"

Print

Def f_print_time (pumped starttime and pendant endtime):

Print "= EXECUTE TIME ="

Print timediff (paired starttime and pendant endtime)

Print

Def f_print_profiling (paired profile details summary):

Print "= SQL PROFILING (DETAIL) ="

Status_title= ('state','duration','cpu_user','cpu_sys','bk_in','bk_out','msg_s','msg_r','p_f_ma','p_f_mi','swaps')

Print "+-- +-- +- -+ "

Print seq3,status_title [0] .center (30)

Print seq3,status_title [1] .center (8)

Print seq3,status_title [2] .center (8)

Print seq3,status_title [3] .center (8)

Print seq3,status_title [4] .center (8)

Print seq3,status_title [5] .center (8)

Print seq3,status_title [6] .center (8)

Print seq3,status_title [7] .center (8)

Print seq3,status_title [8] .center (8)

Print seq3,status_title [9] .center (8)

Print seq3,status_title [10] .center (8), seq3

Print "+-- +-- +- -+ "

For row in p_profiling_detail:

Print seq3,row [0] .ljust (30)

Print seq3,str (row [1]) .rjust (8)

Print seq3,str (row [2]) .rjust (8)

Print seq3,str (row [3]) .rjust (8)

Print seq3,str (row [4]) .rjust (8)

Print seq3,str (row [5]) .rjust (8)

Print seq3,str (row [6]) .rjust (8)

Print seq3,str (row [7]) .rjust (8)

Print seq3,str (row [8]) .rjust (8)

Print seq3,str (row [9]) .rjust (8)

Print seq3,str (row [10]) .rjust (8), seq3

Print "+-- +-- +- -+ "

Print 'bk_in: block_ops_in'

Print 'bk_out: block_ops_out'

Print 'msg_s: message sent'

Print 'msg_r: message received'

Print 'p_f_ma: page_faults_major'

Print 'p_f_mi: page_faults_minor'

Print

Print "= SQL PROFILING (SUMMARY) ="

Status_title= ('state','total_r','pct_r','calls','r/call')

Print "+-+"

Print seq3,status_title [0] .center (35)

Print seq3,status_title [1] .center (15)

Print seq3,status_title [2] .center (10)

Print seq3,status_title [3] .center (5)

Print seq3,status_title [4] .center (15), seq3

Print "+-+"

For row in p_profiling_summary:

Print seq3,row [0] .ljust (35)

Print seq3,str (row [1]) .rjust (15)

Print seq3,str (row [2]) .rjust (10)

Print seq3,str (row [3]) .rjust (5)

Print seq3,str (row [4]) .rjust (15), seq3

Print "+-+"

Print

Def f_get_sqlplan (pairdbinfo.pamphsqltext):

Results= {}

Db= MySQLdb.connect (host=p_dbinfo [0], user=p_dbinfo [1], passwd=p_dbinfo [2], db=p_dbinfo [3])

Cursor = db.cursor ()

Cursor.execute ("explain extended" + p_sqltext)

Records = cursor.fetchall ()

Results ['SQLPLAN'] = records

Cursor.execute ("show warnings")

Records = cursor.fetchall ()

Results ['WARNING'] = records

Cursor.close ()

Db.close ()

Return results

Def f_print_sqlplan (paired sqlplan and paired pictures):

Plan_title= ('id','select_type','table','partitions','type','possible_keys','key','key_len','ref','rows','filtered','Extra')

Print "= SQL PLAN ="

Print "+- -+ "

Print seq3,plan_title [0] .center (6)

Print seq3,plan_title [1] .center (16)

Print seq3,plan_title [2] .center (10)

Print seq3,plan_title [3] .center (10)

Print seq3,plan_title [4] .center (10)

Print seq3,plan_title [5] .center (10)

Print seq3,plan_title [6] .center (10)

Print seq3,plan_title [7] .center (10)

Print seq3,plan_title [8] .center (10)

Print seq3,plan_title [9] .center (10)

Print seq3,plan_title [10] .center (10)

Print seq3,plan_title [11] .center (10), seq3

Print "+- -+ "

For row in p_sqlplan:

Print seq3,str (row [0]) .rjust (6), # id

Print seq3,row [1] .ljust (16), # select_type

# print seq3,row [2] .ljust (10), # table

If not "NonyType" in str (type (row [2])):

Print seq3,row [2] .ljust (10)

Else:

Print seq3, "NULL" .ljust (10)

If not "NoneType" in str (type (row [3])): # partitions

Print seq3,row [3] .ljust (10)

Else:

Print seq3, "NULL" .ljust (10)

# print seq3,row [3] .ljust (10)

If not "NoneType" in str (type (row [4])): # type

Print seq3,row [4] .ljust (10)

Else:

Print seq3, "NULL" .ljust (10)

If not "NoneType" in str (type (row [5])): # possible_keys

Print seq3,row [5] .ljust (13)

Else:

Print seq3, "NULL" .ljust (13)

If not "NoneType" in str (type (row [6])): # key

Print seq3,row [6] .ljust (10)

Else:

Print seq3, "NULL" .ljust (10)

If not "NoneType" in str (type (row [7])): # key_len

Print seq3,row [7] .ljust (10)

Else:

Print seq3, "NULL" .ljust (10)

If not "NoneType" in str (type (row [8])): # ref

Print seq3,row [8] .ljust (10)

Else:

Print seq3, "NULL" .ljust (10)

Print seq3,str (row [9]) .rjust (10), # rows

Print seq3,str (row [10]) .rjust (10), # filters

If not "NoneType" in str (type (row [11])): # Extra

Print seq3,row [11] .ljust (10)

Else:

Print seq3, "NULL" .ljust (10)

Print seq3

Print "+- -+ "

Print

Print "= OPTIMIZER REWRITE SQL ="

For row in p_warning:

Print sqlparse.format (row [2], reindent=True, keyword_case='upper',strip_comments=True)

Print

Def f_get_table (pairdbinfo.pamphsqltext):

Rroomtables= []

Db= MySQLdb.connect (host=p_dbinfo [0], user=p_dbinfo [1], passwd=p_dbinfo [2], db=p_dbinfo [3])

Cursor = db.cursor ()

Cursor.execute ("explain" + p_sqltext)

Rows = cursor.fetchall ()

For row in rows:

Table_name = row [2]

If'

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.