This discussion has been locked. The information referenced herein may be inaccurate due to age, software updates, or external references.

You can no longer post new replies to this discussion. If you have a similar question you can start a new discussion in this forum.

reporting out of SWIS API

ryan.davis26 over 6 years ago

We have a reporting platform that we're trying to leverage the solarwinds API to query data from. It uses a homegrown tool that can use an API to ingest data. This works great for availability, cpu, disk, memory and response time statistics.

However, we're running into issues with interface related tables where the queries are timing out and the SWIS service itself starts to consume over 12GB of ram and then doesn't go back down until it is restarted, here is an example of one query:

select Nodes.Caption as DEVICE_NAME, Nodes.IP_Address as DEVICE_IP,  'InPercentUtil' as PARAMETER_NAME, InterfaceTraffic.DateTime as CHECK_TIME
,InterfaceTraffic.InPercentUtil as OUTPUT_DATA,  interfaces.TypeDescription as 
OUTPUT_DATA2, Interfaces.Caption as OUTPUT_DATA3
from orion.Nodes, orion.npm.InterfaceTraffic, orion.npm.Interfaces
where InterfaceTraffic.nodeID=Nodes.NodeID AND interfaces.nodeid=nodes.nodeid and MINUTEDIFF(InterfaceTraffic.DateTime,getdate()) = 1

tdanner, would you have any suggestions?

Id say we have a medium to large deployment and interfaces are definitely the biggest portion of the environment:

3 Polling Engines

Network Elements 32814

Nodes 2496

Interfaces 21062

Volumes 9256

Thanks!

Top Replies

Parents

0 tdanner over 6 years ago

First, you probably want "GETUTCDATE()" not "GETDATE()" in the WHERE clause - SWIS operates in UTC even when the underlying database tables use local time.
My hunch is that account limitations are getting involved and making this query more complicated. Can you try this query with an account that has no limitations?
Cancel
Vote Up 0 Vote Down

Cancel
0 ryan.davis26 over 6 years ago in reply to tdanner

The account we’re using doesn’t have any limitations.
And interestingly enough, I was performing some tests earlier and the same query above returns about 126,000 records in about 10 seconds according to swql studio (21,060 interfaces * 6) where 6 is the amount of data points per interface per hour.
If I do that query with just a top 127,000 it completes everytime. If I take the top 127,000 out of there it constantly times out more often than not. Not sure what the deal is there
Cancel
Vote Up 0 Vote Down

Cancel

Reply

0 ryan.davis26 over 6 years ago in reply to tdanner

The account we’re using doesn’t have any limitations.
And interestingly enough, I was performing some tests earlier and the same query above returns about 126,000 records in about 10 seconds according to swql studio (21,060 interfaces * 6) where 6 is the amount of data points per interface per hour.
If I do that query with just a top 127,000 it completes everytime. If I take the top 127,000 out of there it constantly times out more often than not. Not sure what the deal is there
Cancel
Vote Up 0 Vote Down

Cancel

Children

0 mesverrum over 6 years ago in reply to ryan.davis26

So in this case a top x is much more computationally efficient than a where by itself, the top command just pulls data until it's done at that max number of entries, the where logic is still evaluating all of the entire history of every interface to see if they match the condition (default settings would have data points going back a year on these). I'm boarding an airplane soon so I don't have a good solution off the top of my head for how to improve the efficiency of your query but hopefully that helps some.
Cancel
Vote Up 0 Vote Down

Cancel
0 ryan.davis26 over 6 years ago in reply to mesverrum

Thank you mesverrum. That being said, it sounds like implementing a top X wouldn't work to get results from all interfaces in the last hour because its only taking the top X from the table and then doing the hourdiff on those? Basically my question is, does the TOP X happen before the hourdiff or after?
Cancel
Vote Up 0 Vote Down

Cancel
0 mesverrum over 6 years ago in reply to ryan.davis26

Top x is after the where condition until it hits the cap, then it says it is done. But this thread on the same situation happening in plain sql seems to point toward your including a top statement could impact the execution plan in a way that makes it run more efficiently.
https://stackoverflow.com/questions/1393508/sql-massive-performance-difference-using-select-top-x-even-when-x-is-much-higher
Cancel
Vote Up 0 Vote Down

Cancel