Jerome Lefebvre

Monitoring Event Frame growth

Blog Post created by Jerome Lefebvre Employee on Aug 16, 2019

What makes Event Frames great is that they are an extremely rich bookmarking feature for your real time data.They can be templatized, have a security model, store computed results, etc. Event frames are stored in the PIFD database, that underlines our AF database, due to this large feature set, they also take up a lot of space in this AF database. This means, that it is possible to run into scaling problems when using event frames.

Whenever you have a scaling problem, you typically want to do the following thing:

1. Size appropriately

2. Monitor

3. Limit growth

4. Offloading

5. Retain only what is needed

 

In this blog post, I want to talk about 2. Monitor.

 

How to do this manually

Within PI System Explorer (PSE), under the property page for a database, you can view a count of the set of object contained in that database. In particular event frames.

 

 

As event frames of the same AF server are all stored in the same SQL database, a possible better number to monitor is the global number of event frames, which is found in the counts for the PI AF Server.

 

It is also possible to get this information using AF SDK, using the GetObjectCount method:  https://techsupport.osisoft.com/Documentation/PI-AF-SDK/html/M_OSIsoft_AF_PISystem_GetObjectCounts.htm 

 

Snippet:

var af = new PISystems()[afServerName];
var counts = af.GetObjectCounts(null);
Console.WriteLine($"Total number of event frame: {counts[AFIdentity.EventFrame]}");

 

Obtain the same information using an SQL Query

 

As Event Frames are stored in an SQL database, one could hope that there is a table that contains only event frames and the number of rows should be related to the number of event frames.

And that is indeed the case.

 

Here is the query:

SELECT
t.NAME AS [Table Name],
p.rows AS [Row Counts]
FROM
sys.tables t
INNER JOIN
sys.partitions p
ON t.OBJECT_ID = p.OBJECT_ID
where t.name Like 'AFEventFrame'
GROUP BY
t.Name,
p.Rows s

 

Monitor and historize this metric

To retrieve a large amount of data out of an SQL databse, the PI Interface for RDBMS https://livelibrary.osisoft.com/LiveLibrary/web/ui.xql?action=html&resource=publist_home.html&pub_category=PI-Interface-for-Relational-Database-(RDBMS-via-ODBC) is off the course the way to go.

But, to historize only a few values at a slow rate, we can combine AF table look up feature and the analysis service.

First, I would create a connection to PIFD using a linked table.

This allows us to retrieve this information via a table data reference

 

We can then historize this data using af periodic AF analysis, to store the data into a tag.

 

This now helps you monitor Event Frame growth.

 

Off course, this number is kind of useless unless you actually have an idea for how many event frames you actual want to keep in your PI System.This is typically done to an initial sizing, base on your system spec, querying needs, etc. And refined base on your actual experience. But, this is a topic for an other time.

Outcomes