用python 写的一个oracle 服务响应时间的实时监控web 小工具
程序员文章站
2022-04-11 13:17:30
...
主要参照这位老铁的脚手架 ：https://github.com/shengxinjing/my_blog/tree/master/monitor
主要工具：
python，flask，SQLLITE （我没有选择mysql，sqllite 够用了，本来就是一个小功能，我喜欢简单）
主要功能：
监控oracle 10g，11g 数据库平均响应时间，通过实施采集数据库rt 并生成趋势图，方便客户通过大屏简单明了，快速查看但前系统的运行状态，尤其是判断是否由于当前业务系统响应慢是否由于数据库异常导致；
#############################数据库采集代码：########################################
# -*- coding: utf-8 -*-
# @Time : 2018/7/13 14:55
# @Author : aaa@qq.com
# @File : monitor.py
# @Software: PyCharm
import time
import MySQLdb as mysql
import cx_Oracle
import datetime
import sqlite3
# 连接到SQLite数据库
# 数据库文件是test.db
# 如果文件不存在，会自动在当前目录创建:
conn = sqlite3.connect('test.db',check_same_thread=False)
# 创建一个Cursor:
cursor = conn.cursor()
# 执行一条SQL语句，创建user表:
try:
cursor.execute(' CREATE TABLE `rt` ( `time` bigint primary key, `rt` double)')
#源数据表
# CREATE TABLE `rt` ( `time` varchar(20) DEFAULT NULL, `rt` double)
except Exception,e:
print Exception,":",e
#也可以使用mysql
#db = mysql.connect(user="root", passwd="mysql", db="test", host="10.80.18.241")
#db.autocommit(True)
#db.ping(True)
#cur = db.cursor()
db2 = cx_Oracle.connect('system1', 'lixora', '100.106.102.60:1521/lixora')
cur2 =db2.cursor()
def getRt():
sql2 = '''select round(value ,2) from V$SYSMETRIC where METRIC_NAME in ('SQL Service Response Time')'''
cur2.execute(sql2)
out1=cur2.fetchall()
print out1[0][0]
now = datetime.datetime.now()
now = int(now.strftime("%H%M%S"))
print now
sql ="insert into rt(time,rt) values (%d,%f)" % (now,out1[0][0])
try:
cursor.execute(sql)
conn.commit()
except Exception,e:
print Exception, ":", e
# print 'ok'
while True:
#采集端自动定时取数时间3秒
time.sleep(3)
getRt()
# cursor.execute('select * from rt')
# values = cursor.fetchall()
# print values[0]
#############################web server 代码：########################################
# -*- coding: utf-8 -*-
# @Time : 2018/7/16 20:00
# @Author : aaa@qq.com
# @File : flask_web.py
# @Software: PyCharm
import sqlite3
import json
from flask import Flask, request, render_template
app = Flask(__name__)
db = sqlite3.connect('test.db',check_same_thread=False)
c = db.cursor()
@app.route('/', methods=['GET', 'POST'])
def hello():
c.execute('select time,rt from rt')
ones = c.fetchall()
print ones
return render_template('mon.html', data=json.dumps(ones))
@app.route('/new', methods=['GET'])
def getnew():
c.execute('select time,rt from rt order by TIME desc limit 1')
v = c.fetchone()
top = [v[0], v[1]]
print top
return json.dumps(top)
app.run(port=8888,debug=True)
########################html mon.html 模板文件###########################################<!DOCTYPE HTML>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<titlte>Lixora's DB RT Trend</titlte>
<script type="text/javascript" src="http://cdn.hcharts.cn/highstock/highstock.js"></script>
<script type="text/javascript" src="http://cdn.hcharts.cn/jquery/jquery-1.8.3.min.js"></script>
<script type="text/javascript" src="http://cdn.hcharts.cn/highcharts/highcharts.js"></script>
<!--<script type="text/javascript" src="http://cdn.hcharts.cn/highcharts/funnel.js"></script>-->
<script type="text/javascript" src="http://cdn.hcharts.cn/highcharts/modules/exporting.js"></script>
<script type="text/javascript">
var data={{data}};
//创建图表
var chart;
$(document).ready(function() {
Highcharts.setOptions({
global:{
useUTC:false
}
})
chart = new Highcharts.StockChart( {
chart : {
renderTo : 'container',
events : {
load : st// 定时器
}
},
rangeSelector: {
inputEnabled: $('#container').width() > 480,
selected: 1
},
exporting:{
enabled:true
},
title : {
text : 'DB Response Time Per Second'
},
series : [ {
name: 'DB RT',
data : data,
type: 'spline',
}]
});
});
//2秒钟刷新一次数据
function st() {
setInterval("getData()", 3000);
}
//动态更新图表数据
function getData() {
$.ajax({
type: "get",
url: "/new",
dataType: "json",
success : function(data){
chart.series[0].addPoint(data,true,true);
}
});
}
</script>
</head>
<body>
<div id="container" ></div>
</body>
</html>############################################demo 效果图##################################
默认显示界面： http://127.0.0.1:8888/