Graphs for usage statistics of the databases

Web page options

The web page with the statistic graphs loads after choosing from a list of services linking in it. On the top and left side of the web page there is a 3 option menu:

• Start Date - Text box and pop-up calendar for picking from when date and time, with default value 1 week ago.

• End Date - Text box and pop-up calendar for picking until when date and time, with default value the last hour.

• Moving Average (Weight) - Dropbox choice between 0 and 4, which represents the number of values selected before and after each value for deviation, in order to have a more normalized distribution in the graphs.

Background and development

Requirements

For the development, the following programs are used:

• Apache web server - For local web page hosting.
• Oracle instant client - For accessing and developing on the database.
• PHP 5 or higher with - For web page development.
  • gd module - installation [[http://ch2.php.net/gd]instructions] + GD lib - this library has more dependencies
  • oci8 module - needs instant client libraries
• Java 1.5 or higher - For Java scripts use.

Monitoring Tables

Each database for which statistics are gathered, has stored in its tablespace the following:

• vstat - An index organized table, storing all the needed statistic data from the database since its creation. (see attached files)
• statcollect - A procedure that queries DBA_HIST_SERVICE_STAT and DBA_HIST_SNAPSHOT, and inserts in vstat table any missing data. (see attached files)
• statcollect_job - A scheduled job which runs every hour the statcollect procedure. It needs to be enabled. (see attached files)

The vstat table is needed for 2 basic reasons:

• DBA_HIST_SERVICE_STAT and DBA_HIST_SNAPSHOT can be queried only for one week ago at the maximum, but in vstat table only the useful statistic are being kept, starting one week before the date of creation of the table.
• It is really faster to keep an index organized table with the useful data, than querying the DBA_HIST_SERVICE_STAT and DBA_HIST_SNAPSHOT each and every time.

Databases and Schema

• Currently gathering statistics from the databases:

• • CMSR - CMS Offline database
  • CMSONR - CMS Online database
  • ATLR - ATLAS Offline database
  • ATONR - ATLAS Online database
  • LHCB - LHCB database
  • LCGR - LCG database
  • PDBR - PDB database
  • COMPR - COMPASS database

• • INT11r - Integration 11
  • INT12R - Integration 12

• Used schema to store the data:
  • usermon

Gradually, more online and offline databases will be monitored.

Libraries and script files

The list of files used for the web page and the graphs is the following:

• common_lib.php - File with common functions.
• lib.php - File with more common functions.
• show_stats.php - Web page file, showing the graphs for the selected service and the option menu.
• index.php - Main web page file with the list of services linking to show_stats.php . They are located in directories named like the database's which they read, under: https://phydb.web.cern.ch/phydb/ . i.e. https://phydb.web.cern.ch/phydb/CMS for CMS, https://phydb.web.cern.ch/phydb/ATLAS for ATLAS.
• clusterplot.php - File for graph calculation and draw.
• vars.inc - File with database credentials.
• style.css - File containing styles and colors.
• datetimepicker.js - Java script file for pop-up date-time picking calendar.

• jpgraph library files
  • jpgraph.php - For producing graphs.
  • jpgraph_line.php - For line graphs.
  • jpgraph_bar.php - For the bar graphs.
  • jpgraph_date.php - For date scale.

When a service is selected from the list in the server's full report web page (new_report_cluster.php ), the service detailed statistic page (show_stats.php) is being loaded for all active statistics, with default values: the most recent (usually last hour's) snapshot and one week's ago, which are queried from vstat table. For every statistic, clusterplot.php is being loaded for the given period as a png image.There's also the option to select a different period, using a javascript date-time picker. The style of the web page is in style.css file (same as new_report_cluster.php), the head function of the page and the database credentials are in common_lib.php.

The clusterplot.php image is loaded with all the parameters that needs for the query in the link. When the data have been fetched from vstat table, the jpgraph libraries give a lot of parameters and function to configure how the data will be shown in a plot.