Submit a ticketCall us

Bridging the ITSM Divide
Integrated help desk and remote support software for faster resolution

Join us on Wednesday, November 29, 2017 at 11 a.m. CT, as we discuss the benefits of effectively integrating your help desk software with remote support solutions to help increase the efficiency of IT administration, improve communication, and decrease mean time to resolution (MTTR) for IT issues of all sizes. This directly impacts end-user satisfaction and your business’ bottom line. Register Now.

Home > Success Center > Database Performance Analyzer (DPA) > DPA Database Metrics Architecture

DPA Database Metrics Architecture

Overview

This document describes the architecture for the database metrics feature of Database Performance Analyzer (DPA). A description of the vSphere metrics is not included here.

 

The term database metric is defined as data that pertains to a specific monitored database instances. This can include database system resources, O/S resources as well as any other information that is accessible via query against the monitored instance or Repository in the case of wait time metrics.

Environment

All versions of DPA

Detail

Metric Properties

Metrics have two sets of associated properties. The first is metadata that describes how metric data is displayed and evaluated; the second specifies how metric data is collected.

 

Properties for metrics that are provided out of the box by DPA (default metrics) are stored in XML files located in the installation directory:

               [DPA HOME] /iwc/tomcat/webapps/iwc/WEB-INF/classes/resources/metrics

Metric Metadata

Metadata for each metric collected is stored in files specific to a database type. For those files, the naming convention is:

                 <Metric Type>-metrics-properties_<Database Type>.xml

where metric type is either 'default', 'custom' or 'userdefined').

These files contain the following properties:

 

Property

Description

Default

Name

Internal name of metric (defined as attribute in XML file)

N/A: required

Display Name

Used in chart titles

N/A: required

Description

Used in chart tooltips

Empty string

Units

Free-form (i.e., not validated) string indicating data units for metric data (only used for display purposes)

Empty string

Percent

True if metric data is percent. Used for chart value range and data validation (>=0, <=100).

False

Scale Data for Display

True if metric data should be scaled during charting based on maximum data value to display. Data Unit property must be set.

False

Data Unit Name

Fully qualified path to default DataUnit enum that represents persisted metric data unit. Set if metric data is to be scaled for charting (e.g., wait time data in seconds can be scaled to minutes, hours, days).

Null

Category

Free-form (i.e., not validated) string that defines tab on the resource page in which metric is displayed

N/A: required

Category Order

Used for intra-category ordering for chart display (integer)

9999

Information Page

Name of HTML file that contains details for metric. Used for popup dialogs from charts.

Null

Layer

Defines which layer in Virtualization charts metric appears. Only required for those metrics that will be displayed in these charts. 
Valid Values:  
DATABASE, VIRTUAL_MACHINE_OS,HOST,STORAGE

Null

Display in Category Layer Chart

True if metric should be displayed in Virtualization Layer chart for its category

False

Display in Summary Layer Chart

True if metric should be displayed in Virtualization Summary Layer chart

False

Value Axis Threshold

Value (integer) that indicates maximum value of numeric chart axis. Only honored if maximum data value in chart does not exceed this value.

0 (indicates not used)

Summary Type

Indicates summary type to use when charting metric data from summary tables (not detail table).
Valid Values: sum, avg, min, max

avg

Heartbeat

Value in seconds used to separate data points in chart (indicates missing data)

N/A: Required

Thresholds

Warning/Critical Min/Max values

Null

Threshold Setting Allowed

Set to false to indicate that thresholds cannot be set for metric

True

Min Y

Not used

 

Max Y

Not used

 

A portion of a metrics properties XML file is displayed below:

metric_properties.png

Metric Queries

Properties that define how a metric is collected is stored in one file for each metric type (default, custom, user-defined). For those files, the naming convention is <Metric Type>-metrics-queries.xml (where metric type is either 'default', 'custom' or 'userdefined').

These files contain the following properties:

Property

Description

Default

Class Name

Name of class that implements metric collection (see section on software components)

N/A: required

Target

Indicates the target data source for the metric query.
Valid Values: MONITORED_DATABASE, REPOSITORY

MONITORED_DATABASE

Name

Internal name of metric (links metric to metadata properties)

N/A: required unless Metrics Names property is set

Metrics Query Data Provider The data provider class where the query gets run, used in Row Based Query Empty unless the query data comes from a metrics data provider class and not the metric query class

Metrics List

Names of those metrics for which the query collects data

Empty if query applies to only one metric, or is using a Row Based Metric

Row Based Metrics List Names of metrics whose data comes from one query Empty if not using a row based query

SQL

Query used to collect the metric data (may collect data for more than one metric)

N/A: required

Frequency

Frequency in seconds at which collection is run

N/A: required

Start Delay

Time in seconds to delay metric collection after monitor is started

0

Query Timeout

Time in seconds query is allowed to run (after which a query timeout error is issued)

N/A: required

Supported Databases

List of database types and, optionally, versions of each type that are supported by the metric query

Null indicating all databases types are supported

 

A portion of a metrics properties XML file is displayed below:

 

metrics_queries.png

Repository Storage

The aforementioned metric properties related to metadata and collection are only stored in the XML files and held in memory while DPA is running. The following data is stored in Repository:

  • Metric IDs
  • Metric Data
  • Threshold Overrides
  • Disabled States

ID Tables

Each metric collected for a given monitored database instance is given an ID that is specific to the database. The same metric will most likely have different IDs for different databases.

The CON_METRICS_NAMES_<Database ID> table contains IDs for both metric names (internal names assigned to a metric) and metric branches. Metric branches are used to distinguish default, custom and user-defined metrics (custom and user-defined metrics are explained later). Each metric is identified by either its ID or its name/branch combination.

CON_METRICS_ NAMES_XXX Table

Column Name

Description

ID

ID of the metric or branch name

NAME

Metric or branch name

 

The CON_METRICS_<Database ID> table contains the ID of a metric for a given monitored database instance. It maps this ID to the metric name ID and branch ID.

CON_METRICS_ XXX Table

ColumnName

Description

ID

ID of the metric for the given monitored database instance

QUERY_ID

Not used (always -1)

METRIC_NAME_ID

ID of the metric's internal name (from CON_METRICS_NAMES_XXX table)

BRANCH_NAME_ID

ID of the metric's branch name (from CON_METRICS_NAMES_XXX table)

 

These tables are populated on demand when data is first collected for a given metric from a monitored database instance. To determine a metric's ID from its name (branch is not required as internal metrics names are unique across metrics), the following query can be run:

Select ID from CON_METRICS_<DbId> where METRIC_NAME_ID =

      (select ID from CON_METRICS_NAMES_<DbId> where NAME='<Metric Name>')

 

Data Tables

Metric data is stored in four tables for each monitored database instance. One table holds the data as it is collected (termed detail data). The other three contain the metric data summarized to ten minute, one hour and one day periods.

CON_METRICS_DETAIL_XXX Table

Column Name

Description

METRICS_ID

ID of the metric (from CON_METRICS_XXX table)

D

Timestamp of the metric data value

V

Metric data value

 Summary Tables

Table Name

Description

CON_METRICS_TEN_MINUTE_XXX

Metric data aggregated into ten minute intervals

CON_METRICS_HOUR_XXX

Metric data aggregated into one hour intervals

CON_METRICS_DAY_XXX

Metric data aggregated into one day intervals

 Summary Table Definitions

Column Name

Description

METRICS_ID

ID of the metric (from CON_METRICS_XXX table)

D

Timestamp at start of interval

V_SUM

Sum of metric data values over given interval

V_AVG

Average of metric data values over given interval

V_MIN

Minimum metric data value in given interval

V_MAX

Maximum metric data value in given interval

 

Threshold Override Table

The out-of-the-box (default) thresholds for metrics can be overridden or removed through a configuration page. These overrides are stored in the table CON_METRICS_THRESHOLDS:

 

Column Name

Description

DB_ID

ID of the entity to which the override applies (may be database ID, virtual machine ID or ESX host ID - see ENTITY_TYPE column). A value of -1 indicates that this override applies to all entities.

ENTITY_TYPE

Indicates the entity type to which this override applies (how to resolve the DB_ID column). Valid values are 'DB', 'VM' and 'HOST'

METRIC_NAME

Metric name

BRANCH_NAME

Metric branch name

THRESHOLD_TYPE

'Warning' or 'Critical' or 'None' (None indicates thresholds have been removed by this override)

MIN_VALUE

Minimum threshold value or null if threshold has no lower limit or null if thresholds were removed by this override

MAX_VALUE

Maximum threshold value or null if threshold has no upper limit or null if thresholds were removed by this override

 

Disabled State Table

Metric collection can be disabled explicitly by the user or as the result of too many errors incurred during collection  The disabled state of a metric is stored in the table CON_METRICS_DISABLED. Only metrics that are disabled are stored in this table.

 

(condprm METRICS_EXCEPTION_LIMIT default is 10).

 

Column Name

Description

DB_ID

ID of the entity to which the disabled state applies (may be database ID, virtual machine ID or ESX host ID - see ENTITY_TYPE column). A value of -1 indicates that the metric is disabled for all entities.

ENTITY_TYPE

Indicates the entity type to which this disabled state applies (how to resolve the DB_ID column). Valid values are 'DB', 'VM' and 'HOST'

METRIC_NAME

Metric name

BRANCH_NAME

Metric branch name

DISABLED_FLAG

Flag to indicate how metric was disabled (0=Explicitly, 1=By Error)

DISABLED_REASON

String indicating why metric was disabled

Custom Metrics

In addition to the default metrics provided by DPA, there is an option for users to add custom metrics. Custom metrics allow a user to enter any SQL of their choosing to be run against the monitored database instance, thus allowing them to collect metrics not provided by DPA. Initially, DPA did not provide a user interface through which custom metrics could be created. Users would have to create their own XML files, adhering to the structure defined for the default metrics. These files are prefixed with the string 'custom' (e.g., custom-metrics-properties_Oracle.xml and custom-metrics-queries.xml) and stored in the directory:

 

                [IGNITE_HOME]/Ignite PI/iwc/tomcat/ignite_config/idc/metrics

 

These files are read at start up, and the metrics defined in them are handled no differently than the default metrics in terms of collection and display. The creation of these files are usually handled through the Sales Engineering team to ensure that they are formatted correctly.

User-Defined Custom Metrics

An option is now provided that allows users to create custom metrics through a user interface in the product. Through this feature, the user can enter in a SQL statement that will be run against the monitored database instance and will provide metric data values as a result.

 

The following properties that can be set for custom metrics:

 

  • Enabled State
  • Database Type (only one allowed per custom metric, immutable after metric creation)
  • Database Version Limitations (optional)
  • Display Name
  • Description
  • Category (allows creation of new categories)
  • Units
  • Is Percent Metric
  • Thresholds
  • Type (see below)
  • Collection Frequency
  • Collection Query Timeout
  • SQL Executed for Data Collection

 

The four types of custom metrics are:

 

  • Single Value - Value returned from the query is the metric data value
  • Delta - Metric data value is the difference between values of two subsequent executions
  • Rate - Metric data value is the difference between values of two subsequent executions divided by the time in seconds between the two executions
  • Timed Query - Metric Data Value is the time in milliseconds it takes to execute the query

 

When a user creates a new custom metric or edits an existing one, two files are updated (or created if necessary). One file is a properties file for the database type to which the metric applies; the second is the query file for these user-defined metrics. These files are prefixed with the string 'userdefined' (e.g., userdefined -metrics-properties_Oracle.xml and userdefined -metrics-queries.xml) and stored in the directory:

 

                [DPA Home]/iwc/tomcat/ignite_config/idc/metrics/userdefined

 

The only additional properties that is added to the properties XML file for user-defined custom metrics is 'enabled' (as these metric types can be disabled globally ) and 'customMetricTypeName' that defines the metric type. Otherwise, the files look the same as those that are shipped with DPA.

 

These files are read at start up, and the metrics defined in them are handled no differently than the default metrics in terms of collection and display. When user-defined custom metrics are created,  updated or deleted, the system will handle the changes as needed.

Wait Time Metrics

Wait time metrics derive their values from wait time data stored in the DPA Repository, as opposed to other resource metrics that collect their data from the monitored database instance. There are two types of wait time metrics. One is a default (out-of-the-box) metric that collects the total wait time for the instance. The second is a custom metric that collects the wait time for an individual wait event/type. The wait event/type is specified by the user at the time the custom metric is created.

 

All wait time metrics have the following characteristics:

 

  • Data is queried from the DPA Repository and not the monitored instance
  • Thresholds are not allowed
  • Assigned to the Waits category
  • Do not appear in virtualization layer charts
  • Collected in units of seconds (from CONSW_XXX tables) but are scaled in metric charts based upon the maximum data value (i.e., can be scaled to minutes, hours or days)
  • Use SUM summary type (as opposed to average which is used for most others)
  • Collection frequency of five minutes with a five minute collection delay after monitor startup (or after custom metric creation/update)

 

Wait time metrics behave the same as other metrics in regards to persistence and display.

Custom Wait Time Metrics

Custom wait time metrics are metrics that collect wait time for an individual wait event/type for a specific database type. Custom wait time metrics are created, updated and deleted the same as custom resource metrics with the following exceptions:

 

  • Category cannot be specified (fixed as 'Waits')
  • Units cannot be specified (fixed as seconds with ability to scale)
  • Thresholds cannot be set
  • Frequency and timeout cannot be set
  • SQL cannot be specified
  • Wait event/type must be specified (search capability provided, no validation performed)

 

Upon creation of a custom wait time metric, the properties are written to the appropriate property XML files. The wait event/type selected is written to the metadata properties file (e.g., userdefined-metrics-properties_Oracle.xml) along with all the other standard properties.

 

Once a custom wait time metric is created, its database type and wait event/type cannot be modified. To change either of these properties, a new custom wait time metric must be created.

If a wait event/type is specified that does not exist or is never seen through DPA monitoring, the metric will always return a value of 0 (will not error as wait event/type names are not validated or verified).

Custom Charts

Chart properties for all metrics are stored in the XML file located in:

 

[DPA HOME]/iwc/tomcat/webapps/iwc/WEB-INF/classes/resources/metrics/charts/ metric-chart-attributes.xml

 

In this file we find the attributes that determine the type of graph we see in DPA. You can see the styles I have here are items like:

  • linesAndMarkers
  • stackedBars
  • nonStackedBars
  • linesOnly
  • markersOnly
  • shadedArea

 

metric_charts.jpg

 

If I build a custom metric and want to change the chart I can in this file. Example on this is we first need to know the system name of the metric. 

 

If you go into the XML where the Custom metrics are kept there is a name that is system generated that we need to know no change the graph. 
That is in this file [DPA Home]/Ignite PI/iwc/tomcat/ignite_config/idc/metrics/userdefined/userdefined-metrics-properties_SQLServer.xml

 

It also shows in the GUI when you make the metric in the screen shot.

2015-11-16_13-47-01.jpg

[DPA Home]/iwc/tomcat/webapps/iwc/WEB-INF/classes/resources/metrics/charts/ metric-chart-attributes.xml

 

Add a line for the Metric to the correct section.

 

<!-- Database CPU -->

<metricChartAttributes metricName="CPU Queue Length" color="blueGradient" style="stackedBars"/>

<metricChartAttributes metricName="CPU Utilization By Database" color="greenGradient" style="shadedArea"/>

<metricChartAttributes metricName="CPU Utilization By Oracle" color="greenGradient" style="shadedArea"/>

<metricChartAttributes metricName="CPU Utilization" color="greenGradient" style="shadedArea"/>

<metricChartAttributesmetricName="CustomMetric-22" color="redGradient" style="shadedArea"/>

 

To get the chart change we have to clear the chart cache and restart the service for DPA. 

 

Last modified

Tags

Classifications

Public