Looking for info to find out which table is used the most, or which query is the slowest

Help Workload Profiling
1

I am using Managed Service and dived through the docs. I found a lot of mentioning of tables in the information_schema database that holds all kind of information about the cluster, databases, queries etc.

But I wish I could find simple queries I can run to see:

  • what is the top 10 most used queries
  • what is the top 10 slowest queries
  • etc.

Would be great if I got pointers where this data is, how to query it.

1 Like
2

Hi Stefandevo!

Since you’re on the managed service, feel free to contact :rescue_worker_helmet: support. :rescue_worker_helmet:They are happy to answer all of your questions and can offer more details.

Best Regards. :relaxed:

3

In the Monitoring dropdown, select Workload Monitoring. You’ll be prompted to begin profiling. The information on running queries and behavior can then be seen in the Activities and Nodes tabs. The explanation of what you see on the Activities and Nodes tabs is explained here in the doc: Troubleshooting Poorly Performing Queries · SingleStore Documentation.
Finally, if you want to run your own active session history (ASH) report against the information_schema, here’s that query for the top 10 most used queries:

use information_schema;
SELECT
(run_count+success_count+failure_count) AS total_executions,
substr(replace(replace(query_text,‘\n’,‘’), ’ ‘,’ '),1,30) as query_text,
mv_queries.activity_name,
database_name,
(elapsed_time_ms/(run_count+success_count+failure_count)) AS avg_elapsed_time_ms,
last_finished_timestamp AS last_run,
(cpu_time_ms/(run_count+success_count+failure_count)) as avg_cpu_time_ms,
(cpu_wait_time_ms/(run_count+success_count+failure_count)) AS avg_cpu_wait_time_ms,
(lock_time_ms/(run_count+success_count+failure_count)) AS avg_lock_time_ms,
(network_time_ms/(run_count+success_count+failure_count)) AS avg_network_time_ms,
(disk_time_ms/(run_count+success_count+failure_count)) AS avg_disk_time_ms,
(round((disk_b/1024/1024),2)/(run_count+success_count+failure_count)) AS avg_io_mb,
(round((network_b/1024/1024),2)/(run_count+success_count+failure_count)) AS avg_network_mb,
round((1000*(memory_bs/1024/1024)/(elapsed_time_ms)),2) AS avg_memory_mb,
(memory_major_faults/(run_count+success_count+failure_count)) AS avg_major_faults
FROM information_schema.mv_activities_cumulative JOIN information_schema.mv_queries
ON mv_queries.activity_name = mv_activities_cumulative.activity_name
WHERE activity_type = ‘Query’
and database_name = ‘s2_dataset_tpch’
and last_finished_timestamp <= now() and last_finished_timestamp >= (now() - interval 10 minute)
order by
total_executions desc
limit 10

1 Like