97 Star 212 Fork 104

茹憶。 / MySQLMTOP

Create your Gitee Account
Explore and code with more than 5 million developers,Free private repositories !:)
Sign up
Clone or download
check_mysql_widget_hit_rate.py 5.39 KB
Copy Edit Web IDE Raw Blame History
#!//bin/env python
#coding:utf-8
import os
import sys
import string
import time
import datetime
import MySQLdb
import global_functions as func
from multiprocessing import Process;
def check_mysql_widget(host,port,user,passwd,server_id):
try:
connect=MySQLdb.connect(host=host,user=user,passwd=passwd,port=int(port),connect_timeout=2,charset='utf8')
cur=connect.cursor()
connect.select_db('information_schema')
datalist=[]
cur.execute("select VARIABLE_VALUE from GLOBAL_STATUS where VARIABLE_NAME ='Qcache_hits';");
result = cur.fetchone()
Qcache_hits=result[0]
cur.execute("select VARIABLE_VALUE from GLOBAL_STATUS where VARIABLE_NAME ='Com_select';");
result = cur.fetchone()
Com_select=result[0]
Query_cache_hits = string.atof(Qcache_hits) / (string.atof(Qcache_hits) + string.atof(Com_select))
Query_cache_hits = "%9.2f" %Query_cache_hits
datalist.append(Query_cache_hits)
cur.execute("select VARIABLE_VALUE from GLOBAL_STATUS where VARIABLE_NAME ='Key_reads';");
result = cur.fetchone()
Key_reads=result[0]
cur.execute("select VARIABLE_VALUE from GLOBAL_STATUS where VARIABLE_NAME ='Key_read_requests';");
result = cur.fetchone()
Key_read_requests=result[0]
if string.atof(Key_read_requests) <> 0:
Key_buffer_read_hits = 1 - string.atof(Key_reads) / string.atof(Key_read_requests)
Key_buffer_read_hits = "%9.2f" %Key_buffer_read_hits
else:
Key_buffer_read_hits=0
datalist.append(Key_buffer_read_hits)
cur.execute("select VARIABLE_VALUE from GLOBAL_STATUS where VARIABLE_NAME ='Key_writes';");
result = cur.fetchone()
Key_writes=result[0]
cur.execute("select VARIABLE_VALUE from GLOBAL_STATUS where VARIABLE_NAME ='Key_write_requests';");
result = cur.fetchone()
Key_write_requests=result[0]
if string.atof(Key_write_requests) <> 0:
Key_buffer_write_hits = 1 - string.atof(Key_writes) / string.atof(Key_write_requests)
Key_buffer_write_hits = "%9.2f" %Key_buffer_write_hits
else:
Key_buffer_write_hits = 0
datalist.append(Key_buffer_write_hits)
cur.execute("select VARIABLE_VALUE from GLOBAL_STATUS where VARIABLE_NAME ='Threads_created';");
result = cur.fetchone()
Threads_created=result[0]
cur.execute("select VARIABLE_VALUE from GLOBAL_STATUS where VARIABLE_NAME ='Connections';");
result = cur.fetchone()
Connections=result[0]
Thread_cache_hits = 1 - string.atof(Threads_created) / string.atof(Connections)
Thread_cache_hits = "%9.2f" %Thread_cache_hits
datalist.append(Thread_cache_hits)
cur.execute("select VARIABLE_VALUE from GLOBAL_STATUS where VARIABLE_NAME ='Key_blocks_used';");
result = cur.fetchone()
Key_blocks_used=result[0]
cur.execute("select VARIABLE_VALUE from GLOBAL_STATUS where VARIABLE_NAME ='Key_blocks_unused';");
result = cur.fetchone()
Key_blocks_unused=result[0]
Key_blocks_used_rate = string.atof(Key_blocks_used) / (string.atof(Key_blocks_used)+string.atof(Key_blocks_unused))
Key_blocks_used_rate = "%9.2f" %Key_blocks_used_rate
datalist.append(Key_blocks_used_rate)
cur.execute("select VARIABLE_VALUE from GLOBAL_STATUS where VARIABLE_NAME ='Created_tmp_disk_tables';");
result = cur.fetchone()
Created_tmp_disk_tables=result[0]
cur.execute("select VARIABLE_VALUE from GLOBAL_STATUS where VARIABLE_NAME ='Created_tmp_tables';");
result = cur.fetchone()
Created_tmp_tables=result[0]
Created_tmp_disk_tables_rate = string.atof(Created_tmp_disk_tables) / (string.atof(Created_tmp_disk_tables)+string.atof(Created_tmp_tables))
Created_tmp_disk_tables_rate = "%9.2f" %Created_tmp_disk_tables_rate
datalist.append(Created_tmp_disk_tables_rate)
result=datalist
if result:
sql="insert into mysql_widget_hit_rate(server_id,Query_cache_hits,Key_buffer_read_hits,Key_buffer_write_hits,Thread_cache_hits,Key_blocks_used_rate,Created_tmp_disk_tables_rate) values(%s,%s,%s,%s,%s,%s,%s)"
param=(server_id,result[0],result[1],result[2],result[3],result[4],result[5])
func.mysql_exec(sql,param)
cur.close()
connect.close()
except MySQLdb.Error,e:
pass
print "Mysql Error %d: %s" %(e.args[0],e.args[1])
def main():
func.mysql_exec("truncate table mysql_widget_hit_rate",'')
#get mysql servers list
user = func.get_config('mysql_db','username')
passwd = func.get_config('mysql_db','password')
servers=func.mysql_query("select id,host,port from servers where is_delete=0;")
if servers:
print("%s: check_mysql_widget_hit_rate controller started." % (time.strftime('%Y-%m-%d %H:%M:%S', time.localtime()),));
plist = []
for row in servers:
server_id=row[0]
host=row[1]
port=row[2]
p = Process(target = check_mysql_widget, args = (host,port,user,passwd,server_id))
plist.append(p)
p.start()
for p in plist:
p.join()
print("%s: check_mysql_widget_hit_rate controller finished." % (time.strftime('%Y-%m-%d %H:%M:%S', time.localtime()),))
if __name__=='__main__':
main()

Comment ( 0 )

Sign in for post a comment

Python
1
https://git.oschina.net/ruzuojun/MySQLMTOP.git
git@git.oschina.net:ruzuojun/MySQLMTOP.git
ruzuojun
MySQLMTOP
MySQLMTOP
master

Search