Applications Systems

DPA Database Metrics Architecture

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 but is in a separate document.

First published date

11/29/2018 10:23 PM

Last published date

7/31/2019 8:03 PM

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.

Product section

Database Performance Analyzer

Resolution

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 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:

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.

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:

[DPA_HOME]/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

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.

[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.

<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.

Disclaimer: Please note, any content posted herein is provided as a suggestion or recommendation to you for your internal use. This is not part of the SolarWinds software or documentation that you purchased from SolarWinds, and the information set forth herein may come from third parties. Your organization should internally review and assess to what extent, if any, such custom scripts or recommendations will be incorporated into your environment. You elect to use third-party content at your own risk, and you will be solely responsible for the incorporation of the same if any.