Use Performance Logger to analyze Line Blockage performance

Performance Logger is an Application Programming Interface (API) that allows different applications to log events of interest in the Historical database. These events can then be used to conduct a detailed inspection of performance. Each event of interest contains the following details:

• The name of the calling application.

• The timestamp capturing when the event occurred.

• The hostname of the machine processing the event.

• The table that is being processed.

• The record that is being processed.

• The dataset corresponding to the record being processed.

• A short description of the event of interest (for example, Queue Request Received or Execution Started).

• Additional context around the event of interest.

The Line Blockage application uses this utility to help the system administrator understand how Line Blockage is performing. When enabled, the Performance Logger will capture the following events:

• When the valve state gets updated.

• When the Line Blockage application receives the request to perform a calculation.

• When the Line Blockage application starts to process the request.

• When the Critical Path Flag is set.

• When the Line Blockage application is done processing the request.

• When the Action Sequence to stop a pump starts.

• When the Action Sequence to stop a pump completes.

To enable the Performance Logger for Line Blockage, you will need to install the Performance Logger database, manage performance runs, and then inspect the results. The following information expands on the details for each step.

Important: The Performance Logger utility is a diagnostic tool that is intended to be used in test environments only. Do not enable this tool in a production environment.

Step 1: Install the Performance Logger database

Note: The Performance Logger database only needs to be manually added in an upgrade scenario. A clean install will add it automatically.

To install the Performance Logger database, make the following call from the shared silo:

Publish-DataTierApplications.ps1 -ServerName "<historical server name>\OASYSHDB" -Install -Configuration @{ Performance = @() }

Step 2: Enable the Performance Logger Process

Note: The performance logger should only run on the hot RealTime system.

Performance Logger is disabled by default. To launch it manually, make the following call from the RealTime silo:

.\scripts\RealTime\monitorPerformanceLogger.run_processes.csc

After the performance run is completed (as described in step 3), make sure you stop the Performance Logger process. To stop the process, execute the following commands from the RealTime silo:

checkOutProcess.exe -s RealTime -i <processID> -k

The processID is found within the RealTime process list in the Network Management Console. Locate the Process ID associated with a process named PerformanceLogger_X.

Step 3: Manage performance runs

You can manage performance runs by completing the following steps:

1. In the Advanced Database Editor (ADE) Application Row Edit window, create a new application record and set the Name field to PerformanceLogger. The description field can be used to describe the purpose of using the utility.

   

2. In the Values tab, set the String Value to a name of your choice. This is the name that will be associated with the events generated by the same run in the Historical database.

   Note: This name is meant to be a user friendly identifier for each performance run. The system will not validate uniqueness of the name across different runs.

   

3. Save the application record.

4. To start a performance run, edit the newly created PerformanceLogger application record by checking Boolean Value. When you save the application record, a run with a unique Run ID will start. This means that calls to the Performance Logger API will start sending events of interest to the Historical database.

   

5. To stop a performance run, edit the PerformanceLogger application record by unchecking the Boolean Value. When you save the application record, the run will stop.

Step 4: Inspect the results

You can inspect the results of the performance run by querying the Output table in the Performance database. The results can be filtered using runName or context. The Context field stores the Flow Network that the record is associated with.

Alternatively you can use the CalculateElapsedTimeForAllLines Stored procedure to group the results and calculate the total processing times. Then, you can use a spreadsheet application to analyze and visualize the data.

Note: Times are saved using the UTC Time zone.

To analyze and visualize the results of the performance run using Microsoft Excel

6. Using the Performance historical database, execute the CalculateElapsedTimeForAllLines Stored Procedure by running the following query:

   execute CalculateElapsedTimeForAllLines @run = 'RunName'

   Where RunName is the value previously set in ADE. The results include the following data:

   Data	Description
   Run Name	The name of the performance run this result set is associated with.
   Context	The name of the Flow Network line the row is associated with.
   Integrity Update	A value of 0 indicates that the row is associated with a device trigger (pump or valve). A value of 1 indicates that the row is associated with an integrity update trigger (automatically recurring on a set time interval).
   Details	A description of the specific event the row is capturing.
   Trigger Time	The timestamp of when the trigger of the Line Blockage calculation occurred, such as the time of closing a valve.
   Processing End Time	The timestamp of the completion of all steps taken by Line Blockage as a result of the trigger including wait time in the Line Blockage processing queue, updating critical flags on affected valves, and executing Action Sequences to shutdown affected pumps.
   LB Trigger Time	The timestamp of when Line Blockage started to process the request. This does not include waiting time in the queue.
   LB Processing End Time	The timestamp of when Line Blockage finished processing the request. This includes updating critical flags on affected valves. It does not include the time to execute Action Sequences to shutdown pumps.
   Total Processing Delta	The duration between Trigger Time and Processing End Time.
   LB Processing Delta	The duration between LB Trigger Time and LB Processing End Time.
   Trigger-to-Critical-Flag Update Delta	The duration between Trigger Time and LB Processing End Time.

7. On the Results tab, right-click on any row and Select All.

8. With all rows highlighted, right-click on any row and select Copy with Headers to copy the result set.

   

9. Paste the result set into a new spreadsheet in Microsoft Excel.

10. Click and drag to select all four time columns (Trigger Time, Processing End Time, LB Trigger Time, and LB Processing End Time), and then right-click and select Format Cells, then Select Number, then Custom and use hh:mm:ss.000 as the type.

    

    This improves the legibility of the time columns.

    

To pivot the table

11. Click Insert > Pivot Table.

    

12. Select all data in the spreadsheet, and click OK.

13. Click the checkboxes to add the following fields to the pivot table: Context, Integrity Update, Trigger Time, and Total Processing Delta.

    Note: Depending on the purpose of your analysis, you can select Total Processing Delta, LB Processing Delta, or Trigger-to-Critical-Flag Update Delta.

    

14. Clear the Seconds, Minutes, and Hours checkboxes.

    

15. Move the following fields to the indicated groups:

    • Context field to the Columns group

    • Trigger Time field to the Rows group

    • Integrity Update field to Filters

    • Total Processing Delta to the Values group

16. Under the Values group, verify that Total Processing Delta is configured to Sum of Total Processing Delta. If it is showing as Count of Total Processing Delta, click on it and select Value Field Settings. Change Count to Sum and click OK.

17. Ensure that your pivot table settings look like the screenshot example below.

    

18. If Excel happens to group the Trigger Time, right-click on any row and click Ungroup.

    

19. Insert a Line Chart by selecting Insert > Insert Line or Area Chart and choose the desired line chart.

    

20. From the Chart Design tab, click Select Data.

    

21. Select your pivot table (Timestamp and Line columns only).

    

22. Click Hidden and Empty Cells, and then select Connect data points with line and click OK.

    

23. Click OK to close the Select Data Source prompt.

24. From the Design tab, add chart elements (such as axis labels, title, and legend). An example chart looks like this:

    

    In this chart, the x-axis shows the Trigger Time (as defined above, in UTC). And the y-axis shows the Total Processing Time (as defined above, in milliseconds).

    Note: By default, the graph will display both Trigger updates (valve or pump) and Integrity Updates automatically recurring on a set time interval. The Integrity Update Filter can be used to separate the two in order to facilitate analysis (0 refers to a Trigger Update and 1 refers to an Integrity Update).

    

    An example of a chart showing Trigger Updates only is as follows:

    

    An example of a chart showing Integrity Updates only is as follows:

    

25. If you would like to generate a similar chart showing Line Blockage processing time only, the pivot table should be built with LB Trigger Time and LB Processing Delta instead.

    

    An example of LB Trigger Time and LB Processing Delta are displayed as follows:

    

26. If you would like to inspect the details of a particular data point on the chart, return to the Performance database and query the Output table.