Information about NV polling

Products

• CA Application Delivery Analysis MTP (NetQoS / ADA)
• CA NetVoyant (NetQoS / NV)

Issue/Introduction

Description:

NetVoyant Polling

This document describes the flow of polling data through the database. Due to many issues with MySQL locking and performance, the database tables used to process, store, and purge data have become very complex.

Solution:

Polling
The first table involved with storing poll data is the termed the "poll" table. The name of this table in the database will be the name of the dataset concatenated with the string "_poll". [Note that it is possible to have different table names by manually setting up the table names in the dataset but in actual practice, this is rarely done.]

For example, the poll table name for the ifstats dataset is "ifstats_poll".

The poll table contains the results of reading the raw OIDs from the device for a particular dataset. Without going into detail, the system pre-determines the OIDs required by scanning the expressions defined for a dataset. Expressions are defined in terms of OIDs from a MIB table or from other expressions.

Data polled from the devices and stored in the poll table does not remain there for very long. The poll data is needed only long enough to calculate deltas (if the OID is a counter value) and calculate the expressions. Normally, the old poll data is deleted from this table as soon as new poll rate data is collected and the deltas/expressions generated. The one exception to this is when special (debug) code is installed on the system in order to hang on to the poll records. We sometimes do this to troubleshoot or diagnose a problem with the device's agent. In this case, a special field in the poll table called "poll_type" is set to the value 1 to denote that this record has been processed.

One other common issue of note is the definition of the "tstamp" field. For the poll tables, this represents the UTC when the poll data was received and stored in the database. When the deltas/expressions are calculated, the tstamp field represents the start interval of the data. For example, consider the two poll records for the OID ifInOctets:

     Poll     Value            Tstamp
     1        3098876          1166535909  (December 18, 2006 07:45:09)
     2        3186091          1166536812  (December 18, 2006 08:00:12)

This would produce a delta value of 87215 with a tstamp of 1166535900 (December 18, 2006 07:45:00). Note that the tstamp is rounded to the poll interval. Expressions calculated from this data would also have the same tstamp (1166535900).

Poll Rate Data

The first expressions calculated from the poll data are termed "Poll Rate". This is because the values of the expressions represent data collected at the polling rate - there are no averages or rollups at this point. The poll rate data is stored in a table named from the dataset name concatenated with the string "_rate", such as "ifstats_rate". However, this is where things get a bit more complicated.

In order to purge the old poll rate data (typically after 1 week), the system uses a feature of MySQL called partitioned tables. In actual form, the rate table is partitioned into several tables, one for each day of retention. For example, if the retention time for interface data was 7 days, there would be one partition table for each day and one for the current day. At the end of the day, the oldest partition table is removed (data purged) per the data retention rates specified in NetVoyant.

If you list the rate tables in the database, you would see something like:

    mysql> show tables like 'ifstats_rate%';
     +---------------------------------------------+
     | Tables_in_nms2 (ifstats_rate%)  |
     +---------------------------------------------+
     | ifstats_rate                                      |
     | ifstats_rate_p1165986000          |
     | ifstats_rate_p1166072400          |
     | ifstats_rate_p1166158800          |
     | ifstats_rate_p1166245200          |
     | ifstats_rate_p1166331600          |
     | ifstats_rate_p1166418000          |
     | ifstats_rate_p1166504400          |
     | ifstats_rate_tmp                             |
     +---------------------------------------------+

In the example above, the tables with the "_p<time stamp>" postfix represent the actual data files. The table ifstats_rate represents a merge of all the data tables and is used by the NetVoyant reporting system. This makes the partitioning of the data tables transparent to the reporting system - all data is accessed from the ifstats_rate table for poll rate data.

The ifstats_rate_tmp table is created as a template for future data tables. It will always be empty.

Looking at the range of data in the table ifstats_rate_p1166072400 you would see:

     mysql> select min(tstamp),max(tstamp) from ifstats_rate_p1166072400;
     +--------------------+-------------------+
     | min(tstamp)   | max(tstamp)  |
     +--------------------+-------------------+
     |  1166076000 |  1166162100 |
     +--------------------+-------------------+

The partition has the time stamp of 1166072400 (Dec 13, 2006 23:00:00) which represents the data for Dec 14. The range of the data records is:

1166076000 - Dec 14, 2006 00:00:00

Thru

1166162100 - Dec 14, 2006 23:55:55

Note that the last partition table, ifstats_rate_p1166504400, contains poll rate expressions for the current day:

     mysql> select min(tstamp),max(tstamp) from ifstats_rate_p1166504400;
     +--------------------+-------------------+
     | min(tstamp)   | max(tstamp)  |
     +--------------------+-------------------+
     |  1166508000 |  1166539500 |
     +--------------------+-------------------+

At midnight, the "repartitioning" occurs. For the rate data, the following is performed:

1. A new partition for the next day is created.
2. The number of partitions to retain is re-calculated (the user may have changed the retention interval of the dataset.
3. The merge table (ifstats_rate in this example) is altered to contain the new set of partition tables
4. The old partition tables that are no longer part of the merge are dropped.
5. An "analyze table" is preformed on each of the remaining partition tables (except for the new one). This keeps the table statistics up to date for future queries.

The merge definition of the rate table (ifstats_rate in this example) contains the definition:

INSERT_METHOD = LAST

Therefore, the delta process is always inserting new expression data into the newest partition (i.e. - for the current day).

Short-Term Rollup Data

Short-term rollup data is defined as any rollup data that has a rollup interval of less than a day. In nearly all installations, this is hourly rollup data. The short-term rollup data is calculated from the poll rate data and calculates the sum, average, minimum, or maximum (based on the expression) of the poll rate data over the rollup interval (again, typically an hour).

The short-term rollups are stored in a table named from the dataset name concatenated with the string "_ltd", such as "ifstats_ltd".

The "ltd" tables use a similar partitioning scheme as the "rate" tables. In this case, however, each partition table represents 30 days of data.

If you list the _ltd tables from the database for a particular dataset, you would see:

     mysql> show tables like 'ifstats_ltd%';
     +-------------------------------------------+
     | Tables_in_nms2 (ifstats_ltd%) |
     +-------------------------------------------+
     | ifstats_ltd                                      |
     | ifstats_ltd_current                       |
     | ifstats_ltd_p1163826000          |
     | ifstats_ltd_p1166418000          |
     | ifstats_ltd_tmp                             |
     +-------------------------------------------+

The partition tables (those ending in a time stamp), the _ltd table, and the _tmp table have the same function as described above in the rate tables. Each night the partitions are examined to determine if a new partition should be added and another removed. This process is identical to the process detailed above for the rate tables.

One additional table found at the _ltd rate is the "current" table. Since many of the NetVoyant views report on the "last" value, the current table contains the last rollup calculated for each poll instance in that dataset. This makes the reporting of current values very fast.

Daily Rollup Data

Daily rollup data is generated from the poll rate data (prior to NetVoyant 5.x, daily rollups were calculated from short-term rollups). The poll rate data is used to support percentile calculations at the daily interval.

The daily rollups are stored in a table named from the dataset name concatenated with the string "_eqd", such as "ifstats_eqd".

The "_eqd" tables do not use a partitioning scheme for data storage. All data is stored in the native _eqd table.

If you list the _eqd tables from the database for a particular dataset, you would see:

     mysql> show tables like 'ifstats_eqd%';
     +--------------------------------------------+
     | Tables_in_nms2 (ifstats_eqd%) |
     +--------------------------------------------+
     | ifstats_eqd                                     |
     | ifstats_eqd_current                      |
     +--------------------------------------------+

As you can see, the _eqd tables also support a current table. For the daily rates, this table contains the last 24 hour rollup (a common reporting view) for each object in the dataset. Each hour this value is updated.

Long-Term Rollup Data

Long term rollup data consists of weekly, monthly and quarterly rollups and is calculated from the daily rollup records. The long term data is stored in a table named from the dataset name concatenated with the string "_gtd", such as "ifstats_gtd".

The "_gtd" tables do not use a partitioning scheme for data storage. All data is stored in the native "_gtd" table.

If you list the "_gtd" table from the database for a particular dataset, you would see:

     mysql> show tables like 'ifstats_gtd%';
     +-------------------------------------------+
     | Tables_in_nms2 (ifstats_gtd%) |
     +-------------------------------------------+
     | ifstats_gtd                                     |
     | ifstats_gtd_current                      |
     +-------------------------------------------+

As you can see, the _gtd tables also support a current table. For these long term rates, this table contains the last 7 day, last 30 day, and last 30 day rollup record for each poll instance in the database.

Baseline Data

Prior to 5.x, all baseline data for all datasets was stored in a single table (baseline_metrics). Due to the large number of poll instance that will be supported in 5.x, the baseline data has been split into separate tables for each dataset. The baseline data for a dataset is stored in a table naed by using the dataset name concatenated with the string "_metrics", such as "ifstats_metrics".

New for 5.x are several new baseline metrics:

• Percentiles - There are three percentiles calculated for each expression. Initially these percentiles are 90, 95, and 99, however the user can modify these values to any desired percentile value.
• 30/60/90 Projections - These three metrics are calculated for each expression.

Environment

Release:
Component: APMAGT