韩锋大师分享了一个MySQL的性能优化工具,一个能自动采集SQL优化相关各种信息的python脚本,对于优化人员而言,这能省不少事,赞!
试用了一下,发现在mysql5.7下,运行不起来,细节上还是有好几个坑的,费了一些周折,终于把坑踩完了,现在把细节说明一下,并把修改后的SQL分享出来;
问题1:调用脚本时,若SQL是用单引号包含(韩老师就是这么示范的:python mysql_tuning.py -p tuning_sql.ini -s 'select xxx),但这样会报错,解决办法:用双引号分隔,如:python mysql_tuning.py -p tuning_sql.ini -s "select * from employees.dept_emp"这样就没问题;
问题2:没有引用string单元,在使用string.atoi时会报错,解决办法:import string;
问题3:mysql5.7后,infomation_schema的几个表INFORMATION_SCHEMA.GLOBAL_VARIABLES、INFORMATION_SCHEMA.SESSION_VARIABLES、 INFORMATION_SCHEMA.SESSION_STATUS要替换成performance_schema下的;
问题4:在显示执行计划时,table与type也是有可能是NULL的,要做空值处理,另外没有显示partitions栏位;
问题5:p_after_status[key]有可能是小数,所以用int去转换会报错,需要用float;
问题6:db_name显示成user_pwd了,这个或者不算问题;
修改后的脚本如下:
点击(此处)折叠或打开
![](/upload/ad_content/seo-2.jpg)
在怀来等地区,都构建了全面的区域性战略布局,加强发展的系统性、市场前瞻性、产品创新能力,以专注、极致的服务理念,为客户提供网站设计、网站制作 网站设计制作按需求定制网站,公司网站建设,企业网站建设,高端网站设计,成都全网营销,外贸营销网站建设,怀来网站建设费用合理。
-
#!/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(0,len(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])>=1024*1024*1024:
-
print seq3,(str(round(string.atoi(row[1])/1024/1024/1024,2))+' G').rjust(58),seq3
-
elif string.atoi(row[1])>=1024*1024:
-
print seq3,(str(round(string.atoi(row[1])/1024/1024,2))+' 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(p_dbinfo,p_sqltext,p_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(p_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(p_option,'STATUS'):
-
#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(p_option,'STATUS'):
-
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(p_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(p_before_status,p_after_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(p_after_status[key])-float(p_before_status[key])).rjust(15),seq3
-
print "+-------------------------------------+-----------------+-----------------+-----------------+"
-
print
-
-
def f_print_time(p_starttime,p_endtime):
-
print "===== EXECUTE TIME ====="
-
print timediff(p_starttime,p_endtime)
-
print
-
-
-
def f_print_profiling(p_profiling_detail,p_profiling_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(p_dbinfo,p_sqltext):
-
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(p_sqlplan,p_warning):
-
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(p_dbinfo,p_sqltext):
-
r_tables=[]
-
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 '<' in table_name:
-
continue
-
if len(r_tables)==0:
-
r_tables.append(table_name)
-
elif f_find_in_list(r_tables,table_name) == -1:
-
r_tables.append(table_name)
-
cursor.close()
-
db.close()
-
return r_tables
-
-
def f_print_tableinfo(p_dbinfo,p_tablename):
-
plan_title=('table_name','engine','format','table_rows','avg_row','total_mb','data_mb','index_mb')
-
db = MySQLdb.connect(host=p_dbinfo[0], user=p_dbinfo[1], passwd=p_dbinfo[2],db=p_dbinfo[3])
-
cursor = db.cursor()
-
stmt = "select engine,row_format as format,table_rows,avg_row_length as avg_row,round((data_length+index_length)/1024/1024,2) as total_mb,round((data_length)/1024/1024,2) as data_mb,round((index_length)/1024/1024,2) as index_mb from information_schema.tables where table_schema='"+p_dbinfo[3]+"' and table_name='"+p_tablename+"'"
-
cursor.execute(stmt)
-
rows = cursor.fetchall ()
-
print "+-----------------+------------+------------+------------+------------+------------+------------+------------+"
-
print seq3,plan_title[0].center(15),
-
print seq3,plan_title[1].center(10),
-
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),seq3
-
print "+-----------------+------------+------------+------------+------------+------------+------------+------------+"
-
for row in rows:
-
print seq3,p_tablename.ljust(15),
-
print seq3,row[0].ljust(10),
-
print seq3,row[1].ljust(10),
-
print seq3,str(row[2]).rjust(10),
-
print seq3,str(row[3]).rjust(10),
-
print seq3,str(row[4]).rjust(10),
-
print seq3,str(row[5]).rjust(10),
-
print seq3,str(row[6]).rjust(10),seq3
-
print "+-----------------+------------+------------+------------+------------+------------+------------+------------+"
-
cursor.close()
-
db.close()
-
-
def f_print_indexinfo(p_dbinfo,p_tablename):
-
plan_title=('index_name','non_unique','seq_in_index','column_name','collation','cardinality','nullable','index_type')
-
db = MySQLdb.connect(host=p_dbinfo[0], user=p_dbinfo[1], passwd=p_dbinfo[2],db=p_dbinfo[3])
-
cursor = db.cursor()
-
stmt = "select index_name,non_unique,seq_in_index,column_name,collation,cardinality,nullable,index_type from information_schema.statistics where table_schema='"+p_dbinfo[3]+"' and table_name='"+p_tablename+"' order by 1,3"
-
cursor.execute(stmt)
-
rows = cursor.fetchall ()
-
if len(rows)>0:
-
print "+-----------------+-----------------+-----------------+-----------------+-----------------+-----------------+-----------------+-----------------+"
-
print seq3,plan_title[0].center(15),
-
print seq3,plan_title[1].center(15),
-
print seq3,plan_title[2].center(15),
-
print seq3,plan_title[3].center(15),
-
print seq3,plan_title[4].center(15),
-
print seq3,plan_title[5].center(15),
-
print seq3,plan_title[6].center(15),
-
print seq3,plan_title[7].center(15),seq3
-
print "+-----------------+-----------------+-----------------+-----------------+-----------------+-----------------+-----------------+-----------------+"
-
for row in rows:
-
print seq3,row[0].ljust(15),
-
print seq3,str(row[1]).rjust(15),
-
print seq3,str(row[2]).rjust(15),
-
print seq3,str(row[3]).rjust(15),
-
print seq3,str(row[4]).rjust(15),
-
print seq3,str(row[5]).rjust(15),
-
print seq3,str(row[6]).rjust(15),
-
print seq3,str(row[7]).rjust(15),seq3
-
print "+-----------------+-----------------+-----------------+-----------------+-----------------+-----------------+-----------------+-----------------+"
-
cursor.close()
-
db.close()
-
-
def f_get_mysql_version(p_dbinfo):
-
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 @@version")
-
records = cursor.fetchall ()
-
cursor.close()
-
db.close()
-
return records[0][0]
-
-
def f_print_title(p_dbinfo,p_mysql_version,p_sqltext):
-
print '*'*100
-
print '*','MySQL SQL Tuning Tools v1.0 for mysql5.7(created by hanfeng modified by ycc)'.center(96),'*'
-
print '*'*100
-
-
print
-
print "===== BASIC INFORMATION ====="
-
title=('server_ip','user_name','db_name','db_version')
-
print "+----------------------+------------+------------+------------+"
-
print seq3,title[0].center(20),
-
print seq3,title[1].center(10),
-
print seq3,title[2].center(10),
-
print seq3,title[3].center(10),seq3
-
print "+----------------------+------------+------------+------------+"
-
print seq3,p_dbinfo[0].center(20),
-
print seq3,p_dbinfo[1].center(10),
-
print seq3,p_dbinfo[3].center(10),
-
print seq3,p_mysql_version.center(10),seq3
-
print "+----------------------+------------+------------+------------+"
-
print
-
print "===== ORIGINAL SQL TEXT ====="
-
print sqlparse.format(p_sqltext,reindent=True, keyword_case='upper')
-
print
-
-
'''
-
def f_print_table(p_value,p_option): #p_option "(key-n => title,max_len,align_value)"
-
for k in p_option.keys():
-
v = p_option[k]
-
print "+",
-
print int(v.split(',')[1])*"-",
-
print "+"
-
-
for k in p_option.keys():
-
v = p_option[k]
-
print "|",
-
print v.split(',')[0].center(int(v.split(',')[0])-2),
-
print "|",
-
-
for k in p_option.keys():
-
v = p_option[k]
-
print "+",
-
print int(v.split(',')[1])*"-",
-
print "+"
-
-
for row in p_value:
-
k=0
-
for col in row:
-
k+=1
-
print "|",
-
if p_option[k].split(',')[2]=='l':
-
print col.ljust(p_option[k].split(',')[1]),
-
elif p_option[k].split(',')[2]=='r':
-
print col.rjust(p_option[k].split(',')[1]),
-
else
-
print col.center(p_option[k].split(',')[1]),
-
print "|",
-
-
for k in p_option.keys():
-
v = p_option[k]
-
print "+",
-
print int(v.split(',')[1])*"-",
-
print "+"
-
'''
-
-
def timediff(timestart, timestop):
-
t = (timestop-timestart)
-
time_day = t.days
-
s_time = t.seconds
-
ms_time = t.microseconds / 1000000
-
usedtime = int(s_time + ms_time)
-
time_hour = usedtime / 60 / 60
-
time_minute = (usedtime - time_hour * 3600 ) / 60
-
time_second = usedtime - time_hour * 3600 - time_minute * 60
-
time_micsecond = (t.microseconds - t.microseconds / 1000000) / 1000
-
-
retstr = "%d day %d hour %d minute %d second %d microsecond " %(time_day, time_hour, time_minute, time_second, time_micsecond)
-
return retstr
-
-
if __name__=="__main__":
-
dbinfo=["","","",""] #dbhost,dbuser,dbpwd,dbname
-
sqltext=""
-
option=[]
-
config_file=""
-
mysql_version=""
-
-
opts, args = getopt.getopt(sys.argv[1:], "p:s:")
-
for o,v in opts:
-
if o == "-p":
-
config_file = v
-
elif o == "-s":
-
sqltext = v
-
-
config = ConfigParser.ConfigParser()
-
config.readfp(open(config_file,"rb"))
-
dbinfo[0] = config.get("database","server_ip")
-
dbinfo[1] = config.get("database","db_user")
-
dbinfo[2] = config.get("database","db_pwd")
-
dbinfo[3] = config.get("database","db_name")
-
-
mysql_version = f_get_mysql_version(dbinfo)
-
-
f_print_title(dbinfo,mysql_version,sqltext)
-
-
if config.get("option","sys_parm")=='ON':
-
parm_result = f_get_parm(dbinfo)
-
f_print_parm(parm_result)
-
f_print_optimizer_switch(dbinfo)
-
-
if config.get("option","sql_plan")=='ON':
-
sqlplan_result = f_get_sqlplan(dbinfo,sqltext)
-
f_print_sqlplan(sqlplan_result['SQLPLAN'],sqlplan_result['WARNING'])
-
-
if config.get("option","obj_stat")=='ON':
-
print "===== OBJECT STATISTICS ====="
-
for table_name in extract_tables(sqltext):
-
f_print_tableinfo(dbinfo,table_name)
-
f_print_indexinfo(dbinfo,table_name)
-
print
-
-
if config.get("option","ses_status")=='ON':
-
option.append('STATUS')
-
-
if config.get("option","sql_profile")=='ON':
-
option.append('PROFILING')
-
-
if config.get("option","ses_status")=='ON' or config.get("option","sql_profile")=='ON':
-
starttime = datetime.datetime.now()
-
exec_result = f_exec_sql(dbinfo,sqltext,option)
-
endtime = datetime.datetime.now()
-
-
if config.get("option","ses_status")=='ON':
-
f_print_status(exec_result['BEFORE_STATUS'],exec_result['AFTER_STATUS'])
-
-
if config.get("option","sql_profile")=='ON':
-
f_print_profiling(exec_result['PROFILING_DETAIL'],exec_result['PROFILING_SUMMARY
文章标题:mysql的性能优化工具(源自韩锋大师,针对5.7修改)
路径分享:
http://cdkjz.cn/article/ghjpds.html