数据可视化的目的是通过可视化分析转化为能快速识别的图文报告。
日常数据表，海量数据，很难一眼就看出问题，将其转换为图形化报表，会比较容易识别出问题点，比如，几点几分出现了高频率应用，宿主机与docker是否出现同时高频率应用。
图形化分析需求
- 监控宿主机Ubuntu CPU运行情况；
- 监控MySQL Thread运行情况；
- 读取指定类型的数据值，并写入MySQL;
- 读取数据库，分析为数据可视化echart Line.
Ubuntu CPU监控功能
库文件
import pymysql
import time
from telnetlib import Telnet
import socket
import re
import os
from scapy.layers.inet import *
import requests
from pyecharts.charts import Line
from pyecharts import options as opts
from pyecharts.charts import Bar, Pie, Page, Gauge
from pyecharts.globals import ThemeType
from pyecharts.globals import CurrentConfig, OnlineHostType
import psutilUbuntu CPU数据可视化实现过程
CPU Usage获取数据源
def get_cpuinfo():
global cpu_used
cpu_used = psutil.cpu_percent(1) # 隔1秒统计获取CPU percent
print("#"*20)
print(f"cpu usage：{cpu_used}")Ubuntu CPU数据可视化
def line2(all_time, sys_cpu):
all_time = []
sys_cpu = []
sql3306(server, 3306)
if sql_3306 == 0:
try:
conn = pymysql.connect(host=server,port=3306,user=user,password=password,database=database ) # 连接数据库
cur1 = conn.cursor()
SQL1 = " SELECT concat(date_format(ddid,'%d'),'-',date_format(ddid,'%H'),'-',date_format(ddid,'%i'))as ddhh,cpu_percent \
FROM ubuntu_mon WHERE DATEDIFF(curDATE(),ddid)<=2 group by concat(date_format(ddid,'%d'),'-',date_format(ddid,'%H'),'-',date_format(ddid,'%i')),cpu_percent;"
cur1.execute(SQL1)
upload_data3 = cur1.fetchall()
# print(upload_data2)
for ii3 in upload_data3:
all_time.append(ii3[0])
sys_cpu.append(ii3[1])
print(all_time)
print(sys_cpu)
conn.close()
except Exception as e:
print('SQL server connection is issue: ',e)
pass
else:
print('SQL server connection is issue')
pass
line = (
Line(init_opts=opts.InitOpts(chart_id='4', bg_color='#003366', theme=ThemeType.MACARONS))
.add_xaxis(all_time)
.add_yaxis('Ubuntu Cpu Usage:', sys_cpu)
.set_global_opts(title_opts=opts.TitleOpts(title="CPU Usage", subtitle='最近48小时的CPU统计',
title_textstyle_opts=opts.TextStyleOpts(color='white'),
subtitle_textstyle_opts=opts.TextStyleOpts(color='white'), ),
datazoom_opts=opts.DataZoomOpts(type_='slider', is_show=True, range_start=10,
range_end=100),
legend_opts=opts.LegendOpts(pos_left='right',
textstyle_opts=opts.TextStyleOpts(color='white',
font_size=14)),
xaxis_opts=opts.AxisOpts(splitline_opts=opts.SplitLineOpts(is_show=True,linestyle_opts=opts.LineStyleOpts(color="#ffffff1f")),type_='category', axislabel_opts=opts.LabelOpts(
rotate=45, color='#ffffff'), name="Time", ),
yaxis_opts=opts.AxisOpts(splitline_opts=opts.SplitLineOpts(is_show=True,linestyle_opts=opts.LineStyleOpts(color="#ffffff1f")),name="Rate", axislabel_opts=opts.LabelOpts(
color='#ffffff', font_size=15), ),)
.set_series_opts(label_opts=opts.LabelOpts(font_size=15, color='#ffffff',is_show=False), )
)
line.render()
return lineMySQL Thread监控功能
获取数据源+写入数据库
def get_mysql_status():
# 连接MySQL数据库
sql3306(server, 3306)
if sql_3306 == 0:
try:
conn = pymysql.connect(
host=server,
user=user,
port=3306,
password=password,
database=db
)
# 获取数据库线程信息
cursor.execute("show status like 'Threads%';")
temp2 = cursor.fetchall()
threads_name = []
threads_count = []
for i2 in temp2:
threads_name.append(i2[0])
threads_count.append(i2[1])
thread_cache = threads_name[0]
thread_connected = threads_name[1]
thread_created = threads_name[2]
thread_running = threads_name[3]
cache_count = threads_count[0]
connected_count = threads_count[1]
created_count = threads_count[2]
running_count = threads_count[3]
print('线程类型 thread type: ', threads_name)
print('线程数 thread count: ', threads_count)
print('线程数 thread running count: ', running_count)
# 写入数据库
t3 = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime())
sql = "INSERT INTO mysql_mon(ddid,thread_cache,cache_count,thread_connected,connected_count,thread_created,created_count,\
thread_running,running_count) \
VALUES ('{}', '{}', {}, '{}',{}, '{}', {}, '{}', {}, );" .format(t3, thread_cache, cache_count, thread_connected, connected_count, thread_created, created_count,
thread_running, running_count)
# 使用 execute() 方法执行 SQL 查询
# print(sql)
# print('**-'*20)
cursor.execute(sql)
# 确认提交数据.
conn.commit()
print("upload success")
# 关闭数据库连接
cursor.close()
conn.close()
except Exception as e:
print('SQL server connection is issue: ', e)
conn.close()
pass
else:
print('SQL server connection is issue')
passMySQL thread数据可视化分析
def line0(mysql_time, thread_cache_count,thread_connected_count,thread_created_count,thread_running_count):
mysql_time = []
thread_cache_count = []
thread_connected_count = []
thread_created_count = []
thread_running_count = []
sql3306(server, 3306)
if sql_3306 == 0:
try:
conn = pymysql.connect(host=server,port=3306,user=user,password=password,database=database ) # 连接数据库
cur1 = conn.cursor()
SQL1 = "SELECT concat(date_format(ddid,'%d'),'-',date_format(ddid,'%H'),'-',date_format(ddid,'%i'))as ddhh,cache_count,connected_count,created_count,running_count \
FROM mysql_mon WHERE DATEDIFF(curDATE(),ddid)<=1 group by concat(date_format(ddid,'%d'),'-',date_format(ddid,'%H'),'-',date_format(ddid,'%i')),cache_count,connected_count,created_count,running_count;"
cur1.execute(SQL1)
upload_data = cur1.fetchall()
# print(upload_data)
for ii1 in upload_data:
mysql_time.append(ii1[0])
thread_cache_count.append(ii1[1])
thread_connected_count.append(ii1[2])
thread_created_count.append(ii1[3])
thread_running_count.append(ii1[4])
print(thread_cache_count)
print(thread_connected_count)
print(thread_created_count)
print(thread_running_count)
conn.close()
except Exception as e:
print('SQL server connection is issue: ',e)
pass
else:
print('SQL server connection is issue')
line = (
Line(init_opts=opts.InitOpts(chart_id='2', bg_color='#003366', theme=ThemeType.MACARONS))
.add_xaxis(mysql_time)
.add_yaxis('Theards Cache:', thread_cache_count)
.add_yaxis('Theards Connected:', thread_connected_count)
.add_yaxis('Theards Created:', thread_created_count)
.add_yaxis('Theards Running:', thread_running_count)
.set_global_opts(title_opts=opts.TitleOpts(title="MySQL Threads", subtitle='最近48小时的线程情况',
title_textstyle_opts=opts.TextStyleOpts(color='white'),
subtitle_textstyle_opts=opts.TextStyleOpts(color='white'), ),
datazoom_opts=opts.DataZoomOpts(type_='slider', is_show=True, range_start=10,
range_end=100),
legend_opts=opts.LegendOpts(pos_left='right',
textstyle_opts=opts.TextStyleOpts(color='white',
font_size=14)),
xaxis_opts=opts.AxisOpts(splitline_opts=opts.SplitLineOpts(is_show=True,linestyle_opts=opts.LineStyleOpts(color="#ffffff1f")),type_='category', axislabel_opts=opts.LabelOpts(
rotate=45, color='#ffffff'), name="Time", ),
yaxis_opts=opts.AxisOpts(splitline_opts=opts.SplitLineOpts(is_show=True,linestyle_opts=opts.LineStyleOpts(color="#ffffff1f")),max_=20, name="Rate", axislabel_opts=opts.LabelOpts(
color='#ffffff', font_size=15), ),
)
.set_series_opts(label_opts=opts.LabelOpts(font_size=15, color='#ffffff',is_show=False), )
)
line.render()
return line数据可视化图表
CPU Usage
MySQL threads
