Nested aggregation with Metric data type

We are using Heroku with multiple dynos. New Relic’s memory metrics does not reflect exact numbers. So I want to get average memory usage by host and then sum all this numbers to get exact usage with the following query.

select sum(mem) from(
    SELECT average(newrelic.timeslice.value) as mem 
    FROM Metric where appName = 'Server Name' 
    and host like 'web%' and metricTimesliceName = 'Memory/Physical' FACET host
)

But this query does not work. As far as I see, subselect is working with events but not with Metric data type.

The question is how can I achieve my goal with NRQL?

1 Like

Hey New Relic stuff,

This is not a big deal for you.

Hi @mehmet4, I’m currently working to determine the expected behavior here. I’ll let you know once I have more details.

In the meantime, I did want to mention a way to streamline your query. Instead of SELECT average(newrelic.timeslice.value) FROM Metric WHERE metricTimesliceName = 'Memory/Physical', you can do SELECT average(apm.service.memory.physical) FROM Metric to query the same data.

That won’t change anything with regards to this specific issue, but it may save you some space in the future.

1 Like

Thanks @jeffrey_s for your good advice.
I am looking forward to see your soluton.

Following on from @jeffrey_s’s input, you’d think something like the following would return the running total of memory usage over time:

FROM Metric
  SELECT sum(apm.service.memory.physical)
    AS 'Total Memory Usage (MB)'
  WHERE appName = 'Server Name'
    AND host LIKE 'Web%'
  TIMESERIES AUTO

But it appears to far overshoot the value I’m expecting.

Hopefully, NR Support has a better idea to output this information.

Instead maybe

FROM Metric
  SELECT max(apm.service.memory.physical)
    AS 'Total Memory Usage (MB)'
  WHERE appName = 'Server Name'
    AND host LIKE 'Web%'
  TIMESERIES AUTO RAW
3 Likes

@rishav.dhar Did you get this issue resolved?

This thread was opened by @mehmet4; they’re likely the best person to check with.

1 Like

Hi @mehmet4, just a quick update — it definitely looks like nested aggregation is incompatible with metric timeslice queries, at least for the moment.

Our engineers are currently working to resolve this, although I don’t have a timeline available.

2 Likes

@jeffrey_s it is good that your engineers are working for it. I am looking forward to see it once it is launched.

1 Like