Use Case: Memory Usage by a Query
Warning
SingleStore 9.0 gives you the opportunity to preview, evaluate, and provide feedback on new and upcoming features prior to their general availability. In the interim, SingleStore 8.9 is recommended for production workloads, which can later be upgraded to SingleStore 9.0.
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.
Note
The MV_
table provides data only as far back as the uptime, as it is cleared out when the aggregator node restarts.
Warning
The measurement AVG_
is the total memory usage, averaged over the execution time of the query.
Last modified: April 26, 2023