MySQL Performance analysis script

"""Goal: This tool is used to analyze the performance of MySQL instances Author: Walleching qq:1721900707 version info: based on python3.4 MySQL     5.7.11 orzdba MySQL users to use some of the permissions: Create user [email protected] ' 127.0.0.1 ' identified by ' 131417 '; """#!/usr/bin/python#!coding:utf-8ImportMysql.connector as ConnectorImportJSONImportArgparseImportSysshow_golbal_value="Select Variable_name,variable_value from Performance_schema.global_variables where variable_name=%s"Show_global_statu="Select Variable_name,variable_value from Performance_schema.global_status where variable_name=%s"defAnaylsis_query_cache (cursor,results):"""This function is used to analyze the query cache of the MySQL instance, and if the query_cache_type=0 description does not turn on the work, the analysis ends. Otherwise, analyze the remaining memory of the query cache, and the hit rate.    Wrap the results of the analysis into the results variable. """Analysis_var=("Query_cache_type",) Cursor.execute (Show_golbal_value,analysis_var) Key,value=Cursor.fetchone ()#if value equals off, the query cache is not turned on for this instance.     ifvalue=='OFF': results['Query_cache']='Query cache function not in use for this instance'    Else:        #If the logic goes here, the instance opens the query cache        #the qcache_free_memory corresponds to the remaining query cache memory. Cursor.execute (Show_global_statu, ("qcache_free_memory",)) Key,value=Cursor.fetchone ()#Since this is a time-delay calculation, it is necessary to use it to find out. ********************qcache_free_memory=value#the query_cache_size corresponds to the memory size of the query cache. Cursor.execute (Show_golbal_value, ("query_cache_size",)) Key,value=Cursor.fetchone (); Query_cache_size=value#memory idle rate for querying the cache        ifFloat (query_cache_size)! =0:query_cache_memory_free_rate=float (qcache_free_memory)/float (query_cache_size)Else: Query_cache_memory_free_rate=None#qcache_hits corresponds to the number of hitsCursor.execute (Show_global_statu, ("qcache_hits",)) Key,value=cursor.fetchone () qcache_hits=value#Qcache_inserts The number of missed hits----is inserted because there is no hit. Cursor.execute (Show_global_statu, ("Qcache_inserts",)) Key,value=cursor.fetchone () qcache_inserts=value#the query cache has a hit rate of        ifFloat (qcache_hits+qcache_inserts)! =0:query_cache_hit_rate=float (qcache_hits)/float (qcache_hits+qcache_inserts)Else: Query_cache_hit_rate=None#Organizational Resultstempresult={} tempresult['qcache_free_memory']=qcache_free_memory tempresult['query_cache_size']=query_cache_size tempresult['query_cache_memory_free_rate']=query_cache_memory_free_rate tempresult['qcache_hits']=qcache_hits tempresult['Qcache_inserts']=qcache_inserts tempresult['query_cache_hit_rate']=query_cache_hit_rate results['Query_cache']=tempresultanalysis_function_sets={'Anaylsis_query_cache': Anaylsis_query_cache}if __name__=="__main__": CNX=None Cursor=None config={        'Host':'127.0.0.1',        'Port': 3306,        'User':'Admin',        'Password':'131417'} results={}    Try: CNX=connector.connect (* *config) cursor=cnx.cursor (buffered=True) Anaylsis_query_cache (cursor,results) forKey,functioninchanalysis_function_sets.items (): function (cursor,results)Print(Results)exceptException as err:Print(ERR)finally:        ifCNX! =None:cnx.close () cursor.close ( )

MySQL Performance analysis script