When viewing a report in NFA I receive an error that states: "A Too many connections error was returned" and no data is visible.

Document ID : KB000006086
Last Modified Date : 14/02/2018
Show Technical Document Details
Issue:

When viewing a report in NFA I receive an error that states:"A Too many connections error was returned"

tooManyConnections.png

 

 

This indicates that the Netqos NQMysql service which runs on port 3307 on your Harvesters has hit the default limit of 250 connections and will not allow anymore connections to be made until some of the existing connections are dropped.

Environment:
Any version of NFA, usually with Anomaly Detector installed.
Cause:

Usually the cause will stem back to Anomaly Detector being installed and running in the environment.  Anomaly Detector runs queries against each Harvester every 15 minutes.  In busy environments, those queries can take a long time to run or hang.  When the next set of queries run 15 minutes later, then create more connections on top of the already open connections and you can eventually run out of connections.

 

To determine which queries are stuck that are causing this problem, please run the command below locally on the Harvesters with the problem to generate a list of queries that are holding a connection open against this instance of mysql.

mysql -P3307 -t -e "show full processlist;" > MysqlProcesslist.txt

 

 

Resolution:

1. The quickest way to free up connections would be to reboot the Harvester, this will free up all connections and allow you to run reports again.  You can also try recycling the "Netqos NQMysql" Service.

2. It is important to see what was causing the connections to fill up by examining the MysqlProcesslist.txt created from the command above in order to prevent this from happening again.  When looking at the file if you see connections that have been open for more then a few seconds with a user and db of 'nsas', these connections are likely coming from Anomaly Detector queries.  

If you are not actually using Anomaly Detector data we would recommend disabling Anomaly Detector and it should prevent the problem from coming back.

3. If you do need Anomaly Detector running, it may be best to limit the number of active sensors at any given time.

Also make sure that any Anti-virus software excludes both the \CA\NFA directory and the C:\Windows\Temp directory.

You can also reduce the frequency in which Anomaly Detector runs these queries by following the steps in this document TEC1825431.

4. You can also try moving the Netqos NQMysql Temp directory to the same drive as the application so that the temp tables do not fill up the C: drive when large queries are run, and the temp tables won't be interfered with by anti-virus software.   See: How do I change the MySql tmp directory in NFA to a different directory?

Moving the Netqos NQMysql temp directory can also have positive benefits when running Flow Forensic reports which can generate very large temp files.

5. If you are running Symantec Endpoint on your Harvesters it could also cause Anomaly Detector queries to be hung up, see: Symantec Endpoint can cause major performance issues with CA Network Flow Analysis. 

6. You should also ensure that your Harvesters are not overloaded and meet the current System Recommendations and Requirements.  Anomaly Detector is very resource intensive and works best on low volume Harvesters.