mysql的功能優(yōu)化工具

2024-07-24 12:31:57

字體：大中小

供稿：網(wǎng)友

韓鋒大師分享了一個(gè)mysql的性能優(yōu)化工具，一個(gè)能自動(dòng)采集SQL優(yōu)化相關(guān)各種信息的python腳本，對(duì)于優(yōu)化人員而言，這能省不少事，贊！
試用了一下，發(fā)現(xiàn)在mysql5.7下，運(yùn)行不起來，細(xì)節(jié)上還是有好幾個(gè)坑的，費(fèi)了一些周折，終于把坑踩完了，現(xiàn)在把細(xì)節(jié)說明一下，并把修改后的SQL分享出來；
問題1：調(diào)用腳本時(shí)，若SQL是用單引號(hào)包含（韓老師就是這么示范的：python mysql_tuning.py -p tuning_sql.ini -s 'select xxx），但這樣會(huì)報(bào)錯(cuò)，解決辦法：用雙引號(hào)分隔，如：python mysql_tuning.py -p tuning_sql.ini -s "select * from employees.dept_emp"這樣就沒問題；

問題2：沒有引用string單元，在使用string.atoi時(shí)會(huì)報(bào)錯(cuò)，解決辦法：import string；

問題3：mysql5.7后，infomation_schema的幾個(gè)表INFORMATION_SCHEMA.GLOBAL_VARIABLES、INFORMATION_SCHEMA.SESSION_VARIABLES、   INFORMATION_SCHEMA.SESSION_STATUS要替換成performance_schema下的；

問題4：在顯示執(zhí)行計(jì)劃時(shí)，table與type也是有可能是NULL的，要做空值處理，另外沒有顯示partitions欄位；

問題5：p_after_status[key]有可能是小數(shù)，所以用int去轉(zhuǎn)換會(huì)報(bào)錯(cuò)，需要用float;

問題6：db_name顯示成user_pwd了，這個(gè)或者不算問題；

   修改后的腳本如下：
點(diǎn)擊(此處)折疊或打開

#!/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)====="

（編輯：武林網(wǎng)）

上一篇：MySQL MGR+ Consul之?dāng)?shù)據(jù)庫高可用方案最棒實(shí)踐

下一篇：怎樣基于生產(chǎn)環(huán)境mysql 5.6.25主從部署新的mysql從庫操作指南