Skip to content

Instantly share code, notes, and snippets.

@tom-clickhouse
Created July 31, 2023 11:55
Show Gist options
  • Save tom-clickhouse/dfe8db5a241473dd450a33c1478ffff7 to your computer and use it in GitHub Desktop.
Save tom-clickhouse/dfe8db5a241473dd450a33c1478ffff7 to your computer and use it in GitHub Desktop.
cpu.sql
WITH
'default' AS db_name,
'upclick_metrics' AS table_name,
10 AS interval_seconds,
(SELECT uuid FROM system.tables WHERE database = db_name and name = table_name) AS table_id,
(SELECT min(event_time) FROM system.part_log WHERE table_uuid = table_id) AS start_time
SELECT
toStartOfInterval(event_time, toIntervalSecond(interval_seconds)) AS t,
dateDiff('second', toStartOfInterval(start_time, toIntervalSecond(interval_seconds)), t) AS seconds,
avg(metric) / 1000000 as cpu_usage
FROM (
SELECT event_time, sum(ProfileEvent_OSCPUVirtualTimeMicroseconds) AS metric
FROM clusterAllReplicas(default, system.metric_log)
WHERE event_time >= start_time
GROUP BY event_time)
GROUP BY t
ORDER BY t DESC
SETTINGS skip_unavailable_shards = 1
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment