def optimizer_switch(self): # 获取mysql版本信息 version = self.basic_information()['rows'][0][0] server_version = tuple([numeric_part(n) for n in version.split('.')[:2]]) if server_version < (5, 7): sql = self.sql_optimizer_switch.replace('performance_schema', 'information_schema') else: sql = self.sql_optimizer_switch return self.engine.query(sql=sql, close_conn=False).to_sep_dict()
def sys_parameter(self): # 获取mysql版本信息 version = self.basic_information()['rows'][0][0] server_version = tuple( [numeric_part(n) for n in version.split('.')[:2]]) if server_version < (5, 7): sql = self.sql_variable.replace('performance_schema', 'information_schema') else: sql = self.sql_variable return self.dao.mysql_query(sql=sql)
def exec_sql(self): result = {"EXECUTE_TIME": 0, "BEFORE_STATUS": {'column_list': [], 'rows': []}, "AFTER_STATUS": {'column_list': [], 'rows': []}, "SESSION_STATUS(DIFFERENT)": {'column_list': ['status_name', 'before', 'after', 'diff'], 'rows': []}, "PROFILING_DETAIL": {'column_list': [], 'rows': []}, "PROFILING_SUMMARY": {'column_list': [], 'rows': []} } sql_profiling = "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" # 获取mysql版本信息 version = self.basic_information()['rows'][0][0] server_version = tuple([numeric_part(n) for n in version.split('.')[:2]]) if server_version < (5, 7): sql = sql_profiling.replace('performance_schema', 'information_schema') else: sql = sql_profiling self.engine.query(sql="set profiling=1", close_conn=False).to_sep_dict() records = self.engine.query(sql="select ifnull(max(query_id),0) from INFORMATION_SCHEMA.PROFILING", close_conn=False).to_sep_dict() query_id = records['rows'][0][0] + 3 # skip next sql # 获取执行前信息 result['BEFORE_STATUS'] = self.engine.query(sql=sql, close_conn=False).to_sep_dict() # 执行查询语句,统计执行时间 t_start = time.time() self.engine.query(sql=self.sqltext, close_conn=False).to_sep_dict() t_end = time.time() cost_time = "%5s" % "{:.4f}".format(t_end - t_start) result['EXECUTE_TIME'] = cost_time # 获取执行后信息 result['AFTER_STATUS'] = self.engine.query(sql=sql, close_conn=False).to_sep_dict() # 获取PROFILING_DETAIL信息 result['PROFILING_DETAIL'] = self.engine.query( sql="select STATE,DURATION,CPU_USER,CPU_SYSTEM,BLOCK_OPS_IN,BLOCK_OPS_OUT ,MESSAGES_SENT ,MESSAGES_RECEIVED ,PAGE_FAULTS_MAJOR ,PAGE_FAULTS_MINOR ,SWAPS from INFORMATION_SCHEMA.PROFILING where query_id=" + str( query_id) + " order by seq", close_conn=False).to_sep_dict() result['PROFILING_SUMMARY'] = self.engine.query( sql="SELECT STATE,SUM(DURATION) AS Total_R,ROUND(100*SUM(DURATION)/(SELECT SUM(DURATION) FROM INFORMATION_SCHEMA.PROFILING WHERE QUERY_ID=" + str( query_id) + "),2) AS Pct_R,COUNT(*) AS Calls,SUM(DURATION)/COUNT(*) AS R_Call FROM INFORMATION_SCHEMA.PROFILING WHERE QUERY_ID=" + str( query_id) + " GROUP BY STATE ORDER BY Total_R DESC", close_conn=False).to_sep_dict() # 处理执行前后对比信息 before_status_rows = [list(item) for item in result['BEFORE_STATUS']['rows']] after_status_rows = [list(item) for item in result['AFTER_STATUS']['rows']] for index, item in enumerate(before_status_rows): if before_status_rows[index][1] != after_status_rows[index][1]: before_status_rows[index].append(after_status_rows[index][1]) before_status_rows[index].append( str(float(after_status_rows[index][1]) - float(before_status_rows[index][1]))) diff_rows = [item for item in before_status_rows if len(item) == 4] result['SESSION_STATUS(DIFFERENT)']['rows'] = diff_rows return result
def __sys_parameter(cluster_name, db_name): # 取出该实例的连接方式 masterInfo = MasterConfig.objects.get(cluster_name=cluster_name) # 获取mysql版本信息 version = __basic_information(cluster_name, db_name)['rows'][0][0] server_version = tuple([numeric_part(n) for n in version.split('.')[:2]]) if server_version < (5, 7): sql = sql_variable.replace('performance_schema', 'information_schema') else: sql = sql_variable return dao.mysql_query(masterInfo.master_host, masterInfo.master_port, masterInfo.master_user, prpCryptor.decrypt(masterInfo.master_password), db_name, sql)
def server_version(self): version = self.query(sql="select @@version").rows[0][0] return tuple([numeric_part(n) for n in version.split('.')[:3]])
def __exec_sql(cluster_name, db_name, sqltext): # 取出该实例的连接方式 masterInfo = MasterConfig.objects.get(cluster_name=cluster_name) result = { "EXECUTE_TIME": 0, "BEFORE_STATUS": { 'column_list': [], 'rows': [] }, "AFTER_STATUS": { 'column_list': [], 'rows': [] }, "SESSION_STATUS(DIFFERENT)": { 'column_list': ['status_name', 'before', 'after', 'diff'], 'rows': [] }, "PROFILING_DETAIL": { 'column_list': [], 'rows': [] }, "PROFILING_SUMMARY": { 'column_list': [], 'rows': [] } } sql_profiling = "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" # 获取mysql版本信息 version = __basic_information(cluster_name, db_name)['rows'][0][0] server_version = tuple([numeric_part(n) for n in version.split('.')[:2]]) if server_version < (5, 7): sql = sql_profiling.replace('performance_schema', 'information_schema') else: sql = sql_profiling conn = MySQLdb.connect(host=masterInfo.master_host, port=masterInfo.master_port, user=masterInfo.master_user, passwd=prpCryptor.decrypt( masterInfo.master_password), db=db_name, charset='utf8') cursor = conn.cursor() 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 cursor.execute(sql) rows = cursor.fetchall() fields = cursor.description column_list = [] if fields: for i in fields: column_list.append(i[0]) result['BEFORE_STATUS']['column_list'] = column_list result['BEFORE_STATUS']['rows'] = rows # 执行查询语句,统计执行时间 t_start = time.time() cursor.execute(sqltext) t_end = time.time() cost_time = "%5s" % "{:.4f}".format(t_end - t_start) result['EXECUTE_TIME'] = cost_time cursor.execute(sql) rows = cursor.fetchall() fields = cursor.description column_list = [] if fields: for i in fields: column_list.append(i[0]) result['AFTER_STATUS']['column_list'] = column_list result['AFTER_STATUS']['rows'] = rows cursor.execute( "select STATE,DURATION,CPU_USER,CPU_SYSTEM,BLOCK_OPS_IN,BLOCK_OPS_OUT ,MESSAGES_SENT ,MESSAGES_RECEIVED ,PAGE_FAULTS_MAJOR ,PAGE_FAULTS_MINOR ,SWAPS from INFORMATION_SCHEMA.PROFILING where query_id=" + str(query_id) + " order by seq") rows = cursor.fetchall() fields = cursor.description column_list = [] if fields: for i in fields: column_list.append(i[0]) result['PROFILING_DETAIL']['column_list'] = column_list result['PROFILING_DETAIL']['rows'] = rows cursor.execute( "SELECT STATE,SUM(DURATION) AS Total_R,ROUND(100*SUM(DURATION)/(SELECT SUM(DURATION) FROM INFORMATION_SCHEMA.PROFILING WHERE QUERY_ID=" + str(query_id) + "),2) AS Pct_R,COUNT(*) AS Calls,SUM(DURATION)/COUNT(*) AS R_Call FROM INFORMATION_SCHEMA.PROFILING WHERE QUERY_ID=" + str(query_id) + " GROUP BY STATE ORDER BY Total_R DESC") rows = cursor.fetchall() fields = cursor.description column_list = [] if fields: for i in fields: column_list.append(i[0]) result['PROFILING_SUMMARY']['column_list'] = column_list result['PROFILING_SUMMARY']['rows'] = rows cursor.close() conn.close() # 处理执行前后对比信息 before_status_rows = [ list(item) for item in result['BEFORE_STATUS']['rows'] ] after_status_rows = [list(item) for item in result['AFTER_STATUS']['rows']] for index, item in enumerate(before_status_rows): if before_status_rows[index][1] != after_status_rows[index][1]: before_status_rows[index].append(after_status_rows[index][1]) before_status_rows[index].append( str( float(after_status_rows[index][1]) - float(before_status_rows[index][1]))) diff_rows = [item for item in before_status_rows if len(item) == 4] result['SESSION_STATUS(DIFFERENT)']['rows'] = diff_rows return result