Feature Request: Provide a convenience view/table for counters with followers

[auselen]

When I have a config similar to

    perf_event_config {
      timebase {
        tracepoint {
          name: "sched/sched_switch"
        }
        period: 1
      }
      followers {
        raw_event {
          type: 12
          config: 17
        }
        name: "CPU_CYCLES"
      }
      followers {
        raw_event {
          type: 12
          config: 8
        }
        name: "INST_RETIRED"
      }
    }

I would like to have a table like below, including column values such as CPU_CYCLES being in deltas rather than accumulated/sampled counter value.

ts	cpu	sched_switch	CPU_CYCLES	INST_RETIRED
891848972656	3	1	786603	2134024

This would allow me to calculate metrics like IPC (INST_RETIRED / CPU_CYCLES) as a new column easily.

Currently I do this with joining, grouping, hard-coded pivoting over using WINDOWed values (to calculate deltas) which seems like waste given this should be already available at perf data consumption.

[LalitMaganti]

I believe with #4545 + #4579 the situation should be signifcantly improved. Specifically, you should be able to do:

        INCLUDE PERFETTO MODULE linux.perf.counters;

        select
          sample_id,
          ts,,
          linux_perf_counter_for_sample(sample_id, 'CPU_CYCLES') as cpucycles
          linux_perf_counter_for_sample(sample_id, 'INST_RETIRED') as inst_retired
        from perf_sample

[LalitMaganti]

Now calculating deltas from these should be pretty straightforward with a window function. Sure, we could add something directly for that - @cphlipot1 for thoughts on this.

The main problem with linux_perf_sample_with_counters is pivoting so we might need a function which computes the delta directly.

[auselen]

a function which computes the delta directly

Wouldn't be easier to record delta during perf data ingestion?

[LalitMaganti]

There are lots of problems with computing things like deltas in C++ rather in SQL at the point where you want to query. For example, people often want to first pre-filter to a region of time or a given subset of events and then compute the delta after.

If you have a "delta" column in the trace, what you're basically saying is that "we think deltas only matter on the raw data and not otherwise". And having a delta column which is wrong is far more confusing than not having one at all. This is the same reason why counters don't have a "duration" column - we used to have one but once you filter etc, you find it confuses people tremendously when their "duration" is not what they expect.

This is why for "derived" properties of counters like duration, deltas, rates, we try as much as possible to do it as late as possible instead of eagerly. That way you make it so the "deltafiaciton" is the last thing you do and everything makes sense. This is a general philosophy of all counters in Perfetto, not just for perf.

[cphlipot1]

Now calculating deltas from these should be pretty straightforward with a window function. Sure, we could add something directly for that - @cphlipot1 for thoughts on this.

The main problem with linux_perf_sample_with_counters is pivoting so we might need a function which computes the delta directly.

Personally i don't have a strong need for deltas myself, but i don't really see an issue with this being added.

would not be surprised if people who regularly work with perf.data probably are used to working with deltas as that is how the data is stored internally in the raw file itself. Other tools like "perf script" IIRC expose the deltas as a property of each sample instead of cumulative sums as well.

[auselen]

How would implementation for delta functionality would look like? I can give it a try may be.

[LalitMaganti]

Thinking more, wondering if we need a function at all. You should be able to do:

        INCLUDE PERFETTO MODULE linux.perf.counters;

        with counters as (
          select
            sample_id,
            ts,
            linux_perf_counter_for_sample(sample_id, 'CPU_CYCLES') as cpucycles
            linux_perf_counter_for_sample(sample_id, 'INST_RETIRED') as inst_retired
          from perf_sample
        )
        select
          sample_id,
          ts,
          cpucycles - lag(cpucycles) over (partition by perf_session_id order by ts) as cpucycles_delta
          inst_retired - lag(inst_retired) over (partition by perf_session_id order by ts) as inst_retired_delta
       from counters

Maybe we just need a macro to simplify the lag and minus operation?

[auselen]

I was able extract this information before - that wasn't the problem. It is mostly about convenience. Sharing lengthy SQL statements then others making updates, is error prone.

My use case is actually fixed; per cpu, capture counters at context switch then interpret slices. I'm looking for solid simplified SQL statements to achieve that, helping me evolve queries into advanced ones.

[LalitMaganti]

Fair enough. I'd maybe suggest having a macro like so in the standard library then:

CREATE PERFETTO MACRO linux_perf_counter_delta_for_sample(sample_id Expr, counter_name Expr)
RETURNS Expr AS
linux_perf_counter_for_sample($sample_id, $counter_name) - lag(linux_perf_counter_for_sample($sample_id, $counter_name)) over (partition by perf_session_id order by ts)

Which you can use as:

          select
            sample_id,
            ts,
            linux_perf_counter_delta_for_sample!(sample_id, 'CPU_CYCLES') as cpucycles
            linux_perf_counter_delta_for_sample!(sample_id, 'INST_RETIRED') as inst_retired
          from perf_sample