Monitoring Hydra's Database and Applications

Introduction

As soon as Hydra is launched into production mode you should set up monitoring for Hydra's database instance.

The following parameters are to be monitored:

• Basic parameters of the billing server (load avg, CPU and RAM usage, I/O of the disk array, the number of processes, free space within the disk array).
• Key figures for the database instance (the number of sessions, current load, SGA status, and so on).
• Scheduled task statuses in Hydra.
• Monitoring the amount of unrated CDRs and the date of the last load.
• The number of rows in most important tables such as EX_CALL_DATA_REC, EX_DATA_COLLECT, EX_TRAFFIC_COLLECT_C, SD_GOOD_MOVES.

To monitor Hydra, you can use any popular system which allows receiving data with the help of CLI scripts. Most popular monitoring solutions among providers are free solutions like Zabbix and Nagios. 

To monitor key figures of the Hydra's database instance you can use the scripts (from the Oracle section), created especially for Zabbix.

Monitoring Task Execution in Hydra

In order to set up monitoring for task execution in Hydra, you can use either one of the custom scripts (1) or hydra_monitoring.sh
hydra_job_monitoring.sh 

#!/bin/sh
  
source /etc/profile
  
rval=0
  
ORA_USER="AIS_NET"
ORA_PASS="mypass"
SQLPLUS_PATH="$ORACLE_HOME/bin/sqlplus"
 
if [ -n "$3" ]; then
       ORA_SID="$3"
       export ORACLE_SID=$ORA_SID
fi
  
sql=""
  
case $1 in
  
'job_state')
    if [ -n "$2" ]; then
        echo "
        SELECT to_char(N_JOB_STATE_ID, 'FM99999999999999990') FROM SS_V_JOBS WHERE N_JOB_ID=$2;
        " | ${SQLPLUS_PATH} -s ${ORA_USER}/${ORA_PASS}@${ORACLE_SID} |
        awk '{  if ($1 == 2034) print "Running" 
            else if($1 == 1034) print "Waiting"
            else if($1 == 3034) print "Starting"
            else if($1 == 4034) print "Locked"
            else if($1 == 5034) print "Deleted"
            else if($1 == 6034) print "Error"
            else if($1 == 7034) print "Cant Start"
        }'
    else
        rval=1
        echo "No JOB_ID" >&2
    fi
    ;;
'job_last_start')
    if [ -n "$2" ]; then
        sql="
        SELECT to_char((sysdate - D_LAST_START) * (86400), 'FM99999999999999990') FROM SS_V_JOBS WHERE N_JOB_ID=$2;
        "
    else
        rval=1
        echo "No JOB_ID" >&2
    fi
    ;;
*)
    echo "Hydra monitoring tool"
    echo "usage:"
    echo "    $0 job_state <JOB_ID> [SID]  -- Check job status."
    echo "    $0 job_last_start <JOB_ID> [SID]  -- Check job last start date/time."
    rval=1
    exit $rval
    ;;
esac
  
if [ -n "$sql" ]; then
       echo "$sql" | ${SQLPLUS_PATH} -s ${ORA_USER}/${ORA_PASS}@${ORACLE_SID}
fi
rval=$?
 
exit $rval
  

There are 7 types of statuses:

N stands for an off-normal status. Exception: a manually locked task.

To see the list of tasks and their IDs, go to Administration -> Tasks - Scheduled tasks.
We highly recommend monitoring all tasks in the system.

For monitoring new Task Execution in the last 15 minutes, you can use the following SQL request:

SELECT COUNT(*) 
FROM ss_v_job_seances
WHERE d_start > SYSDATE - 15/24/60
AND   c_reason = 'A'

If the result is '0', then Task Execution is doesn't work.

Monitoring Task Runs

Apart from monitoring task statuses, you should also monitor the statuses of completed task runs. With the help of such monitoring, you will be able to track any off-normal statuses of completed task runs, as well as the execution duration for the tasks. To receive details on the last 10 completed task runs, you should use the following SQL request:

where:

• num_N_JOB_ID — task ID.

There are 5 possible task execution results ( see N_JOB_STATUS in the SQL request above):

Off-standard statuses are marked with N.

The D_START and D_FINISH fields of the SQL request contain the begin date and the end date of the execution. For running tasks, D_FINISH is set to NULL.

To see the list of tasks and their IDs, go to Administration -> Tasks - Scheduled tasks.
We highly recommend monitoring all tasks in the system.

Monitoring Tablespace

You should monitor the statuses of the database tablespaces. Use this SQL request to receive the required details:

As a result, you will get a table similar to the example below:

Fileds stand for:

• TABLESPACE — a name of a tablespace
• Used MB — amount of used blocks within the used disk volume space
• Free MB — amount of free blocks within the used disk volume 
• Total MB — a total used disk space volume
• Total Max MB — a maximum available disk space that can be occupied by the database. It can be greater than the volume of the available disk space.
• Free Max MB — a maximum available disk space that can be used for increased data. It can be greater than the volume of the available disk space.
• Pct. Free — a free volume percentage. It is calculated as a ratio of Free Max MB to Total Max MB.

You should pay attention to the Pct. Free key figure estimated for the HYDRA and HYDRA_INDEX spaces. If it drops to really low numbers (less than 20%), the system administrator should be automatically informed about the problem concerning the free space. Otherwise, for example, some important system tasks in the database will be brought to a halt with the following error message:

Monitoring Unrated Phone Calls

The request below returns a number of unrated phone calls over the last hour:

The standard value here is 0, i.e. no unrated phone calls at all.

Monitoring the Date of the Last CDR Load

The following request is used to view the date and time of the last CDR load into Hydra (this option is convenient for analyzing by a person):

As a result, the response to the request will contain either the exact date in the following format:

or the «Never» row, if there is no CDR in the database:

The following request returns result containing either the date and time of the last CDR load (calculated in seconds since the date of the last load up to the current moment) or «-1» in case there is no CDR in the database. The following request is a good option for processing within monitoring systems:

Triggers for monitoring should be set up depending on either the interval for loading CDR or RADIUS accounting.

Monitoring the Date of the Last Sessions Update

The following request is used to monitor the date of the last sessions of updating customer data in Hydra (this option is convenient for analysing by a person):

As a result, the response to the request will contain either the exact date in the following format:

or the «Never» string, if there are no sessions in the database:

The following request returns a response containing either the date and time of the last sessions update (calculated in seconds since the date of the last update up to the current moment) or «-1» in case there are no sessions in the database. This request is a good option for processing within monitoring systems:

Triggers for monitoring should be set up depending on the interval for loading RADIUS accounting.

Monitoring the Date of the Last Successful Payment Load

The following request is used to monitor the date of the last successful payment load from external payment systems into Hydra (this option is convenient for analyzing by a person):

As a result, the response to the request will contain either the exact date in the following format:

or the «Never» row, if there are no successful payments from external systems in the database: 

To view the date of the last successful load for a certain payment system you should use the following request:

where:

• num_N_TO_ACCOUNT_ID — stands for the payment system account ID of the provider organization.

The request below is followed by a response containing either the date and time of the last successful payment load (calculated in seconds since the date of the last load up to the current moment), or «-1» in case there are no successful payments from external payment systems in the database. This request can be used for processing within monitoring systems: 

A similar request when monitoring by payment system:

where:

• num_N_TO_ACCOUNT_ID — stands for the payment system account ID of the provider organization.

Triggers for monitoring should be set up depending on how often payments arrive.

Monitoring Events Execution

The following request is used to monitor the number of errors and warnings appeared in events queue over the last hour: 

The standard value here is 0, i.e. no errors or warnings at all.

Monitoring Replication Consistency

In order to check replication, you should use the following request at both databases on behalf of a user with the SYSDBA permission (otherwise it will not be executed on the standby server). 

The result must be the same for both servers. It is permitted to have a difference of not more than 1-2 versions due to replication delay.

To set up monitoring for replication at /etc/sudoers you should add a permission for the hzabbix user to run Oracle script that will access the database on behalf of SYSDBA.

To run the script

The monrep.sh script containing a request for obtaining max(sequence#)

Monitoring the Number of Table Rows

You must monitor the number of table rows because a great number of rows in certain tables will inevitably cause performance issues. Such issues are typically located only when the number of table rows has exceeded all tolerable limits due to the incorrect configuration of the system. As a result, it is impossible to delete exceeded data in the short run.

See below an example of a request for checking the number of table rows:

The table of threshold values

To define the number of completed PPP sessions per month you can use the following request:

SD_GOOD_MOVES

This table contains charge log and invoice lines.
When the threshold value is exceeded you need to set up data aggregation and archiving in the system. See the Hydra Billing Admin Guide, Using the System->Administration->Tasks->Standard tasks-> Archiving charge logs 

EX_DATA_COLLECT

This table contains statistics of PPP sessions traffic. When the threshold value is exceeded you should go to the settings for the Deleting old CDRs tasks and decrease the value in the EX_DATA_COLLECT records aging period parameter.

EX_CALL_DATA_REC

This table contains data on CDRs and PPP sessions.
When the threshold value is exceeded you must: 

1. Use the following script to define which type of data has the majority 

   
   

   SQL> SELECT SI_REF_PKG_S.GET_NAME_BY_ID(N_CDR_TYPE_ID) VC_CDR_NAME,        COUNT(*) FROM   EX_CALL_DATA_REC GROUP BY N_CDR_TYPE_ID;   VC_CDR_NAME              COUNT(*) ------------------------------------------------------------------------ Phone call       107552 PPP session (unrated)  3774012

2. In case of a majority of CDRs (phone calls), you should contact technical support for exporting CDRs in a file.
3. In case of a majority of PPP sessions, you should decrease the value of the Timeout for deleting old CDRs parameter in network services which PPP sessions are created for.

SS_SESSION_LOGS

This table contains data on system session logs that are created with the help of the MAIN.INIT procedure. When the threshold value is exceeded you should contact technical support for deleting old session records.

Monitoring System Applications

Web Applications

HOPER

When starting a process, a PID file is created.

• For a version 3.3 and higher, its location is specified in the config file, typically at /var/run/hydra/hoper/unicorn.pid.
• For a version below 3.3, its location cannot be changed. It is located at shared/unicorn.pid of the application installation root.

The following coding returns 0 if the process exists, or 1, if either it does not exist or the PID-file of the process cannot be found (by the root user):

HUPO

When starting a process, a PID file is created.

• For a version 3.3 and higher, its location is specified in the config file, typically at /var/run/hydra/hupo/unicorn.pid .
• For a version below 3.3, its location cannot be changed. It is located at shared/pids/unicorn.pid of the application installation root.

The following coding returns 0 if the process exists, or 1, if either it does not exist or the PID-file of the process cannot be found (by the root user):

HDD

When starting, a hdd_default and a PID file are created.

• For a version 3.3 and higher, its location is specified in the config file, typically at /var/run/hydra/hdd/hdd_default.pid .
• For a version below 3.3, its location cannot be changed. It is located at tmp/ of the application installation root.

The following coding returns 0 if the process exists, or 1, if either it does not exist or the PID-file of the hdd_default process cannot be found (by the root user):

Agents

hamd

When starting a process, a PID file is created, with its location specified in the config file (/etc/hamd/hamd.conf). Typically, a PID file is located along the /var/run/hydra/hamd.pid path.

The following coding returns 0 if the process exists, or 1, if either it does not exist or the PID-file of the process cannot be found (by the root user):

We also recommend checking for the process at the port. The following coding returns 0 if the process can listen to the required port, otherwise, the value is 1:

hard

When starting a process, a PID file is created, with its location specified in the config file (/etc/hard/hard.conf). Typically, a PID file is located along the /var/run/hydra/hard.pid path.
The following coding returns 0 if the process exists, or 1, if either it does not exist or the PID-file of the process cannot be found (by the root user):

We also recommend checking for the process at the port. The following coding returns 0 if the process can listen to the required port, otherwise, the value is 1:

hcd

When starting a process, a PID file is created, with its location specified in the config file (/etc/hcd/hcd.conf). Typically, a PID file is located along the /var/run/hydra/hcd.pid path.
The following coding returns 0 if the process exists, or 1, if either it does not exist or the PID-file of the process cannot be found (by the root user):

We also recommend checking for the process at the port. The following coding returns 0 if the process can listen to the required port, otherwise, the value is 1:

hid

When starting a process, a PID file is created, with its location specified in the config file (/etc/hid/hid.conf). Typically, a PID file is located along the /var/run/hydra/hid.pid path.

The following coding returns 0 if the process exists, or 1, if either it does not exist or the PID-file of the process cannot be found (by the root user):

We also recommend checking for the process at the port. The following coding returns 0 if the process can listen to the required port, otherwise, the value is 1:

hpd

When starting a process, a PID file is created, with its location specified in the config file (/etc/hpd/hpd.conf). Typically, a PID file is located along the /var/run/hydra/hpd.pid path.
The following coding returns 0 if the process exists, or 1, if either it does not exist or the PID-file of the process cannot be found (by the root user):

We also recommend checking for the process at the port. The following coding returns 0 if the process can listen to the required port, otherwise, the value is 1:

FreeRADIUS

When starting a process, a PID file is created, with its location specified in the config file (typically, /etc/freeradius/radiusd.conf). Typically, a PID file is located along the /var/run/radiusd/radiusd.pid path.
The following coding returns 0 if the process exists, or 1, if either it does not exist or the PID-file of the process cannot be found (by the root user):

We also recommend checking for the process at the port (most often, the UDP port 1812 is used for authorizing). The following coding returns 0 if the process can listen to the required port, otherwise, the value is 1:

Application: A Script for a Database User with Required Permissions

The following script contains commands for creating a user with the permissions required for monitoring the system and Oracle, you should run it on behalf of the SYS user:

Creating a user for monitoring