Add more dashboards about clickhouse

[serxa]

Here is a template for all types of charts that are now available to be built from system tables using new features:

CurrentMetric (system.metric_log)

Single line

SELECT
  toStartOfInterval(event_time, INTERVAL {rounding:UInt32} SECOND)::INT AS t,
  avg(metric)
FROM (
  SELECT event_time, sum(CurrentMetric_Query) AS metric
  FROM clusterAllReplicas(default, merge('system', '^metric_log'))
  WHERE event_date >= toDate(now() - {seconds:UInt32})
    AND event_time >= now() - {seconds:UInt32}
  GROUP BY event_time)
GROUP BY t
ORDER BY t WITH FILL STEP {rounding:UInt32} SETTINGS skip_unavailable_shards = 1

Two-lines

SELECT
  toStartOfInterval(event_time, INTERVAL {rounding:UInt32} SECOND)::INT AS t,
  avg(SystemReplicasThreads),
  avg(SystemReplicasThreadsActive)
FROM (
  SELECT event_time,
    sum(CurrentMetric_SystemReplicasThreads) AS SystemReplicasThreads,
    sum(CurrentMetric_SystemReplicasThreadsActive) AS SystemReplicasThreadsActive
  FROM clusterAllReplicas(default, merge('system', '^metric_log'))
  WHERE event_date >= toDate(now() - {seconds:UInt32})
    AND event_time >= now() - {seconds:UInt32}
  GROUP BY event_time)
GROUP BY t
ORDER BY t SETTINGS skip_unavailable_shards = 1

Single line per host (double group by is not required)

SELECT
  toStartOfInterval(event_time, INTERVAL {rounding:UInt32} SECOND)::INT AS t,
  hostname() as label,
  avg(CurrentMetric_SystemReplicasThreadsActive)
FROM clusterAllReplicas(default, merge('system', '^metric_log'))
  WHERE event_date >= toDate(now() - {seconds:UInt32})
    AND event_time >= now() - {seconds:UInt32}
GROUP BY t, label
ORDER BY t, label SETTINGS skip_unavailable_shards = 1

ProfileEvent (system.metric_log)

Single line

SELECT
  toStartOfInterval(event_time, INTERVAL {rounding:UInt32} SECOND)::INT AS t,
  avg(metric)
FROM (
  SELECT event_time, sum(ProfileEvent_Query) AS metric
  FROM clusterAllReplicas(default, merge('system', '^metric_log'))
  WHERE event_date >= toDate(now() - {seconds:UInt32})
    AND event_time >= now() - {seconds:UInt32}
  GROUP BY event_time)
GROUP BY t
ORDER BY t WITH FILL STEP {rounding:UInt32} SETTINGS skip_unavailable_shards = 1

Two-line

SELECT
  toStartOfInterval(event_time, INTERVAL {rounding:UInt32} SECOND)::INT AS t,
  avg(SelectQuery),
  avg(InsertQuery)
FROM (
  SELECT event_time,
    sum(ProfileEvent_SelectQuery) AS SelectQuery,
    sum(ProfileEvent_InsertQuery) AS InsertQuery
  FROM clusterAllReplicas(default, merge('system', '^metric_log'))
  WHERE event_date >= toDate(now() - {seconds:UInt32})
    AND event_time >= now() - {seconds:UInt32}
  GROUP BY event_time)
GROUP BY t
ORDER BY t SETTINGS skip_unavailable_shards = 1

Single line per host (double group by is required: first sum on second scale, then average over rounding interval)

SELECT
  toStartOfInterval(event_time, INTERVAL {rounding:UInt32} SECOND)::INT AS t,
  label,
  avg(metric)
FROM (
  SELECT event_time,
    hostname() as label,
    sum(ProfileEvent_Query) AS metric
  FROM clusterAllReplicas(default, merge('system', '^metric_log'))
  WHERE event_date >= toDate(now() - {seconds:UInt32})
    AND event_time >= now() - {seconds:UInt32}
  GROUP BY event_time, label)
GROUP BY t, label
ORDER BY t, label SETTINGS skip_unavailable_shards = 1

AsynchronousMetrics (system.asynchronous_metric_log)

Single line

SELECT
  toStartOfInterval(event_time, INTERVAL {rounding:UInt32} SECOND)::INT AS t,
  avg(value)
FROM
  clusterAllReplicas(default, merge('system', '^asynchronous_metric_log'))
WHERE event_date >= toDate(now() - {seconds:UInt32})
  AND event_time >= now() - {seconds:UInt32}
  AND metric = 'OSSystemTimeNormalized'
GROUP BY t
ORDER BY t WITH FILL STEP {rounding:UInt32} SETTINGS skip_unavailable_shards = 1

Multiple metrics

SELECT
  toStartOfInterval(event_time, INTERVAL {rounding:UInt32} SECOND)::INT AS t,
  metric as label,
  avg(value)
FROM
  clusterAllReplicas(default, merge('system', '^asynchronous_metric_log'))
WHERE event_date >= toDate(now() - {seconds:UInt32})
  AND event_time >= now() - {seconds:UInt32}
  AND metric in ['OSSystemTimeNormalized', 'OSUserTimeNormalized', 'OSIrqTimeNormalized']
GROUP BY t, label
ORDER BY t, label SETTINGS skip_unavailable_shards = 1

Single line per host (double group by is not required)

SELECT
  toStartOfInterval(event_time, INTERVAL {rounding:UInt32} SECOND)::INT AS t,
  hostname() as label,
  avg(value)
FROM
  clusterAllReplicas(default, merge('system', '^asynchronous_metric_log'))
WHERE event_date >= toDate(now() - {seconds:UInt32})
  AND event_time >= now() - {seconds:UInt32}
  AND metric = 'OSUserTimeNormalized'
GROUP BY t, label
ORDER BY t, label SETTINGS skip_unavailable_shards = 1

Read/Write on the same chart (I'm not sure if this one is actually a good idea)

SELECT
  toStartOfInterval(event_time, INTERVAL {rounding:UInt32} SECOND)::INT AS t,
  label,
  avg(if(label='NetworkReceiveBytes', value, -value))
FROM (
  SELECT event_time,
    sum(value) AS value,
    if(metric LIKE 'NetworkReceiveBytes%', 'NetworkReceiveBytes', 'NetworkSendBytes') as label
  FROM clusterAllReplicas(default, merge('system', '^asynchronous_metric_log'))
  WHERE event_date >= toDate(now() - {seconds:UInt32})
    AND event_time >= now() - {seconds:UInt32}
    AND (metric LIKE 'NetworkReceiveBytes%' OR metric LIKE 'NetworkSendBytes%')
  GROUP BY event_time, label)
GROUP BY t, label
ORDER BY t, label SETTINGS skip_unavailable_shards = 1