Flow Statistics gives "No Matching Records Found" error for Harvester with High Traffic

Products

• CA Network Flow Analysis (NetQos / NFA)

Issue/Introduction

We are using the Flow Statistics page in the NFA Administration GUI. Verified that port 8067 is open in both directions between the NFA Console machine and the Harvesters.

The graphs at the top of the page are showing data. When a Harvester is selected, a “Loading” indicator appears for over a minute, then a “No Matching records found” message appears.

The current Reporter\Logs\WebServicesQueryLog*.log shows the following timeout error:

11:13:57 -1 - Query:

[email protected]:3308

Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

select id, min(flowRate) as minIFFlows, max(flowRate) as maxIFFlows, avg(flowRate) as avgIFFlows, percentile(flowRate, 95) as pct95IFFlows, name, ip, interfaces from ( select (select count(interfaces.id) from interfaces where interfaces.routerid = routers.id ) as interfaces, routers.ID as id, (sum(interfaceflows.InFlows + interfaceflows.OutFlows)/15) as flowRate, routers.sysName as name, INET_NTOA(routers.routerAddress) as ip from interfaceflows, routers where interfaceflows.endtime >= 1453219500 and interfaceflows.endtime <= 1453305900 and routers.harvesterID = 4 and interfaceflows.AgentID in (select agents_all_view.ID from agents_all_view where agents_all_view.routerid = routers.id) group by routers.id, interfaceflows.endtime ) x group by id

(1 min 30 sec) 

By default, the Time Period for data in the Flow Statistics page is set to “Daily”. By shortening this Time Period, we can reduce the amount of data returned by the MySQL queries and reduce the possibility of Timeout errors. 

Environment

NFA 

Cause

This indicates that the Flow Statistics page is timing out when running a MySQL query of the reporter.flow_stats database table. It is possible for this to occur for just one particular Harvester at a time if that Harvester is more heavily loaded than the other Harvesters.

Resolution

-By default, the Time Period for data in the Flow Statistics page is set to “Daily”. By shortening this Time Period, we can reduce the amount of data returned by the MySQL queries and reduce the possibility of Timeout errors. 

-We can also reduce the likelyhood of a timeout by reducing the amount of data stored in the interfaceflows table in the Reporter database. By default, Enterprise Overview data is stored in the interfaceflows table for 30 days. However, NFA itself has no way of accessing EOV data older than 24 hours. EOV data older than 24 hours can only be accessed via the CAPC. But the amount of data stored in this table can slow down the Flow Statistics page.
 

To correct this you can delete rows from the database to cut this data down to the last weeks worth of data by following the steps below.

1. Make a backup of the reporter.interfaceflows table on the NFA console server: 
   
   mysqldump -P3308 -unetqos -pnetqos reporter interfaceflows > interfaceflows.sql
    
2. Login to the 'reporter' database on the NFA console server with: 
   
   mysql -P3308 -unetqos -pnetqos reporter 
    
3. Then you can delete anything older than 1 week with the query below: 
   
   delete from interfaceflows where endtime < (unix_timestamp() -604800); 
   
   **Note** you can adjust the time frame you are deleting from by replacing 604800 with the number of seconds you want to delete older than. For example you can use 86400 to delete everything older than 24 hours.** 
    
4. Then to prevent this problem from coming back we can update the settings so that it will prune once a week instead of once a month. 
   
   update parameter_descriptions set DefaultValue='1w' where Parameter='flowInfoLimit'; 
   
   **Note** The default is '1n' which is equal to 1 month. You can also use '1d' for one day, or any other time frame that will work in your environment.