MySQL检查与性能优化示例脚本
最近在玩python，为了熟悉一下python，写了个mysql的检查与性能优化建议的脚本。
虽然，真的只能算是一个半成残次品。也拿出来现眼一下。
不过对于初学者来说，还是有一定的参考价值的。比如说如何接受命令参数，python如果调用shell等。
这个脚本的主要过程就是把mysql的状态信息输出到一个临时文件，再读取临时文件解析一下比对结果。
其实做好难的不是脚本本身，而是看你对于mysql的方方面面的熟悉程度了。
如果想完善的话，其实可以做几方面的事情。
比如说根据机器配置信息（如内存，CPU），查看一下配置信息是否合理。查看一个其它配置项是否合理，用户帐号等检查之类。
关于OS方面，只是简单输出，还可以做简单分析。如磁盘是否太满了，IO/CPU是否过高等。
可惜最近没时间弄。不多说了，直接上代码（也不怕人笑了）。
#!/usr/bin/python import getopt import os import sys import platform import re usages = ''' Usage: mysql_sts_analyze.py [OPTION] The parameter changes recommendation of MySQL, based on the global status & system variables. -H hostname Connect to host. Default: localhost -P port Port number to use for connection to MySQL Server. Default: 3306 -u username User for login. -p password Password to use when connecting to server. -t interval time interval between two SLEEP commands. Default: 300 (seconds) ''' error_messages = { "max_conn_excessive" : "too many connections. Recommendation: increase max_connections, or drop unused connection.", "excessive_tmp_tables" : "Created_tmp_disk_tables / Created_tmp_tables *100 >= 25%. Recommendation: increase the tmp_table_size & max_heap_table_size, or check the SQLs use temporay tables.", "small_table_open_cache": "table_open_cache too small. Recommendation: increase the table_open_cache variables", "dead_lock_error" : "Dead lock occurs in the system. Recommendation: Invest by check more info from INNODB_TRX, INNODB_LOCKS, INNODB_LOCK_WAITS.", "buffer_pool_hit_rate_low": "Buffer pool hit rate usually should be up to 998/1000. Recommendation: Increase the innodb_buffer_pool_size" } def parse_status(name): dict_variables={} dict_sts_1={} dict_sts_2={} list_innodb_sts=[] try: fh = open(name, "r") except IOError: print ("Error: File %s not found." %(name)) return ({},{},{},[]) else: iShowStsCnt = 0 # 1: global variables, 2: status one, 3: status two, 4: innodb sts iCurrentParse = 0 for line in fh.readlines(): newStrl = line.replace("\n", "") if(newStrl == 'SHOW GLOBAL VARIABLES' and iCurrentParse == 0) : iCurrentParse = 1 continue elif( newStrl == 'SHOW GLOBAL STATUS' and iShowStsCnt == 0 and iCurrentParse == 0) : iShowStsCnt += 1 iCurrentParse = 2 continue elif( newStrl == 'SHOW GLOBAL STATUS' and iShowStsCnt == 1 and iCurrentParse == 0) : iCurrentParse = 3 continue elif( newStrl == 'SHOW ENGINE INNODB STATUS' and iCurrentParse == 0): iCurrentParse = 4 continue elif( newStrl == 'END SHOW') : iCurrentParse = 0 continue #show engine innodb status no need to parse if(iCurrentParse == 4): list_innodb_sts.append(newStrl) continue; #newStr = line.replace(" ", "").replace("\t", "").strip() #newStrl=newStrl.lower() paras = newStrl.split("\t") if len(paras)==2 : if( iCurrentParse == 1): dict_variables[paras[0]]=paras[1] elif (iCurrentParse == 2): dict_sts_1[paras[0]] = paras[1] elif (iCurrentParse == 3): dict_sts_2[paras[0]] = paras[1] fh.close() return dict_variables,dict_sts_1,dict_sts_2,list_innodb_sts; def generate_mysql_output(sh_script, hostname,port,username,password,tmpfile,sleep_time_interval): cmd = '%s %s %s %s %s %s %s' %(sh_script, hostname,port,username,password,tmpfile,sleep_time_interval) #print ("Executing shell script to gather mysql status:\n" + cmd) print("-------------------------------\nGeneral Statistic info:\n-------------------------------") os.system(cmd) def show_os_sts(): #process CPU info cmd = 'top -b -n 1 - p `pidof mysqld|awk \'{print $1}\'`' print("-------------------------------\nShow CPU usage info:\n-------------------------------\n" + cmd) os.system(cmd) #RMA info cmd = 'free -lh' print("-------------------------------\nShow RAM usage info:\n-------------------------------\n" + cmd) os.system(cmd) #Disk info cmd = 'df -lTh' print("-------------------------------\nShow disk usage info:\n-------------------------------\n" + cmd) os.system(cmd) #IO info cmd = 'iostat -x 1 1' print("-------------------------------\nShow IO info:\n-------------------------------\n" + cmd) os.system(cmd) #network INFO cmd = 'sar -n DEV 1 1' print("-------------------------------\nShow network usage info:\n-------------------------------\n" + cmd) os.system(cmd) def show_common_sts(dict_sts_1,dict_sts_2): toltal_queries1 = int(dict_sts_1['Com_select']) + int(dict_sts_1['Com_insert']) \ + int(dict_sts_1['Com_update']) + int(dict_sts_1['Com_delete']) toltal_queries2 = int(dict_sts_1['Com_select']) + int(dict_sts_1['Com_insert']) \ + int(dict_sts_1['Com_update']) + int(dict_sts_1['Com_delete']) query_per_second = float(toltal_queries1 + toltal_queries2) / float(int(dict_sts_2['Uptime']) - int(dict_sts_1['Uptime'])) #print("###query per second: %f" % (query_per_second)) print("Uptime: %s Threads: %s Slow queries: %s Open tables: %s Queries per second: %.2f" %(\ dict_sts_2['Uptime'],\ dict_sts_2['Threads_running'], \ dict_sts_2['Slow_queries'], \ dict_sts_2['Open_tables'], \ query_per_second \ )) def show_recommend_changes(dict_variables,dict_sts_1,dict_sts_2): print ("-------------------------------\nStatus Variables Relvant Recommend\n-------------------------------") #Max_used_connections > 85% if( int(dict_sts_2['Max_used_connections']) *100 / int(dict_variables['max_connections']) > 85) : print (error_messages['max_conn_excessive']) if(int(dict_sts_2['Created_tmp_disk_tables']) *100 / int(dict_sts_2['Created_tmp_tables']) > 25): print(error_messages['excessive_tmp_tables']) #Open_tables / Opened_tables * 100% >= 85% table_open_cache if( int(dict_sts_2['Open_tables'])*100/int(dict_sts_2['Opened_tables']) < 85): print(error_messages['small_table_open_cache']) def index_of_str(s1, s2): n1=len(s1) n2=len(s2) for i in range(n1-n2+1): if s1[i:i+n2]==s2: return i else: return -1 #print(index_of_str('12abc34de5f', 'c34')) def show_innodb_recommend(list_innodb_lines): print ("-------------------------------\nInnodb Engine Status Relvant Recommend\n-------------------------------") cnt =0; for elem in list_innodb_lines: if (index_of_str(elem,'LATEST DETECTED DEADLOCK') >= 0): print (error_messages['dead_lock_error']) elif(index_of_str(elem,'Buffer pool hit rate') >= 0): #Format: Buffer pool hit rate 944 / 1000 #buff_pool_Reg = re.compile(r'(\D+) (\d+) / (\d+)') #grp = buff_pool_Reg.search(elem) grp = re.search(r'(\D+) (\d+) / (\d+)', elem) num1 = int(grp.group(2)) num2 = int(grp.group(3)) if(num1*100/num2 < 998): print (grp.group(0)) print (error_messages['buffer_pool_hit_rate_low']) cnt += 1 if (cnt == 100): pass ######################################## ## Main program start hear ######################################## HOSTNAME='localhost' PORT=3306 USERNAME='root' PASSWORD='root' if platform.system().lower() == 'linux': TMPFILE = '/tmp/output-mysql.txt' else: TMPFILE='e:\\output-mysql.txt' SHELL_SCRIPT='./gather_mysql_sts.sh' #unit second SLEEP_INTERVAL=300 #TMPFILE='/tmp/output-mysql.txt' try: opts, args = getopt.getopt(sys.argv[1:], '-h-H:-P:-u:-p:-t:') #print(opts) for opt_name, opt_value in opts: if opt_name in ('-h'): print(usages) sys.exit() if opt_name in ('-H'): HOSTNAME = opt_value continue; if opt_name in ('-P'): PORT = opt_value continue; if opt_name in ('-u'): USERNAME = opt_value continue; if opt_name in ('-p'): PASSWORD = opt_value continue; if opt_name in ('-t'): SLEEP_INTERVAL = opt_value continue; except getopt.GetoptError as e: print ('ERROR: %s' % str(e)) print(usages) sys.exit(2) if platform.system().lower() == 'linux': generate_mysql_output(SHELL_SCRIPT,HOSTNAME,PORT,USERNAME,PASSWORD,TMPFILE,SLEEP_INTERVAL) dict_variables,dict_sts_1,dict_sts_2,list_innodb_sts = parse_status(TMPFILE) show_common_sts(dict_sts_1,dict_sts_2) show_recommend_changes(dict_variables,dict_sts_1,dict_sts_2) show_innodb_recommend(list_innodb_sts) if platform.system().lower() == 'linux': show_os_sts() #print (dict_variables) #print (dict_sts_1) #print (dict_sts_2) sys.exit(0)
gather_mysql_sts.sh脚本
#!/bin/bash if [ $# -ne 6 ]; then echo "Invalid parameter numbers $#" exit fi hostname=$1 port=$2 username=$3 password=$4 tmpfile=$5 sleep_interval=$6 if test -e $tmpfile then rm -rf $tmpfile fi mysql -h $hostname -P $port -u $username -p$password 1>/dev/null 2>&1<< EOF #mysql -h $hostname -P $port -u $username -p$password << EOF TEE $tmpfile; SELECT NOW(); SELECT "SHOW GLOBAL VARIABLES"; SHOW GLOBAL VARIABLES; SELECT "END SHOW"; SELECT "SHOW GLOBAL STATUS"; SHOW GLOBAL STATUS; SELECT "END SHOW"; SELECT SLEEP($sleep_interval); SELECT "SHOW GLOBAL STATUS"; SHOW GLOBAL STATUS; SELECT "END SHOW"; SELECT "SHOW ENGINE INNODB STATUS"; SHOW ENGINE INNODB STATUS\G SELECT "END SHOW"; NOTEE; EOF
执行帮助如下：
[root@master python-study]# ./mysql_sts_analyze.py -h Usage: mysql_sts_analyze.py [OPTION] The parameter changes recommendation of MySQL, based on the global status & system variables. -H hostname Connect to host. Default: localhost -P port Port number to use for connection to MySQL Server. Default: 3306 -u username User for login. -p password Password to use when connecting to server. -t interval time interval between two SLEEP commands. Default: 300 (seconds)
执行结果输出：
./mysql_sts_analyze.py -u root -p Xiaopang*803 -t 10 ------------------------------- General Statistic info: ------------------------------- Uptime: 136 Threads: 2 Slow queries: 0 Open tables: 54 Queries per second: 1.00 ------------------------------- Status Variables Relvant Recommend ------------------------------- table_open_cache too small. Recommendation: increase the table_open_cache variables ------------------------------- Innodb Engine Status Relvant Recommend ------------------------------- Buffer pool hit rate 950 / 1000 Buffer pool hit rate usually should be up to 998/1000. Recommendation: Increase the innodb_buffer_pool_size ------------------------------- Show disk usage info: ------------------------------- df -lTh Filesystem Type Size Used Avail Use% Mounted on /dev/mapper/cl-root xfs 29G 21G 8.8G 70% / devtmpfs devtmpfs 486M 0 486M 0% /dev tmpfs tmpfs 497M 8.0K 497M 1% /dev/shm tmpfs tmpfs 497M 6.6M 490M 2% /run tmpfs tmpfs 497M 0 497M 0% /sys/fs/cgroup /dev/sda1 xfs 1014M 140M 875M 14% /boot tmpfs tmpfs 100M 0 100M 0% /run/user/0 ------------------------------- Show RAM usage info: ------------------------------- free -lh total used free shared buff/cache available Mem: 992M 446M 193M 17M 352M 380M Low: 992M 799M 193M High: 0B 0B 0B Swap: 0B 0B 0B ------------------------------- Show CPU usage info: ------------------------------- top -b -n 1 - p `pidof mysqld|awk '{print $1}'` top - 23:15:37 up 2 min, 2 users, load average: 0.46, 0.39, 0.17 Tasks: 1 total, 0 running, 1 sleeping, 0 stopped, 0 zombie %Cpu(s): 4.0 us, 2.9 sy, 0.0 ni, 81.9 id, 11.1 wa, 0.0 hi, 0.0 si, 0.0 st KiB Mem : 1016476 total, 197836 free, 457576 used, 361064 buff/cache KiB Swap: 0 total, 0 free, 0 used. 389412 avail Mem PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND 2091 mysql 20 0 1327188 368768 15456 S 0.0 36.3 0:02.02 mysqld ------------------------------- Show network usage info: ------------------------------- sar -n DEV 1 1 Linux 3.10.0-514.el7.x86_64 (master) Wednesday, July 14, 2021 _x86_64_ (1 CPU) 11:15:37 HKT IFACE rxpck/s txpck/s rxkB/s txkB/s rxcmp/s txcmp/s rxmcst/s 11:15:38 HKT enp0s3 6.06 9.09 0.44 1.13 0.00 0.00 0.00 11:15:38 HKT enp0s8 0.00 0.00 0.00 0.00 0.00 0.00 0.00 11:15:38 HKT lo 0.00 0.00 0.00 0.00 0.00 0.00 0.00 Average: IFACE rxpck/s txpck/s rxkB/s txkB/s rxcmp/s txcmp/s rxmcst/s Average: enp0s3 6.06 9.09 0.44 1.13 0.00 0.00 0.00 Average: enp0s8 0.00 0.00 0.00 0.00 0.00 0.00 0.00 Average: lo 0.00 0.00 0.00 0.00 0.00 0.00 0.00
