import multiprocessing
import pymysql
import xlrd
import time
import datetime
import paramiko
import os
import glob
def select_sql_1(cursor,sql):
#执行查询sql,返回第一行数据
cursor.execute(sql)
result = cursor.fetchone()
return result[1]
def select_sql_all(cursor,sql):
# 执行查询sql,返回所有数据
cursor.execute(sql)
result = cursor.fetchall()
return result
def count_data(num_1,num_2):
#进行百分比计算
result = int(num_1)/int(num_2)*100
return result
def del_files(path):
#删除文件夹下的文件
fileNames = glob.glob(path + r'/*')
for fileName in fileNames:
try:
os.remove(fileName)
print("remove file:%s" % fileNames)
except:
pass
def print_out(i):
#保存数据库主机连接信息的Excel表
data = xlrd.open_workbook(r"d:\data.xls")
table = data.sheets()[0]
# 打开一个文件用于追加。如果该文件已存在,文件指针将会放在文件的结尾;如果该文件不存在,创建新文件进行写入
file = open(r"d:\测试文件\mysql\{0}.txt".format(table.cell_value(i, 4)), 'a', encoding='utf-8')
file_vmstat = open(r"d:\测试文件\mysql\{0}_vmstat.txt".format(table.cell_value(i, 4)), 'a',
encoding='utf-8')
db = pymysql.connect(
host=table.cell_value(i, 0),
port=int(table.cell_value(i, 1)),
user=table.cell_value(i, 2),
password=table.cell_value(i, 3),
database=table.cell_value(i, 4),
charset='utf8'
)
cursor = db.cursor()
#连接到服务器主机,执行Linux命令进行数据监控
ssh = paramiko.SSHClient()
ssh.set_missing_host_key_policy(paramiko.AutoAddPolicy())
ssh.connect(table.cell_value(i, 0), 22, 'name', 'password', timeout=5)
VARIABLES = dict(select_sql_all(cursor, "show VARIABLES like \"%%\";"))
stdin, stdout, stderr = ssh.exec_command("vmstat 1 3") # 执行命令
file_vmstat.write(str(datetime.datetime.now()) + "\n") #写入文件
for j in range(5):
cmd_result = stdout.readline(), stderr.read() # 读取命令结果
file_vmstat.write(str(cmd_result) + "\n")
global_status = dict(select_sql_all(cursor, "show global status;"))
file.write(str(datetime.datetime.now()) + "\n" + str(global_status) + "\n")
file.write(str(datetime.datetime.now()) + "\n")
# key_buffer_size
file.write("读取索引的缓存未命中率为%.2f%%" % count_data(global_status.get("Key_reads"),global_status.get("Key_read_requests")) + "\n")
# # 单位时间内的Key_reads
file.write(
"单位时间从磁盘读取索引的请求次数为%.2f%%" % count_data(global_status.get("Key_reads"), global_status.get("Uptime")) + "\n")
# # 最大使用的连接数除以设置的最大连接数,理想值 ≈ 85%
file.write("连接数占用率为%.2f%%" % count_data(global_status.get("Max_used_connections"),VARIABLES.get("max_connections")) + "\n")
# # blocks数使用率(理想值 ≈ 80%)
# file.write("blocks数使用率为%.2f%%" % count_data(global_status.get("Key_blocks_used"), global_status.get("Key_blocks_used") + global_status.get("Key_blocks_unused"))+ "\n")
# # 磁盘上创建临时表占比(理想值<= 25%)
file.write("磁盘上创建临时表占比为%.2f%%" % count_data(global_status.get("Created_tmp_disk_tables"),global_status.get("Created_tmp_tables")) + "\n")
# # table_open_cache理想值 (<= 95%)
file.write("表打开率为%.2f%%" % count_data(global_status.get("Open_tables"), VARIABLES.get("table_open_cache")) + "\n")
# # 文件打开数(open_files)(理想值<= 75%)(服务器有个配置最大文件打开数:/etc/security/limits.conf)
file.write("文件打开数为%.2f%%" % count_data(global_status.get("Open_files"), VARIABLES.get("open_files_limit")) + "\n")
# 表扫描率超过4000,说明进行了太多表扫描,很有可能索引没有建好
file.write("表扫描率为%.2f%%" % count_data(global_status.get("Handler_read_rnd_next"),
global_status.get("Com_select")) + "\n")
# Innodb缓冲池(命中率不低于95%)
file.write("缓冲池的读命中率为%.2f%%" % count_data(global_status.get("Innodb_buffer_pool_read_requests"),
global_status.get("Innodb_buffer_pool_reads") + global_status.get(
"Innodb_buffer_pool_read_requests")) + "\n")
# 缓冲池的空闲率
file.write("表扫描率为%.2f%%" % count_data(global_status.get("Innodb_buffer_pool_pages_free"),
global_status.get("Innodb_buffer_pool_pages_total")) + "\n")
# 缓冲池的脏块的百分率
file.write("表扫描率为%.2f%%" % count_data(global_status.get("Innodb_buffer_pool_pages_dirty"),
global_status.get("Innodb_buffer_pool_pages_total")) + "\n")
cursor.close()
db.close()
file.close()
ssh.close()
file_vmstat.close()
if __name__ == '__main__':
del_files(r"d:\测试文件\mysql")
num = 0
#执行监控间隔时间,执行总次数,mysql服务器配置Excel行数
sleep_time,frequency,nrows = 20,2,8
while True:
num = num + 1
#进程池,使用多进程,加快执行速度
pool = multiprocessing.Pool(processes=3)
for i in range(1,nrows):
pool.apply_async(print_out(i))
time.sleep(sleep_time)
pool.close()
pool.join()
if num >= frequency:
break
Python实现监控mysql相关指标(show global status 与 服务器CPU等)
最新推荐文章于 2024-05-29 11:21:17 发布