Grafana, InfluxDB, two tags and one amount. Or how to calculate the sum of subgroups?

• Apache.JMeter
• Gatling
• Loadrunner enterprise
• Yandex.Tank

There are three options:

1. Just the amount grouped by Type tag
2. Grafana-way. We use a stack of values
3. Sum of highs with subquery

How it all began

[outputs.influxdb]
  urls = ["http://influxdb_server:8086"]
  database = "telegraf"
  username = "login-InfluxDb"
  password = "*****"
  retention_policy = "month"
  influx_uint_support = false

[agent]
  collection_jitter = "2s"
  interval = "2s"
  precision = "s"

[[inputs.jolokia2_agent]]
  username = "login-Jolokia"
  password = "*****"
  urls = ["http://127.0.0.1:7777/jvm-service"]

[[inputs.jolokia2_agent.metric]]
  paths = ["Usage","PeakUsage","CollectionUsage","Type"]
  name = "java_memory_pool"
  mbean = "java.lang:name=*,type=MemoryPool"
  tag_keys = ["name"]

[[processors.converter]]
  [processors.converter.fields]
    integer = ["CollectionUsage.*", "PeakUsage.*", "Usage.*"]
    tag = ["Type"]

SELECT max("Usage.committed")
FROM "telegraf"."month"."java_memory_pool"
WHERE
    host =~ /^${host:regex}$/ AND
    jolokia_agent_url =~ /^${jolokia:regex}$/ AND
    $timeFilter
GROUP BY
    "Type", "name", time($granularity)

SELECT max("Usage.committed")
FROM "telegraf"."month"."java_memory_pool"
WHERE
    host =~ /^serverName$/ AND
    jolokia_agent_url =~ /^http:\/\/127\.0\.0\.1:7777\/jvm-service$/ AND
    time >= 1583834400000ms and time <= 1583877600000ms
GROUP BY
    "Type", "name", time(1m)

Remember that 721 is the expected number of points in response to requests to InfluxDB with the current settings for the time interval (12 hours) and grouping step (1 minute).

Remember the values ​​for 20:00: NON_HEAP pools 172.5 MiB , and HEAP pools 128 MiB . We will focus on these values ​​in the future.

The question remains: how to get what size is allocated for all HEAP pools and all NON_HEAP pools in total?

1. Just the amount grouped by Type tag

1.1. Sum grouped by tag

SELECT sum("Usage.committed")
FROM "telegraf"."month"."java_memory_pool"
WHERE
    host =~ /^${host:regex}$/ AND
    jolokia_agent_url =~ /^${jolokia:regex}$/ AND
    $timeFilter
GROUP BY
    "Type"

1.2. Amount grouped by tag per minute

1.3. Tag grouped per second

2. Grafana-way. We use a stack of values

2.1. Just make Hover tooltip / Stacked value: cummulative

1. Add series override / HEAP / Stack : A
2. Add series override / NON_HEAP / Stack : B

1. Stacking & Null value / Stack : On
2. Hover tooltip / stacked value : cummulative
3. Hover tooltip / mode : single

• Stack A B Stacking & Null value / Stack: On, ;
• Hover tooltip / Mode , Single, Hover tooltip .

2.2. Stacked value: cummulative with hiding intermediate lines

1. Add new metrics with a different name and value 0 to the results.
2. Add new metrics to Stack A and Stack B , to the top of the stack.
3. Hide from the display - the original lines of HEAP and NON_HEAP .

3. The sum of the highs with the subquery

3.1 Sum of increments of the cumulative sum of maxima

SELECT sum("U") FROM (
        SELECT non_negative_difference(cumulative_sum(max("Usage.committed"))) AS "U" 
        FROM "month"."java_memory_pool" 
        WHERE 
            (
                "host" =~ /^${host:regex}$/ AND 
                "jolokia_agent_url" =~ /^${jolokia:regex}$/
            ) AND 
            $timeFilter 
        GROUP BY time($granularity), "Type", "name"
)
GROUP BY "Type", time($granularity)

3.2 Sum of highs with a change in the grouping level

SELECT sum("A")
FROM (
    SELECT max("Usage.committed") as "A"
    FROM "month"."java_memory_pool" 
    WHERE
            (
                "host" =~ /^${host:regex}$/ AND 
                "jolokia_agent_url" =~ /^${jolokia:regex}$/
            ) AND 
            $timeFilter
    GROUP BY time($granularity), "Type", "name"
)
WHERE $timeFilter
GROUP BY time($granularity), "Type"

References (instead of references)

• grafana (6.5.0)
• influxdb (1.7.9)
• telegraf (1.13.3)
• jolokia-jvm-1.6.2-agent.jar (1.6.2)