Use Case: Memory Usage by a Query
How to find the total typical memory usage of a recent run of a query?
Use the following query to get the total typical memory usage of a recent run of a query for all activities on the aggregator and on all partitions of the leaf nodes:
SELECTaggregator_activity_name,SUM(memory_bs * 1000) / (SUM((CASE WHEN mvae.activity_name = aggregator_activity_name THEN elapsed_time_ms ELSE 0 END))) AS avg_clusterwide_mem_bytes_per_second,ANY_VALUE(query_text)FROM information_schema.mv_activities_extended mvaeINNER JOIN information_schema.mv_queries mvq ON mvae.aggregator_activity_name = mvq.activity_nameGROUP BY 1ORDER BY 2 DESC;
This gives us the cluster-wide memory usage of all instances of user queries, divided by the sum of query latency of those instances.
LIMIT 5 clause at the end.
MV_ table provides data only as far back as the uptime, as it is cleared out when the aggregator node restarts.
AVG_ is the total memory usage, averaged over the execution time of the query.
Last modified: April 26, 2023