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
