A real-time monitoring web tool for oracle service response time written in python

The old fellow's scaffold is mainly based on the old iron scaffold. https://github.com/shengxinjing/my_blog/tree/master/monitor

Main tools:

python, flask, sqllite (I didn't choose mysql, sqllite is enough, it's a small function, I like simplicity)

Main functions:

Monitor the average response time of oracle 10g and 11g databases. By collecting the database rt and generating the trend chart, it is convenient for customers to view the operation status of the front system quickly through the large screen, especially to judge whether the current business system response is slow due to the abnormal database;

 

#############################Database collection code:########################################

# -*- coding: utf-8 -*-
# @Time    : 2018/7/13 14:55
# @Author  : Lixora@Foxmail.com
# @File    : monitor.py
# @Software: PyCharm

import time
import MySQLdb as mysql
import cx_Oracle
import datetime
import sqlite3


# Connect to SQLite database
# The database file is test.db
# If the file does not exist, it is automatically created in the current directory:
conn = sqlite3.connect('test.db',check_same_thread=False)
# Create a Cursor:
cursor = conn.cursor()
# Execute a SQL statement to create the user table:
try:
   cursor.execute(' CREATE TABLE `rt` (  `time` bigint primary key,  `rt` double)')

#Source data table
#  CREATE TABLE `rt` (  `time` varchar(20) DEFAULT NULL,  `rt` double)
except Exception,e:
    print Exception,":",e

#You can also use 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:
    #Acquisition terminal automatic timing access time 3 seconds
    time.sleep(3)
    getRt()
    # cursor.execute('select * from rt')
    # values = cursor.fetchall()
    # print values[0]

#############################web server code:########################################

# -*- coding: utf-8 -*-
# @Time    : 2018/7/16 20:00
# @Author  : Lixora@Foxmail.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 template file###########################################
<!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}};
  //Create charts
  var chart;
  $(document).ready(function() {
   Highcharts.setOptions({
    global:{
        useUTC:false
    }
   })

   chart = new Highcharts.StockChart( {
    chart : {
     renderTo : 'container',
     events : {
      load : st// timer
     }
    },
    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',
     }]
   });
  });

  //Refresh data once in 2 seconds
  function st() {
   setInterval("getData()", 3000);
  }

  //Dynamically update chart data
  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 rendering##################################

Default display interface: http://127.0.0.1:8888/

 

 

 

 

 

Tags: Highcharts Javascript MySQL Database

Posted on Thu, 13 Feb 2020 15:10:45 -0500 by behicthebuilder