To avoid potential confusion:
I think Jack’s talking about a different notion of “refresh” than the one you had in mind. But that’s because the stats are up always up to data and there’s no need to periodically update them. information_schema queries read internal in-memory data structures and format the output as a SQL table. In the case of workload profiling tables, these data structures are updated on the fly by queries as they execute, even when no infoschema queries are running.
Though it’s tangential, it’s still worth keeping in mind that, as Jack described, stats for a query can get reset if it’s dropped from the in-memory plancache.
mv_activities with a delta of 5 min would indeed suffice to get finished/success counts. The delta simply controls the length of a sleep inside the query.
However, I wouldn’t recommend using mv_activities to get average latency. The issue is that mv_activities includes metrics for not-yet-finished instances of a query, so its elapsed_time_ms isn’t quite a measure of latency. In particular, if you were to divide elapsed_time_ms by the instance count (success_count + run_count + failure_count), you’d underestimate average latency.
To compute average metrics per execution, I’d recommend mv_finished_tasks instead. That table’s metrics exclude running instances. However, since we don’t currently provide a delta table for mv_finished_tasks - in the sense that mv_activities[_extended] is the delta of two reads of mv_activities[_extended]_cumulative - it can only directly tell you about average latency across all instances of a query since the time it was last added to the in-memory plancache. To compute average latency over just those instances that ran within a certain window of time, you’d have to manually take two snapshots, at the start and end of the window, and then compute a delta manually, as Jack mentioned.