mysql的功能优化工具
发布时间：2022-04-04 11:29:28 所属栏目：MySql教程 来源：互联网
导读：韩锋大师分享了一个mysql的性能优化工具，一个能自动采集SQL优化相关各种信息的python脚本，对于优化人员而言，这能省不少事，赞！ 试用了一下，发现在mysql5.7下，运行不起来，细节上还是有好几个坑的，费了一些周折，终于把坑踩完了，现在把细节说明一下
|
韩锋大师分享了一个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了，这个或者不算问题； 修改后的脚本如下： 点击(此处)折叠或打开 #!/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 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 "+--------------------------------+------------------------------------------------------------+" 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() 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) 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 "+--------------------------------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+" print "===== SQL PROFILING(SUMMARY)=====" （编辑：永州站长网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
站长推荐