Your data. Anywhere you go.

New Relic for iOS or Android


Download on the App Store    Android App on Google play


New Relic Insights App for iOS


Download on the App Store


Learn more

Close icon

Calculating rough utilization percentages with variable timeslices?


#1

Hey folks,

I will admit up front that I’m not awesome at SQL queries, so it’s entirely possible that I’m just missing something. I’m trying to use the Chart Builder to build a widget that will display rough utilization over time, out of a theoretical maximum. We have 20 threads of a process available for usage. If someone uses, say, 5 of the 20 available threads at 100% for a minute, then that’s 25% utilization for that minute. I’ve been able to make this work with this query:

SELECT (sum(duration)/1200)*100 as 'Estimated Usage'
FROM Transaction TIMESERIES 1 minute EXTRAPOLATE

The place where I’m hitting my head against the wall is that this only works with TIMESERIES 1 as long as the widget is capable of displaying 1-minute increments. as soon as I expand the timerange far enough, the number of 1-minute slices exceeds the maximum displayable, and the increment changes to something coarser, 20 minutes or so, and the math breaks.

My question is, is there a way of inserting the size of the timeslice into the query so that the math will work regardless of the time range? For instance, instead of:

sum(duration)/1200

(1200 comes from 60 (for minutes)*(20 threads))

Could I do something functionally equivalent to:

sum(duration)/timeslice_size

and then remove TIMESERIES 1 altogether?

I realize that there’s probably an angle I’m just not thinking of, so I appreciate any guidance. Thanks!


#2

@tony.etienne

For query optimisation purposes, there is a max number of buckets NRQL can return. It seems that when you expand your time range you are hitting the max number of buckets problem.

You will always be able to use TIMESERIES 1 MINUTE, so long as the time period of your query remains within 366 buckets. So, 366 minutes.

I can run a query for example;

SELECT average(duration) FROM Transaction SINCE 366 MINUTES AGO TIMESERIES 1 minute 

Which works just fine, but increasing to SINCE 367 MINUTES AGO breaks this query due to the limitation described.


If you need historical data at 1 minute increments, you can try a query like:

SELECT average(duration) FROM Transaction SINCE '2020-03-23 09:00:00 UTC' UNTIL '2020-03-23 10:00:00 UTC' TIMESERIES 1 minute 

By hardcoding in a time period that is less than 367 minutes in length.


Alternatively, you can use TIMESERIES MAX, which will get you the most granular data available to you based on your selected time period.


#3

Hi Ryan, thanks for the reply, I appreciate your taking the time.

However, I think I already understood the part you were talking about. Sorry that I’m being unclear; as I mentioned I’m new to this so I apologize for explaining poorly :slight_smile: (I have a suspicion that I’m asking questions like “how many apples are in this pear?”)

I think what I’m getting at is this: I’d like to use TIMESERIES MAX so that I don’t have to restrict my time ranges for my widgets, but I’d need to use something other than 1200 in my equation so that I’m dividing the time correctly. Obviously, 1200 works well when using TIMESERIES 1. I was hoping that there might be a token or similar that would represent the size of each bucket (1 min, 5 min, 15, etc) so that I could formulate my query as something like SELECT (sum(duration)/(seconds * bucket_size * threads))*100... so that if the bucket represented 15 minutes, I’d still get the correct utilization math. Does that make sense?


#4

Does the Rate function help you out?

From: https://docs.newrelic.com/docs/query-data/nrql-new-relic-query-language/getting-started/nrql-syntax-clauses-functions#func-rate

" Use the rate( ) function to visualize the frequency or rate of a given query per time interval. For example, you might want to know the number of pageviews per minute over an hour-long period or the count of unique sessions on your site per hour over a day-long period."

This query will generate a line chart showing the rate of throughput for APM transactions per 10 minutes over the past 6 hours.

SELECT rate(count(*), 10 minute) FROM Transaction SINCE 6 hours ago 
TIMESERIES

#5

Hmm, mayyyyyyyyybe? I’ll poke at it for a bit and see what I can get. Thanks for the pointer :slight_smile:


#6

No worries!

Yeah I wasn’t sure if it’s an exact fit for what you’re looking for, but it felt like the closest we can get by default. If rate() doesn’t work, then I’m not sure we could exactly match your needs even with some funky nrql queries.