NTA Analysis running slow

Hi All,

We are running NTA 4.2.1 and NPM 12.0.1 to gather flow data for customers connecting to our systems. The sales team sometimes use NTA to run reports to see the detail of data used by source IP address for a given time period. They do this by logging into the NTA summary screen, entering the customers IP address in the Top Right Hand corner and selecting Endpoint IP address. and then changing the time period, typically they will be selecting all of the previous month. These reports can run for hours (or until the browser times out). I'm seeing lots of long running query events in the orionweb.log (as seen below). We have a dedicated SQL 2016 server for the DB, a dedicated Flow DB Server and a dedicated NPM server which is running the web interface, puller etc. None of the servers seem to be under significant load. Is there something I can do to optimise these queries as it becoming unusable. I accept we may be going about this the wrong way. All they want to be able to do is put in a client IP address and get a report for a given time period that shows source and destination (inbound and outbound) to the customers IP address broken down by traffic type. Is there a better way to do this? Any help or advise much appreciated.

2016-12-15 15:37:42,617 [18] (2) WARN SolarWinds.InformationService.Contract2.InfoServiceProxy - (null) Support! -- LONG RUNNING QUERY: 85295.4193 ms: SELECT TotalBytes

FROM Orion.NetFlow.TransferredDataTop(Filter='NSF:E:***.**.**.***;I:1;TD:2016-12-01T00:00:00~2016-12-15T23:45:00,360,True,False;FD:Ingress', Limit=0, Rx=False, Tx=True, TransferredDataType='Bytes', ReturnAsTransferred=False, TopKey='' ) RETURN XML RAW

2016-12-15 15:38:24,196 [17] (0) WARN SolarWinds.InformationService.Contract2.InfoServiceProxy - (null) Support! -- LONG RUNNING QUERY: 131123.694 ms: SELECT DirectionID, DirectionName, NodeID, InterfaceID, TotalBytes, TotalPackets, TotalBytesIngress as TotalPacketsIngress FROM Orion.NetFlow.TransferredDataTop(Filter='NSF:E:***.**.**.***;I:1;TD:2016-12-01T00:00:00~2016-12-15T23:45:00,360,True,False;FD:Ingress', Limit=5, Rx=True, Tx=True, TransferredDataType='Packets', ReturnAsTransferred=False, TopKey='') RETURN XML RAW

2016-12-15 15:38:24,274 [16] (0) WARN SolarWinds.InformationService.Contract2.InfoServiceProxy - (null) Support! -- LONG RUNNING QUERY: 131142.531 ms: SELECT DirectionID, DirectionName, NodeID, InterfaceID, TotalBytes, TotalPackets, TotalBytesIngress FROM Orion.NetFlow.TransferredDataTop(Filter='NSF:E:***.**.**.***;I:1;TD:2016-12-01T00:00:00~2016-12-15T23:45:00,360,True,False;FD:Ingress', Limit=5, Rx=True, Tx=True, TransferredDataType='Bytes', ReturnAsTransferred=False, TopKey='') RETURN XML RAW

2016-12-15 15:38:24,290 [18] (2) WARN SolarWinds.InformationService.Contract2.InfoServiceProxy - (null) Support! -- LONG RUNNING QUERY: 41670.0431 ms: SELECT TotalBytes

FROM Orion.NetFlow.TransferredDataTop(Filter='NSF:E:***.**.**.***;I:1;TD:2016-12-01T00:00:00~2016-12-15T23:45:00,360,True,False;FD:Ingress', Limit=0, Rx=True, Tx=False, TransferredDataType='Bytes', ReturnAsTransferred=False, TopKey='' ) RETURN XML RAW

*** Assembly SolarWinds.Licensing.Framework, Version=3.0.0.477, Culture=neutral, PublicKeyToken=0bdb206719860e1f, .NET version v4.0.30319 ***

*** Assembly SolarWinds.Orion.Licensing, Version=1.0.0.752, Culture=neutral, PublicKeyToken=null, .NET version v4.0.30319 ***

2016-12-15 15:38:24,820 [21] (0) WARN SolarWinds.InformationService.Contract2.InfoServiceProxy - (null) Support! -- LONG RUNNING QUERY: 131679.2934 ms: SELECT AppID, ApplicationName, MapTo, ProtocolName, NodeID, InterfaceID, TotalBytes, TotalPackets, TotalBytesIngress, TotalPacketsIngress

FROM Orion.NetFlow.ApplicationsTop(Filter='NSF:E:***.**.**.***;I:1;TD:2016-12-01T00:00:00~2016-12-15T23:45:00,360,True,False;FD:Ingress', Limit=5, Rx=True, Tx=True, TopKey='') RETURN XML RAW

2016-12-15 15:38:25,304 [11] (0) WARN SolarWinds.InformationService.Contract2.InfoServiceProxy - (null) Support! -- LONG RUNNING QUERY: 132168.4828 ms: SELECT Protocol, ProtocolName, NodeID, InterfaceID, TotalBytes, TotalPackets, TotalBytesIngress, TotalPacketsIngress FROM Orion.NetFlow.ProtocolsTop(Filter='NSF:E:***.**.**.***;I:1;TD:2016-12-01T00:00:00~2016-12-15T23:45:00,360,True,False;FD:Ingress', Limit=5, TopKey='') RETURN XML RAW

2016-12-15 15:38:25,335 [14] (0) WARN SolarWinds.InformationService.Contract2.InfoServiceProxy - (null) Support! -- LONG RUNNING QUERY: 132192.665 ms: SELECT CountryCode, CountryName, NodeID, InterfaceID, TotalBytes, TotalPackets, TotalBytesIngress, TotalPacketsIngress FROM Orion.NetFlow.CountriesTop(Filter='NSF:E:***.**.**.***;I:1;TD:2016-12-01T00:00:00~2016-12-15T23:45:00,360,True,False;FD:Ingress', Limit=5, Rx=True, Tx=False, TopKey='') RETURN XML RAW

2016-12-15 15:38:25,350 [15] (0) WARN SolarWinds.InformationService.Contract2.InfoServiceProxy - (null) Support! -- LONG RUNNING QUERY: 132202.1329 ms: SELECT CountryCode, CountryName, NodeID, InterfaceID, TotalBytes, TotalPackets, TotalBytesIngress, TotalPacketsIngress FROM Orion.NetFlow.CountriesTop(Filter='NSF:E:***.**.**.***;I:1;TD:2016-12-01T00:00:00~2016-12-15T23:45:00,360,True,False;FD:Ingress', Limit=5, Rx=False, Tx=True, TopKey='') RETURN XML RAW

*** Assembly App_Web_lyuf3uxp, Version=0.0.0.0, Culture=neutral, PublicKeyToken=null, .NET version v4.0.30319 ***

2016-12-15 15:38:27,394 [20] (0) WARN SolarWinds.InformationService.Contract2.InfoServiceProxy - (null) Support! -- LONG RUNNING QUERY: 134245.7984 ms: SELECT TosID, TosName, NodeID, InterfaceID, TotalBytes, TotalPackets, TotalBytesIngress, TotalPacketsIngress FROM Orion.NetFlow.TosTop(Filter='NSF:E:***.**.**.***;I:1;TD:2016-12-01T00:00:00~2016-12-15T23:45:00,360,True,False;FD:Ingress', Limit=5, TopKey='') RETURN XML RAW

*** Assembly App_Web_inaccuratedatawarning.ascx.be89cd80.vcauplmm, Version=0.0.0.0, Culture=neutral, PublicKeyToken=null, .NET version v4.0.30319 ***

2016-12-15 15:38:38,487 [12] (0) WARN SolarWinds.InformationService.Contract2.InfoServiceProxy - (null) Support! -- LONG RUNNING QUERY: 145333.3406 ms: SELECT Caption, NodeID, InterfaceID, TotalBytes, TotalPackets FROM Orion.NetFlow.UniqueVisitorsTop(Filter='NSF:E:***.**.**.***;I:1;TD:2016-12-01T00:00:00~2016-12-15T23:45:00,360,True,False;FD:Ingress', Limit=5, Rx='True', Tx='True', TopKey='') RETURN XML RAW

2016-12-15 15:39:05,165 [16] (0) WARN SolarWinds.InformationService.Contract2.InfoServiceProxy - (null) Support! -- LONG RUNNING QUERY: 26680.3404 ms: SELECT Caption, StartTime, TotalBytes

FROM Orion.NetFlow.UniqueVisitorsDetail(Filter='NSF:E:***.**.**.***;I:1;TD:2016-12-01T00:00:00~2016-12-15T23:45:00,360,True,False;FD:Ingress', TopKeys='Visitors', Absolute=False, Rx='True', Tx='True') RETURN XML RAW

2016-12-15 15:39:05,773 [25] (0) WARN SolarWinds.InformationService.Contract2.InfoServiceProxy - (null) Support! -- LONG RUNNING QUERY: 41473.644 ms: SELECT DirectionID, StartTime, TotalBytes

FROM Orion.NetFlow.TransferredDataDetail(Filter='NSF:E:***.**.**.***;I:1;TD:2016-12-01T00:00:00~2016-12-15T23:45:00,360,True,False;FD:Ingress', TopKeys='Transmitted,Received', Absolute=False, Rx=True, Tx=True, TransferredDataType='Packets', ReturnAsTransferred=False) RETURN XML RAW

2016-12-15 15:39:06,366 [23] (0) WARN SolarWinds.InformationService.Contract2.InfoServiceProxy - (null) Support! -- LONG RUNNING QUERY: 42057.8872 ms: SELECT DirectionID, StartTime, TotalBytes

2016-12-15 15:39:24,276 [19] (3) WARN SolarWinds.InformationService.Contract2.InfoServiceProxy - (null) Support! -- LONG RUNNING QUERY: 57537.3045 ms: SELECT ConversationID, IPSort1, IPAddress1, Hostname1, IPSort2, IPAddress2, Hostname2, NodeID, InterfaceID, TotalBytes, TotalPackets, TotalBytesIngress, TotalPacketsIngress

FROM Orion.NetFlow.ConversationsTop(Filter='NSF:I:1;TD:2016-12-01T00:00:00~2016-12-15T23:45:00,360,True,False;E:***.**.**.***;FD:Ingress', Limit=25, TopKey='') RETURN XML RAW

Find more posts tagged with

nta 4.2.1

performance tuning

Accepted answers

sean.martinez

What is the Disk Configuration for the NTA Flow Storage Database? Are you running RAID, or 10k or SSD drives?

The query times are showing 40 seconds to 2-3 minutes to obtain from the NTA Flow Storage database.

If you are also doing a lot of joins in the Report, you will probably need more than 4 CPU Cores on the Flow Storage. SQL Joins use a lot of CPU time to correlate records.

All comments

sean.martinez

What is the Disk Configuration for the NTA Flow Storage Database? Are you running RAID, or 10k or SSD drives?

The query times are showing 40 seconds to 2-3 minutes to obtain from the NTA Flow Storage database.

If you are also doing a lot of joins in the Report, you will probably need more than 4 CPU Cores on the Flow Storage. SQL Joins use a lot of CPU time to correlate records.

jxchappell

To add to Seans point...

This sounds like an issue with selecting too much data for the speed of the Server/Disk/Network...Think about these things...default historical data in NTA is 30 days...we have about 130-140 devices in NTA and our database is 180 GB...for 30 days of data that averages out to a little more than 1 GB per device to build into a report...moving 1 GB of raw data and then parsing it into a report is going to be very time consuming on all of your hosts...much less if you're trying to select multiple devices

xr219

Hi Guys,

Thanks for this. Yes, I suspected as much. We are only gathering data from two edge routers which are in failover mode, so only really getting data from one at any one time... but there is a lot of data. Ideally I'd like to export the data out to another database and let our developer guys run reporting against that. I'm banging my head against a wall trying to explain to our executive team that this isn't a billing platform. What I'd like to do is set it so that it just shows / records traffic by port and traffic by destination per day. This would greatly speed up the performance (I think) as it would in effect be summary data, and not every single conversation... but at the same time I'd need the full summary and not something as simple as a top talkers report. Reason being, some of the data sources are on packages which are as low as 25MB, while others could be on 8GB or unlimited. 10Mb over plan for someone on 8GB plan is of little or no consequence, but 10MB for someone on a 25MB plan could be thousands of dollars.