RAM Usage, CPU Usage and Mean Execution Time by Query

Help
1

Hello,

I need to know if there’s a way to query information about RAM Usage, CPU Usage and Mean Execution Time for every SQL query executed on SingleStore.

Thanks.

2

You can get that information from SingleStoreDB Cloud · SingleStore Documentation.

1 Like
3

I executed a simple select * from a table I have and then tried to execute this query:

SELECT queries.query_text FROM information_schema.mv_activities_extended AS activities
JOIN information_schema.mv_queries AS queries
WHERE activities.activity_name = queries.activity_name;

But for some reason it didn’t show the select all query, any idea why this happened ?

4

Can someone help me with my issue ?

5

anyone with the answer for the above question, i too need a query that dashboard uses to get the Percentage of CPU utilization per node.

6

@nhjf - MV_ACTIVITIES retain the data only for short interval of time, this interval is controlled by the value of activities_delta_sleep_s session variable.

I recommend querying MV_ACTIVITIES_CUMULATIVE table to get the queries and metrics for an extended period of time. MV_ACTIVITIES_CUMULATIVE reports the sum of the profiling statistics of each task in the set, as collected over its lifetime. Given that this table shares the cumulative stats, to get the metrics at a given time t1, take a snapshot of the table/query record at t0 and take the delta of stats between t0 & t1.

Query to get the metrics per query plan,

SELECT  a.ACTIVITY_TYPE AS activityType,
        a.ACTIVITY_NAME AS activityName,
        a.DATABASE_NAME AS databaseName,
        q.QUERY_TEXT AS queryText,
        IFNULL(a.SUCCESS_COUNT, 0) AS successCount,
        IFNULL(a.FAILURE_COUNT, 0) AS failureCount,
        IFNULL(a.CPU_TIME_MS, 0) AS cpuTimeMs,
        IFNULL(a.ELAPSED_TIME_MS, 0) AS elapsedTimeMs,
        IFNULL(1000*a.MEMORY_BS, 0)/IFNULL(a.ELAPSED_TIME_MS, 0) AS avg_memory_used_b
    FROM
        information_schema.mv_activities_cumulative a
        LEFT JOIN information_schema.mv_queries q
        ON a.ACTIVITY_NAME = q.ACTIVITY_NAME
   where activitytype = 'Query';

The easiest way would be to use “Historical Workload Monitoring” grafana dashboard we recently launched to get this information.

7

Responded in the original thread CPU Usage in terms of load average and utilization percentage on nodes; feel free to reach out if you require additional clarifications