Querying the Metrics Database
Introduction
The metrics database uses the TimescaleDB extension to efficiently manage time series data from Paragon Active Assurance, providing additional functions for data aggregation and rollup.
In this chapter we will describe the database objects and fields of the metrics database and their usage within an SQL query.
For full coverage of TimescaleDB features, go to docs.timescale.com.
Database Objects to Query
Each plugin in Paragon Active Assurance has a number of database views which the user can query:
- one view for tests
- five views for monitors with metrics aggregated at different granularities: 10 seconds (raw), 1 minute, 5 minutes, 30 minutes, and 60 minutes.
The naming convention for the view objects is the following:
vw_(monitor|test)_metrics_<plugin_name>for raw metrics datavw_monitor_metrics_minute<minutes>_<plugin_name>for metrics aggregated over time intervals of size<minutes>for the purpose of monitoring.
For example, the HTTP plugin has the following database objects that can be queried:
vw_test_metrics_http vw_monitor_metrics_http vw_monitor_metrics_minute1_http vw_monitor_metrics_minute5_http vw_monitor_metrics_minute30_http vw_monitor_metrics_minute60_http
Each view contains two categories of information:
-
measurement: This is metadata such as the name of the test or monitor and the Test Agent(s) used. It is needed to retrieve the information related to a task in a test or monitor, and it should be included in the WHERE clause of an SQL statement to narrow down the search. Examples of fields in this category are:
- Common to monitoring and testing:
account_short_name,task_name,stream_name,plugin_name - Specific to testing:
test_name,test_step_name,periodic_test_name - Specific to monitoring:
monitor_name,task_started,monitor_tags
monitor_name, task_name, stream_name, test_name, test_agent_name
- Common to monitoring and testing:
- metric: This is data collected in the network at a given point in time, such as throughput, delay, and jitter.
The time column is common to all views and must be included in each query.
However, the set of metrics returned depends on the task type: for example, an HTTP
measurement will have a different set of metrics from a Ping measurement.
The columns holding specific measurements differ from one task to another, and they are usually part of the metrics table in the view definition.
For example, the vw_monitor_metrics_http view contains:
connect_time_avg, first_byte_time_avg, response_time_min, response_time_avg, response_time_max, size_avg, speed_avg, es_timeout, es_response, es
Running Queries in TimescaleDB
Depending on the time interval of interest, it is possible to run queries on metrics using either
- the predefined views available in TimescaleDB directly, or
- the predefined views as a data source for metrics aggregation on custom time intervals.
Queries on Predefined Views
These queries are recommended if the data granularity of interest is already present in TimescaleDB.
For example, if the HTTP plugin is enabled in Control Center, the following query
SELECT -- time field "time", -- metric response_time_avg as "avg_response_time" FROM -- data source (5 minute rollups) vw_monitor_metrics_minute5_http WHERE -- time interval to analyze "time" between '2021-06-21 09:55:30+00' AND '2021-06-21 09:57:00+00' AND -- constraints on metadata stream_name = 'Stream 1';
returns the value of the response_time_avg metric for a 5-minute
interval detected between 2021-06-21 09:55:30+00 and 2021-06-21 09:57:00+00 by the stream
named Stream 1.
Queries with Custom Time Interval
To run aggregate functions on metrics based on time intervals, TimescaleDB introduced the concept of time buckets to customize the granularity of the measurements given an underlying data set.
The function time_bucket accepts arbitrary time intervals as well as
optional offsets and returns the bucket start time.
A user can apply aggregate functions on metrics grouped by the output from
time_bucket to define metrics granularity dynamically, if the time
interval of interest is not available in the TimescaleDB database.
For example, if the HTTP plugin is enabled in Control Center, the query
SELECT
-- time field
time_bucket('30s',"time") as "time",
-- metric
percentile_cont(0.5) WITHIN GROUP (ORDER BY response_time_avg) as "avg_response_time"
FROM
-- data source (raw data)
vw_monitor_metrics_http
WHERE
-- time interval to analyze
"time" between '2021-06-21 09:55:30+00' AND '2021-06-21 09:57:00+00'
AND
-- constraints on metadata
stream_name = 'Stream 1'
GROUP BY "time"
ORDER BY "time";returns the median value of the response_time_avg metric within
30-second intervals between 2021-06-21 09:55:30+00 and 2021-06-21 09:57:00+00 by the
stream named Stream 1.
For better query performance, it is recommended that you use as data source the view with the highest possible level of granularity that guarantees acceptable data accuracy.
Further information on TimescaleDB features for data analysis can be found in the TimescaleDB documentation: docs.timescale.com/timescaledb/latest/getting-started/query-data/
Configuring Data Retention Periods
You can change the data retention periods for raw data and rollups in the file
/etc/netrounds/metrics.yaml. The relevant properties are laid out in the
table below:
| Property name | Retention period for | Default retention period |
retain-duration-raw |
10-second interval | 72.0h (3 days) |
retain-duration-1min |
1-minute rollups | 168.0h (7 days) |
retain-duration-5min |
5-minute rollups | 720.0h (30 days) |
retain-duration-30min |
30-minute rollups | 4320.0h (180 days) |
retain-duration-hour |
60-minute rollups | 8760.0h (365 days) |
In addition, there is a property cleanup-poll-duration which governs how
soon any changes to the retention periods will take effect. If you set a retention period
lower than cleanup-poll-duration, the change will come into effect only
after cleanup-poll-duration. In such a situation, it is best to lower
cleanup-poll-duration as well.
Durations must be specified as decimal numbers with one of the following unit suffixes: ns, us, ms, s, m, h, as in the above table. (There is no "day" suffix.)