Get average number of spans (queries) in a transaction grouped by path

Elastic Observability APM
1

Elastic deployment version: v7.6.1

I'm trying to create a visualization which gives me the average number of queries per request. So basically what I want to see is:

  • GET /users/ => 5 queries
  • POST /users/ => 3 queries
  • GET /users/:id/books => 555 queries (N+1 query problem)

Our Java backend application is using the Java apm agent and everything works as expected. An example transaction:

image
image1514×818 77.9 KB

Option 1:

I found in the docs that there is a transaction.span_count.started property which gives me what I need (no all spans are queries, but that's not really an issue). I can also see the property when I check out the doc of the request:

image

However, this seems to be an unmapped field, so I cannot use it in a visualization. I searched for "No cached mapping for this field" in this forum/Goolge/Github, but none of the solutions worked. This is what I tried:

  • Refresh index in Kibana management
  • Remove index (it is automatically added again when going to the apm tab)

The field is mentioned in the docs:

But it's missing here:

Since the apm index is automatically created, I'm not sure if I should change it manually. I noticed that the field is not in the mapping:

"span_count": {
    "properties": {
        "dropped": {
            "type": "long"
        }
    }
},

And that the transaction mapping is not dynamic:

"transaction": {
    "dynamic": "false",
        "properties": {
....

So, should I change this mapping manually or is there something missing in apm-server? And if I change it manually, will it be overwritten by apm-server?

Option 2:

Don't use the transaction.span_count.started field.

In this case, I would need to do something like this:

  1. count # of spans of with span.action: query, group them by transaction.id
  2. annotate every transaction with url.path (the property is not available on query spans, so I would need to get it somehow from the request span of that transaction)
  3. take the average of the # of spans and group them by url.path

I'm quite new to Kibana, so I'm not certain how I would achieve this. I was able to create a visualization to get the slowest requests based on the average transaction duration, but that's a single group by.

2

You could try using the metrics used for the "Time spent by span type" graph (span.self_time):

https://www.elastic.co/guide/en/apm/agent/java/current/metrics.html#metrics-application

That also lets you filter by the types of spans you are interested in. For example type: "db" and subtype: "mysql".

If that doesn't work out, you can customize the index mapping with APM Server settings: https://www.elastic.co/guide/en/apm/server/current/configuration-template.html

3

Thank you for the suggestions!

Seems the append_fields setting is not available on elastic cloud: https://www.elastic.co/guide/en/cloud/current/ec-manage-apm-settings.html

Any idea why the field is not in fields.yml by default? https://github.com/elastic/apm-server/blob/master/model/transaction/_meta/fields.yml

Maybe I'm also overthinking it and I don't need to average over transactions of the same path, but it would be useful to directly see the path.

4

I'd suggest to contact support then. Maybe they can set it for you.

Not all fields are indexed by default. That is to save space for fields you don't normally use for aggregations. However, your use case seems like it could benefit from that field being indexed.

Did you try out the span.self_time metric-based approach?

5

Yes, yes, perfect!

I took me a while to figure it out, but it is exactly what I need.

For anyone else who is interested, create a new visualization with:

  • Metric: average span.self_time.count
  • Bucket: split rows on transaction.name (group by), which is in this case our java method

Filter on span.type: db.

So I won't need the extra mapping. Thanks a lot for pointing me in the right direction @felixbarny!

1 Like
6

Sounds like something that could be very useful for lots of people.

@gil do you think including such a visualization in the curated UI would make sense?

@Joren_Inghelbrecht would you mind adding this visualization, including a screenshot, to the https://github.com/elastic/apm-contrib repo?

7

@felixbarny I'd love to contribute. You want me to add an export of the visualization and some screenshots in an issue? I can open a PR too, but I'm not sure where the visualization should be placed.

8

A PR would be great. Seems this would be the best place to add it: https://github.com/elastic/apm-contrib/tree/master/kibana

We can fine tune the file locations in the PR.

A 7.x version is enough and a screenshot of the visualization would be great :+1:t2:

9

@felixbarny @gil I think it would be great if "Filters" section can let us filter by span type. This way we could have separate charts for DB, external http calls etc and a list of calls of that particular span type. This would help us separately drill down each component of the service.

I do see a recent ticket similar to this: https://github.com/elastic/kibana/issues/40304

10

Just back from vacation -

do you think including such a visualization in the curated UI would make sense?

I'm happy to see it was straightforward to build the visualization in kibana, that's one of the benefits of storing APM data in the Elastic stack. It might make sense to incorporate this into the curated UI but would be happy for it to land in apm-contrib asap.

"Filters" section can let us filter by span type

Yes! Thanks for the feedback here and in the linked issue.

11

can you please put your visualization in apm-contrib.

12

I had a very busy week, so will try to get back to this tomorrow.

13

Please do let us know here once you have done it.

14

@felixbarny @gil @multazim PR can be found here: https://github.com/elastic/apm-contrib/pull/39

15

This topic was automatically closed 20 days after the last reply. New replies are no longer allowed.