mysql的功能优化工具

2022-07-31 18:43:27

字体：大中小

来源：转载

供稿：网友

韩锋大师分享了一个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()
    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)

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)====="

（编辑：错新网）

上一篇：怎样基于生产环境mysql 5.6.25主从部署新的mysql从库操作指南

下一篇：MySQL MGR+ Consul之数据库高可用方案最棒实践